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

Selection Criteria using multiple combo boxes on form witha subform

P: 3
This database is used to track TV series that are being watched or have been watched.
I have multiple combos on a tab form to to use as filter options. After making selection in the verious combo boxes, I then choose a to click (prodecure Event) buttton that uses the following Code to anylize the records.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Select_Records_Click()
  2. On Error GoTo Err_btn_Select_Records_Click
  3.  
  4.     Dim xAnd As String
  5.     Dim SelCrit As String
  6.     Dim stDocName As String
  7.     Dim stLinkCriteria As String
  8.  
  9.     stDocName = "Frm_TV_Series_Edit"
  10.  
  11.     'Selection Criteria
  12. 'Intialization
  13.     xAnd = ""
  14.     SelCrit = ""
  15. 'This Section Processes Series Items for a specific Series
  16.     If IsNull(Me![Select_Series_Name]) = False Then
  17.     SelCrit = SelCrit & xAnd & "[TV_Series_Name_ID] =" & Me![Select_Series_Name]
  18.     xAnd = " And "
  19.     End If
  20. 'This Section Processes Series Items for Specific Source
  21.     If IsNull(Me![Select_Series_Source]) = False Then
  22.     SelCrit = SelCrit & xAnd & "[Source_ID] =" & Me![Select_Series_Source]
  23.     xAnd = " And "
  24.     End If
  25. 'This Section Processes Series Items for Specific Status
  26.     If IsNull(Me![Select_Series_Status]) = False Then
  27.     SelCrit = SelCrit & xAnd & "[Viewing_Status_ID] =" & Me![Select_Series_Status]
  28.     xAnd = " And "
  29.     End If
  30.  
  31.     DoCmd.OpenForm stDocName, , , SelCrit
  32.  
  33. Exit_btn_Select_Records_Click:
  34.     Exit Sub
  35.  
  36. Err_btn_Select_Records_Click:
  37.     MsgBox Err.Description
  38.     Resume Exit_btn_Select_Records_Click
  39. End Sub
The form Frm_TV_Series_Edit has a subfrom frm_Season. The form uses a query that includes the tbl_TV_Series and directley related tables.
The subform uses a query that includes tbl_TV_Season and some dirctely related tables. The two queries use the (PK)TV_Series_ID.

The select Specific Series and Specific Status work just fine (the are associted with the main form query). The select Specific Source does not work (it is associted with the subform query)

I know I need different syntax other than just "[Source_ID] =" since it should be comparing the field on the subform (not the main form) with the combo box Me![Select_Series_Source].

I know that the subform is considered a control but after extensive searching online and reading - (lots of trial and errors) - I just can't figure out the correct syntax.
Jul 3 '16 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,442
You may find the following links helpful :
Referring to Items on a Sub-Form.
Example Filtering on a Form.
Let us know if you still have problems. If so, explaining exactly what and where your problem is would be likely to encourage other experts to offer help.
Jul 7 '16 #2

P: 3
Here is code that I have which can't find the Source_ID on the main form (query) that being called - it needs to look at the subform to find Source_ID.
Expand|Select|Wrap|Line Numbers
  1. 'This Section Processes Series Items for Specific Source
  2.     If IsNull(Me![Select_Series_Source]) = False Then
  3.     'SelCrit = SelCrit & xAnd & "[Source_ID] =" & " & Me![Select_Series_Source]"
  4.     xAnd = " And "
  5.     End If
I tried the various syntax examples that should refer to the subform and its Source_ID; the "[Source_ID]" needs to replaced with syntax that refers to the subform.
I tried Me.[frm_Series]![Source_ID] - it still can't find the Source_ID which needs to match [Select_Series_Soure] which is from a combo box on the selection criteria form.

I guess I am just to old to understand this!

I am happy to post whatever is needed - this is the first time that I had to deal with the kind of one to many relationships (where the form has a subform)
Jul 7 '16 #3

NeoPa
Expert Mod 15k+
P: 31,442
This may be tricky then. I need more info from you in order to give specific answers. The linked article is my attempt to explain the concepts as clearly as possible without a specific scenario.

We often to a bit of extra hand-holding - and that's fine - but I'd need to know the names of all the relevant objects and how they relate to each other in order to understand your situation fully enough to walk you through it.

Maybe you could use the linked article to go through your form structure and post here what's what.
Jul 8 '16 #4

P: 3
Expand|Select|Wrap|Line Numbers
  1. 'This Section Processes Series Items for Specific Source
  2.     If IsNull(Me![Select_Series_Source]) = False Then
  3.     SelCrit = SelCrit & xAnd & " [Source_ID] =" & " & Me![Select_Series_Source]"
  4.     xAnd = " And "
  5.     End If
Okay! To start with I have a form with tabs , Select Series, Reports, & Edit Fields. The Select Series tab has three combo boxes to pick what is to be selected; Series, Source, & Status.

The form that is being called for is Frm_TV_Series_All that uses a query Qry_Main. That form has a subform (Frm_Season) that uses a query (qry_season) the form and subform are linked with Series_ID. The Series & Status selection criteria work fine because they are attributes of the form (Frm_TV_Series_All) and its query.


I change the code to try to get it to refer to the subform which has the Source_ID
Expand|Select|Wrap|Line Numbers
  1. 'This Section Processes Series Items for Specific Source
  2.     If IsNull(Me![Select_Series_Source]) = False Then
  3.     SelCrit = SelCrit & xAnd & " Me.[frm_Season].Form.[Source_ID] =" & " & Me![Select_Series_Source]"
  4.     xAnd = " And "
  5.     End If
I get the followig error: Syntax error (missing operator) in the query expression 'Me.[Frm_Season].Form.[Source_ID] = & Me![Select_Series_Source]

The Select_Series_Source is the name of the combo box on the Series tab of the selection criteria form.
Jul 8 '16 #5

Post your reply

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