Newbie combo-box question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one table in the database, having company ID, name and address.. In a
form I wish to use a combo-box (or something similar) to select a specific
company and a text box should give the corresponding address. At this stage I
do not wish to change any of that data. How do I link the address-text-box to
the choice made by the combo.box?

I'm sure it's simple, but I just have no clue.

Johannes
 
First make sure that these related fields are included in the rowsource of
the combo and make sure that you increase the Column Count property to
include any new fields. Adjust the ColumnWidths property according to
whether you want to see the values when the combo is dropped down. Then for
each related field you want to display create a textbox. In the
ControlSource of the new textbox put

=me.MyCombo.column(3)

Then because the column property is indexed starting with 0, replace 3 with
the column number minus 1 of the field you want to display. IOW, the above
will cause the value from the 4th column to be displayed in the textbox.

Note that this is not the same thing as navigating to the record with the
matching company id. That is also pretty simple to do:

Here's what you need to accomplish the record navigation (make the record
with the key matching the selected value the current record). Create your
unbound listbox. Make sure the bound column of the list/combo is the primary
key field for your table. In the AfterUpdate event of the listbox you would
have the following code:

with me.recordsetclone
.findfirst "PrimaryKey=" & me.lstPrimaryKey
if not .nomatch then
me.bookmark=.bookmark
endif
end with

Replace "PrimaryKey" with the name of the primary key field in the forms
Recordsource and replace "lstPrimaryKey" with the name of the listbox
control.

If the Primary key field is text you will need to revise the findfirst line
to wrap the value coming from the unbound listbox in quotes. This is easily
done by putting a pair of quote characters everywhere a quote character
should appear. Note that there are 3 quotes (") before the first & and 4
quotes after the second &

..findfirst "PrimaryKey=""" & me.lstPrimaryKey & """"
 
Thanks, I think I can make this work. I tried to derive this from the
Northwind example (Orders - form, address) which I got with my Access
installation, but there it seems to work differently. Or not?

Is their a way to do this without writing code?

Thanks in any case,

Johannes

Sandra Daigle said:
First make sure that these related fields are included in the rowsource of
the combo and make sure that you increase the Column Count property to
include any new fields. Adjust the ColumnWidths property according to
whether you want to see the values when the combo is dropped down. Then for
each related field you want to display create a textbox. In the
ControlSource of the new textbox put

=me.MyCombo.column(3)

Then because the column property is indexed starting with 0, replace 3 with
the column number minus 1 of the field you want to display. IOW, the above
will cause the value from the 4th column to be displayed in the textbox.

Note that this is not the same thing as navigating to the record with the
matching company id. That is also pretty simple to do:

Here's what you need to accomplish the record navigation (make the record
with the key matching the selected value the current record). Create your
unbound listbox. Make sure the bound column of the list/combo is the primary
key field for your table. In the AfterUpdate event of the listbox you would
have the following code:

with me.recordsetclone
.findfirst "PrimaryKey=" & me.lstPrimaryKey
if not .nomatch then
me.bookmark=.bookmark
endif
end with

Replace "PrimaryKey" with the name of the primary key field in the forms
Recordsource and replace "lstPrimaryKey" with the name of the listbox
control.

If the Primary key field is text you will need to revise the findfirst line
to wrap the value coming from the unbound listbox in quotes. This is easily
done by putting a pair of quote characters everywhere a quote character
should appear. Note that there are 3 quotes (") before the first & and 4
quotes after the second &

..findfirst "PrimaryKey=""" & me.lstPrimaryKey & """"
--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have one table in the database, having company ID, name and
address.. In a form I wish to use a combo-box (or something similar)
to select a specific company and a text box should give the
corresponding address. At this stage I do not wish to change any of
that data. How do I link the address-text-box to the choice made by
the combo.box?

I'm sure it's simple, but I just have no clue.

Johannes
Click to expand...
Click to expand...
 
I believe that the Northwind example is done with an AutoLookup query.
An Autolookup query is fairly straightforward to create. Lets say you have
Customers and Orders and on your Orders form you want to select the CustId
and then display the related CustName and CustAddress fields. The Customers
table is on the 'one' side of the Customers->Orders relationship and Orders
is on the Many side. For an AutoLookup query to work, you must include the
linking field from the Many side of the relationship and then you would
include the related fields (CustName, Address) from the One side. Using the
query designer you would select both tables, join them on the Custid field
and then drag the Custid field from the Orders table onto the Query grid
along with the relevant fields on the Order table, then drag in any other
fields from the Customer table except the Custid field (you could include it
too but it is easier to leave it out).


You can test the Autolookup query using the datasheet view of the query -
you should be able to go to a new record, put in a valid Custid and
immediately see the related CustName and Address fields.


When you have this working you can use the query as the basis for your form.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks, I think I can make this work. I tried to derive this from the
Northwind example (Orders - form, address) which I got with my Access
installation, but there it seems to work differently. Or not?

Is their a way to do this without writing code?

Thanks in any case,

Johannes

Sandra Daigle said:
First make sure that these related fields are included in the
rowsource of the combo and make sure that you increase the Column
Count property to include any new fields. Adjust the ColumnWidths
property according to whether you want to see the values when the
combo is dropped down. Then for each related field you want to
display create a textbox. In the ControlSource of the new textbox put

=me.MyCombo.column(3)

Then because the column property is indexed starting with 0, replace
3 with the column number minus 1 of the field you want to display.
IOW, the above will cause the value from the 4th column to be
displayed in the textbox.

Note that this is not the same thing as navigating to the record
with the matching company id. That is also pretty simple to do:

Here's what you need to accomplish the record navigation (make the
record with the key matching the selected value the current record).
Create your unbound listbox. Make sure the bound column of the
list/combo is the primary key field for your table. In the
AfterUpdate event of the listbox you would have the following code:

with me.recordsetclone
.findfirst "PrimaryKey=" & me.lstPrimaryKey
if not .nomatch then
me.bookmark=.bookmark
endif
end with

Replace "PrimaryKey" with the name of the primary key field in the
forms Recordsource and replace "lstPrimaryKey" with the name of the
listbox control.

If the Primary key field is text you will need to revise the
findfirst line to wrap the value coming from the unbound listbox in
quotes. This is easily done by putting a pair of quote characters
everywhere a quote character should appear. Note that there are 3
quotes (") before the first & and 4 quotes after the second &

..findfirst "PrimaryKey=""" & me.lstPrimaryKey & """"
--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have one table in the database, having company ID, name and
address.. In a form I wish to use a combo-box (or something similar)
to select a specific company and a text box should give the
corresponding address. At this stage I do not wish to change any of
that data. How do I link the address-text-box to the choice made by
the combo.box?

I'm sure it's simple, but I just have no clue.

Johannes
Click to expand...
Click to expand...
Click to expand...
 
Back
Top