ComboBox Form Update

[Dogberry]

Hi
I have very limited VBA knowledge. With the following code it works perfectly when clicking a search button but comes up as "No Match" when I run it as a combobox value change. Any ideas please

Search

Private Sub cmdSearch_Click()
Dim bkmk As Variant
Dim strField As String
Me.RecordsetClone.MoveFirst
'Find the first record that matches what
'is in the search text box.
Me.RecordsetClone.FindFirst "Variety Like" _
& Chr(34) & Me.txtSearch & "*" & Chr(34)

If Me.RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
bkmk = Me.RecordsetClone.Bookmark
Me.Recordset.Bookmark = bkmk
End If
End Sub

Change

Private Sub cboVariety_Change()
Dim bkmk As Variant
Dim strField As String
Me.RecordsetClone.MoveFirst
'Find the first record that matches what
'is in the search text box.
Me.RecordsetClone.FindFirst "Variety Like" _
& Chr(34) & Me.cboVariety & "*" & Chr(34)

If Me.RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
bkmk = Me.RecordsetClone.Bookmark
Me.Recordset.Bookmark = bkmk
End If


End Sub

 

[arnelgp]

you instanciate a Recordset object and use that, because whenever you user Me.Recordset.Clone, all the state of the Clone is lost, including the bookmark and .Nomatch.
instead use this:

Private Sub cmdSearch_Click()
Dim bkmk As Variant
Dim strField As String
Dim rs As Dao.Recordset

set rs = Me.RecordsetClone
With rs
.MoveFirst
'Find the first record that matches what
'is in the search text box.
.FindFirst "Variety Like " _
& Chr(34) & Me.txtSearch & "*" & Chr(34)

If .NoMatch Then
MsgBox "No Match"
Else
bkmk = .Bookmark
Me.Bookmark = bkmk
End If
.Close
End With
Set rs=nothing
End Sub


Private Sub cboVariety_Change()
Dim bkmk As Variant
Dim strField As String
Dim rs As Dao.Recordset

set rs = Me.RecordsetClone
With rs
.MoveFirst
'Find the first record that matches what
'is in the search text box.
.FindFirst "Variety Like " _
& Chr(34) & Me.cboVariety & "*" & Chr(34)

If .NoMatch Then
MsgBox "No Match"
Else
bkmk = .Bookmark
Me.Bookmark = bkmk
End If
.Close
End With
set rs=nothing

End Sub

 

[isladogs]

Did you see my reply to your previous post?

Use the After_Update event in your combo box to filter the output to the unique ID of the combobox output
Use the After_Update event instead of the Change event as the latter will run after each keystroke which you don't want

For example, you have two combos cboPlantType and cboVariety
In the after update event of each set a variable equal to the combo result

Private Sub cboPlantType_AfterUpdate

Dim strPlantType As String
strPlantType=Me.cboPlantType

End Sub

NOTE - if Dogwood or whatever isn't the bound field this will need adapting to include the column number with zero as the first column
e.g. strPlantType=Me.cboPlantType.Column(2)

Private Sub cboVariety_AfterUpdate

Dim strVariety As String
strVariety=Me.cboVariety

Me.RecordSource=DLookup("ID","tblPlantDetails","PlantType= '" & strPlantType & "' And Variety = '" & strVariety & "'")

'following line refreshes the display but may not be needed
Me.Requery

End Sub

Obviously change all names to fit your database
HTH