Saving forms with stored procedures?

In Access, we can make a project file which connect to a SQL Server.
So we can inhirit all features of SQL Server.

When using project file, we can run a store procedure by:
CurrentProject.Connection.Execute "EXEC storeprocedure"
or any action query:
CurrentProject.Connection.Execute "INSERT|UPDATE|DELETE clause"

We also use a ADODB Recordset to get data from a table or a view:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.LockType = ...
.CursorType = ...
.Open stSQL
End With

Here is some words of mine.
 
I've had some success but I've had to change the way I think about my forms to fit more inline with the Access way of doing things. I'm still having problems though - now using stored procedures that have parameters on my form. I have one that populates the form itself, and then another procedure that is populating a combo box on the form. I had to set the recordsource for the combo box in VBA in order to get the parameters passed in correctly - but it doesn't seem to fire properly when I try to add a new row. Very frustrating!

I'm also having problems with the "unique table" property. Sometimes in the properties sheet the Unique Table dropdown has a table populated that I can select - sometimes there's nothing there and I have to set it in VBA. Now I'm getting an error even when I try to do that! And I think without that property set, I can't save data back from my form. I'm populating the form with a stored procedure.
 
Also - one more question: How would someone accomplish this?

I have fields in my table that represent percentages - they are decimal data types in SQL Server. When I display them on my forms though, I them shown *100. In other words in the database the number would be stored as .5 but I want it shown as 50% in the form. And when they edit it, it has to divide back by 100. I can't seem to get this to work as Access isn't even allowing me to enter 50 into the textbox!

Thanks!
 
Put an unbound text box on the form and hide the original one. In the AfterUpdate event of the unbound text box put:

Code:
If Me.YourUnBoundTextBoxNameHere <= 1 Then
   Me.YourBoundTextBoxNameHere = Me.YourUnBoundTextBoxNameHere
Else
   Me.YourBoundTextBoxNameHere = Me.YourUnBoundTextBoxNameHere / 100
End If
 
Bob - this solution looks good but would it work on a continous form? I'm not sure how to code control references in this case. I'm doing something similar in a form that looks like this:

Percent Amount
------- --------
1 45
.5 70
.5 80


When someone changes something in the percent column, I want to write code that updates the amount column (amount is unbound in this case). If I use the syntax you wrote, it updates all the amount records on the form. I just want to update the one in the current record. Is this possible?
 
If I use the syntax you wrote, it updates all the amount records on the form. I just want to update the one in the current record. Is this possible?

It's not updating all of the records, but because the one control is unbound it can actually only have one value at a particular time. To solve that on a continuous form you'll probably have to create another field in your table and actually bind it to the field (normally not desired, but sometimes you have to say, "The laws of cause and effect...who gives a smeg?")
 
It's not updating all of the records, but because the one control is unbound it can actually only have one value at a particular time. To solve that on a continuous form you'll probably have to create another field in your table and actually bind it to the field (normally not desired, but sometimes you have to say, "The laws of cause and effect...who gives a smeg?")


This is terrible. I'm quickly learning that continuous forms are a major pain in the ass!

Does this mean that there is no way in Access to single out a specific control in a specific record on a continous form? There must be something like Me.cmdAmount(2).value = 50 ?
 
i might be missing something here (again) but try this on the *bound* control:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.BoundControl > 0 And Me.BoundControl < 101 Then
        Me.BoundControl = (Me.BoundControl) / 100
    Else
        MsgBox "bad number"
        Cancel = True
        Me.Undo
    End If

End Sub
with this code the user can type an Int directly into the control. also, there is no need to show the value initially as (value*100); you can show it "as is" (Dec), but, you could format the form-control to Percent if you want users to see (value*100), or not. both seem to work for me.
(i'm not using SQL Server, so i have to hope this will work for you but i did define the field as decimal and am using a continuous form).
 
Last edited:

Users who are viewing this thread

Back
Top Bottom