Dlookup - adding new data

[0nyx175]

Hi guys. I’ve got the following information:



New_Customer_Table
Account Number
Title
Surname
Address
Post-code


Existing_Customer_Table
Account Number
Title
Surname
Address
Post-code


I’d like to produce a form to add new records into the new_customer_table and if the user enters an account number we already hold in the existing_customer_table, for it to populate the form with their information.


I’ve started using the following dlookup connected to a form which should add information into new_customer_table


=DLookUp("[Surname]","[Existing_Customer_Table]","[Account Number] = " & Chr(34) & [Account number] & Chr(34))


This brings the data through perfectly from existing_customer_table and displays it in the text box, however when I submit the record, it doesn’t send it to the new_customer_table as the dlookup removes any link to that table.



I've attached a copy of the database for you guys to have a look



Can anyone suggest a solution?

 

[plog]

Stop everything. Why do you have 2 tables of similar structure? That's a huge sign of doing something incorrectly. What's the difference between those 2 tables? When does a new customer become and existing customer?

 

[0nyx175]

Dont worry about that.. i've stripped this down to it's most basic form so i could put it on the forum.

Essentially, i'm inputting customer purchases onto a database and would like some validation so that if we have had a purchase from them before, it will pop up with their address on the form so we dont have to type it again

 

[pr2-eugin]

What plog meant was.. Your table structure is wrong.. I was about to say the same thing.. Why do you have two tables of the same structure? You need is one table and on the AccountNumber Before_Update() event see if there is another Record using DCount() if the DCount > 0 then the data already exist in the Table.. So cancel the update and take them to the record..