Displaying Information From a Table in a Form

[Guest]

Hi Everyone,

I have two tables. One is named Customers, which contains my customer names
and addresses, provinces etc.
The second table is named orders.
I am working on a form to enter data into the orders table. I have set up
the look-up wizard to allow me to select my customer from those which are in
my Customer table. Is it possible to have the address and province of the
customer I select automatically come over onto the form when I select that
customer from the drop down list?

Thank you for any help.

 

[Douglas J. Steele]

Have those additional fields be part of the RowSource for the combo box.

In the AfterUpdate event of the combo box, put code to take those additional
columns and add them to text boxes on the form:

Private Sub cboCustomer_AfterUpdate

Me.txtAddress = Me.cboCustomer.Column(1)
Me.txtProvince = Me.cboCustomer.Column(2)

End Sub

The above assumes that Address is the second column of the combo box, and
Province is the third (the Column collection starts numbering at 0). It's
not necessary that the additional columns be displayed in the combo box.

 

[Guest]

An alternative method I've been using successfully is to invoke the query
builder on the form - not necessary to type any code using this method.

The procedure to do this is to open the form and switch the view to Design
View, enter the properties menu (F4) and click the ... on Record Source. You
will then be asked if you want to create a query based on the table.

From there, add as many fields from as many tables as your heart desires,
save, and they will then be included in the Field List.

(subject specific assistance)
Another handy thing I've been doing is to insert a listbox into the orders
form, and pull in some customer data that can identify your customer (aka
first name, last name), by telling it to look up values in another
table/query, (identify the data) and tell it to remember the id for later use
(in this case, link it back to your 1:M relationship field you have already
set up).

Once you have done that, when you click on a customer in the listbox, it
will autopopulate the relevant data in the orders form.

Maelinar

 

[John W. Vinson]

From there, add as many fields from as many tables as your heart desires,
save, and they will then be included in the Field List.

Just be aware that many multitable queries will a) not return any records if
the joins aren't correct, b) will return many duplicate records (for the same
reason), and c) not allow updating.

John W. Vinson [MVP]

 

[Guest]

lol we are talking a simple customers/orders database here - sometimes you
MVP's think situations are more complex than they need to be.