Lookup combo box problem

[Skip Bisconer]

My object here is to find a record in a table then update two fields in the tabl, e RouteID and Seqence number. I need the RouteID to retain the value of PegRoute until the value of PegRoute changes. I thought I had this worked out before with the help of this forum but when I put it out to the field it didn’t function properly.
I am using an unbound Combo box to find a record on a form with the table as a row source. I am having a problem using the following code in that it doesn’t find the record until I return to the field a second time after the lookup. I need the table record to come up when the combo box is loaded.
Private Sub Combo42_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[OEOO_ORDR] = " & Str(Nz(Me![Combo42], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
‘Have RouteID always retain the value of PegRoute
If Nz(Me.RouteID, vbNullString) = vbNullString Then
Me.RouteID = Me.PegRoute
Me.Requery
End If
End Sub

 

[RuralGuy]

Is the [OEOO_ORDR] field a numeric field? What version of Access are you using? Do you have a reference set to DAO? You are using ActiveX (ADO) but I'm more familiar with DAO.

 

[Skip Bisconer]

Yes OEOO_ORDR is numeric. I don't understand the DAO or ADO question. I don't believe I am using Active X here so maybe I'm using DAO.

 

[RuralGuy]

FYI...Me.Recordset.Clone is a property of an ActiveX (ADO) RecordSet so you are using ADO here. From a code window you can go to Tools>References to see what libraries you have loaded. You can run both ADO and DAO together without a problem if you specify the library.
ie: Dim rs As DAO.RecordSet

It would look something like this in DAO. You will need to make sure you have set a reference to the DAO library.

Private Sub Combo42_AfterUpdate()
' Find the record that matches the control.
   Me.RecordsetClone.FindFirst "[OEOO_ORDR] = " & Me.Combo42
   If Not Me.RecordsetClone.NoMatch Then
      Me.Bookmark = Me.RecordsetClone.Bookmark
   End If
   'Have RouteID always retain the value of PegRoute
   If Len(Me.RouteID & "") = 0 Then
      Me.RouteID = Me.PegRoute
   End If
End Sub

What did you plan to do if there is "NoMatch"?

 

[Skip Bisconer]

Thanks for your help RG,

When I apply this code I get a runtime error "Update or Cancel without AddNew or Edit" when I try to select an order from the combo box. It debugs at RouteID = PegRoute

Private Sub Combo44_AfterUpdate()
Dim rs As DAO.Recordset
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[OEOO_ORDR] = " & Me.Combo44
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
'Have RouteID always retain the value of PegRoute
If Len(Me.RouteID & "") = 0 Then
Me.RouteID = Me.PegRoute 'Debug stops here
End If
End Sub

 

[RuralGuy]

Do you have a query as the RecordSource of the form? Is it updateable?

 

[Skip Bisconer]

No, the the record source is the table.

 

[RuralGuy]

Aside from the fact that you should have a query of the table as your RecordSource is Me.PegRoute a control on your form? I see nothing in the code that should cause that error.

 

[Skip Bisconer]

PegRoute is an unbound textbox. I am trying to get the bound text box RouteID to always be the same as PegRoute.

 

[RuralGuy]

PegRoute is an unbound textbox. I am trying to get the bound text box RouteID to always be the same as PegRoute.

You are trying to get a bound control to be the same as an unbound control? What is giving PegRoute any value other then Null?

 

[Skip Bisconer]

the value of PegRoute is entered by the user. I believe you have solved my problem with your earlier input. I just had to walk away for a while and come back. I thought I had set up a requery on the combobox to eliminate orders already assigned and instead I requeried the form. This dropped all the info out of the bound controls therefore according to my process there was no data bound controls to update. I appreciate your valuable assistance and hope I will be more observant in the future.

 

[RuralGuy]

Sometimes all one needs is a sounding board to solve problems. Glad I could be of assistance.