Lookup question

[Nicolette]

In my access class I took the Prof said to create a table with the states in it and give each state an ID number, store the number in the fields and use a query and relationships to she the abbrevation when needed like in forms and such but when I try to make the relationships it makes a table called States_1 for the shippingstate and uses the states table for the billing state. it does this also with the shipping and billing country fields and the coutry table. What did I miss in the lesson? The dropdown box always shows the StateID not the abbrevations. but i read that this will actually make the database larger and is unneeded and it will some how affect the databases ability to sort and create reports. so am i better off simply typing in the state and country?

 

[PNGBill]

Don't Type the State.

The table is just a text field.

The form Text Box Control can include formatting to cover such things as State, tel Number etc.

Should you wish to have the Form Control as a List Box, then you can either have the datasource a query, a table or even just a value list.
Value lists are good where the options are limited and don't change,eg Mr, Mrs, Miss & Ms.
For States, you could well have a StateTable. If you use a Primary Key and Have a Number then you have two fields in the Table.

When you setup your Form's Control you need to be aware the first field is Zero and the 2nd is One.

This may explain why it doesn't work.

You also need to set the widths of the display in your List Box. This is all done in the Form's Control properties.

 

[AccessBlaster]

Hi Nicolette,

See if this sample is close to your needs.


Regards,


Richard

 

[G37Sam]

In my access class I took the Prof said to create a table with the states in it

But your prof didn't say you can get help from the Access Forums did he?

 

[Nicolette]

But your prof didn't say you can get help from the Access Forums did he?

LOL I took the class last semester

 

[ConneXionLost]

A solution similar to AccessBlaster's but with a different arrangement/structure.

Cheers,

 

[boblarson]

A solution similar to AccessBlaster's but with a different arrangement/structure.

Cheers,

A good example EXCEPT with one very BIG no, no. Do NOT use lookups at table level, even on this. It will only make things a big pain when trying to actually use it and especially with reports/queries.

Just a reminder about why it is not good to use lookups at table level.

 

[Nicolette]

I had heard that but I was told again it was really ok my concern is the ability to use in reports and such

 

[boblarson]

I had heard that but I was told again it was really ok my concern is the ability to use in reports and such

If you read the link I provided you'll see why it can be a pain. I would not use them at table level. You can use them at FORM level (with combo boxes and list boxes) but don't use them at table level. Users should not be working in the tables anyway so at form level is where they should be, so it is a natural place for them and then they don't get all wonky on you when you go to pull data out and report on it.

 

[AccessBlaster]

Hi Bob,

I’m always a little confused on this topic.

Is it ok to have a lookup table that is linked with a relationship as long as you don't start at the table level?

And is it ok to have a State field in your lookup table, and a State field in your main table like in my example?


Thanks

 

[vbaInet]

There are lookup fields, but not lookup tables. In your case you implemented your relationships without lookups (which is a good thing). Lookup fields are where you see a drop-down box when a field gets focus on a table. Those fields act as combo boxes.

 

[Nicolette]

see a drop-down box when a field gets focus on a table. Those fields act as combo boxes.

so those are the ones to avoid?

 

[vbaInet]

so those are the ones to avoid?

Yes. Ensure that on the Lookup tab of your field (in the table view), the Display Control property is set as Text Box and not any of the other two options. Create your relationships manually via the Relationships view.

 

[Nicolette]

but they are ok to use in forms?

 

[PNGBill]

Yes, have your Lookup in the Form rather then the Table.

On the surface, to the user, no difference.

 

[vbaInet]

If you're talking about using combo boxes in forms then yes they are perfectly fine to use. You simply avoid them in table level.

 

[Nicolette]

all righty got it thanks

 

[Nicolette]

ok i'm still having problems, I trying to use this in a form.

I have tblStates fields in order are:
StateID (PK)-autonumber
ShortState-text(abbreviations)
State-text(entire state name)

my form is setup as follows:
Control Source : BillingState
Row Source: tblStates
Row Source type: Table/query
Bound Column: 1 (i was told the numbers start with 0 so first column would be 0 snd column would be 1 etc)

I have played with the numbers in the Bound Column and all i keep getting in the dropdown in the form is the numbers from StateID

 

[G37Sam]

Set bound column to 1
Set column count to 3, then column widths to 1";1";1"
List width to 3"

 

[Nicolette]

Sweet you rock!!!!!!