data entry - Auto fill data for exisitng accounts in records

[iamratman]

First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.

So now I would like to add some more functionality to this existing project.

My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.

I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.

The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.

Second I haven’t figured out how to call up the customers information from just the account field.

I’ve googled this and haven’t found anything terribly helpful.

 

[Smart]

Second I haven’t figured out how to call up the customers information from just the account field.

In the after update event of the account no field

dim CustomerInfo as string

customerInfo = nz(dlookup("[nameofcustomernamefield]","[queryname]"),"")

[formcustomerfieldname] = customerInfo

(nameofcustomernamefield is the name of the customer name column in your query)

(formcustomerfieldname is the name of the customer field on your form)

 

[iamratman]

Smart,

thanks for responding and for the info.

I am playing with the code you offered so far so good thanxs. I have two problems.

1. How do I get the following code to work with the last name field as well as the first? I have been messing with it for a week yet with no luck.

Private Sub ACCTNUM_AfterUpdate()
Dim CustomerInfo As String

CustomerInfo = Nz(DLookup("[FIRSTN]", "[AllData_Query]"), "")

[FIRSTN] = CustomerInfo

End Sub


2. I have some before update events on the form, one of them is to prevent the same account number from being entered in the db more than three times per day. This after update event seems to nulify my 3 times a day limit event. Can this be fixed by adding code or by moving the event some where else?

This is the Before update event code on the form I am talking about:
If NewRecord Then
If DCount("Acctnum", "tourn_tab", "Acctnum=" & Forms!tourn_fm!ACCTNUM & " And Date = #" & Format(Forms!tourn_fm!DATE, "mm-dd-yyyy") & "#") = 3 Then
MsgBox "This account has been added 3 times today!", , "Daily Limit Reached!"
Cancel = True
Me.Undo
End If
End If
End Sub



Any further sugestions would be great. Any one please let me know if any of this is unclear. thanks.