Using a combo box to enter data into a table via datasheet

  • Thread starter Thread starter marysurfer
  • Start date Start date
M

marysurfer

Hi there,
I have a field in my table which I am trying to populate from a Combo
Box. The Combo box is populated from a table which has 2 columns - one
a code (the key) and the other a description related to the code. The
Combo Box appears fine when I am trying to enter data into the said
table field (I am in Datasheet view), but it is picking up the wrong
column from the Combo Box - I want column 2 (the description), but I
get column 1 (the code) no matter what I set the "Bound Column" to be
for the table field.
Can some one advise?!?
Thanks.
 
Hi there,
I have a field in my table which I am trying to populate from a Combo
Box. The Combo box is populated from a table which has 2 columns - one
a code (the key) and the other a description related to the code. The
Combo Box appears fine when I am trying to enter data into the said
table field (I am in Datasheet view), but it is picking up the wrong
column from the Combo Box - I want column 2 (the description), but I
get column 1 (the code) no matter what I set the "Bound Column" to be
for the table field.
Can some one advise?!?
Thanks.
Click to expand...

Change the bound column number in the combo's properties box to 2.

HTH - Keith.
www.keithwilby.com
 
I have done that and I still get Col 1being selected.
You do mean bound column unver the "Lookup" tab from which you select
"Display Control" to be Combo Box; or is there somewhere else I should
be doing this.
PS: I have used this same Combo Box to pupulate another field in the
table. I did want column 1 in that case. Would that effect things?
Thanks.
 
I have done that and I still get Col 1being selected.
You do mean bound column unver the "Lookup" tab from which you select
"Display Control" to be Combo Box; or is there somewhere else I should
be doing this.
Click to expand...

The "bound column" property is on the "data" properties tab.
PS: I have used this same Combo Box to pupulate another field in the
table. I did want column 1 in that case. Would that effect things?
Thanks.
Click to expand...

Yes but if you're populating another field with column 1 data then you don't
need to store column 2 data, you can get it for free by including the
combo's row source table in the form's query and using an outer join to it.
For example, if you have your two tables like this:

tblForm:

Field1
Field2
fldCombo

tblCombo:

ID
Description

you'd include both tables in the form's query and outer join between
fldCombo and ID so that all records in tblForm are loaded by the form. I
haven't explained this too well but I hope you get the gist.

Regards,
Keith.
 
Thanks for all your help so far. The thing is I am not using a Form. Am
I barking up the wrong tree. Are these things only possible if you are
using Forms? I just have a table in datasheet view, using another table
as the source of a Combo Box.
Yes I am new to Access.
Thanks.
 
Thanks for all your help so far. The thing is I am not using a Form. Am
I barking up the wrong tree. Are these things only possible if you are
using Forms? I just have a table in datasheet view, using another table
as the source of a Combo Box.
Yes I am new to Access.
Thanks.
Click to expand...

All my previous replies assumed you were using a form, my apologies, I
should not have made that assumption.

You should not use tables directly to manipulate data, you should use a
form. A table is a bucket for your data, nothing more. You should also not
use lookups in tables, they are evil and must be stopped ;-) Instead, use
combo boxes on your forms and use the lookup table as the combo's row
source. You'll find this quite straightforward once you've familiarised
yourself with the concept.

Regards,
Keith.
 
Thanks for your help. I will do this. It does look the way to go. I'll
have to read up!
Thanks again.
 
Thanks for your help. I will do this. It does look the way to go. I'll
have to read up!
Thanks again.
Click to expand...

My pleasure. Post back when you're ready for more info.

Regards,
Keith.
 
Sorry to be back so soon!
I have done the join query. I generated a form using this query as the
form's "Record Source", but not sure how to get the content of
Decsription feild into the fldCombo feild (using your scenario below).
I think I need to good text book as using Access on the fly is proving
more complicated than I thought!

tblForm:
Field1
Field2
fldCombo


tblCombo:
ID
Description
 
Sorry to be back so soon!
I have done the join query. I generated a form using this query as the
form's "Record Source", but not sure how to get the content of
Decsription feild into the fldCombo feild (using your scenario below).
I think I need to good text book as using Access on the fly is proving
more complicated than I thought!
Click to expand...

A picture is worth a thousand words, search the help for queries :-)

Regards,
Keith.
 
There are two things involved in showing data with combo boxes.

The query can have as many columns as you want.
The bound column is the data that will be stored in the field that the
combo is bound to. This property is on the Data Tab.

The number of columns on the format tab tells the control how many
columns are involved in the query.
The column widths are what will tell the control what column you SEE in
the box even though the data stored may be a different column. If in
your query the ID is the first and the description is the second column
then the col widths should be something like 0.0";1.0"
This will allow the description to show but at the same time save the
ID field. That way, if you change the description for an item then the
next time the description is shown the new description will be used
since it is the ID that was saved in the record not the actual
description.

Ron
 
Back
Top