Cascading Combo Boxes -- HELP!!!

[magicfap]

I am currently trying to create a simple set of cascading combo boxes. The first box (participant) is a combo box of all of the possible participants that were created in my participants table. The second combo box (source) should narrow down the choice of sources to only the sources submitted by the participant chosen.

_____________________________________________________
To clear up some things:

A participant has an:

ID - AutoNumber
NAME - Text field of the name of the participant
EMAIL - a text field that stores information about the email
and an ORGANIZATION - a text field of the passable

a source has:

ID -pk autonumber
PARTICIPANT - this is a LU of participants in the participant table (by Email)
DATECREATED - this is a date field of when the source was recived
and SOURCE FORMAT - a LU of formats (EMAIL, LETTER, PHONE)

____________________________________________________
I want to have the user choose a participant by their email

and then choose a source based on the date created field.
___________________________________________________

The first problem I ran into is that Access, to the best of my knowledge will not let you use a date field as a LU

I have looked at other people who have had this problem on the forums but have not been able to solve it yet.

You should know that I have almost no experience with VBA.

Thank you in advance for the help,

magicfap

P.S. also you should know that the combo boxes are contained in a form called Comment

 

[jsv2002]

You need to link the 2 tables with a foreign key eg. the autonumber allocated to the participant should be in the source table to link your "Source" to the participant without this you do not have a viable structure.

Have a look at the attached example of cascading combos and you should get the idea pay particular attention to the table structure and you will need to look at the forms on current event procedure to see the code.

Good luck John

 

[magicfap]

Thank you so much for the help. I am having some slight problems with implementing it. I am getting a compiler error on the line

Set rs = Nothing

I am not sure where you have defined rs. I think that I am close to figuring out. At this point choosing a participant updates the source to have a value that is the same as the participant ID. You mentioned looking at the current event procedure. I am relatively new at access so I'm not sure where to look for that.

Thanks so much for your help so far,

Magicfap

 

[jsv2002]

Yeah sorry was something I stripped out of another database I had been working on as a test example rs = recordset must have slipped past me

 

[fluid]

You need to link the 2 tables with a foreign key eg. the autonumber allocated to the participant should be in the source table to link your "Source" to the participant without this you do not have a viable structure.

Have a look at the attached example of cascading combos and you should get the idea pay particular attention to the table structure and you will need to look at the forms on current event procedure to see the code.

Good luck John

Hi,
I'm trying to muddle my trough Cascading combo boxes as well. and I've got a question about the "test6" database.

For the DepartmentID Combo box the row source is chosen where [siteID]=1.

What does the "=1" refer to? the column ?

 

[Mr. B]

Tables do not have to be linked in order to utilize cascading combo boxes as long as a query can be created that will produce a sub set of data either from a second table with a FK to the first table or to a duplicated value in the same table.

Just take a look at the Row Source for both combo boxes, expecitally the second one.

Attached is a zip file containing a demo of cascading combo boxes from a single table.

With a little effort you should be able to use the same techniques that I ued in this demo to accomplish your desired result. The trick is the VBA code in the After Update event of the first combo box:

With Me.cboJobs
.Requery
.SetFocus
.Dropdown
End With

HTH

 

[jsv2002]

I assume you where looking at the row source for DepartmentID? this will change after each update of the siteID combobox reflecting the choise there and limiting what you can select to the foreign key in the departments ID table.

Using the above suggested will also work but the way I did it stores the values for each record created.

The DB I stripped the example out of had a seperate form for entering site details with a subform to enter departments for that site eg 1 site could have multiple departments...

The afterupdate event of the siteID is what you require.

good luck john