combo-box unbound column

  • Thread starter Thread starter Emidio
  • Start date Start date
E

Emidio

Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look-up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks
 
Hi Emidio,

It sound like ID# needs to be the bound column. To display the name field
just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column (zero based
index) of cboPatientID. Change cboPatientid to the name of your combo.
 
Hi Emidio,

Adding to what Sandra wrote, if you have more than one person with the same
name, I'd suggest that you either add one more column with something that
will visually differentiate them (such as a truncated telephone number --
last 4 digits), or concatenate that with the persons name in the second
column..

Willy
 
Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column(1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the name field
just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column (zero based
index) of cboPatientID. Change cboPatientid to the name of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks
Click to expand...

.
Click to expand...
 
Ok - but you can (and should) still leave the id field as the bound column,
just leave the name field as the first non-zero width column in your combo
and it will display in the control even though the ID column is the bound
one. You shouldn't try to build a related record off a non-unique column
from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column(1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the name
field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the name
of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks
Click to expand...

.
Click to expand...
Click to expand...
 
I tried your suggestion. the selected patient's record is
properly fetched but the NAME still does not display in
the combobox.
Any suggestions.

Thanks again,
Emidio
-----Original Message-----
Ok - but you can (and should) still leave the id field as the bound column,
just leave the name field as the first non-zero width column in your combo
and it will display in the control even though the ID column is the bound
one. You shouldn't try to build a related record off a non-unique column
from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column(1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the name
field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the name
of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Emidio wrote:
Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks

.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi Emidio,

What is your rowsource query for the combo? Also what do you have in the
ColumnWidths property and the Column Count property?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I tried your suggestion. the selected patient's record is
properly fetched but the NAME still does not display in
the combobox.
Any suggestions.

Thanks again,
Emidio
-----Original Message-----
Ok - but you can (and should) still leave the id field as the bound
column, just leave the name field as the first non-zero width column
in your combo and it will display in the control even though the ID
column is the bound one. You shouldn't try to build a related record
off a non-unique column from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column(1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the name
field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the name
of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks

.
Click to expand...

.
Click to expand...
Click to expand...
 
Hi, The rowsource query generates two columns of data:
patient names (col 1) and patient ID in col 2. the column
count is 2 and widths is 1";2", bound column is 2. the
control itself is unbound. Actually this does work but
when I leave the record I would like the control to
display the current patient name, so I added code to the
form's oncurrent event to assign the patient name to the
combobox. When I do this the combobox displays a null
whenever I go to a new record - I would like it to display
the patient name of the current record.

Thanks for your patience.
Emidio
-----Original Message-----
Hi Emidio,

What is your rowsource query for the combo? Also what do you have in the
ColumnWidths property and the Column Count property?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I tried your suggestion. the selected patient's record is
properly fetched but the NAME still does not display in
the combobox.
Any suggestions.

Thanks again,
Emidio
-----Original Message-----
Ok - but you can (and should) still leave the id field as the bound
column, just leave the name field as the first non- zero width column
in your combo and it will display in the control even though the ID
column is the bound one. You shouldn't try to build a related record
off a non-unique column from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Emidio wrote:
Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column(1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the name
field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the name
of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks

.

.
Click to expand...
Click to expand...

.
Click to expand...
 
Ok, everything looks fine to me. Then only thing I see is that if you want
the combo to always reflect what is in the current record you need to assign
the patientId of the current record to the combo (rather than the patient
name) since PatientId is the bound column. Try that and let me know if that
solves the problem.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi, The rowsource query generates two columns of data:
patient names (col 1) and patient ID in col 2. the column
count is 2 and widths is 1";2", bound column is 2. the
control itself is unbound. Actually this does work but
when I leave the record I would like the control to
display the current patient name, so I added code to the
form's oncurrent event to assign the patient name to the
combobox. When I do this the combobox displays a null
whenever I go to a new record - I would like it to display
the patient name of the current record.

Thanks for your patience.
Emidio
-----Original Message-----
Hi Emidio,

What is your rowsource query for the combo? Also what do you have in
the ColumnWidths property and the Column Count property?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I tried your suggestion. the selected patient's record is
properly fetched but the NAME still does not display in
the combobox.
Any suggestions.

Thanks again,
Emidio

-----Original Message-----
Ok - but you can (and should) still leave the id field as the bound
column, just leave the name field as the first non- zero width
column in your combo and it will display in the control even
though the ID column is the bound one. You shouldn't try to build
a related record off a non-unique column from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column(1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the
name field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the
name of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks

.

.
Click to expand...

.
Click to expand...
Click to expand...
 
Hey it works!
Sandra you're a genius.
My deepest thanks for your help and persistence.
Emidio
-----Original Message-----
Ok, everything looks fine to me. Then only thing I see is that if you want
the combo to always reflect what is in the current record you need to assign
the patientId of the current record to the combo (rather than the patient
name) since PatientId is the bound column. Try that and let me know if that
solves the problem.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi, The rowsource query generates two columns of data:
patient names (col 1) and patient ID in col 2. the column
count is 2 and widths is 1";2", bound column is 2. the
control itself is unbound. Actually this does work but
when I leave the record I would like the control to
display the current patient name, so I added code to the
form's oncurrent event to assign the patient name to the
combobox. When I do this the combobox displays a null
whenever I go to a new record - I would like it to display
the patient name of the current record.

Thanks for your patience.
Emidio
-----Original Message-----
Hi Emidio,

What is your rowsource query for the combo? Also what do you have in
the ColumnWidths property and the Column Count property?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Emidio wrote:
I tried your suggestion. the selected patient's record is
properly fetched but the NAME still does not display in
the combobox.
Any suggestions.

Thanks again,
Emidio

-----Original Message-----
Ok - but you can (and should) still leave the id field as the bound
column, just leave the name field as the first non- zero width
column in your combo and it will display in the control even
though the ID column is the bound one. You shouldn't try to build
a related record off a non-unique column from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column (1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the
name field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the
name of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks

.

.

.
Click to expand...
Click to expand...

.
Click to expand...
 
No problem, glad you've got it working the way you want it now :-)

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hey it works!
Sandra you're a genius.
My deepest thanks for your help and persistence.
Emidio
-----Original Message-----
Ok, everything looks fine to me. Then only thing I see is that if
you want the combo to always reflect what is in the current record
you need to assign the patientId of the current record to the combo
(rather than the patient name) since PatientId is the bound column.
Try that and let me know if that solves the problem.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi, The rowsource query generates two columns of data:
patient names (col 1) and patient ID in col 2. the column
count is 2 and widths is 1";2", bound column is 2. the
control itself is unbound. Actually this does work but
when I leave the record I would like the control to
display the current patient name, so I added code to the
form's oncurrent event to assign the patient name to the
combobox. When I do this the combobox displays a null
whenever I go to a new record - I would like it to display
the patient name of the current record.

Thanks for your patience.
Emidio

-----Original Message-----
Hi Emidio,

What is your rowsource query for the combo? Also what do you have
in the ColumnWidths property and the Column Count property?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
I tried your suggestion. the selected patient's record is
properly fetched but the NAME still does not display in
the combobox.
Any suggestions.

Thanks again,
Emidio

-----Original Message-----
Ok - but you can (and should) still leave the id field as the
bound column, just leave the name field as the first non- zero
width column in your combo and it will display in the control
even though the ID column is the bound one. You shouldn't try to
build a related record off a non-unique column from the combo.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Thanks, but a second textbox is exactly what I wanted to
avoid. I want one textbox to display the name but when the
user selects from the dropdown list I want to access the
record having the ID# listed in the second column.
I tried to use "docmd.findrecord combobox.column (1)" but
it still returns always the record of the first listed
duplicate name in the dropdown list.

Thanks for your help
Emidio
-----Original Message-----
Hi Emidio,

It sound like ID# needs to be the bound column. To display the
name field just add a new textbox and in the ControlSource put:

=cboPatientId.column(1)

The new textbox will always display the value in the 2nd column
(zero based index) of cboPatientID. Change cboPatientid to the
name of your combo.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Emidio wrote:
Hi all,
Is there a way to get the value of the "unbound" column in
a two column dropdown list? I have a combo-box to look- up
patient records based on patient names (bound column) and
unique id #'s (unbound column). The problem is when there
are many patients with the same name and the user picks
other than the first name in the list, access always
retrieves the first record. I could make the ID# the bound
column but then the patient name is not displayed when the
box is not opened and I want the box to display patient
name.

Thanks

.

.

.
Click to expand...

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