Search Records

[DonK]

I want to design a form to search spare parts records by description rather
than part number. That is, the user types in PUMP in a text box and the query
returns all records with the word PUMP in its description to a list box on
the same form.
Bearing in mind a description might read "DRAIN PUMP ASSY-MAYTAG MAV SERIES"
Any suggestions as to how I would go about this?

 

[John W. Vinson]

I want to design a form to search spare parts records by description rather
than part number. That is, the user types in PUMP in a text box and the query
returns all records with the word PUMP in its description to a list box on
the same form.
Bearing in mind a description might read "DRAIN PUMP ASSY-MAYTAG MAV SERIES"
Any suggestions as to how I would go about this?

You could have a textbox, txtSearch let's call it; base your Listbox
on a query

SELECT PartNumber, Description
FROM Parts
WHERE Description LIKE "*" & [Forms]![YourForm]![txtSearch] & "*"
ORDER BY PartNumber;

(or perhaps order by description, whichever works better for you).

You'll need to requery the listbox in the AfterUpdate event of
txtSearch.

John W. Vinson [MVP]

 

[DonK]

Thank you for that John, looks exactly what I want......however, I'm getting
a VB error: "Compile Error....Expected Function or variable."

Private Sub txtSearch_AfterUpdate() - (is highlighted in yellow)
Me.Requery.List2
End Sub

DonK

I want to design a form to search spare parts records by description rather
than part number. That is, the user types in PUMP in a text box and the query
returns all records with the word PUMP in its description to a list box on
the same form.
Bearing in mind a description might read "DRAIN PUMP ASSY-MAYTAG MAV SERIES"
Any suggestions as to how I would go about this?

You could have a textbox, txtSearch let's call it; base your Listbox
on a query

SELECT PartNumber, Description
FROM Parts
WHERE Description LIKE "*" & [Forms]![YourForm]![txtSearch] & "*"
ORDER BY PartNumber;

(or perhaps order by description, whichever works better for you).

You'll need to requery the listbox in the AfterUpdate event of
txtSearch.

John W. Vinson [MVP]

 

[DonK]

John, have realised my error. I re-phrased the After Update to: list2.requery
and it's now working perfectly. Thanks again.
--
DonK

Thank you for that John, looks exactly what I want......however, I'm getting
a VB error: "Compile Error....Expected Function or variable."

Private Sub txtSearch_AfterUpdate() - (is highlighted in yellow)
Me.Requery.List2
End Sub

DonK

I want to design a form to search spare parts records by description rather
than part number. That is, the user types in PUMP in a text box and the query
returns all records with the word PUMP in its description to a list box on
the same form.
Bearing in mind a description might read "DRAIN PUMP ASSY-MAYTAG MAV SERIES"
Any suggestions as to how I would go about this?

You could have a textbox, txtSearch let's call it; base your Listbox
on a query

SELECT PartNumber, Description
FROM Parts
WHERE Description LIKE "*" & [Forms]![YourForm]![txtSearch] & "*"
ORDER BY PartNumber;

(or perhaps order by description, whichever works better for you).

You'll need to requery the listbox in the AfterUpdate event of
txtSearch.

John W. Vinson [MVP]