Access 2007 - Combo Box with multi columns updating a field

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

Guest

I'm using Access 2007 and running into a problem with the combo boxes. I have
a combo box on form B that has three columns ID, First Name, Last Name from
table A. I'm displaying the First Name and Last Name and want to update one
field in table B with both the First Name and Last Name that is selected.
Currently everything is working fine except that the selection is only
updating the field in table B with the First Name and not the First Name and
Last Name. I know I could solve this by creating one field to store both
First and Last and using that field in the combo box but that is not an
option at this time. Any ideas?
 
Store the ID, not the first name or last name.

You should have a relationship between Table A and Table B based on the
ClientID field, so that is the field you should be storing. Since the
ClientID is unique, there is no ambiguity. Even if 2 clients have exactly
the same name, they have a different ID.
 
I am. The Name field in Table B is a number field that is only storing the ID
from Table A. Somehow though it is only displaying the First Name field
through that relationship and not the First Name and Last Name field from the
relationship.
 
Take a look at the RowSource property of the combo.

It is probably something like this:
SELECT ID, [First Name], [Last Name] FROM Table A;

Change it to:
SELECT ID, [First Name] & " " & [Last Name] AS FullName FROM Table A;

You can probably click the Build button (...) beside this property to get
the Query Designer to build this for you.

Once you get that working as you expect, you probably want to change these
properties as well:
Column Count 2
Column Widths 0
If you do want to see the ID number, set the Column Widths to (say):
0.5", 2"
and the ListWidth to:
2.7"
which is 2.5 + 0.2 for the scrollbar.
 
Ok, I tried what you suggested and I'm starting to wonder if it isn't a bug.
I've included my sql below for you. Now, after making a selection from the
drop down it is displaying the last name only and storing the first name. The
reason I'm working so hard on this is that I use this same thing in several
other places. Your continuing help would be appreciated.

SELECT Agent.ID, Agent.[First Name] & " " & Agent.[Last Name] AS FullName
FROM Agent
ORDER BY Agent.[Last Name], Agent.[First Name];


Allen Browne said:
Take a look at the RowSource property of the combo.

It is probably something like this:
SELECT ID, [First Name], [Last Name] FROM Table A;

Change it to:
SELECT ID, [First Name] & " " & [Last Name] AS FullName FROM Table A;

You can probably click the Build button (...) beside this property to get
the Query Designer to build this for you.

Once you get that working as you expect, you probably want to change these
properties as well:
Column Count 2
Column Widths 0
If you do want to see the ID number, set the Column Widths to (say):
0.5", 2"
and the ListWidth to:
2.7"
which is 2.5 + 0.2 for the scrollbar.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Todd M Heflin said:
I am. The Name field in Table B is a number field that is only storing the
ID
from Table A. Somehow though it is only displaying the First Name field
through that relationship and not the First Name and Last Name field from
the
relationship.
Click to expand...
Click to expand...
 
The RowSource looks right (without actually seeing your table/field names).

What other properties are set for this combo, particularly:
Column Count
Bound Column
Column Widths
List Width

Where is this combo? Is this in table design?
Or is this the combo on your form? (They can be different.)

I doubt that it is a bug, but if you have the combo in your table, it can be
confusing as explained here:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Todd M Heflin said:
Ok, I tried what you suggested and I'm starting to wonder if it isn't a
bug.
I've included my sql below for you. Now, after making a selection from the
drop down it is displaying the last name only and storing the first name.
The
reason I'm working so hard on this is that I use this same thing in
several
other places. Your continuing help would be appreciated.

SELECT Agent.ID, Agent.[First Name] & " " & Agent.[Last Name] AS FullName
FROM Agent
ORDER BY Agent.[Last Name], Agent.[First Name];


Allen Browne said:
Take a look at the RowSource property of the combo.

It is probably something like this:
SELECT ID, [First Name], [Last Name] FROM Table A;

Change it to:
SELECT ID, [First Name] & " " & [Last Name] AS FullName FROM Table A;

You can probably click the Build button (...) beside this property to get
the Query Designer to build this for you.

Once you get that working as you expect, you probably want to change
these
properties as well:
Column Count 2
Column Widths 0
If you do want to see the ID number, set the Column Widths to (say):
0.5", 2"
and the ListWidth to:
2.7"
which is 2.5 + 0.2 for the scrollbar.

Todd M Heflin said:
I am. The Name field in Table B is a number field that is only storing
the
ID
from Table A. Somehow though it is only displaying the First Name field
through that relationship and not the First Name and Last Name field
from
the
relationship.

:

Store the ID, not the first name or last name.

You should have a relationship between Table A and Table B based on
the
ClientID field, so that is the field you should be storing. Since the
ClientID is unique, there is no ambiguity. Even if 2 clients have
exactly
the same name, they have a different ID.

message
I'm using Access 2007 and running into a problem with the combo
boxes.
I
have
a combo box on form B that has three columns ID, First Name, Last
Name
from
table A. I'm displaying the First Name and Last Name and want to
update
one
field in table B with both the First Name and Last Name that is
selected.
Currently everything is working fine except that the selection is
only
updating the field in table B with the First Name and not the First
Name
and
Last Name. I know I could solve this by creating one field to store
both
First and Last and using that field in the combo box but that is not
an
option at this time. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top