Using a combo box to find a record

J

JeanetteJH

I have a inventory details form that has 2 unbound combos on it. The first
combo is for the supplier and the second one if for the part number. The
part number is filtered by the supplier so that the only parts that show up
in this combo is the part numbers that that supplier supplies to us. The
rest of the form contains details about the part. I would like to be able to
go to the record where my InventoryID (bound to table) is equal to my
cboItemId after it is filtered. I have used the following code (Thanks to
Allen Browne):
Sub CboItemID_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboItemID) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
Debug.Print "[InventoryID]=""& Me.cboItemID"""

rs.FindFirst "[InventoryID] = "" & Me.cboItemID"""
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The only thing that I get is the MsgBox "Not found: filtered?"
Any help will be greatly appreciated.

Jeanette
 
K

Ken Snell \(MVP\)

Is InventoryID a text field (not a numeric field)? The code you're using
assumes it's a numeric field.

If it's a text field:

rs.FindFirst "[InventoryID] = '"" & Me.cboItemID & ""'"
 
J

JeanetteJH

It is a text field and I copied your code, but it still does the same thing.
Is it because the field is filtered based on another field?
Thanks for your help.

Jeanette


Ken Snell (MVP) said:
Is InventoryID a text field (not a numeric field)? The code you're using
assumes it's a numeric field.

If it's a text field:

rs.FindFirst "[InventoryID] = '"" & Me.cboItemID & ""'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



JeanetteJH said:
I have a inventory details form that has 2 unbound combos on it. The first
combo is for the supplier and the second one if for the part number. The
part number is filtered by the supplier so that the only parts that show
up
in this combo is the part numbers that that supplier supplies to us. The
rest of the form contains details about the part. I would like to be able
to
go to the record where my InventoryID (bound to table) is equal to my
cboItemId after it is filtered. I have used the following code (Thanks to
Allen Browne):
Sub CboItemID_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboItemID) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
Debug.Print "[InventoryID]=""& Me.cboItemID"""

rs.FindFirst "[InventoryID] = "" & Me.cboItemID"""
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The only thing that I get is the MsgBox "Not found: filtered?"
Any help will be greatly appreciated.

Jeanette
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Sorry... try this:

rs.FindFirst "[InventoryID] = '" & Me.cboItemID & "'"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


JeanetteJH said:
It is a text field and I copied your code, but it still does the same
thing.
Is it because the field is filtered based on another field?
Thanks for your help.

Jeanette


Ken Snell (MVP) said:
Is InventoryID a text field (not a numeric field)? The code you're using
assumes it's a numeric field.

If it's a text field:

rs.FindFirst "[InventoryID] = '"" & Me.cboItemID & ""'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



JeanetteJH said:
I have a inventory details form that has 2 unbound combos on it. The
first
combo is for the supplier and the second one if for the part number.
The
part number is filtered by the supplier so that the only parts that
show
up
in this combo is the part numbers that that supplier supplies to us.
The
rest of the form contains details about the part. I would like to be
able
to
go to the record where my InventoryID (bound to table) is equal to my
cboItemId after it is filtered. I have used the following code (Thanks
to
Allen Browne):
Sub CboItemID_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboItemID) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
Debug.Print "[InventoryID]=""& Me.cboItemID"""

rs.FindFirst "[InventoryID] = "" & Me.cboItemID"""
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The only thing that I get is the MsgBox "Not found: filtered?"
Any help will be greatly appreciated.

Jeanette
Click to expand...
Click to expand...
Click to expand...
 
J

JeanetteJH

That did the trick - Thank you very much!

Jeanette

Ken Snell (MVP) said:
Sorry... try this:

rs.FindFirst "[InventoryID] = '" & Me.cboItemID & "'"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


JeanetteJH said:
It is a text field and I copied your code, but it still does the same
thing.
Is it because the field is filtered based on another field?
Thanks for your help.

Jeanette


Ken Snell (MVP) said:
Is InventoryID a text field (not a numeric field)? The code you're using
assumes it's a numeric field.

If it's a text field:

rs.FindFirst "[InventoryID] = '"" & Me.cboItemID & ""'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a inventory details form that has 2 unbound combos on it. The
first
combo is for the supplier and the second one if for the part number.
The
part number is filtered by the supplier so that the only parts that
show
up
in this combo is the part numbers that that supplier supplies to us.
The
rest of the form contains details about the part. I would like to be
able
to
go to the record where my InventoryID (bound to table) is equal to my
cboItemId after it is filtered. I have used the following code (Thanks
to
Allen Browne):
Sub CboItemID_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboItemID) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
Debug.Print "[InventoryID]=""& Me.cboItemID"""

rs.FindFirst "[InventoryID] = "" & Me.cboItemID"""
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The only thing that I get is the MsgBox "Not found: filtered?"
Any help will be greatly appreciated.

Jeanette
Click to expand...
Click to expand...
Click to expand...
 
J

JeanetteJH

I just have one other question - Is there a way to sort the filtered data?
Thanks for any assistance.

Jeanette

JeanetteJH said:
That did the trick - Thank you very much!

Jeanette

Ken Snell (MVP) said:
Sorry... try this:

rs.FindFirst "[InventoryID] = '" & Me.cboItemID & "'"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


JeanetteJH said:
It is a text field and I copied your code, but it still does the same
thing.
Is it because the field is filtered based on another field?
Thanks for your help.

Jeanette


:

Is InventoryID a text field (not a numeric field)? The code you're using
assumes it's a numeric field.

If it's a text field:

rs.FindFirst "[InventoryID] = '"" & Me.cboItemID & ""'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a inventory details form that has 2 unbound combos on it. The
first
combo is for the supplier and the second one if for the part number.
The
part number is filtered by the supplier so that the only parts that
show
up
in this combo is the part numbers that that supplier supplies to us.
The
rest of the form contains details about the part. I would like to be
able
to
go to the record where my InventoryID (bound to table) is equal to my
cboItemId after it is filtered. I have used the following code (Thanks
to
Allen Browne):
Sub CboItemID_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboItemID) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
Debug.Print "[InventoryID]=""& Me.cboItemID"""

rs.FindFirst "[InventoryID] = "" & Me.cboItemID"""
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The only thing that I get is the MsgBox "Not found: filtered?"
Any help will be greatly appreciated.

Jeanette
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

More details please...sort which data? he data in the form?

If yes, check out the OrderBy and OrderByOn properties for the form. You can
find information about them in VBA Help.

Post back if you have questions.
 
J

JeanetteJH

Thanks - I was making it harder than it needed to be. I just changed the
sort properties in the query the control was based on.

Jeanette
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clearing combo boxes and a text box 2
Creating a Search using a combo box and then creating a report. 4
Word macro that can call cells out of Excel for a find and replace 0
Dynamic Naming of Form Button 3
Error 3201 1
Combo box doesn't search last names with apostrophe 2
help needed with a combo box 7
Problem with Combo Box FindNext 10

Top