VBA Newbie...Help with ADODB.Recordset

[sphere_monk]

I am using the following code to find a workorder record when the workorder ID is chosen in the cboWOID combo box. It finds workorders fine until I enter a new workorder. Then it can't find the new workorder. Can anyone pick out what I'm missing? Thanks!

Private Sub cboWOID_AfterUpdate()
MsgBox ("Running cboWOID_AfterUpdate")
Dim strCriteria As String
Dim WOtoFind As String
Dim rst As New ADODB.Recordset
Set rst = Me.Recordset

rst.MoveFirst

WOtoFind = Me.cboWOID
strCriteria = "WOID = " & Me!cboWOID

rst.Find strCriteria

If rst.EOF Then GoTo SetDefaults

MsgBox ("Found Matching Record")

Call ChangeColor(cboWOID)
Call DisableFields
GoTo Exit_cboWOID_AfterUpdate

SetDefaults:
MsgBox ("Did not find matching record")
MsgBox ("Moving to New Record")
rst.AddNew
Call EnableFields
Call NewWODefaults(cboWOID)
Call ChangeColor(cboWOID)
Me!txtDate.SetFocus

Exit_cboWOID_AfterUpdate:
Exit Sub

End Sub

 

[boblarson]

I may be wrong here, but since you are setting the ADO recordset to the form's recordset, you would need to have some code to requery the recordset so that the new information would show up. So, in the beginning of the code, before doing anything else, you might want a
Me.Requery

 

[Pat Hartman]

Is the problem that the new workorder has not yet been saved? To force Access to save the current record:

DoCmd.RunCommand acCmdSaveRecord

 

[sphere_monk]

Thank you both for your replies....I really appreciate it!

I tried Rst.requery. Once the new workorder ID is input, and then selected in the cboWOID combo box, the code below now finds the workorder and prints the msgbox"Found Workorder", but the fields on the form come up with "#Name?". If I then type in a new WO ID in the combo box, I get error # 3704 - "Operation is not allowed when object is closed" when trying to run rst.Requery.

As for the DoCmd.RunCommand acCmdSaveRecord, I have been updating the table with new workorders by tabbing through all the fields and letting access fire the Form.Afterupdate Event. This saves the record to the table the same as the DoCmd.RunCommand acCmdSaveRecord command would right? Or do I have to include this command in my code?

Thanks, again, for your help!

Private Sub cboWOID_AfterUpdate()
MsgBox ("Running cboWOID_AfterUpdate")
Dim strCriteria As String
Dim WOtoFind As String
Dim rst As New ADODB.Recordset
Set rst = Me.Recordset

rst.Requery
rst.MoveFirst

WOtoFind = Me.cboWOID
strCriteria = "WOID = " & Me!cboWOID

MsgBox ("Searching for WOID #" & WOtoFind)
rst.Find strCriteria

If rst.EOF Then GoTo SetDefaults

MsgBox ("Found Matching Record")

Call ChangeColor(cboWOID)
Call DisableFields
GoTo Exit_cboWOID_AfterUpdate

SetDefaults:
MsgBox ("Did not find matching record")
MsgBox ("Moving to New Record")
DoCmd.GoToRecord Record:=acNewRec
Call EnableFields
Call NewWODefaults(cboWOID)
Call ChangeColor(cboWOID)
Me!txtDate.SetFocus

Exit_cboWOID_AfterUpdate:
Exit Sub

End Sub

 

[boblarson]

You may not need to do it based on what Pat said. However, you missed where I had said to put the .requery. I didn't say to do it on the AfterUpdate ADO recordset. I meant to requery the form's recordset (Me.Requery), that is, if you are using a bound form for input.

 

[sphere_monk]

Excellent!!! That was it!

You're right, I didn't catch that I had to requery the form itself and not just the recordset. I removed the rst.requery from the cboWOID_AfterUpdate and put a Me.requery in the Form_AfterUpdate and Form_AfterInsert events. The cboWOID_AfterUpdate code now finds the new record and loads it into the form properly!

Thanks again for your help!