*#$&@%^ combo box in a form

[RoyVidar]

I think I'm chasing my tail here.
I simply cannot find "record source" anywhere when I call up the
property box for that field... not under "data source" tab, not
under "all" tab. I must be a doofus or something.

When using the dropdown found in the controlsource property of the
combo box, you may select the field in the forms underlying
recordsource
you wish to bind the combo to.

 

[Rick Brandt]

I think I'm chasing my tail here.
I simply cannot find "record source" anywhere when I call up the
property box for that field... not under "data source" tab, not under
"all" tab. I must be a doofus or something.

The property upi are looking for is ControlSource. In that you choose one
of the fields from your RecordSource (which is a property of the form).

 

[Guest]

When I do as you suggest, all that shows up is "3" or "5"... the numeric
value of the foreign key or primary key (depending on whether I try to pull
this from the Contacts table or the Contacts Type table). Or, conversely, if
I play around with it until the text shows rather than the numeric key, every
single contact gets changed to the same contact type when I make a change in
the contact type for a given record.

 

[Rick Brandt]

When I do as you suggest, all that shows up is "3" or "5"... the
numeric value of the foreign key or primary key (depending on whether
I try to pull this from the Contacts table or the Contacts Type
table). Or, conversely, if I play around with it until the text shows
rather than the numeric key, every single contact gets changed to the
same contact type when I make a change in the contact type for a
given record.

Let's return to basics here.

The RowSource controls what is in the the drop-down list. It can have one
or more columns. If it has more than one column then you also have to set
the ColumnCount property of the ComboBox to match. So if your RowSource
query returns both the ID and the Text and your ColumnCount is set to 2 and
you have set ColumnWidths to 1";1" and you have the ListWidth property set
to 2" then you should see both the ID and the Text in the drop down list
(each column will be one inch wide). After making a selection you will see
the ID number (we will change that in a minute).

Now the ControlSource should be set to the field where you want your
selection stored in the form's RecordSource. Normally this would be a
numeric ID field and you would store the ID, not the text. Setting the
BoundColumn property of your ComboBox to 1 will cause the ID column to be
the one that is saved.

You should now find that when you move to a record and make a selection in
the ComboBox that the number value of the chosen ID is stored in that record
of the table (when you save the record).

When you have that, you can then change the ColumnWidths property of the
ComboBox from 1";1" to 0";2". That will hide the ID column when the list is
dropped and will cause the undropped ComboBox to show the Text value instead
of the numeric ID. The VALUE of the ComboBox will still be the numeric ID
value, but you will see the text value.

 

[Guest]

I give up. I absolutely can't get the flipping thing to work.

Thanks, all, for trying - I must be doofus for sure.

 

[Corey-g via AccessMonster.com]

Hi Sue,

I completely understand your frustrations with Access, but giving up isn't
the answer...

First of all, you need to look at this the right way. You have multiple
thinggs going on, and your confusing them. You have tables (only 2, but
still you have them), and form(s), and then controls.

So, a form can be based on a table, and the controls on the form can be based
on other tables, or queries, or recordsets, and the list goes on.

So, you are having an issue with a control on a form. The previous posters
all discussed how to set the properties for these various components, but you
missed how / what they were talking about. Lets see if we can clear it up a
bit. So, you need to set the "Record Source" for the form. When looking at
a form in design view, you can tell what contol properties you are looking at
by what is listed in the drop-down box at the top of the properties window.
So, find the one named form, and the top item (on the 'All' tab) is
recordsource. If you want to work with data from your Contacts table, this
should be set to that table.

Now, when you place a combo box onto the form, it has a couple important
properties. The first one is 'Control Source' - this is what field in
the underlying table you want this control to modify / view / etc... (I
beleive you called it Contact Type in a previous post) The second one is the
recordsource. This one is used to populate the values that are allowed in
the table (In your case, you want the combo box to display the type, but the
primary key value - aka a number - to be what is stored in the field in the
table. Hope I haven't lost you yet...

So, you would want to make the 'RECORD SOURCE" property of the combo box the
lookup table (Contact Type). Do this by clicking in the field, then you
should see a drop down arrow on the right - and this should list all the
tables and queries in your database. Then set column count (right below) to
2, and the Column widths to 0;1.5 notice that this is zero (don't show the
primary key value), then a semi-colon, then a value in inches for how wide
you need so that you can see the text.

So, as long as the Form has it's record source property set to the table
(contacts), and the 'Row Source' of the combo box set to the Contact Type
table, everything should work as you want...

HTH (Hope That Helps)

Corey

 

[Guest]

I appreciate your post - all of your posts - and your patience.

At the top of the "All" property box I have Name, then Control source, then
Format, then Decimal places, etc. There's a lengthy list of things under
"All", but not one of them is Record source.

My Control source is set to ContactType (taken from tblContact). But as God
is my witness, there is nothing titled Record source anywhere on the All tab
or on the Data tab of the dialog box for this control.

I'm using Access 2002. Surely this isn't so outdated that Record source
isn't there???

AAAAAAARRRRRRRRRGGGGGGGGGGHHHHHHHHHHHHH.

 

[Fred Boer]

Dear NC_SUE:

If you are willing to perservere, we won't give up on you! I think it might
help if you had a working example to look at. If you'd like, I'd be willing
to take your database and create a nice simple form with a combobox that
does what you'd like. That might give you just the boost you need to get
over this hump. If you like, you can send it to f r e d b o e r
@NOyahooSPAM.com (I deliberately messed up my email address to avoid
spammers grabbing it - just remove the spaces and the words "NO" and "SPAM"
to reveal the true address.)

I'm assuming it isn't too big for email (<10MB)...

Cheers!
Fred Boer

 

[Rick Brandt]

I appreciate your post - all of your posts - and your patience.

At the top of the "All" property box I have Name, then Control
source, then Format, then Decimal places, etc. There's a lengthy
list of things under "All", but not one of them is Record source.

My Control source is set to ContactType (taken from tblContact). But
as God is my witness, there is nothing titled Record source anywhere
on the All tab or on the Data tab of the dialog box for this control.

I'm using Access 2002. Surely this isn't so outdated that Record
source isn't there???

AAAAAAARRRRRRRRRGGGGGGGGGGHHHHHHHHHHHHH.

You are looking the properties of a Control. It will not have a RecordSource
property. The Form will have such a property though.

The property sheet shows the properties for the object currently having focus
when you are in design view. That object will be shown in the property sheet's
label bar. You are probably seeing ComboBox: NameOfComboBox.

If you click on the gray square in the extreme upper left of the form that will
give focus to the form and you will then see Form: in the top of the property
sheet and THEN you should see the RecordSource property on the data tab.

 

[Aussie Jeff]

Rick

Thanks for commenting on my use of tables to store lookup data. I am afraid
that I have been involved in a large commercial software package (EDRMS)
based on SQL Server and Oracle. Lookup tables were used extensively for
exactly that and also because lookup data (eg in my example Postcodes) can
be added to then tables are far better than hardcoding the choices. At some
sites, this system had GIGABYTES of data and well over 200 concurrent users.

My comments are absolutely valid and do not belong in the realms of a CD
collection table!

My other comment was to correct the statement that "Dropdowns belong only in
the forms - not in the tables." This is not true when it comes to Access as
you would well know...it is a powerful rapid application development feature
of Access to define dropdowns in the tables to facilitate form design.

As far as poor Sue is involved this is not helping her...obviously.

I am about to suggest that I send her a set of screen grabs to paint the
thousand words that only a picture can do!

This is obviously very long thread to solve what is a rather straight
forward issue for those who know..

cheers
Jeff

 

[Aussie Jeff]

Sue,

Send me an email to (e-mail address removed) (dont worry i get massive amounts of
spam at that address) or u can messenger me there as well. I can send you a
word doc with some screen grabs of the steps involved with some descriptive
passages in support.

I think most people follwing this thread know how to do all this by now...so
going offline will be easier to get you going!

hoping to solve your frustrations (AAAAARRRRGGGGGGHHHHH!!) asap!

cheers
Jeff