Combo Box selection / AUTO POPULATE subsequent text boxes

[kealaz]

In my form, I have a combo box with a selection of vendors that a purchase
order will be cut to. When I select a vendor, I would like my form to look
at a different table [tblVENDOR] for the information to automatically
populate the next 4 text boxes.
ADDR_ST
ADDR_CITY
PHONE
FAX

I would like to use the After Update event for my combo box [VENDORNAME].
What code would I use for this?

Thank you very much!!!

 

[John W. Vinson]

In my form, I have a combo box with a selection of vendors that a purchase
order will be cut to. When I select a vendor, I would like my form to look
at a different table [tblVENDOR] for the information to automatically
populate the next 4 text boxes.
ADDR_ST
ADDR_CITY
PHONE
FAX

I would like to use the After Update event for my combo box [VENDORNAME].
What code would I use for this?

Thank you very much!!!

If you're trying to store these fields in your order table... DON'T. These
fields should exist in the Vendor table, only; you can use a Query joining the
vendor table to your orders table if you need to see them together.

To *display* (without storing) the address and so on on your Form, you can
include the fields in the Combo Box's RowSource query, and set its column
count big enough to include them. Set the combo's ColumnWidths property to
zero for these fields so they don't show up when the combo's dropped down
(unless it would be helpful to do so, e.g. if you have a NAPA Auto Parts in
five different cities and want the user to be able to select the right one).

You can put textboxes on the form with Control Source like

=comboboxname.Column(n)

where n is the zero based index of the field you want to see - i.e. if the
phone is the fifth field in the combo box's rowsource query use (4).

 

[M Skabialka]

Two ways come to mind:
Add the other columns to your combo query - if you make them 0" wide the
user won't see them. After update
ADDR_ST = [VENDORNAME].Column(1)
ADDR_CITY = [VENDORNAME].Column(2) etc
Not that Column(0) is the selected VENDORNAME.

Or you can do a DLOOKUP
ADDR_ST = DLookup("[ADDR_ST]", "tblVENDOR", "[VENDORNAME] = '" &
Forms![yourForm]!VENDORNAME & "'") etc
A primary key would be better than the name for the DLookup though.

Mich

 

[kealaz]

Hi John,

Thank you for your reply. I really appreciate all the help I get from this
forum.

The selection of vendors that appear in my combo box is coming from a "temp"
table and is not ALL the vendors that we do business with. It would only be
the vendors that we currently have a req open for. So, the selection might
be 1-10 (give or take). It is coming from a table called [tblBUY] with a
select query

Row Source Type: Table/Query
Row Source: SELECT tblBUY.VENDORNAME FROM tblBUY;

This table [tblBUY] does not have the address/phone/fax information. That
information is stored in [tblVENDOR]. How do I get the address/phone/fax
into column(1), column(2), etc. if the information is in a different table?
If I can get them into those other columns, then I can get them into the
other text boxes with...

Me!VENDORNAME.Value = Me!VENDORNAME.Column(0)
Me!ADDR_ST.Value = Me!VENDORNAME.Column(1)
Me!ADDR_CITY.Value = Me!VENDORNAME.Column(2)
Me!PHONE.Value = Me!VENDORNAME.Column(3)
Me!FAX.Value = Me!VENDORNAME.Column(4)

..... but I'm stuck on how to get that info into the subsequent columns when
the information is coming from a different place then the first column
[column(0)]

Thanks again for all of your help!!!


:

 

[kealaz]

Thank you Mich! The DLookup worked Great!
I really appreciate your help with this!!!

Two ways come to mind:
Add the other columns to your combo query - if you make them 0" wide the
user won't see them. After update
ADDR_ST = [VENDORNAME].Column(1)
ADDR_CITY = [VENDORNAME].Column(2) etc
Not that Column(0) is the selected VENDORNAME.

Or you can do a DLOOKUP
ADDR_ST = DLookup("[ADDR_ST]", "tblVENDOR", "[VENDORNAME] = '" &
Forms![yourForm]!VENDORNAME & "'") etc
A primary key would be better than the name for the DLookup though.

Mich

In my form, I have a combo box with a selection of vendors that a purchase
order will be cut to. When I select a vendor, I would like my form to
look
at a different table [tblVENDOR] for the information to automatically
populate the next 4 text boxes.
ADDR_ST
ADDR_CITY
PHONE
FAX

I would like to use the After Update event for my combo box [VENDORNAME].
What code would I use for this?

Thank you very much!!!

 

[John W. Vinson]

The selection of vendors that appear in my combo box is coming from a "temp"
table and is not ALL the vendors that we do business with. It would only be
the vendors that we currently have a req open for. So, the selection might
be 1-10 (give or take). It is coming from a table called [tblBUY] with a
select query

Row Source Type: Table/Query
Row Source: SELECT tblBUY.VENDORNAME FROM tblBUY;

This table [tblBUY] does not have the address/phone/fax information. That
information is stored in [tblVENDOR].

So base the combo box on a query that DOES contain that information! Just join
tblBuy to tblVendor, by VendorID (if it's included, which it should be) or if
need by by VENDORNAME. For the latter (actually to reliably get the address
information at all), VENDORNAME should be uniquely indexed in tblVendor.

 

[kealaz]

Hi John,

There is a relationship between the two tables. I have tried the following.

SELECT tblBUY.VENDORNAME, tblVENDOR.ADDR_ST, tblVENDOR.ADDR_CITY,
tblVENDOR.PHONE, tblVENDOR.FAX FROM tblBUY GROUP BY tblBuy.VENDORNAME;

AND I've tried...

SELECT tblBUY.VENDORNAME FROM tblBUY SELECT tblVENDOR.ADDR_ST,
tblVENDOR.ADDR_CITY, tblVENDOR.PHONE, tblVENDOR.FAX FROM tblVENDOR GROUP BY
tblBuy.VENDORNAME;

Both of these are not working. I know there is something wrong with the
syntax.

Can you help me with this query? I am putting this code into the Row Source
in the properties for the Combo box VENDORNAME.

Thank you so much!!!

The selection of vendors that appear in my combo box is coming from a "temp"
table and is not ALL the vendors that we do business with. It would only be
the vendors that we currently have a req open for. So, the selection might
be 1-10 (give or take). It is coming from a table called [tblBUY] with a
select query

Row Source Type: Table/Query
Row Source: SELECT tblBUY.VENDORNAME FROM tblBUY;

This table [tblBUY] does not have the address/phone/fax information. That
information is stored in [tblVENDOR].

So base the combo box on a query that DOES contain that information! Just join
tblBuy to tblVendor, by VendorID (if it's included, which it should be) or if
need by by VENDORNAME. For the latter (actually to reliably get the address
information at all), VENDORNAME should be uniquely indexed in tblVendor.

 

[kealaz]

Okay, I got the following to work

SELECT tblVENDOR.VENDORNAME, tblVENDOR.ADDR_ST, tblVENDOR.ADDR_CITY,
tblVENDOR.PHONE, tblVENDOR.FAX FROM tblVENDOR INNER JOIN tblBUY ON
tblVENDOR.VENDORNAME=tblBUY.VENDORNAME;

BUT, I had to take out "GROUP BY tblBUY.VENDORNAME" which I had in there so
that I would get only one of each VENDORNAME. When I tried....

SELECT tblVENDOR.VENDORNAME, tblVENDOR.ADDR_ST, tblVENDOR.ADDR_CITY,
tblVENDOR.PHONE, tblVENDOR.FAX FROM tblVENDOR INNER JOIN tblBUY ON
tblVENDOR.VENDORNAME=tblBUY.VENDORNAME GROUP BY tblBUY.VENDORNAME;

I get the following error message.
You tried to execute a query that does not include the specified expression
"VENDORNAME" as part of an aggregate function.



Can you help me with the syntax on this?


THANK YOU VERY MUCH!!!!!

 

[kealaz]

Got it!

SELECT DISTINCT (tblVENDOR.VENDORNAME), tblVENDOR.ADDR_ST,
tblVENDOR.ADDR_CITY, tblVENDOR.PHONE, tblVENDOR.FAX FROM tblVENDOR INNER JOIN
tblBUY ON tblVENDOR.VENDORNAME=tblBUY.VENDORNAME;


Thanks for your help!