Auto fill query

[darrellx]

I am a new access user and need some help. I have created an auto fill query to fill in information on a sales order table from my customer table based on CustomerID.

I have alsol created a sales order form. When I enter the customer ID in the form the rest of the data does not fill in.
Actually nothing happens.

How do I link the form to the query so that the form knows where or how to look for the auto fill customer data.

Thanks

Darrell R

 

[TomCologne]

Right click in the top left corner of the form in Design View>Properties.

Then set the "Recordsource" to the query. After that click each control to switch the properties box to the control's properties and select the appropriate qry field as "ControlSource".

To get used to Access use the wizards to create the db objects. They're not perfect but will give you at least a first impression of some basics.

TomCologne

 

[willir]

Darrell
Welcome to Tek-Tips

By autofill, do you mean
- have the form display existing records as you scroll through a record set?
- when you select a customer, the form populates with the info for the customer?

Although a little vague, it sounds like you are looking for the latter.

If so let me guess...
You have a customer table which would include their address.
The sales order form also has an address field.
When you select the customer, you want the address field on the customer table to populate the address on the order form??

There are several ways to achieve this. Assuming the following...

tblCustomer
CusomterID - primary key
CusotmerName
CusomterAddress
...etc

tblOrder
OrderID - primary key
OrderDate
CusomterID - foreign key to tblCusomter
ShipToAddress

frmOrder - control names on form match field names on table.
sbfrmCustomer - displays cusomter address, LinkChild and LinkMaster is CusomterID.

- CustomerID on frmOrder is a changed to a combo box with following properties...
RowSource:
SELECT CusotmerID, CustomerName, CustomerAddress FROM tblCusomter

ControlSource: CustomerID
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0";1";0"

For the AfterUpdate Event Procedure for the combo box

Me.ShipToAddress = Me.CusomterID.Column(2)

This will populate the ShipToAddress field with the cusomter address.

Other approaches would be to use a command button to transfer the address. Use the OnClick event. This would add the extra protection of not accidentally over-writing the ShipToAddress field.

Or you can use some code...

If Len(Me.ShipToAddress & "") = 0 Then
   Me.ShipToAddress = Me.CusomterID.Column(2)
End If

The above will only populate the address text box with the customer address info only if there is nothing currently in the ShipToAddress field.

Richard

 

[PHV]

You may find usefull ideas in the Northwind sample database.

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