VBA Update syntax

[it2hill]

Alright I have 2 tables Members and TestScores. I have a cmdButton on the members form that opens the test scores form and populates the form with the member information from the members form. The txt boxes on test scores form are not bound to the members or test scores tables. I can capture the information from the form, but can not figure out how to write the update sql in VBA. I know I have to create a RS and so forth.. Thanks so much

 

[scriverb]

Here is the basic code for updating a recordset.

Dim db as DAO.database
Dim rs as DAO.recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("TestScores", dbOpenDynaset)
'Numeric field FindFirst(Use Only one)
rs.FindFirst "[MemberID] = " & Me![MemberID]
'Text field FindFirst{Use Only one)
rs.FindFirst "[MemberID] = '" & Me![MemberID] & "'"
If Not rs.NoMatch then
rs.edit
rs("Scores1&quot = me![Scores1]
'. . .as many as necessary!!!
rs.update
else
'do nothing no record found
end if
rs.close
Set rs = db.OpenRecordset("Members", dbOpenDynaset)
rs.FindFirst "[MemberID] = " & me![MemberID]
If Not rs.NoMatch then
rs.edit
rs("Field1&quot = me![DataField1]
' . . .as many as needed
rs.update
else
'. . .Do nothing no record found
end if
rs.close
db.close

You will have to modify this to fit your fieldnames and structure etc. This code will work to perform updating of records in VBA code. You must find the correct record first based on data from your form. I have provided two lines of code depending if the lookup field is Numeric or Text. Use one only. The Text version surrounds the field with single quotes to set the data off as a string for comparison purposed.

Good luck.



Bob Scriver

 

[it2hill]

First off thanks for the quick response. It works.. here is the problem. I am not searching for a test in the test table (PRT is the name). So I just want to add a new test score from this form everytime. right now the test scores are being assigned to the last record in the RS everytime, I was not sure how to create a new... I thought addNew would, but it is not.

Here is my codePrivate Sub cmdUpdate_Click()

Dim db As DAO.Database
Dim rsPRT As DAO.Recordset

Set db = CurrentDb
Set rsPRT = db.OpenRecordset("PRT", dbOpenDynaset)
'I tried movelast here as well
rsPRT.AddNew
rsPRT.Edit
rsPRT("SSN&quot = Me![SSN]
rsPRT("Date&quot = Me![Date]
rsPRT("Pass_y_n&quot = Me![Pass_y_n]
rsPRT("RunTime&quot = Me![RunTime]
rsPRT("CurlUps&quot = Me![CurlUps]
rsPRT("PushUps&quot = Me![PushUps]
rsPRT("Sit_and_Reach&quot = Me![Sit_and_Reach]
rsPRT.Update

rsPRT.Close
db.Close

End Sub


Again, thanks for the first response. It got me moving!!

Shane

 

[scriverb]

If you are sure that your record to be edited is the last record in the recordset then you just need to do a rs.MoveLast. This requires the record to already be created by another process. When you do this you are going to edit the existing record so the rs.AddNew doesn't apply. If you wanted to Add a new record then drop the rs.MoveLast and use the rs.AddNew. They both then use the rs.Update to finish the processes.

Dim db As DAO.Database
Dim rsPRT As DAO.Recordset

Set db = CurrentDb
Set rsPRT = db.OpenRecordset("PRT", dbOpenDynaset)
'I tried movelast here as well
rsPRT.MoveLast
rsPRT.Edit
rsPRT("SSN&quot = Me![SSN]
rsPRT("Date&quot = Me![Date]
rsPRT("Pass_y_n&quot = Me![Pass_y_n]
rsPRT("RunTime&quot = Me![RunTime]
rsPRT("CurlUps&quot = Me![CurlUps]
rsPRT("PushUps&quot = Me![PushUps]
rsPRT("Sit_and_Reach&quot = Me![Sit_and_Reach]
rsPRT.Update

rsPRT.Close
db.Close

Let me know if this gets you going in the right direction. If not please be more specific as to how the record is being created in the recordset and I can help you more.
Bob Scriver