Question Help Me Please :)

[rob1]

Right, I have two Tables .. One Customer, One Transaction
Fields in Customer:
C-ID
CName
CAddress

Fields in Transaction:
T-ID
P-ID
Date
C-ID
CName
CAddress

How do i create a situation so I can select C-ID in the Transaction Table and it fill the CName and CAddress fields for me.

I have created the relationship for the 2 C-ID fields so i can select which record, but it wont "lookup" or "fill" the other 2 fields.

Please Help Me

 

[Isskint]

Hello rob

First off just a couple of pointers.
There is no need to duplicate the CName and CAddress in the transaction table. By storing the C-ID in transactions you can easily use the DLOOKUP() function to display CName and CAddress on reports, forms and queires. Also it is not a good idea to have a field called Date because Date is a function and method used by Access. Use T-Date instead?

So to your problem. I take it you want to acheive this auto fill on a form?
All you need do is put a textbox on the form for CName and put DLookUp("[cname]","[Customers]","[C-ID]=" & [C-ID]) as the ControlSource and another textbox for CAddress and put =DLookUp("[Caddress]","[Customers]","[C-ID]=" & [C-ID]) as the ControlSource. This will work on Reports and Queries also.

 

[rob1]

thats perfect .. and yes i want it on a form.

Thankyou very much

 

[Trevor G]

If you wanted to go down the line of an update query here is the SQL Code you would copy into a New Query and then click cancel and then select the SQL view at teh top left

UPDATE Customer LEFT JOIN Transactions ON Customer.C_ID = Transactions.C_ID SET Transactions.CName = [Customer]![CName], Transactions.CAddress = [Customer]![CAddress];

 

[rob1]

Hi Isskint,

It is definetely the dlookup i was after but i just seem to be having trouble with it.

Ive tried the codes that you put but it just shows #Error when I take the form out of design view.