Populate form unbound fields from another table

[ponneri]

Hi.

I have a form bound to a table that is used for entering records. On this form, there is a combo box that has some field values from the underlying table. And i have 5 unbound fields too, that need to display some values.

As soon as I pick a value in the combo box, I need to just display in the unbound text fields - values taken from another table or query; as this combo box field is common to both tables.

So, what I'm saying is my record source is different for the form and the unbound fields on form.

I tried the Dlookup option, but it did not work properly. How can I use a querydef or recordset method to populate the unbound fields with values ?

Any help will be greatly appreciated, as I'm stuck on this silly issue !

:banghead:

 

[arnelgp]

if the rowsource of your combobox is a table and this table is where you want to get the values of your textbox, include the fields you want to display in your textbox in your combobox.

set their column width to 0, if you don't want to display them.

on the after update event just get the values from the combobox's column to your textbox:

private sub combo_afterupdate()
' retrieve second column value from combobox
me.unboundText1 = me.combo.column(1)
' retrieve third column value from combobox
me.unboundText2 = me.combo.column(2)
end sub

 

[ponneri]

Great Idea, arnelgp !

I'll surely try that and see.

 

[ponneri]

Hi.

I did try as said.

private sub combo1_afterupdate()

me.Text1.value = me.combo1.column(0)
me.Text2.value = me.combo1.column(1)
me.Text3.value = me.combo1.column(2)
me.Text4.value = me.combo1.column(3)

end sub

But, there is a strange problem.

The unbound boxes are able to display only first two text fields. column(3) and column(4) are also text values but they do not show up in the third and fourth unbound boxes.

What could be the reason ?

 

[arnelgp]

did you hide these columns? try unhiding them, for test, by setting the column widths other than 0. see if there is indeed a value.

 

[ponneri]

Thanks again.

Yes, I did try that and I'm able to see the values in the combo box.

But, for any value I pick in the combo, I'm able to populate first two unbound fields only. 3 & 4 unbound text boxes still empty.

 

[sneuberg]

Also make sure your Column Count is 4 in the combo box properties

 

[arnelgp]

if you can upload your db, it would be much help.

 

[Galaxiom]

Another way would be to put the other fields in a subform and use the combo as the LinkMasterFields.

The subform can be made to look like part of the mainform by turning off the border and other extraneous stuff on the form and the subformcontrol.

No code required.

 

[ponneri]

Thanks a lot for the many options, arnelgp.

BTW, after setting the column count property for the combo, it worked well.

Great help indeed !