Combo Box Display

[ssaucedo]

I have a table (states) that has 3 three fields:

id
state name
abbreviation

the table consists of all the US states and there associated abbreviations.

my goal is to have two fields on a data entry form link to a table (invoices):

state
abbrv

the state field to be a combo box linked to the states table which will allow the user to select a state name from the drop down list in the combo box, and for the corresponding abbreviation to be auto-populated in the abbrv field.

and finally for the actual name/abbreviation to display on my report and the the id number that access really stores from the states table. how can i do this?

[RuralGuy]

To start with since you have the lookup table, all you need in the Invoices table is the ID for the record you want from the State table.

[ssaucedo]

ok, but my ultimate goal is to be able to break out the data for addresses on letters and envelopes, so how do i get there?

[RuralGuy]

Not a problem since it can easily be accomplished with a query. Define the relationship with the Relationship screen first. Then create a query of the Invoice table and add the State table to the query and you will see Access add the join. You can then add any fields you want from the State table and it is as if the fieldare all in one table. We can then take it to the next step.

[ssaucedo]

accomplished

[RuralGuy]

When you look at the query in Datasheet mode, if you change the StateID in a record you will see the other fields change to match it. The same thing can be done in a form with a ComboBox bound to the StateID field with a RowSource of a query of the State table. The cbo wizard can create that cbo for you.

[ssaucedo]

the field in my query displays what i want and even if i place the field on the report it displays what i want (not the id).

however, if i put the field name into a query expression, say for example i am stringing fields together to concatenate etc. when the expr is placed on the report the field displays the id and not the name/info.

[RuralGuy]

The query should include the fields you want to display from both tables and need not include the Join field if you do not display it.

[ssaucedo]

i think that i am really missing the boat on this, because i am so confused. the field works on the query, form and report. the field just does not display properly when in an expression and the expr is then linked to a report. sorry i am so dense.

[RuralGuy]

Are you creating your query for the report in the query builder and joining the State table to the query so you can display the State name or abreviation?

[ssaucedo]

no the state info has already been entered via a form and resides in the main invoice table.

i have a query "monthly billing" in which i pull in fields from 2 tables

contract
invoice

this query provides all the pieces of information needed for the invoice.

however, the state field which displays fine in the query, form and report does not display fine when i include it into an expression in the same query and link the expr to the report. the id # displays

[RuralGuy]

Add the State table to the "Monthly Billing" query and pull the State or Abbreviation field rather than the ID field. You will see it works just fine.

[ssaucedo]

[quote=RuralGuy;7398 Define the relationship with the Relationship screen first. [/quote]

ok i think i have a problem with the relationship, so i have my state lookup table and my invoice table.

so should i have a "stateid" field on both the state and invoice table?

[RuralGuy]

ok i think i have a problem with the relationship, so i have my state lookup table and my invoice table.

so should i have a "stateid" field on both the state and invoice table?

The "StateID" field should be the PrimaryKey field of the State table and should be a ForeignKey field in the other tables.

[ssaucedo]

The "StateID" field should be the PrimaryKey field of the State table and should be a ForeignKey field in the other tables.

tried that and kept getting mismatch expression at the query level