Combo box Requery problem!

[mihalisp]

Hi everyone,
I experience the following problem.

I have form1(datasheet view) which get records from table1.
In this Form1 there are 4 fields customer_id,Name,Surname and Address.
Address is a combo box which gets records from a query (customers_and_address_query :

SELECT cust.customer_id,cust.name,cust.surname, addr.street
FROM cust INNER JOIN addr ON cust.customer_id = addr.customer_id
WHERE (((cust.customer_id)=[Forms]![Form1]![customer_id]));
)

The problem is that when i choose an address from the combobox for a specific customer(e.g. the 1st record of the Form1) ,when i move to another record ,the records of that combobox are still the same as the ones of the 1st record(1st customer).

I tried the following code but the problem persists.address1 is the name of the combobox.

Private Sub address1_AfterUpdate()

Me!address1.Requery

End Sub

Additionaly,if i select something from the combobox for the 1st record-customer of the Form1 its ok,but when i move to the 2nd record of the Form1 and try to select something from the combobox (it has similar records regarding the records of the combo for the 1st record of the form as i explained above) ,the record of the combo for the 1st customer of the Form1 disappears!!!

The combo box is bound to the relevant field of the table that the whole form depends on (Table1).

Here is what i really want:

In my database i have seperate tables for Customers and their addresses and phones,that is to say a customer may have 2 or more addresses or phone numbers.

So in this Form i have customer name and surname and 2 combo boxes for address No1 and address No2 of the customer.I want in both of these combos to be visible all the addresses of each customer so that i can select from these combos which of them i want to be AddressNo1 and AddressNo2.

It must be something with the after update event of the combos or the on current of the form but i dont know well vba.
Probably the problem occurs because i dont know which event of the form or the combo's i must use!!!

Can you help me?

Thank you in advance!

 

[PHV]

In the Current event procedure of the form:
Me!address1.RowSource = Me!address1.RowSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mihalisp]

Thanks my friend,I tried it but it still has problem.

When i select something inside the 1st combo regarding 1st record of the form,this selection remains and it is visible.

But when i move to the 2nd record of th form ,that selection from the combo that i selected for the 1st record disappears.

Again,When i move back to the first record it becomes visible but the selection of the 2nd combo disappears.

 

[MajP]

You can not do cascading combo boxes in a continous form or datasheet view. The reason is when you change the rowsource of the combo it changes it for all representations of the combo.

So if you select "abc" for the first record, then move to the second record and filter out "abc" it disappears from all the combos.

The work around is to put a textbox over top of the combo covering up everything but the pulldown. So you can pick from the combo, put you are seeing the display of the textbox. With proper formatting, you can not tell the difference.

 

[mihalisp]

The combo boxes are not cascading

The bound field of the combo is Address1 on table1.All records for this field in this table are Null from the start.This table has only values for the customer id name surname,but it has null values for the address1 field.

So ,in form1 which depends on table1 the user sees values only in customer id name and surname .The combo box address1 has null values.When i select something from the combo box i want it to be automatically saved in the correspondent field address1 of the table1 (which is bounded to).Each combo in this datasheet displays addresses (getting records from a query which joins table with addresses and table with customer details) that are filtered by the Form_field_Addresses1.

SELECT cust.customer_id,cust.name,cust.surname, addr.street
FROM cust INNER JOIN addr ON cust.customer_id = addr.customer_id
WHERE (((cust.customer_id)=[Forms]![Form1]![customer_id]));
)