Creating a search Field that pulls up a record

[RapidRepairArnold]

First post, so please be careful.



Background Me: New to Access, don't have much coding experience within Access.

Background on problem: I would like to be able to type in a 6 digit Index/Unique key in a text box and then have it load the information up into the form so that we can do more data manipulation like reducing and increasing inventory. But the manipulation is another problem for another day

I've searched around and I see alot of recommendations on using a drop down or combo box, but that won't be feasible (we have over 700 products). In addition, the end goal down the road is to incorporate a bar code scanner into the mix to just scan the bar code with the 6 digit number.

Does anyone have any ideas? I've played around and tried "After Update" and "On Change" but I didn't get too far.

Thank you in advance, and I am quite impressed with this particular community. Definitely bookmarked it!

[JeroenMioch]

Filtering is your solution.
My database does the same with a personell number. I enter (or scan) the number and automaticly the appropriate record is shown.

Private Sub YourUniqueNumberField_AfterUpdate()
    FilterForm
End Sub

Private Sub FilterForm()
    Dim strFilter As String
    Dim blnFilter As Boolean
    
    blnFilter = False
    
    strFilter = " 1=1 "
    If Me.YourUniqueNumberField <> "" Then
        strFilter = strFilter + " and YourField_In_Database like '*" & YourUniqueNumberField & "*'"
        blnFilter = True
    End If

    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
   
    Me.FilterOn = True
End Sub

YourUniqueNumberField is the name of the field your searching with
YourField_In_Database is the name of the field in your table

Off course you can adjust the names to your situation.

I use this way of filtering for all my databases and it hasnt failed me once.
If you have a barcode scanner then you make sure your focus is on the search field, You scan the number from a barcode and the record pulls up. The only thing you need is a good barcode font and you have it up and running in a matter of minutes.
I use free 3 of 9 barcode font, As the name reveals, a free barcode font wich you can find online.

Let me know how it works for you

Oh, one more thing; Make sure your field (on your form and in your table) is TEXT format.

[RapidRepairArnold]

JeroenMioch,

Thanks.

So this is the code I have. Mind you, this is just a rough draft that I threw together. Please note that field name "Unique ID" is named with a space...is that bad Karma? Something is nagging in the back of my mind saying that I should avoid spaces and other special characters in my field name.

------------------------------------
Private Sub SearchField_AfterUpdate()
FilterForm
End Sub
------------------------------------
Private Sub FilterForm()
Dim strFilter As String
Dim blnFilter As Boolean

blnFilter = False

strFilter = " 1=1 "
If Me.SearchField <> "" Then
strFilter = strFilter + " and Unique_ID like '*" & SearchField & "*'"
blnFilter = True
End If


If blnFilter Then
Me.Filter = strFilter
Else
Me.Filter = "1=2"
End If

Me.FilterOn = True
End Sub
------------------------------------

I try typing the Unique ID in the the search field, but all it is doing is editing the record.

[JeroenMioch]

Yes you ahould avoid spaces in field names and any other names of objects alltogether
The searchfield (wich i would place in the header of the form) has to be an unbound textfield.
In the main section of the form you have your fields wich contain the records data. These fields have to be bound to the corresponding table, obviously.

If the Unique ID field in your table is witch a space in the name, then in the code i gave you have to put it in brackets.
You cannot refer to Unique_ID in the code, while the name of that field in your table is Unique ID.
So IF its called Unique ID then refer to it as [Unique ID] but as i stated, its best to avoid space in names so you better change it.

If my above recommendations dont work then perhaps you can send your (empty) database to me then ill make it work for you.