populating combo box

[mikevds]

mikevds@optonline.net

Access Tables

Table: Name of Hymnal

Fields
Hymnal ID
Name of Hymnal

Table
Hymnal Song Info.

Fields
Id Hymnal
Name of Song
Hymnal Song
Hymnal Number
Hymnal Name
Composer Name
Arranger Name
Arranger Number
Author Number
Author and Key
Scripture
Key
Tune
Meter
Media Type

The Purpose is I want to have 2 populated list boxes
The other one from Hymnal Song Info. Table, Name of Song Field

One from Name of Hymnal Table, Name of Hymnal Field

Populated Box 1- click on Name of Song

Populated Box 2 click on Name of Hymnal, only displays hymnals from that song.

I tried using the SQL Coding, but it’s not populating, then it messes up my other forms for Audio CD’s that display is cdauthor name and cdname.

Here is the SQL Coding, I’ve used but I am having trouble, I am willing to email the database so you can see my problem. Otherwise it would be very time consuming.




HymnalSongInfooneQuery
SELECT HymnalSonginfo.[ID Hymnal], HymnalSonginfo.[Hymnal Name], HymnalSonginfo.[Name of Song], HymnalSonginfo.[Hymnal Song Number], HymnalSonginfo.[Hymnal Number], HymnalSonginfo.[Composer Name], HymnalSonginfo.[Composer Number], HymnalSonginfo.[Arranger Name], HymnalSonginfo.[Arranger Number], HymnalSonginfo.[Author Number], HymnalSonginfo.[Author and Key], HymnalSonginfo.Scripture, HymnalSonginfo.Key, HymnalSonginfo.Tune, HymnalSonginfo.Meter, HymnalSonginfo.[media Type], [Name of Hymnal].[Hymnal Number ID], [Name of Hymnal].[Name of Hymnal]
FROM HymnalSonginfo LEFT JOIN [Name of Hymnal] ON HymnalSonginfo.[ID Hymnal] = [Name of Hymnal].[Name of Hymnal];

can u email me the sql coding

 

[qdogfball]

It looks like to me you have a great setup for a form/subform instead of list boxes.

With your table design and the one to many relationships from Hymnal to Hymnal songs, you should have an easy time setting this up.

Why try to make something hard if you don't have to?

 

[qdogfball]

Check out this quick DB..

Other threads..

http://www.access-programmers.co.uk/forums/showthread.php?t=129847

 

[qdogfball]

Ok,

That was much easier than I remember!

Just create a listbox using the Access toolbar Wizard

The first thing it asks (at least in Access 2003) is List box Wizard is 3 things, you want the bottom one.

"Find a record on my form based on the value I select in my list box."

Click next.

Use the field you want to see in the list box (Name of Hymnal)
> it over to use it

Finish!

There you go!

Access generates the underlying code in the After update event.

Private Sub List12_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Hymnal ID] = " & Str(Nz(Me![List12], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[mikevds]

List Boxes

Thanks QDogFBal for your help,

Now how do I set it up to populate 2 list boxes

Thanks,

Mike Va nDer STad

 

[qdogfball]

Mike,

Maybe I am not thinking clearly.

If you have a combo or list box that you can select the different type of hymnals in it, and then a subform which shows which songs are in that hymnal, does this not accomplish what you are trying to do?

Why would you need two list boxes?

Sorry if I am not totally understanding how this project needs to work!