Combo box problem for new Access user

[badflyer]

Hi Guys, need some help please :)

I'm totally new to access, so i hope someone can help. ok i have a form with a combo box. I have two choices in my combo box, for arguments sake, lets call the options Elvis and Lennon. These two choices come from a table i created called tb_artist where ID 1 = Elvis and ID 2 = lennon.

Now, i now have a second table called tb_song, with five Elvis songs and 5 lennon songs listed in it. All Elvis songs have a ID = 1 and Lennon = ID 2
(basically a two column table with headings ID and Song)

In a second combo box, i want the drop down to show either Elvis or Lennon songs, depending on the choice i made in the first combo box...

Really easy i bet, but i'm pulling my hair out trying to figure it out! I've not used queries before, so can someone kindly tell me the best way of doing what i need? Thanks

[OBP]

badflyer the simplest way to do this, because you can verify it each step of the way is as follows.
on the Second Combo, base it's Row Source on a Query of your second table.
In the Query's "Criteria Row" for the ID column enter the following
[forms]![FormName]!Combo1
where FromName is the name of your Form and Combo1 is the actual Combo and number of your first Combo.
For this to work your first Column in your first Combo should be ID, assuming that your second table uses ID to identify the singers and not their names (Bad form to use names).

[badflyer]

Thanks for the reply, but I'm lost. I've added the simple form i am working on with this message... Can you show me?

[OBP]

badflyer, it is a good job that you asked for help because I had forgotten the last essential step.
This is what I have done, I renamed your Artist Combo "Combo1" and added the artist ID to the Combo as Column 1.
I deleted your Song Combo.
I created a simple Select Query based on the tb_Songs Table.
I then recreated the Songs Combo based on the Songs Query.
I added

[forms]![frm_Artiste]![Combo1]

to the Criteria Row of the Songs Query in the ID Column,which means if you select an Artist in the first combo and then run the query it will only display those records in the Songs table that have that ID number.
The last part was to add an "Event Procedure" to Combo1.
This goes in the "After Update" event and uses this code

Me.Combo2.Requery

Which just means that each time you select an Artist the Song Combo is refreshed using the new selection ID.

I have had to convert the Database from Access 97 to Access 2000 and then back again, so I hope it still works OK.

[badflyer]

Absolutely brilliant mate! Thankyou - i think ive got the idea :)

for the next step, Would It is possible to do this just in VBA? say after the aftereventprocedure in combo1?

[OBP]

badflyer, yes it is possible to do it all using VBA, but it gets quite complicated. I must admit that I have never bothered as this method works so well.
To do it in VBA would require the use of an SQL statement to replace the Query's output that combo 2 uses.
The Record Set that the SQL creates would have to be assigned to Combo 2's Row Source.