Dependent combo boxes and also unsure if need a sub-form???

[deanndra]

First, I want to say thank you to Scott and the others who replied to my first post here. I had to put that database on hold for the moment when I was tasked with a new one.

I am building another database from scratch. This one is for job announcements. I've built only 2 tables (I know this is a no-no, but it was demanded by those wanting this database so I've complied). The field name properties and data types in both tables are virtually identical, so I'll just post one table. *and I apologize for the lenth of this post!!!*

t_Army

• JA_armyID Autonumber
• Positions Number
• Title Text
• Para_Line Text
• AreaConsiderati on Text
• Location Text
• Unit Text
• DateRcvdJA Date/Time
• OpenDate Date/Time
• CloseDate Date/Time
• NumberAppsSent Number
• DateCertSent Date/Time
• DateCertRcvd Date/Time
• Selection Text
• EOD Date/Time
• Notes Memo

The other table name is t_Air with a different PK field name of JA_airID.

The reason the 2 are split up is because they do have different information that will later be added by the user in the form.

So, the first of 2 problems:
It is possible to have multi-positions come in for the same JA_ID (the PK). So if 5 positions come in, I need to be able to make 5 textboxes appear for the following fields:
Para_Line
Selection
EOD

After speaking to the computer guy here, he doesn't see how that's possible without a subform. It is desired for this to be a single-record form (and I believe that term to mean that one record displays at a time) but hopefully all that above information on the same record without having to click command buttons here, there and everywhere. And while it would be much easier to just create more field names in the event multiple positions are requested, that creates ALOT of used room for something not needed that often (5-10% of the time). Any suggestions on how to make that work?

Problem 2:
Dependent combo boxes. I have a good 150 pgs of research printed thus far and still can't find how. The desired scenario is as follows:

A combo box for data value "unit" in the form. This will bring up a drop-down list that states (for the ease of understanding) the following:
Units:
A
B
C
D
E
F
G
H

The problem is getting the dependent combo box control to work. For Units A-D, there are a variety of subunits. Subunits belonging to Unit A will not ever belong to another unit, same goes for ALL subunits. So again, for ease of understanding, Units and subunits together would look something like this:
A1
A11
A111
B2
B22
B222
C5
C5555

So when the user goes into Unit and clicks "A" within that drop-box, another drop-box will appear with the options of "1", "11", "111" etc. But nothing other than the 1's.

I mean, there are about 50 million OTHER demands that these people want, but these are the 2 largest problems which myself and the computer guy absolutely don't believe we can work out.

ANY ideas are appreciated! Ideas, suggestions, whatever! Thank you so much if anyone replies!

[deanndra]

I just realized that maybe I should have also put that this is with Access 2003. Sorry about that.

[seangibson]

I am also looking for a solution to problem #2. I found a useful link to another site which explained how do accomplish it, but it was written for Access 97 so either it doesn't work with Access 2003 or I'm doing something wrong when I attempt it. I'd certainly like to see a solution...

[deanndra]

I did find this link on someone else's post [http://www.thescripts. com/forum/thread605958.ht ml]. I'm attempting it as we speak. I hope it works because I've certainly put alot of time and effort into it!

[MMcCarthy]

I did find this link on someone else's post [http://www.thescripts. com/forum/thread605958.ht ml]. I'm attempting it as we speak. I hope it works because I've certainly put alot of time and effort into it!

deanndra

You just can't create a database with the structure you have outlined. If some of the fields will have multiple values then they need to be in separate tables and you will need to use subforms to enter them.

Have a look at this tutorial on Database Normalisation and Table Structures. If you don't want to have a lot of problems entering and retrieving data you will need to look at the structure of your tables again.