Newbie Form/Dropdown question

[jfhspike]

I have a table ("Person") with birth date information: month, day, year, all separate, because any one or more might be "unknown". I store "unknown" as a -1; for known values, I use a conventional encoding (jan = 1, feb = 2, etc.).

I have a form for the "Person" table that lets me enter data for a person.

I want the user to enter the birth date information on the form. If I just put up a text-box for the "month" field, it says either "-1" or "6" (for June), etc. That means that the user has to know that "-1" means "unknown", etc., which is disgusting. It also means that the user can enter "13" for the month, and I have to check it via validation, etc.

What I'd like to have is a dropdown that has the following on it:
Unknown
Jan
Feb
Mar
...
Dec
1
2
3
...
12

so that the user can select any one of these, either with the mouse, or by typing (some users like month-names, some like numbers!), but can ONLY select one of these.

My thought was to have a linked table, MonthDictionary, that looks like this:

Name value
Unknown -1
Jan 1
Feb 2
...
1 1
2 2
...
12 12

where the "name" field is a String, and the value field is a Number.

What I need:

1. To use the MonthDictionary to populate the drop-down menu
2. To make the user's selection end up in the "Month" field of the Person table record.

What I can't figure out:
* should I make a "relationship" between "month" and "month dictionary" in the "relationships" for the database? If so, I assume it should be one-to-many [one 'month dictionary' item corresponds to the months of MANY people's birth].

* How do I get the selected month-dictionary-name to generate an associated month that gets entered into the "month" field of the person?

* How to I make the field controlled by the drop-down allow "selection by typing" (so that if the user types "Ja" s/he gets "January"), but NOT allow anything except choices that are in the drop-down list?

Thanks in advance for any help you can give.

--John

 

[Dairy Farmer]

How about this?

 

[jfhspike]

How about this?

Thanks...that's a good first step. (For those who don't want to open the database, it shows a drop-down combo-box with (-1, unknown), (1, jan), (2, feb), ..., (12, dec) all listed; when I select one, the corresponding number (-1, 1, ..., 12) gets put into the Month field of Person.)

It doesn't, however, let me type EITHER "1" or "January" or "Ja" at the combo-box and get the month I want. (I can type numbers, but not names.) [There's an implicit problem with wanting both "January" and "1" as names for the first month: the relationship between the monthDictionary and the month field of the Person table becomes many-to-many. So when an already-entered person is displayed, should "5" show up, or "May"? To be honest, I don't care WHICH shows up. But the ambiguity may make this problem harder than I thought. In particular, your "T_Month" table has the MonthNumber set to be "Indexed", which isn't possible if I want both "January" and "1" to be allowed as month-names.]

The other problem is that while the example you sent gives me an example of a database in which the basic functionality I asked about is implemented, it doesn't really tell me how I could replicate it. (I did learn that I can use the "LimitToList" property on a combo-box to avoid bad data -- thanks! I should have figured that out, but somehow never noticed it when I scanned through the properties.)

I don't mean to sound ungrateful -- I just want to ask others if they can perhaps give me a little more information about METHOD rather than just "solution." (Even a pointer to something to read would be great!)

Thanks!
-John

 

[Dairy Farmer]

Create a table T_Person.
Create all the fields you would like, but leave all all but the basic formating out. i.e. create the a PK (Primary Key), but leave things like Default Values, Validation,... out for now. Do them later when you have created all the tables.
Now look at what FK's (Foreign Keys) you want for some of the fields. i.e. DOBMonth
Create Tables for those FK's. i.e. T_Months
Now you can "link" them.
In this case I wanted a dropdown list for DOBMonth. Open the T_Person table in design view. For the data type select Lookup. Follow the prompts.
T_Months has 0 -12 as MonthNumber (PK) and Jan - Dec as MonthName. When creating the Lookup I told it to show the PK.
Now the tables are linked.
Next create a form based on T_Person. Use the wizards and a Combo Box is created for DOBMonth using the PK (numbers).
Then, in form design, goto Properties, Data, Record Source and build a query.
Make sure that both tables are included and that MonthNumber has a relationshi to DOBMonth. There should be a line that connects then. This is the relationship.
If it is not there then drag MonthNumber onto DOBMonth and it is created.
Add all fields to your query and close.
Lastly, on the form add the field MonthName. Now when you select 3 it will show March. A good idea would be to disable and lock the text box MonthName so that the user does not try to enter data into it.

 

[jfhspike]

Create a table T_Person.
Create all the fields you would like, ...

Thanks much!

-John