combobox requery complex error

[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!!!

Can anyone help me with these 2 problems???

Thank you in advance!

 

[mihalisp]

can anyone help me,please?

 

[missinglinq]

What exactly are you using this combobox for? Is the combobox bound to a field in the underlying table? Unbound controls, including comboboxes, retain their last entered values in moving from record to record. If you want the value to persist, i.e. show the correct value for a given record, it has to be bound to that record.

 

[mihalisp]

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.

Can you help me?

 

[mihalisp]

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

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.

 

[JamesMcS]

Is your bound column 1 or 0 in its properties? I'm guessing your bound field is customer_ID which would be 0.

 

[mihalisp]

the bound field of the combo is Address1 on table1.All records for this fieeld 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.

 

[JamesMcS]

If the combo box is null, you won't have anything to pull back into the other fields surely?

The problems you described in your original post are caused by the combo box not being bound to anything, and I would guess your text boxes are n't bound to anything either.

 

[mihalisp]

all the fields of that form are bound to correspondent (same name) fields in Table1.

What is it that you are missing and i can't make myself clear?

 

[JamesMcS]

The combo box address1 has null values.

Null combo box = nothing to pull back

Me!address1.RowSource = Me!address1.RowSource

Makes no sense.

It must be something with the after update event of the combos or the on current of the form

Care to share the code?

It really does sound like you're not binding the right column in your combo box(es). Bear in mind they start from 0.

When they're bound properly, you need to put something like [textboxname].text=combobox.column(x) in the afterupdate of the combo box. That will populate the text boxes.