VBA Save and update function

errant

Registered User.
Local time
Today, 06:32
Joined
Sep 1, 2012
Messages
16
Hi, I'm trying to make a VBA program to update and Save record, unfortunately, the following program only effect on the 1st record in the table.
This is the button function that I placed in a form with detail, can anyone can point out where is the problem?

Private Sub btItemSave_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim txtItemNo As String

***Set db = CurrentDb
***Set rst = db.OpenRecordset("ItemMaster", dbOpenDynaset)
***rst.Edit
***rst!UpdatedDate = Now()
***rst!Time = Now()
***rst!UpdatedBy = txtUserID 'Varient UserID
rst.Update
rst.Close
db.Close

On Error GoTo Err_btItemSave_Click

***DoCmd.RunCommand acCmdSaveRecord

Exit_btItemSave_Click:
Exit Sub
Err_btItemSave_Click:
***MsgBox Err.Description
***Resume Exit_btItemSave_Click
***
End Sub
 
You are only choosing the first record in the chosen table. You either need to use a query to find the particular record you wish to change or create a loop to go through a set
 
Thanks for your reply but don't understand, how should I write this program look like?
 
Create a query with all the records you wish to update, pass that in the openrecordset,
Create a loop like something like

Do While not rs.eof

rs!field = value
..update

rs.movenext
Loop

And then they'll update each one not just the first
 
Is it would be update all data in this field?
Actually I just want to update one record set which in my form, I tried to figure it out but no luck to make this work.
 
What isn't working?

How many records are you wanting to update?



Private Sub btItemSave_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim txtItemNo As String

Set db = CurrentDb

' Create a Query with just the records you wish to update
' Set rst = db.OpenRecordset("ItemMaster", dbOpenDynaset)

Set rst = db.OpenRecordset("NEWQUERY", dbOpenDynaset)

rst.Edit
rst.MoveFirst
Do While not rst.EOF


rst!UpdatedDate = Now()
rst!Time = Now()
rst!UpdatedBy = txtUserID 'Varient UserID
rst.Update
rst.MoveNext

Loop

rst.Close
db.Close

On Error GoTo Err_btItemSave_Click

DoCmd.RunCommand acCmdSaveRecord

Exit_btItemSave_Click:
Exit Sub
Err_btItemSave_Click:
MsgBox Err.Description
Resume Exit_btItemSave_Click

End Sub
 
Seriously unless you're doing this as a learning exercise you don't need code. You can create an UPDATE query for this and just simply execute to make the changes to the whole table.
 
It better say just want current record, is my existing program similar? Or this is totally different? Please help on this, it would be get much more if I can figure this out!
Anyway, I just want to make a save function and get update and update by use in these fields.
 
Well, you keep changing your mind. Initially you said you wanted it to update all the records on the form but now you're saying that you only want the current record amended. I don't understand what your request is because you also said in your original post that the code is working for the current record. Please make yourself clear.
 
It better say just one current data update with the save button. Sorry for this confusion!
 
Best way then would be to create an UPDATE Query with a Parameter value of the ID of the record you want to update, just use the FORMS!FormName!Field notation and use the DoCmd.Run in your button event.
 
I would simply bind the form to the table (using the Record Source property) and bind the controls to the fields (using the Control Source property). Access will handle all your saves and deletes.

If you're unsure what a bound form is I suggest you use the form wizard, select a table or query and walk through the process. Then look for the properties I mentioned and it should be clear.
 
You could also set the DEFAULT value of the control to Date(), Time() or Now() to pre fill the value.
 
You could also set the DEFAULT value of the control to Date(), Time() or Now() to pre fill the value.
Good point AlexHedley.

Also, looking at your first post why don't you combine the Date and Time (i.e. UpdatedDate and Time) into one field and use the Now() function to pre-fill it? Another thing is avoid using field names like Time, Date etc. These are reserved for Access and could cause problems in the future. Here's a pretty good list of them:

http://allenbrowne.com/AppIssueBadWord.html
 
Thanks Gentlemen, I'm trying to make different way that you guys suggested.
 

Users who are viewing this thread

Back
Top Bottom