Combo box to lookup values in a table

[gilescis]

I have a small database with 3 tables.
tblBilltoCustomer
Fields - Key - BillCustID , companyname, address1, address2, city, state

tblOrders

tblCustomers

I have a form that has tblCustomers as the main form then tblOrders as a sub form.

I want to place a combo box on the form that will lookup a company name from the tblBilltoCustomer table then brin in the address1, address2, city,state , into the form for that record. But then I need that same info to print on a rpt.

I can get the lookup to work using =cboCompanyName.Column() but the addresses , city and state will not show on report
Is there any good samples of lookup fields

Any Ideas are greatly appreciated

Dean

 

[CEH]

From the toolboc pick combo. Then use the wizard and follow the steps....

For example…… Form of Customers….
Fields: CustID(PK), CustName, CustAddress, CustCity, CustState, CustZip

Create a query with all fields, then a form from this query.
From toolbox insert “ComboBox”
In the wizard select “Find a record on my form based on the value I select in my combo box”
In next step select what fields you want to see in the dropdown… I used“CustName”
I named the combo “cboCustomers”
Finish the wizard.

It will create this code:

Private Sub cboCustomers_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![cboCustomers] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This will fill in the fields from the customer you pick in your dropdown.

 

[gilescis]

Ok, I understand that, but on my form I want to have the following

CustomerName ( Lookup box form tblBilltoCustomer)
Address 1 (form tblBilltoCustomer)
City (from tblBilltoCustomer)
etc.........

How ho I get the other feilds. Do I put an unbouded text box on the form for the rest of the data

 

[gilescis]

So when I select joen doe from the tblBilltoCustomer
it will dispaly

John Doe
102 Commerce St
City , State , Zip

 

[gilescis]

whooops spelling

John Doe

 

[gilescis]

I got the other fields to work now, But heres the problem. If I am in record # 4 and I select a company name and it populates the address, when I switch to the next record that same name and address is in the fields. So then when I change it in record 5 and go back to rec 4 the info changed to what I made it in rec 5

Thanks

 

[CEH]

Is your form based on a query of table tblBilltoCustomer???? It doesn't sound like it......