Query problem - selection criteria from subform

[SunGodly]

Hello, all. I am a newbie developer and I'm having trouble with a query. The application is a residential telephone service database. Each resident can have more than one telephone line, and each line can have a promotional discount based on the service plan selected. The main form is the ResidentInfo form, which includes a ResidentServices subform to display information for each phone line the resident has. The ResidentInfo form includes a combo box where the user can select from one of five geographic locations (stored in a separate table). The ResidentServices form has a combo box where the service plan is selected based on the location selection above. Here is where I'm having trouble: I have a ResidentPromos subform in the ResidentServices subform where the user should be able to select promotions based on the service plan selection. I have spent two days trying to figure out the proper query to display only the promos that are available for the selected plan, with no success. I know this is probably not too difficult for an experienced developer but I just can't seem to decipher it. I will be more than happy to provide additional details if anyone is willing to tackle this one.

Thank you very much!

 

[pdldavis]

Hi, If your Resident Services subform has the information you want and it's a question of restricting that information based on your combo box criteria, then you should be able to use the expression builder to link your query to the combo box. Is that what you are after?

Another thought - I've never been a fan of nesting subforms. An alternative way to to that might place your combo boxes on the main form, turning them from invisible to visible as you make your selections. The last combo box would display the subform results after update. Build one subform that uses the combo box selections for the subform query criteria.

Hope that helps.

 

[SunGodly]

Thank you very much for your reply. I notice that I left something out of my description. Each resident can have more than one phone line and each line can have MULTIPLE promotions. That is why I have the subform within the subform. An example might help to explain the situation.

A resident has two lines. On the first line he has selected Basic service. There are two promos available - Basic 2 Months Free and Basic 3 Months Free. For the second line, the resident has chosen Economy service, also with two promos available (2 and 3 Months Free). What happens is that all four choices are showing up in the combo box when only the two for that specific service selection should be showing up.

I agree that nested subforms are probably not a good idea and I think that part of the problem is there. I can't seem to base the query or expression builder criteria on information in the parent subform. However, I don't know another way to offer an unlimited number of promotions for each line.

This seems like it should be a much simpler task than it has turned out to be. I fear that I may have set up something incorrectly in the tables and/or relationships. If you have any other ideas, please let me know. And thanks again!

 

[pdldavis]

Hi, It should not be that difficult to do what your are describing but without seeing that kind of arrangement I don't think I can provide a further productive comment. Lousy short term memory and subforms don't mix well. If you would like to send the form along with the tables and a little bit of sample data I'd be happy to have a look at it or perhaps someone else can assist with this.

Dan

pdldavis@yahoo.com