Combobox to go to record question

[BrettS]

Hi,

I've been using the common VBA code for using a combobox to go to a
particular record, I believe it may have been written by one of the MVPs
here. The code is:

Private Sub Filtercategory_AfterUpdate()
On Error GoTo err_Handler
Dim rs As DAO.Recordset

If Not IsNull(Me.Filtercategory) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[File Category] = """ & Me.Filtercategory & """"
If rs.NoMatch Then
MsgBox "Not found: Try Another Record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.Filtercategory = ""

Me.Refresh

exit_Here:
Exit Sub

err_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "***ERROR***"
End Sub


I was wondering if I could use a combobox with two columns and an SQL query
showing two fields from the table to filter for two fields at once? I'm
guessing I'd need to change this line:

rs.FindFirst "[File Category] = """ & Me.Filtercategory & """"

But I'm not familiar enough with VBA to know the syntax or if that is
possible. The second field is called [File ID #].

Thank you in advance for any help,
Brett

 

[S.Clark]

You would need to alter:
rs.FindFirst "[File Category] = """ & Me.Filtercategory & """"

to use multiple fields:
rs.FindFirst "[File Category] = """ & Me.Filtercategory & "" AND [File#] =
'" & cboFileNum & "' "

Of course, you'll need to make provisions for things like:
-Dependencies
-One value being entered vs. two values

but hopefully you get the gist.

 

[BrettS]

Thanks for the response, this is what I was looking for, however I'm still
have some problems, I have changed the line to

rs.FindFirst "[File Category] = """ & Me.Filtercategory & "" AND [File ID #]
= '" & Me.Filtercategory & "'"

But it keeps giving me a syntax error when I try to use the combobox. I
have also tried using Me.Filtercategory.Column(0) and
Me.Filtercategory.Column(1) in the appropriate spots like this:

rs.FindFirst "[File Category] = """ & Me.Filtercategory.Column(0) & "" AND
[File ID #] = '" & Me.Filtercategory.Column(1) & "'"

but they still give syntax errors as well. I feel like adding the column
specification would be necessary to make this work as well, correct?

-Brett

You would need to alter:
rs.FindFirst "[File Category] = """ & Me.Filtercategory & """"

to use multiple fields:
rs.FindFirst "[File Category] = """ & Me.Filtercategory & "" AND [File#] =
'" & cboFileNum & "' "

Of course, you'll need to make provisions for things like:
-Dependencies
-One value being entered vs. two values

but hopefully you get the gist.

Hi,

I've been using the common VBA code for using a combobox to go to a
particular record, I believe it may have been written by one of the MVPs
here. The code is:

Private Sub Filtercategory_AfterUpdate()
On Error GoTo err_Handler
Dim rs As DAO.Recordset

If Not IsNull(Me.Filtercategory) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[File Category] = """ & Me.Filtercategory & """"
If rs.NoMatch Then
MsgBox "Not found: Try Another Record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.Filtercategory = ""

Me.Refresh

exit_Here:
Exit Sub

err_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "***ERROR***"
End Sub


I was wondering if I could use a combobox with two columns and an SQL query
showing two fields from the table to filter for two fields at once? I'm
guessing I'd need to change this line:

rs.FindFirst "[File Category] = """ & Me.Filtercategory & """"

But I'm not familiar enough with VBA to know the syntax or if that is
possible. The second field is called [File ID #].

Thank you in advance for any help,
Brett