By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,935 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,935 IT Pros & Developers. It's quick & easy.

Base a subform on multiple combobox selection problem

P: 13
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", "Descriptions")
tblArea
(Feilds: "AreaID", "AreaName")
tblAreaStation
(Fields: "AreaStationID", "StationName", "AreaID")
tblClassAreaStation
(Fields: "ClassAreaStationID", "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_AfterUpdate 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
Jul 6 '10 #1
Share this Question
Share on Google+
6 Replies


beacon
100+
P: 579
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?
Jul 6 '10 #2

P: 13
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_AfterUpdate()
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
Jul 6 '10 #3

beacon
100+
P: 579
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.
Jul 7 '10 #4

nico5038
Expert 2.5K+
P: 3,072
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", "StationName", "AreaID")

tblClasses
(Fields: "ClassID", "ClassName", "Descriptions", "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)
Jul 7 '10 #5

P: 13
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
Jul 8 '10 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 8 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.