Update records with VBA

[2077delta]

I have a form with a subform on it. The sub form uses two of the fields on the main form to provide criteria to the subform's underlying query. The subform returns 12 records, one for each month of the year. The final field in subform's query is a calculated field (calculates a new price). I put a button on the main form and attached some code to let it udate the price table with the newly calculated prices from the subform. The main form is named frmSFPPriceMain, the subform is frmSFPPriceSub, and I want to update the [CalculatedPrice] field from the subform to a field named FinalPrice in a table called tblUPCPrices. Here is the code

Sub UpdateSFPPrices()

On Error GoTo UpdateSFPPrices_err
Dim dbs As Database, rst As Recordset
' Return Database variable pointing to current database
Set dbs = CurrentDb
Set rst = dbs.openRecordset("SELECT * FROM tblUPCPrices WHERE (([ProductID]=[FORMS]![frmSPFPriceMain].[ProductID]) AND ([Year]=[FORMS]![frmSPFPriceMain].[PriceYear]));")
With rst
' Edit the Recordset object
.Edit
'Edit the field
![FinalPrice] = Forms![frmSFPPriceSub].[CalculatedPrice]
.Update 'Save Changes
End With
dbs.Close

UpdateSFPPrices_Exit:
Exit Sub

UpdateSFPPrices_err:
MsgBox Err.Description
Resume UpdateSFPPrices_Exit

End Sub

I get an error when it hits the "Edit" line in the code. The error is "Method or Data Member not Found". I thought I had to use the "Edit" method to modify the Recordset. Finally will this update all the records in the Recordset or just the current record? If just the current Recordset, how do I get it to update all the records?

 

[SydneyGeek]

Check to see whether this is an editable Recordset:
1. Open the underlying query for the subform and see whether the New Record (*) symbol is black or dimmed. If dimmed, any attempt to change the recordset will fail. You may need to alter teh query to make it editable.

To answer the second question, you may find it easier to include the [FinalPrice] and [CalculatedPrice] fields in the same query. Then when you open the recordset you can do something like:

With rst
  Do Until .EOF
    .MoveFirst
    .Edit
    ![FinalPrice]=![CalculatedPrice]
    .Update
    .MoveNext
  Loop
End With

HTH

Denis

 

[2077delta]

Thanks for the help. Here is how my code looks now.

Sub UpdateSFPPrices()

On Error GoTo UpdateSFPPrices_err
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
' Return Database variable pointing to current database
Set dbs = CurrentDb
Set rst = Me.Recordset
With rst
.MoveFirst
Do Until .EOF
.Edit
![FinalPrice] = ![Calculated Price]
.Update
.MoveNext
Loop
End With
dbs.Close

UpdateSFPPrices_Exit:
Exit Sub

UpdateSFPPrices_err:
MsgBox Err.Description
Resume UpdateSFPPrices_Exit

End Sub

I moved the .movefirst method outside of the loop statement because it was causing an infinite loop as the routine was always going to the first record of recordset. I have one more question. I was attempting to insert a message box to alert the user the price table had been updated. Unfortunately depending on where I put it, the message either appeared three times or not at all. Could you let me know how to do this and why if I put it right after the dbs.Close statement it gives me the message three times?

 

[SydneyGeek]

Yep, sorry about the .MoveFirst blooper. I wasn't in front of Access when I wrote the code, and I have done to to myself more than once...
I would have thought that this construct ...

With rst 
.MoveFirst 
Do Until .EOF 
.Edit 
![FinalPrice] = ![Calculated Price] 
.Update 
.MoveNext 
Loop 
End With 
MsgBox "Your records are now updated"
dbs.Close

...would do the job. Maybe you could try

Loop 
End With 
MsgBox "Your records are now updated"
Set rst=Nothing
dbs.Close

HTH

Denis

 

[2077delta]

Thanks for the reply. I had already tried what you suggested. For some reason, the message box appears three times i.e. you cancel the first and another one pops up, you canel that one and a third one pops up. I have a question on locking records in a form. Is there a way to populate the form from a query, allow the user to edit fields without modifying the underlying table(s) and then hit a button to update the desired fields in the table? If you know or can point me to some reference about this, I would greatly appreciate it.

Thanks again.

 

[SydneyGeek]

Not sure why the triple pop-up. Did you put in the Set rst=Nothing line?
As for the form locking, I don't know how to edit bound controls without modifying the underlying code. If you are having record locking problems, how many users do you have? Is the database split into Front end (interface) ? Back end (data)? And which version of Access are you running? All of these are relevant -- try a search for Record Locking. There are 2 kinds: Optimistic and Pessimistic, which lock different numbers of records. Teh other issue is the Access 97 locks a bigger "Page" (ie more records) by default then the later veriosn, if my memory serves me correctly.
Other possible resources are http://www.mcwtech.com and http://www.mvps.org
The first is the home of 2 of the Access Developer's Handbook guys (Litwin, Getz, Gilbert). The second hosts The Access Web by Dev Ashish. Rod Stephens also has some useful stuff on his site.

HTH

Denis