trying to link tables in query, only works for 1st record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables: Customers and Order.
I want to be able to create a form where you type (or select) the customer
account number and their address details are automatically entered into their
respective fields.
I have succesfully done this using a query, as described quite freuquently
on forums. HOWEVER, I can only do this for the first record. When i go to the
next record it doesnt fiull in the info.
Any ideas anyone??
Thanks in advance.
 
The code that you used to retrive the data the first time, call it from the
form OnCurrent event, so this code will be triggered every time you move from
one record to another.
 
Before I do that, can you tell me what you did to retrieve the data that
apear the first time only?
 
I used a query that took the Account Number from the Order table, the Account
adress fields (5 in total) and the rest of the fields from Order table. (the
maching fields in each table are EXACTLY the same formats too).

Rich
 
There are few methods of doing that, the one I like is:
Instead of typing the customer number in a text box, create a combo where
the customer number will be entered, the RowSource of the combo will include
all the fields you want to display in the form, that relate to a specific
customer.

Select Id, Cust_Name , Cust_Address From Customers

Create text boxes in the form where you want the name and the address to be
displayed, in the control source of this text boxes you can write

For customer name
=[Combo Name Created].Column(1)

For Address
=[Combo Name Created].Column(2)

Note, the column count start with 0, if you have more fields you want to
display, then add them to the row source of the combo.
In this method, by selecting limit to list of the combo, the user can't make
mistakes with the wrong customer number.

I hope that clear, and you'll also like that method.
 
Back
Top