Find Box

[StudentX01]

Hey everyone,

Im creating a database for college using MS access 2003.

I have 3 table that contain data and i am building form to allow a user to search for a particular customer. The customer ID in my customer table is an auto number.

I have a button on my form and it shows the find and replace box when pressed which is what i want but i then want it so that when a user types a customer ID number in to that find and replace box and it is an exact match for the find and replace box to auto close else show an Error message and allow the user to input another customer number,

Im sure there is macro code to do this so im asking if anyone knows or has any better ideas?

Thanks for your time

 

[KeithIT]

Okay, is this for a class assignment? If so, can you do this another way?

What if you created a combo box on your form that is bound to two fields in your table: Field1=Customer ID and Field2=Customer Name (which can be either first name, last name or the concatenation of the two). If you have the combo box bound to column 1 and only show column 2, then set the After Update event of the combo box to run the following code:

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo1], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Where CustomerID= the field for your customer ID and Combo1= the name of the combo box, your form will automatically display the information for that customer when his/her name is selected in the combo box.

Let me know if this helps or if you would prefer another solution.

 

[StudentX01]

Thanks for your reply,

The idea works but its not working in the way that i want it to. Becuase i have to use the find function i need to find code that does this:

If a customer ID is typed in the find box and found in the database then display that record and hide the find box else a message box that says
"customer not found please try again or add new record"

I hope that makes sense, if not i will try and explain further

Thanks