sub form listbox based on auto number field from main form

[lico]

Hello,
i have a form based on a table, in the table i have a field called ID which is an Autonumber .
for every record, the form lists a new Autonumber in the ID field. the same form also includes a sub form in which i'm trying to create a list box that lists the values of the ID field from main form and let the user choose only from one of them.
i can't base the list on the ID field from the table because i only want the values that are currently in use in the main form to show.

if anyone have an idea

thanks

 

[John Big Booty]

Build a query to collect the data you require in your list box. In the criteria of the query under ID the put ; [Forms]![FRM_YourMainFormName]![ID]

Now use this query to populate your List Box.

 

[lico]

hi John,
thanks for your replay,
i tried to do as you said, when i'm moving to the combo box that based on the populated list i get a parameter box that askes me for the ID number insted of giving me the list.
when i posted my question i wrote that the list is based on the main form but it's actually based on onther sub form, maybe referring to sub form in the query criteria is different then reffering to a sub form?

i'll describe exactely what i did so you can tell me where did i go wrong:
i created a query based on the table that contains the ID field which is an auto number. in the criteria filed i entered the path of the ID controller from the second sub form. in the third sub form i created a combo box based on the query with the creteria.

the result was that when i open the combo box in order to choose a number, i get a parameter box asking meforthe ID number.

again, thanks a lot for your help!

 

[John Big Booty]

OK given that you are now getting the ID from a subform you will need to change the criteria accordingly. Bookmark this link for future reference, it is a handy resource for the correct syntax for referring to sub forms and their properties.

Your criteria will now look like; [Forms]![FRM_YourMainFormName]![FRM_YourSubform1Name].[form].[ID]

 

[SOS]

And just as an aside - you need to refer to the name of the subform container (which is the control on the main form which houses the subform on the main form) and not refer to the subform name itself unless the container and the subform are named the same thing.

 

[John Big Booty]

Good point SOS

 

[SOS]

Good point SOS

A very common thing that gets missed just because in a lot of cases the subform and subform container end up named the same and so it just works. But then there are the cases when someone names it something different when adding with the wizard or manually adding a subform (or subreport) on to a form/report and then they miss that as well. One of the more annoying things I think about Access (until you get to just know it).

It was Bob Larson, I think, who I got that explanation from and it totally made sense from there on out. I had so much trouble before.

 

[lico]

hi SOS and John,
thanks alot for yuor replays, i'll give it a try soon.

 

[alelover]

Hello folks,
I am having a similar problem. I can search my records with no problem but I end up with a main record for each subformrecord. So if a concert has 20 songs I get 20 records of that concert. I tried entering the criteria the way you have it there and it keeps asking me to Enter Parameter Value.
[Forms]![Recordings]![RecordingsSubform].[form].[RecordingID]
Do I put this as the criteria for the RecordingID or the TrackTitle in my RecordingsSubform. Do I even have it right? I sort of understand this stuff but there is a whole lot more I don't. I have attached a condensed version of my dB if anyone would like to look at it. I would be grateful for any help.

 

[SOS]

Get rid of the table RECORDINGS in qryTracks. For the subform you just want the TRACKS in the query. The subform will then display the correct records for each RecordingID.

 

[alelover]

The subform always shows the right records. But it shows a recordingID record for all the records in the subform. So if one concert has 10 songs there will 10 records for the one RecordingID. In my Condensed dB I have 4 concerts but it show 69 records. There are 69 songs in the qry. If I make my main form get it's data from the Recordings table instead of the query I get the one record per concert but then I can't search by Track Title.

 

[SOS]

You also need to remove the TRACKS table from the query in the main form.

As for searching by track you will have to use different code for that as it is in a different table. You can't have both tables in both main and subform as it will do exactly what you see.

 

[alelover]

I've done that too. It kills my search by Track Title ability. My search code works good if everythings in the same table. How can I make it search and find data from a different query than the main one I have set as the Record Source?

 

[SOS]

I've done that too. It kills my search by Track Title ability. My search code works good if everythings in the same table. How can I make it search and find data from a different query than the main one I have set as the Record Source?

You have to change the way to search. It is going to be somewhat complex and I don't have time to help with that intense of stuff. If I were you I would allow searches for the main form on the main form and then if someone wants to search by track, popup a different form.

 

[alelover]

Thanks for all your help. It did teach me a couple things.