The RowSource of your Combo Box could have the "stateid" and "statename"
fields, in that order. If you set the bound field as the "stateid" field (in
the wizard or the ComboBox properties) but set its width to 0, the user will
see the state's name, but the stateid will be stored in your table.
To retrieve the state name with your data, you join the primary table to the
state table on stateid and pull down into the Query Builder grid the
statename from the state Table.
FYI, for "stateid" in my state lookup table, I use the USPS state code, eg.,
AK for Alaska, AR for Arkansas, ... TX for Texas, etc. That's because I want
to show it to the user and may want to use that field in some uses (e.g., an
address report or printing envelopes or mailing labels). If I had no need to
use the field, I could use a number and just not show it to the user (see
above) by setting the width in the combo to 0.
If you are even near-serious about developing database applications, invest
the time and energy to learn VBA code. If you are a "power user" wanting to
do so, I've observed that Dr. Rick Dobson's books on "Programming Microsoft
Access <versionnumber>" from Microsoft Press are useful -- that is one of
the particular audiences that he tried to address, and he succeeded in doing
so.
Larry Linson
Microsoft Access MVP
"my-wings" <my_wings@TAKEOUTatt.net> wrote in message
news:U6OMc.322203$Gx4.294533@bgtnsc04-news.ops.worldnet.att.net...[color=blue]
>
> "Larry Daugherty" <Larry.NoSpam.Daugherty@verizon.net> wrote in message
> news:FOIMc.3244$PK5.720@nwrddc02.gnilink.net...[color=green]
> > Did you follow the track to
www.mvps.org/access ? If not, it's worth a[/color]
> peek
>
> Yep. I actually googled the words "access lookup field evil" (without the
> quotes) and it's the top entry. All the reasons not to use lookup fields
> were there. But I still don't understand what to use instead.
>[color=green]
> >
> > Having a lookup table with the state codes is a great idea but you get[/color][/color]
at[color=blue]
> it[color=green]
> > with the query for your combobox on the form rather than having it[/color]
> percolate[color=green]
> > up as an artifact of a table. You have exactly the same access to your[/color]
> data[color=green]
> > and in exactly the same form.[/color]
>
> But this is the part I don't get. How does the data get from the combo box
> on the form back into your table? And what is recorded in the table? I
> still have this field called "state"....how does it get any information[/color]
into[color=blue]
> it, and what kind of information is there? I understand the solution is
> queries and combo boxes on forms, but I don't understand how things are
> hooked together if there is no lookup field in the table.
>[color=green]
> >
> > After more carefully re-reading your post, I have a feeling that you may
> > have confused Lookup Fields in tables with "lookup tables".[/color]
>
> No. I was referring to the Lookup Field you get in a table when you select
> Lookup Wizard from the data types.
>[color=green]
> > Lookup tables
> > are a good thing. They are just normal tables with some meaningful list
> > that is held for reference. I think you used the term Lookup Wizard[/color][/color]
when[color=blue][color=green]
> > you were actually referring to the Combobox Wizard.[/color]
>
> Nope. Was referring to the Lookup wizard available in table design, not[/color]
the[color=blue]
> one that creates combo boxes on forms.
>[color=green]
> > Lookup tables good; Lookup Fields BAD. All is well.[/color]
>
> Or all will be well when I figure out what to use instead of lookup[/color]
fields,[color=blue]
> lol.
>
> I appreciate your reply, but as you can see, I'm still puzzling over this.
> I'm working my way through a book called Microsoft Office Access 2003[/color]
Inside[color=blue]
> Out. I'm only on Chapter 5, so maybe things will clarify themselves as I
> proceed. Right now, it looks like in the places I would have put lookup
> fields, the author used the relationship builder to show a connection
> between the regular table and the lookup table. When I look at the tables
> with data, there are no drop down lists. Just the data itself.
>
> Excuse me a minute while I go build a quick form.....
>
> Back with an "ah ha!". Let's see if I have this right:
>
> 1. Create tblAddresses with a "state" field.
> 2. Create tlkpStates with its "state" field.
> 3. Go to Tools/Relationships and add both tables.
> 4. Drag the tblAddresses "state field" to tlkpStates "state field" and
> create a many-to-one relationship.
> 5. Create a form.
> 6. Use the combo box wizard to create a combo box from tlkpStates. At the
> appropriate screen, say that you want to store the result in tblAddresses
> "state" field.
>
> Then, as long as you use the form to enter things, the only things in the
> tblAddresses "states" field will be entries from tlkpStates. On the other
> hand, if someone were to enter directly in the table, there would be no
> protection that the entry exists in the tlkpStates. Which is why you can't
> let people enter things directly in tables, I'll bet.
>
> I hope I have this right finally. The above looks like what everyone has
> been saying, but without working my way through the combo box wizard, I
> didn't see where the "connection" was being made.
>
> I think I'm marginally less confused now, but hopefully someone will let[/color]
me[color=blue]
> know if I still don't understand this correctly.
>
> Thanks.
>
> Alice
>
>[/color]
