Combobox mess - I'm an Access MORON - HELP!

[Jeff]

I think I'm about to go NUTS! I have tried and Tried and TRIED to figure out
my problem and have spent at least a GaBazillion $$$ on every book ever
published on Access but still can't solve this problem - I'm sure there's a
simple answer but I'm hopelessly stuck. L For simplicity's sake, here's an
example of what I'm trying to do:



I have two tables (actually, my full database has a lot more) - tblPeople
and tblSalutations, these look like this:



tblPeople contains:



PeopleID (PK)

LastName

FirstName

SalutationID (FK)





tlbSalutation contains:



SalutationID (PK)

SalutationName





SalutationName contains values such as Mr., Ms., Miss, Mrs., etc.



What I want to do is to create a form where I can enter new people into
tblPeople and selecte the correct salutation from a combo box that looks up
all of the values in the tblSaluations.



Now for the problem - I can do this using the "lookup wizard" but I have
read, in many places on the 'net the doing so can create many other
problems - essentially, the "lookup wizard" is "evil." Therefore, I would
like to do the same thing using some other method but I can't find any
examples that tell me, specifically, how to do this.



I'm afraid I'm a complete Access moron and would like to find an example
that would "hold my hand" through the complete process. My actual database
is much more complex than just this example but I'm REALLY stuck on this
problem.



Thanks for your help.

 

[Jeff]

Well, I can't tell you how many days/weeks/months I've been trying to figure
out how to solve the mystery of avoiding the "evil" lookup wizard. (There
are sure a lot of warnings about it but essentially no real solutions.)
Wouldn't you know it, but just after I posted the original message in this
thread, I came across someone else's solution. That one, however, only had
six steps and I had to mess around with it in order to get it to work. So, I
wrote down my own version (which I'll keep in a SAFE place <grin>) and will
post it here.



In order to create a combo box without using the lookup wizard, here's how:





Assuming I have two tables:



tblPeople contains:

PeopleID (PK)
LastName
FirstName
SalutationID (FK)

tlbSalutation contains:

SalutationID (PK)
SalutationName





1.. Create form, based upon the main table (tblPeople) - this form
contains the FK (or "many" side in the 1:n relationship) of the related
table (tblSalutation) that contains the value to be looked up. It is okay to
use the Form Wizard for this part. Do not include the table to be looked up
on this form - only the main table.
2.. After form has been created, open the form in design view.
3.. Right click on box that contains the foreign key (SalutationID)>Change
to>Combo Box
4.. Right click on the same box (now appears as a combo box)>Properties
5.. Make sure that the Format tab has been selected
6.. Click on Column Count then enter "2" (without quotes) because the
tblSalutation table contains two columns.
7.. Click on Column Widths then enter "0,1" (without quotes) - this will
hide the first column (PK) of tblSalutation
8.. Now click on the Data tab
9.. Set Bound Column to "1" (without quotes)
10.. Click on Row Source >then "."
11.. The Query Builder window opens
12.. Select the table that contains the lookup values (tblSalutation),
click OK
13.. Double click on each attribute (field) - SalutationID (PK) &
SalutationName so that they appear on the query. Leave the "show" setting
set to checked on both of these items (the ID will not show up on the query)
14.. Close and save the query.
15.. Click OK on the next screen.
16.. The following should now appear in the Row Source box:
SELECT tblSalutations.SalutationID, tblSalutations.Salutation FROM
tblSalutations;



17.. Close and save the Combo Box properties window
18.. The form should now work! Yippee!