Using the results of a query over more than one field in a form

[Grommit]

I am running a query to get name, address 1, & address 2, from a table. I
wish to use the results from this query in three separate fields on my form
but I am having difficulty using other than the first column in the resulting
query.
eg. 1st field is a combo box where the query is run from. This returns
Customer name, address 1 & address 2 but only puts column 1 ie customer name
in the box. I then have 2 text boxes address 1 & address 2 and I need to
display Column 2 & column 3 from the query in these. Is there any way I can
do this using access without writing code.

 

[Jeff Boyce]

Yes, but ...

If those controls on your form are "bound" to fields in a table, it seems to
violate basic relational database design to have the values (name, addr1,
....) stored in one table (from your query), but then stored again
(redundantly) in the table to which those controls are bound.

If those controls on your form are "unbound" (for display purposes only),
you can use something like (untested):

Me!txtAddr1 = Me!cboCustomerName.Column(1)
Me!txtAddr2 = Me!cboCustomerName.Column(2)

Note that .Column() is zero-based, so you have to start counting columns in
the combobox with "0, 1, ..."

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mr. B]

Grommit,

If you want all of the records from the results of your query to be
available in your form, try placing you query as the Record Source for your
form, not the combo box. You would not need the combo box. Convert the
combo box to a text box.

You would then be able to use the navigation buttons to move between records
returned by the query.

 

[Grommit]

Thanks for the info Jeff
Sounds like that could work.
I had already tried binding the
fields but didn't work as you said it gave
errors

Cheers

Grommit