Form to search customer by account number

donsi

Registered User.
Local time
Today, 12:23
Joined
Sep 1, 2016
Messages
73
Hi All,

I have a table with guest FN, LN and Acc#. I build the form to do the search, but it seems to be updating 1st records every time search is performed.

The way I wanted to work is when employee enters the Acc# and hits enter, it will show FN and LN in the box. My form is bound to the table. Form has three txtbox. 1st will be to enter Acc# and bound to the Acc# column, 2nd for FN, and bound to FN column, 3rd for LN, and bound to LN column. VBA code is set in FN txtbox "On Focus".

I would like users to be able to enter new records if it wasn't found.

Also, Acc is the Primary Key as I wanted to have a unique number.

New to VBA, still learning.

Code:
Option Compare Database



Private Sub TxtCOCustFN_GotFocus()

Dim varX As String
Dim varY As String
Dim blnGood As Boolean
On Error GoTo Problem
varY = DLookup("FirstName", "TblCustomers", "AccNo = " & [Forms]![SubFrmCustSearch]![AccNo])
Me.TxtCustFN = varY
varX = DLookup("LastName", "TblCustomers","AccNo = " & [Forms]![SubFrmCustSearch]![AccNo])
Me.TxtCustLN = varX
Me.Requery
Exit Sub
Problem:
If Err.Number = 94 Then
Me.TxtCustFN = " "
Me.TxtCustLN = " "
MsgBox "Please Enter guest Name"
Call DoCmd.RunCommand(acCmdRecordsGoToNew)
End If
If Err.Number = 3022 Then
blnGood = True
Call DoCmd.RunCommand(acCmdSaveRecord)
        blnGood = False
End If
End Sub
 
Last edited:
Why not use an unbound combo box on the form (i'd probably put it in the Heater Section) to find the selected record. You can even use the wizard to create the combo box which will create the required code
 
But that will give me drop down list, and we have 1000+ customers and adding. Would it let users to enter few numbers to narrow down?
 
As you type in the Acc# it would find the correct entry in the list. The code in the After Update event would then find the appropriate record. I suggest you at least try it. It does work.
 
Thanks Bob. It did work. My intention was to use it in a form that wasn't link to the same table, so I was keep selecting different option in wizard, but I build sub form that will be attached to this one and it works great. Thanks for your help.
 

Users who are viewing this thread

Back
Top Bottom