Base a subform on multiple combobox selection problem

[BabyLucifer666]

hello,

What I'm trying to do is create a list of classes based on what Area and Station they are in. I have the following tables set up already:

tblClasses
(Fields: "ClassID", "ClassName" , "Descriptio ns")
tblArea
(Feilds: "AreaID", "AreaName")
tblAreaStation
(Fields: "AreaStationID" , "StationNam e", "AreaID")
tblClassAreaSta tion
(Fields: "ClassAreaStati onID", "ClassID", "AreaStationID" )

I have an unbound form (frmClassLookup ) with two unbound combo boxes, cboArea and cboStation. (cboStation's row source based on cboArea's choice).

I also have a query "qryClasslookup " that shows the classname, Description, Area, Station, and placed criteria for Area to [Forms]![frmClassLookup]![cboArea], (ditto with station) and I have an event procedure on cboStation_Afte rUpdate to open the query.

Problem 1: the query comes up blank (when I run the query outside the form it works fine, but using the comboboxes it doesn't work)

Problem 2: I would like the query to open in the same window, in a subform, not in a seperate datasheet view

Thanks for any help you can give!!
VC

[beacon]

Hi BL,

Welcome to Bytes!

Does the subform work inside the form without the AfterUpdate event code or before the AfterUpdate event is triggered (i.e. does the subform show data before you change the value in the combo box and that's when it returns blank data)? Can you post the code in your AfterUpdate event?

[BabyLucifer666]

HI!

Thanks for you help.

When I open the main form the subform (which is shown in datasheet view) is blank. But when i open the subform seperately it shows data.

The AfterUpdate code is just an openquery:

Private Sub cboStation_Afte rUpdate()
DoCmd.OpenQuery "qryClassLookup "

End Sub

But doing it this way opens a seperate window, I want to update the subform to show query results, i wouldn't mind even putting a button so once the combo box options are picked, the user can just click a button to update the subform.??

Thanks again

[beacon]

Since your main form is unbound, is your subform also unbound? Is there a reason why your main form is unbound? I created a replica of your database based on the info you provided and, if my relationships are the same as yours, your form could definitely be bound, which would allow the query to appear for the class ID.

I added the Class ID to the query and added a text box on the form. Then, in the cboStation, I added a DLookup behind it that placed the value returned in the text box. If you were to bind the subform to the form, this text box, with the class ID value in it, could link to the subform. Then, you could add a .Requery to the subform in the BeforeUpdate for the cboArea and/or cboStation to requery the subform anytime the value in one of the combo boxes changes.

One of the experts may know a way to accomplish what you're trying to do with two unbound forms, but unfortunately I'm not familiar with a method (that includes using a command button).

I'll be happy to continue to help though if your questions spark something.

[nico5038]

When a Class is situated in a Station and a Station belongs to an Area, then use this table structure:

tblArea
(Feilds: "AreaID", "AreaName")

tblStation
(Fields: "StationID" , "StationNam e", "AreaID")

tblClasses
(Fields: "ClassID", "ClassName" , "Descriptio ns", "StationID" )

By recording the StationID in the Class table, the Area is defined automatically.

Best to use bound forms and define for the foreign keys in the tables the link to the table, thus Access will automatically show a combobox for the selection of the proper value.

Nic;o)

[BabyLucifer666]

Thanks for you help, I got it working, the mainform is bound to a qry and the subform is bound to another qry. The comboboxes now filter the subform to show the results needed.

Nic, I think that will be useful to try, THANKYOU.

BL

[nico5038]

Best to bind a form to a table to prevent trouble when editing the record. (Queries can cause a "non update" situation)

When you want to filter a form, use the wizard to create a new combobox and select the last option for filtering the form, very easy !

Nic;o)