subform from a combo box

[maxmangion]

i have a main form where a user can input the data.

now i created another form in which it is going to contain a combo box and a subform. now when the user selects a value from the combo box, the subform should display related records with the value of the combo box.

i tried it in the following manner

i started the form wizard and i chose the field (from the main table) that i want to be the combo box and finished the wizard. in design view i created a sub form and i chose to match the value of the combo with that of a field in the sub form. although i was getting the results, each time i selected a value from the combo, the first record of the main form was being amended to the new value selected.

what did i do wrong ?

 

[namliam]

you cant do it like that, due to the very problem you are experiencing....

For the recordset of the subform:
select ... from ... where [Keyvalue = value from combo]

then requery after update... Presto....

Good luck...

 

[maxmangion]

For the recordset of the subform:
select ... from ... where [Keyvalue = value from combo]

can u explain it a bit further pls!

1. shall the combo box be created unbound ?
2. when i draw the sub form, what shall i choose to create the link?
3. the above code you mentioned, where shall i put it?

sorry for asking several questions, but i did not understand your solution.

you know of any similar threads or a link, which may serve me as a guide ?

 

[namliam]

For the recordset of the subform:
select ... from ... where [Keyvalue = value from combo]

can u explain it a bit further pls!

1. shall the combo box be created unbound ?
2. when i draw the sub form, what shall i choose to create the link?
3. the above code you mentioned, where shall i put it?

sorry for asking several questions, but i did not understand your solution.

4)you know of any similar threads or a link, which may serve me as a guide ?

1) yes unbound
2) no link, thats the point...
3) Select statement goes in the recordssource of the subform
4) several tho i am not searching for you, but i know they are out there. Also this may even be in the FAQ

BR

 

[maxmangion]

thx, i have a clearer idea now, thanks to your reply. i will try to follow it this way.

thank you very much!

 

[maxmangion]

by recordsource of the sub form, do you mean the source object in the properties window?

if yes would this be correct

Select tblSongs.Album, tblSongs.Song, tblSongs.Singer, tblSongs.Length FROM tblSongs WHERE tblSongs.Album = Forms!frmViews!Albums

 

[maxmangion]

unfortunately i am still stuck with this thing

the following is my exact scenario:

i have 3 tables:

tblAlbums
AlbumID (primary key)
Album
Released
SingerID

tblSingers
Singer ID (primary key)
Singer
Country

tblSongs
SongID
Song
Album (combo box getting values from tblAlbums)
Singer (combo box getting values from tblSingers)
Length
Bitrate
.......

now i have 4 forms

frmAlbums (created by wizard based on tblAlbums) working ok
frmSingers (created by wizard based on tblSingers) working ok
frmSongs (created by wizard based on tblSongs) working ok

now i am creating the 4th form which should be frmViews. i did a tab control with 2 pages for now. In the first page i am going to make a combo box to display the list of albums. Below it i am going to make a subform which should display the records from the tblSongs based on the value of the combo box. Similarly i am go to do the same with Singer on the next page of the tab control.

now when i create the combo box for the Albums, from the wizard i chose the option "I want the combo box to look up the values in a table or query", i chose the tblAlbums table, selected AlbumID & Album fields, and i chose the value "Remember the value for later use". When i finished the wizard and returned to the combo box, the combo was populated with all the Albums in the tblAlbums.

Now here starts my problem, I am drawing the Subform below the combo box and when the wizard pops up, i am cancelling it so i am having an unbound sub form. Now when i go to Properties i am going into the "Source Object" and type the following:

SELECT tblSongs.Album, tblSongs.Singer, tblSongs.Song FROM tblSongs WHERE tblSongs.Album = Forms!frmViews!Album (combo reference),

but i am getting an error message saying "The form name you entered doesn't follow Microsoft Access object-naming rules."

any help is greatly appreciated as i am stuck with this and i cannot understand what i am doing wrong.

 

[maxmangion]

pls is it possible that someone give me a hint and put me in the right direction, because i am totally stuck ?

thanks

 

[goldenvision]

pls is it possible that someone give me a hint and put me in the right direction, because i am totally stuck ?

thanks

I have just done a very similar task.

Can you zip down and post your database and I'll apply the same logic to it.

 

[maxmangion]

unfortunately i am unable to upload the file, because it is slightly bigger than 100kb, is it possible that i send it to you by email, or you send me a sample of yours ?

Thank you!

 

[namliam]

There may just be a sample from Mile-O, check the FAQ and Sample forums....

Regards

 

[le888]

unfortunately i am unable to upload the file, because it is slightly bigger than 100kb, is it possible that i send it to you by email, or you send me a sample of yours ?

Thank you!

See attachement. Maybe this help...

Le