Record Search

[Guest]

I have a text field on my form that I use to enter a "part number". I want to
be able to use this field to search for a specific record. If I was to create
a combo box on the form header would I be able to select a part number and
have it go to that record? What is the same part number was used on other
records? Would it go to the first record it finds? How would this be possible
and is this possible?

 

[Guest]

Try this link on "Searching for a record based on a text box value in a form"

http://www.databasedev.co.uk/text_search.html

 

[Dirk Goldgar]

I have a text field on my form that I use to enter a "part number". I
want to be able to use this field to search for a specific record. If
I was to create a combo box on the form header would I be able to
select a part number and have it go to that record? What is the same
part number was used on other records? Would it go to the first
record it finds? How would this be possible and is this possible?

You can let the combo box wizard build you a combo box to "find a record
on my form". If there are more than one record with the same part
number, though, that will only find the first one. You might add your
own "Find Next" button, with code to find the next record with the same
part number as the current one. Code for that button might look like
this (completely untested air code):

'----- start of code -----
Private Sub cmdFindNext_Click()

If IsNull(Me.PartNumber) Then
MsgBox "No current part number"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext "PartNumber=" & Me.PartNumber
If .NoMatch Then
MsgBox "There are no more records with this part
number."
Else
Me.Bookmark = .Bookmark
End If
End With

End If

End Sub.
'----- end of code -----

 

[John Vinson]

I have a text field on my form that I use to enter a "part number". I want to
be able to use this field to search for a specific record. If I was to create
a combo box on the form header would I be able to select a part number and
have it go to that record? What is the same part number was used on other
records? Would it go to the first record it finds? How would this be possible
and is this possible?

The toolbox Combo Box Wizard has an option to create an unbound Combo
Box to do just this - it will jump to the first record for that part
number.

Or, you could use some VBA code in the combo's AfterUpdate event to
filter the form to display only the desired records. Guessing at
fieldnames and such, and assuming that the PartNo field is of Text
datatype, the code might be

Private Sub cboFindPart_AfterUpdate()
If IsNull(Me!cboFindPart) Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = "[PartNo] = '" & Me!cboFindPart & "'"
Me.FilterOn = True
End If
End Sub

The Form will then show only the record or records for that part
number.

John W. Vinson[MVP]

 

[Guest]

Thanks for your help but I do have one follow up question. What if this part
number field is a text box but the same part number has been used multiple
times. Will the combo search box list this part number more than once or just
a one time?

 

[Dirk Goldgar]

Thanks for your help but I do have one follow up question. What if
this part number field is a text box but the same part number has
been used multiple times. Will the combo search box list this part
number more than once or just a one time?

That depends on how you set it up. If you build a combo box to find a
part number, you will normally set its Row Source query so that it
returns only one row per part number. If you are selecting your part
numbers from the same table on which the form is based, that probably
means using the DISTINCT clause in the query; e.g.,

SELECT DISTINCT PartNumber FROM MyTable;

That returns only one instance of each part number, no matter how many
there are in the table.

On the other hand, if you want to build a combo box to find a specific
*record*, then PartNumber alone isn't enough to do it. The combo box's
query must select the primary key field(s) of the form's recordsource,
in order to identify the record, and maybe *also* select the part number
as a second column. If you choose to have the combo show the part
number but have the primary key as its bound column, then you'll see a
part number in the list multiple times and will have to choose which one
of those instances represents the record you want. For this purpose,
you want to make sure that the combo box's list includes all the data
columns necessary to let the user identify which record he wants to
choose.