Dlookup help

[kirnavi]

I am creating a database. There are two main tables - Table 1 (customer information) is created via a Make Table query, which pulls from an Oracle db, which is the db for our department's customer service system. Because Table 1 has to be manually refreshed, it may lag behind or be ahead of Table 2. Table 2 (order information) is populated via a form. The two tables share a Customer ID. Up until I created this db, the users were entering their order information into an Excel sheet, which I imported to create Table 2. For this example, let's say each customer will only ever have one order so there will be a record to record match.

Here's my problem:
I want the user to see the customer's name when he/she types in the Customer ID. I got the DLookup to work for NEW records - that is, for records that exist in Table 1 but have not yet been entered into Table 2 - but it will NOT work for the 300+ records that I imported from Excel. For those records, the Name textbox remains blank when I browse through the records or if I type in the Customer ID, gives me an error about entering duplicate values.

Furthermore, if Table 1 (customer info) is lagging behind Table 2 (order info), I want the user to just go ahead and enter all the order information and when he/she goes back to update the order at a later date (after Table 1 has been refreshed), the name to just pop up.

Can anyone help or offer any thoughts?
Thanks.

 

[PHV]

Why not have Table 1 linked to an Oracle view showing always accurate customer informations ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244