Runtime Error 3075 and runtime error 2001 filtering multiple combo boxes

Hi Darlene,

Spotted the following errors.  Post back after you have tested these corrections and provide feedback as to any remaining problems. for the part number problem, I think the problem is that the spaces and dash are not part of the stored value in the table, and therefore you are not getting a match with the combo box values.  See the change I made below to the sql statement
    strSQLSF = strSQLSF & " ComtechSystemsIncConfigurationManagementData.PartNumber = " & Trim(cboNumericField)
 The trim function should take care of the spaces, but will not take care of the dash. After you've confirmed the effect of the above change and the corrections below, we can  proceed from there.
............................................Errors............................................
'COMBO BOX #1

 cboColumnField.RowSource = strSQL      s/b       cboRowField.RowSource = strSQL


'COMBO BOX #2
cboNumericField.RowSource = strSQL      s/b       cboColumnField.RowSource = strSQL

Netminder,
I object to refund of points on grounds of principle.  I took the time to review asker's code, and believe that I provided the corrections to the major cause of the asker's combo box problems.  To date, there has been no feedback whatsoever.  I think an explanation is in order.  Thanks for your attention to this matter.

                          PDB

puppydogbuddy

I understand, and I haven't had a chance to review your response yet and I will today.  I requested the cancelation before you responded.  Will respond.

puppydogbuddy

Thank you for your help.  Let me tell you what I did and what I think my real problem is.  I went to the following website "http://www.candace-tripp.com/pages/mainf.aspx" and took the cascadecombobox example and just changed three parts in the code and in the form.  I changed cboRowField to cboProjectName and RowField to Project Name (enclosed in brackets in the code), cboColumnField to cboJobNumber and ColumnField to Job Number (enclosed in brackets in the code), and cboNumericField to cboPartNumber and NumbericField to PartNumber.

In the example cboNumericField and NumericField are numeric fields.  PartNumber field that I need is not numeric.  If I'm not mistaken, when writing code, if the field is numeric, there is a different way to write it.  Look at my code compared to the example code and I think that's my problem.  Because the only thing that I changed was those three fields within code, sql properties of the fields, and the names of the fields.  I didn't touch any other part of the code.

MY CODES

Option Compare Database
Option Explicit

Private Sub cboJobNumber_AfterUpdate()

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = Null
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And  "
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'"
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.PartNumber;"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = " SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "'"
         
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboPartNumber_AfterUpdate()

    Dim strSQLSF As String
   
    strSQLSF = " SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And "
    strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = " & cboPartNumber
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Private Sub cboProjectName_AfterUpdate()

     Dim strSQL As String
     Dim strSQLSF As String
         
     'Job Number
     cboJobNumber = Null
     
     'Part Number
     cboPartNumber = Null
     
     strSQL = "SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"
     
     cboJobNumber.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
         
     Me!sfrmForm.LinkChildFields = "[Project Name]"
     Me!sfrmForm.LinkMasterFields = "[Project Name]"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

     cboProjectName = Null
     cboJobNumber = Null
     cboPartNumber = Null
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     Me.RecordSource = "ComtechSystemsIncConfigurationManagementData"
     Me.Requery
         
exit_cmdShowAll_Click:
    Exit Sub
   
err_cmdShowAll_Click:
    MsgBox Err.Description
    Resume exit_cmdShowAll_Click
   
End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim strSQL As String
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
    cboProjectName.RowSource = strSQL
   
End Sub

EXAMPLE CODES

Option Compare Database
Option Explicit

Private Sub cboColumnField_AfterUpdate()

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboNumericField = Null
   
    strSQL = " SELECT DISTINCT tblDemo.NumericField FROM tblDemo "
    strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "' And  "
    strSQL = strSQL & " tblDemo.ColumnField = '" & cboColumnField & "'"
    strSQL = strSQL & " ORDER BY tblDemo.NumericField;"
   
    cboNumericField.RowSource = strSQL
   
    strSQLSF = " SELECT * FROM tblDemo2 "
    strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "' And  "
    strSQLSF = strSQLSF & " tblDemo2.ColumnField = '" & cboColumnField & "'"
         
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "RowField;ColumnField"
    Me!sfrmForm.LinkMasterFields = "RowField;ColumnField"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboNumericField_AfterUpdate()

    Dim strSQLSF As String
   
    strSQLSF = " SELECT * FROM tblDemo2 "
    strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "' And  "
    strSQLSF = strSQLSF & " tblDemo2.ColumnField = '" & cboColumnField & "' And "
    strSQLSF = strSQLSF & " tblDemo2.NumericField = " & cboNumericField
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "RowField;ColumnField;NumericField"
    Me!sfrmForm.LinkMasterFields = "RowField;ColumnField;NumericField"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Private Sub cboRowField_AfterUpdate()

     Dim strSQL As String
     Dim strSQLSF As String
         
     cboColumnField = Null
     cboNumericField = Null
     
     strSQL = "SELECT DISTINCT tblDemo.ColumnField FROM tblDemo "
     strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "'"
     strSQL = strSQL & " ORDER BY tblDemo.ColumnField;"
     
     cboColumnField.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM tblDemo2 "
     strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "'"
         
     Me!sfrmForm.LinkChildFields = "RowField"
     Me!sfrmForm.LinkMasterFields = "RowField"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

     cboRowField = Null
     cboColumnField = Null
     cboNumericField = Null
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     Me.RecordSource = "tblDemo"
     Me.Requery
         
exit_cmdShowAll_Click:
    Exit Sub
   
err_cmdShowAll_Click:
    MsgBox Err.Description
    Resume exit_cmdShowAll_Click
   
End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim strSQL As String
   
    strSQL = " SELECT DISTINCT tblDemo.RowField FROM tblDemo ORDER BY tblDemo.RowField;"
    cboRowField.RowSource = strSQL
   
End Sub

For tblDemo2  I made query.

I am also getting the following error "ORDER BY clause (ComtechSystemsIncConfigurationManagementData.[Job Number]) conflicts with DISTINCT."  If I do as the HELP tells me too (either delete DISTINCT or delete the field being used by the ORDER BY, then the cboJobNumber combobox ends up with the Project Name column in it.  So this I don't understand either.

Waiting to hear from you.

Darlene,
Thanks for your nice response.  I have a client emergency today, but will get back to you asap.  In the meantime, in answer to whether there is a different way to reference a text string vs a numeric string, the answer is yes.....numeric and text are distinguished by the number of quotes used in the string.  

partno referenced as numeric in your code:
                                              (note only 2 double quotes used for numeric syntax)
           strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = " & cboPartNumber

should be reference as text:
  ( the quotes from left to right below: double quote, single quote/double quote, and double quote/single quote/double quote)
           strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = '" & cboPartNumber & "'"

Replace all your partno strings with the text string syntax as illustrated above.  At least this is something you can implement right away and provide some feedback until I get finished with my client emergency.  keep in mind though that even after you get your string syntax corrected, you still may not get a match if the search string and comparison string have different text formats....in terms of spaces, dashes, etc.  That can be dealt with by using the trim function and other functions to place them on equal footing in  the code that does the comparison.

Will get back with you ASAP.

Okay I did like you told me and I'm still getting the last error:

"ORDER BY clause (ComtechSystemsIncConfigurationManagementData.[Job Number]) conflicts with DISTINCT."

This is in the cboProjectName after update code.

If I change DISTINCT to DISTINCTROW it works but gives me the Project Name column instead of the Job Number column in the cboJobNumber field.  

And even though I got it to work that far, when I select the cboPartNumber field I get a syntax error MISSING OPERATOR about the following:

'" & cboPartNumber & "'"

I'm going to start over again with the form now that I know how to do the numeric field and see if it works.  Give me what information you can provide on the above issues.  Thank you.  Really need your help on this.

Darlene,

The error, "order by conflicts with distinct" occurs when there are fields in the order by clause that are not included in the select clause.  The distinct argument requires that all order by arguments be fields in the returned result.

In your case, you are  ordering by [Job Number], but  [Job Number] does not appear in your select clause.  Change your select clause as follows:


    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "

This should also correct your other error.  Let me know.

Okay so this is the one that is taking the hit:

strSQL = "SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"

So please be patient with me.  Where does this fit in:

 strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "

Remember this is the cboProjectName after update code.

It's 4:00 here and I will be leaving @ 5 pm.  So if I don't get it today I will respond tomorrow.

Thank you.

Hi Darlene,
Aha!! you caught my typo.  Good for you.

Substitute my corrected select clause (below):
strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "

in place of yours (below):
strSQL = "SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
_________________________________________________________________
The reason is because your order by clause (below) references [Job Number]:

     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"
 and [Job Number] does not appear on your select clause (above).........you only have [Project Name] referenced there.

This is what is causing your order by to conflict with distinct.  This is what I meant when I stated that the "order by conflicts with distinct" occurs when there are fields in the order by clause that are not included in the select clause.  The distinct argument requires that all order by arguments be fields in the returned result.

Question for you.  I get order by conflict with distinct error when I click on the combo box #2 which is the Job Number field.  Does this mean that the  error is in the Job Number after update code or is it resulting from the sql in combo box #1?

This is what confusing me.  Because both combo box #1 and combo box #2 has an orderby clause.

Hi Darlene,
I looked at the code you had for all 3 combo boxes, and the only that would have caused a distinct/orderby conflict is cboProjectName...and if you added [job Number] to your selection list, that conflict should have been resolved. .......but, from what you are telling me, you are still getting  the distinct/order by conflict?


Hmm.  The only thing I can think of is that you need to group by JobNumber before odering by JobNumber because a job could have more than one project.  So, try inserting the group by clause (below) on the line in your code just before the order by.  

strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number] "

Try it and let me know.

Okay did what sent.  I get the following error when I click on combo box #2:

"You tried to execute a query that does not include the specified expression 'Project Name' as part of the aggregate function."

These are the codes for combo box #1 and 2 as I have them now:

Private Sub cboProjectName_AfterUpdate()

'COMBO BOX #1

     Dim strSQL As String
     Dim strSQLSF As String
         
     cboJobNumber = Null
     cboPartNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"
     
     cboJobNumber.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
         
     Me!sfrmForm.LinkChildFields = "[Project Name]"
     Me!sfrmForm.LinkMasterFields = "[Project Name]"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = Null
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number],ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And  "
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'"
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.PartNumber;"
   
    cboJobNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "'"
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Also yes, in the job number field the job number does appear more than once.

I think we are on the right track. The message is telling you that projectname has to be included in the group by clause.  

strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "

Try it and let me know.
 

Now that works perfectly for combo box #1 and 2, but now I dont get a list of part number in combo box # 3.  #3 is is not coming from the table itself it's coming from a query.  The example that I used had two tables but I want this information from the same table. Is that possible? This is the code for combo box #3:

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

    Dim strSQLSF As String
   
    strSQLSF = " SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And "
    strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = '" & cboPartNumber & "'"


    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Same problem, need to group by partnumber/jobnumber/projectname because partnumber can appear on more than one job/project........and because of the grouping, you have to add an order by to see the [Project Name] in sequence.

strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[PartNumber], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"

Try it and let me know.

Okay, I added the above in the cboJobNumber after update code and changed

cboJobNumber.RowSource = strSQL -> cboPartNumber.RowSource = strSQL

Combobox 1 and 2 still works but I get the following error from #3:

"Syntax error (missing operator) in query expression:
strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And  "

So what did I do wrong.  I'm not getting anything out of the #3 box.

Now you've lost me...I thought the last change I submitted to you was for cboPartNumber_AfterUpdate code. Why did you change the cboJobNumber after update???  I thought that was working. Maybe you changed the wrong code??  If that is not the problem post your entire code again so I can look at it.

Okay, I'm sorry.  We worked on comboboxes 1 and 2 and I saved 3 for last.  Anyway here is the entire code that I have for all three boxes. Also take a look at the form_open code at the end, it may have some affect:

Private Sub cboProjectName_AfterUpdate()

'COMBO BOX #1

     Dim strSQL As String
     Dim strSQLSF As String
         
     cboJobNumber = Null
     cboPartNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"
     
     cboJobNumber.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
         
     Me!sfrmForm.LinkChildFields = "[Project Name]"
     Me!sfrmForm.LinkMasterFields = "[Project Name]"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = Null
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number],ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And  "
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[PartNumber], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "'"
         
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

    Dim strSQLSF As String
   
    strSQLSF = " SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And "
    strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = '" & cboPartNumber & "'"


    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim strSQL As String
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
     cboProjectName.RowSource = strSQL
   
End Sub

Darlene,
I think the problem with combo#3 is that there is no sql to update the selection list for cboProjectName in the afterupdate of cboPartNumber. Try inserting the following code starting in the first line of code in cboPartNumber_AfterUpdate just after the "Dim strSQLSF As String".  If this code is not added, the update of ProjectNames in combo#3  occurs only one time (when the form opens).  After you've made the changes, if there are any errors, post your entire code.  It should be easier to debug now that all the code will have been entered.
_____________________________________________________
Dim strSQL As String
          cboProjectName = Null
     cboJobNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[ProjectName];"
     
     cboProjectName.RowSource = strSQL

_________End of Insert Code__________________________________


I have summarized what your code is doing below.

------------cbo#3ProjectName_AfterUpdate Event------------------
Combo#1> cboJobNumber> groups by JobNumber/Project Name>Orders by>JobNumber
      code fired in cboProjectName_AfterUpdate updates selection list for cboJobNumber        
      subform displays list of ProjectNames for JobNumber selected from combo box


------------cbo#1JobNumber_AfterUpdate
Combo#2> cboPartNumber>> groups by PartNumber/JobNumber/ProjectName>>>Orders by ProjectName >>>>code fired in cboJobNumber_AfterUpdate updates selection list for cboPartNumber.  subform displays list of ProjectNames/JobNumbers for PartNumber selected


---------------cbo#2cboPartNumber_AfterUpdate-----------------------------------------------
Combo#3>cboProjectName>>>>>>>no code fired in cboPartNumber_AfterUpdate updating selection list for ProjectName.  subform displays list of Project NamesJob Numbers/PartNumbers

I'm still getting the same error:

"Syntax error (missing operator) in query expression:
strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And  "

Here's the code after I inserted what you gave me.  I also notice that the partnumber is not mentioned in the select statement but the code is in partnumber after update:

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

     Dim strSQL As String
     
     cboProjectName = Null
     cboJobNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[ProjectName];"
     
     cboProjectName.RowSource = strSQL

    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""

    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Waiting to hear from you.

Darlene,
change this:
 ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And  "

to this:
 (ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "')"

If PartNumber is displayed on your subform after making your selection from combo#3, then go ahead and add it to select, group by, and order by as last field in the sort;  if not ...you don't
need to do anything.

Let me know what happened.

Okay, you lost me this time.  Do I paste this in like this:

     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE  (ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "')"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[ProjectName];"


using the parenthesis?

LOL!!yes.

Okay, I'm still getting the missing operator error and I'm not sure anymore where it's coming from.  It's got to be the following because the cbo reads the name:

strSQL = strSQL & " WHERE  (ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "')"

But I can tell if the error is coming from the cboProjectName_afterupdate or the cboPartNumber after_update.  I don't the error until I click on the PartNumber combobox. The sql came out of the cboPartNumber_afterupdate.

If the error does not crop up until you hit the cboPartNumber combo then the AfterUpdate of that combo box is where I would expect the error.  In looking at the most recent code for cboPartNumber_AfterUpdateCode, I just realized the strSQLSF code for the subform is no longer there.  So that must be it.

Right after this line in cboPartNumber_AfterUpdate>>>> cboProjectName.RowSource = strSQL

Insert the following code for the subform, making sure you don't overwrite any of the existing code.
    Dim strSQLSF As String     '<<<<<this should really be with the declarations at the beginning of the code for combo#3>>>>>>>>

    strSQLSF = " SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And "
    strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = '" & cboPartNumber & "'"



We should be very close.  Let me know!

I'm not getting the error anymore but the when I select a job number from cboJobNumber the whole subform is empty now.  

It not filtering by Job Number anymore and I stop getting the syntax error after I took the "And" from the end of the following:

strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'And"

in the cboJobNumber_afterupdate.

I replaced what you sent and that's when the subform ended up empty.

Okay my mistake

I replaced what you sent in the wrong combobox.  I'm not getting the syntax error anymore because I deleted the "And" and the jobnumber combobox is filtering now.  But I'm still getting the "order by clause conflict with the distinct".  This is the codes I have now:

Private Sub cboProjectName_AfterUpdate()

'COMBO BOX #1

     Dim strSQL As String
     Dim strSQLSF As String
         
     cboJobNumber = Null
     cboPartNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"
     
     cboJobNumber.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
         
     Me!sfrmForm.LinkChildFields = "[Project Name]"
     Me!sfrmForm.LinkMasterFields = "[Project Name]"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = Null
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number],ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[PartNumber], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "'"
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

     Dim strSQL As String
     
     cboProjectName = Null
     cboJobNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE (ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "')"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[ProjectName];"
     
     cboProjectName.RowSource = strSQL
     
     strSQLSF = " SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
     strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And "
     strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = '" & cboPartNumber & "'"


    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""

    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Darlene,
Which combo box is giving you the order by conflict with distinct?  I want to be sure I am looking at the right code.  And if I understand you correctly, that is the only error showing up as of now??

Combo box 1 and 2 are filtering perfectly.  Combobox #3 is the one that is not filtering and I get the "ODERBY clause conflict with DISTINCT".

Darlene,
Even though combo  #2 (cboPartNumber) is filtering correctly, it's order by is [ProjectName], which is not on the selection list for Combo#2...........this appears to be where the conflict is coming from. You can verify that ProjectName does not appear on combo#2's selection list, or if it does, some project names appear to be out of sequence.

This would explain why combo#3 (cboProjectName) is not working because when you try to make a selection of a ProjectName on the cboProjectName combo box#3......guess what ...it is not in the right sort order and the order by conflict has manifested itself.

 Add [ProjectName] to the Select statement for combo #2 (note that ProjectName appears in the order by clause in code, but it is not on the selection list for cboPartNumber (both, in code or when you click the button)

Let me know.



 

Okay question:

Does Combobox #1 (cboProjectName) afterupdate triggers combobox#2 (cboJobNumber)?  This if so, Combobox #2 afterupdate triggers Combobox #3 (cobPartNumber) right?

Then when I try to select a part number and it gives me the conflict error, it shows this statement as the cause:

ComtechSystemsIncConfigurationManagementData.[Project Name]

but I don't know from which combobox, #2 or #3 because they both have the same statement.

Also in the partial code out of the cboPartNumber

     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE (ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "')"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"

Part Number is not mentioned at all so does that matter.

I'm really trying to understand how this works, please be patient with me.

Okay, got it to show the part number in the cboPartNumber field with the following code:

Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = Null
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Project Name]  FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "'"
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

and I'm not getting the conflict error anymore.  Now the only thing and I think this is with the cboPartNumber combobox, that it shows all the part numbers and I need for it to only show the ones associated with the cboProjectName and cboJobNumber choosen.

Will this have to do with the cboPartNumber after update?

Hi Darlene,
Don't worry about me being patient with you...the only time I get impatient is when I am expected to meet a deadline and things are impeding me from making it ....which isn't the case here.

Good job updating the code for combo#2.  I think you are getting the gist of it, now.
Now to your questions:
1. <<<Does Combobox #1 (cboProjectName) afterupdate triggers combobox#2 (cboJobNumber)?  This if so, Combobox #2 afterupdate triggers Combobox #3 (cobPartNumber) right?>>>>>

Yup, you are on the right track, although "triggers" is not the right word..."filters" is better. selections made in Combo #1 create a filtered list, which is passed to combo #2.  Combo#2's selections filter the filtered list, which is then passed to combo#3 for the final selection...........hence, the term cascading combos.

2.<<<<<Now the only thing and I think this is with the cboPartNumber combobox, that it shows all the part numbers and I need for it to only show the ones associated with the cboProjectName and cboJobNumber choosen.  Will this have to do with the cboPartNumber after update?>>>>>>>

No, as per #1 above , it as to do with an incomplete filtered list being received from combo #2.

change the where clause in combo#2 from this:
 strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"

To this:
 strSQL = strSQL & " WHERE (ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'" & " And  ComtechSystemsIncConfigurationManagementData.[JobNumber] = '" & cboJobNumber & "'")"

Try it and let me know.

Okay thank you, thank you, thank you.  It filters the part number combobox but if you go to select a part number from the combobox there is no information in the subform. The subform is blank.  This is my entire code now for the cboJobNumber afterupdate, which works:


Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = Null
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Project Name]  FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'"
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

So now to make a selection from the part number combobox, it blanks out the subform, the subform is empty. The partnumber combobox is not filtering when you make a selection in it.

I'm raising your points because you have been so patient and very helpful in making me understand what I'm doing.

Darlene,
Glad I could help.  Thanks for points.

Re your questions:
Now that combo #2 is filtering properly, you are passing a correctly filtered selection list to combo#3, and you can see that the PartNumbers are on the list for Combo#3 buuuutttttt, guess what?
Although you can see all the partnumbers on the list, you can't select a specific PartNumber  because the field PartNumber has not been made available for selection (it doesn't respond to your mouse click on a specific partnumber).  In order to be made available for selection, PartNumber must appear in the select clause in the sql statement. Also, in order for Combo#3 to filter a specific PartNum for a specific project and job, the ProjectName, JobNumber, and PartNumber must all be part of the where clause.  

With that in mind you need to update the selection list and where clause in combo #3.

change this:
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"

To this:
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber],  ComtechSystemsIncConfigurationManagementData.[PartNumber]
FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'" And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'" And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[PartNumber] = '" & cboPartNumber & "'"

Let me know what happens.

Okay,

I get a "type mismatch" on this line:

strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'" And ""

When I select a part number, Project Name and Job Number goes blank.

Okay I figured out the type mismatch problem:

THIS

strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'" And ""

SHOULD BE

strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"

The AND was in the wrong place but my subform is still going blank and also the Project Name and Job Number is blank and the only thing you see is the part number selected.

Also my SHOWALL button won't reset.  Here's the code for the SHOWALL button:

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

     cboProjectName = Null
     cboJobNumber = Null
     cboPartNumber = Null
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     Me.RecordSource = "ComtechSystemsIncConfigurationManagementData"
     Me.Requery
         
exit_cmdShowAll_Click:
    Exit Sub
   
err_cmdShowAll_Click:
    MsgBox Err.Description
    Resume exit_cmdShowAll_Click
   
End Sub

Hi Darlene,
The ShowAll is an easy fix (see below), but I need to all the code for Combo#3 in order to fix Combo#3.

Change:
     cboProjectName = Null
     cboJobNumber = Null
     cboPartNumber = Null

To:
     cboProjectName = ""
     cboJobNumber = ""
     cboPartNumber = ""

Spaces ("") are used to reset text data type

Let me know.

Okay I changed the SHOWALL code to the above and it works once and when you hit the SHOWALL button again the Project Name field is blank and you get a "ENTER PARAMETER VALUE" for the cboProjectName field.  You have to close the form and reopen again for it to work again.

Here's the code for combobox #3:

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

     Dim strSQL As String
    Dim strSQLSF As String
     
     cboProjectName = Null
     cboJobNumber = Null
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber],  ComtechSystemsIncConfigurationManagementData.[PartNumber] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.PartNumber = '" & cboPartNumber & "'"

     
     cboProjectName.RowSource = strSQL
     
     strSQLSF = " SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And  "
     strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And "
     strSQLSF = strSQLSF & " qryFilterInfo.PartNumber = '" & cboPartNumber & "'"


    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""

    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Thank you.

Darlene,
1. Go thru each combo and make sure that you are using Spaces ("") to reset text data types.
ProjectName, JobNumber, and PartNumber are all text data types.
  (example below)  

'COMBO BOX #3
Change this:
     cboProjectName = Null
     cboJobNumber = Null
To this:
    cboProjectName = ""
     cboJobNumber = ""

2. You left the group by and order by clauses out of combo#3.  You need to put the clauses shown below after the where clause:

strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber], ComtechSystemsIncConfigurationManagementData.[PartNumber] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[ProjectName],ComtechSystemsIncConfigurationManagementData.[JobNumber];"

Let me know how it goes.

Okay I'm getting a syntax error (missing operator) here

     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[ProjectName], ComtechSystemsIncConfigurationManagementData.[JobNumber], ComtechSystemsIncConfigurationManagementData.[PartNumber] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[ProjectName],ComtechSystemsIncConfigurationManagementData.[JobNumber];"

This is in Combobox #3.

Boy, Access error messages can be very vague, can't they?  One thing I see is that I gave you incorrect spelling for the project name and job number fields....Sorry about that:
Try correcting the misspellings and see if that fixes the problem.  If not, let me know.....and send me the entire code for combo#3.

[ProjectName] >>>>>>>>should be [Project Name]

[JobNumber]   >>>>>>>>should be [Job Number]


You need to correct all occurences of misspelling. Do a global search and replace, but select one at a time, don't do a replace ALL.  In case you don't know how to do this, while in the VB Editor, highlight [ProjectName], select Edit>replace from the command menu
           search field  [ProjectName]
           replacement [Project Name]

Hopefully that will take care of it; if  not, post entire code for Combo#3.

Also, I would like to clarify I prefer re-setting text fields to spaces, although null could be used......the reason being is that nulls tend to be problematic in queries, etc if not specially handled.....whereas spaces do not have to be speciallly handled.

Okay here is combobox #3 code and I did recognize the changes to the field codes before I ran the code.  But I'm getting a syntax error (missing operator) error on the two lines I inserted that you sent.  Here;s the code:

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

     Dim strSQL As String
     Dim strSQLSF As String
     
     cboProjectName = ""
     cboJobNumber = ""
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number],  ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.PartNumber "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name],ComtechSystemsIncConfigurationManagementData.[Job Number];"

     
     cboProjectName.RowSource = strSQL
     
     strSQLSF = " SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY qryFilterInfo.[Project Name], qryFilterInfo.[Job Number], qryFilterInfo.PartNumber"
     strSQL = strSQL & " ORDER BY qryFilterInfo.[Project Name],qryFilterInfo.[Job Number];"


    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""

    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Ok, your where clause is incomplete.  In your final selection, you are selecting a partnumber for the ProjectName and JobNumber previously selected with the other combo boxes, and your where clause must reflect that.

 Substitute the following where clause for the one you have in combo#3, being careful not to overwrite any code not related to the where clause.

    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "' And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[PartNumber] = '" & cboPartNumber & "'"

Let me know how it goes.

Okay this is what I have now:

     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number],  ComtechSystemsIncConfigurationManagementData.PartNumber FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "' And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.PartNumber = '" & cboPartNumber & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.PartNumber "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name],ComtechSystemsIncConfigurationManagementData.[Job Number];"

I am still getting the syntax error (missing operator) in query expression here:

     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "' And"

This happens as I try to select a part number.

It looks pretty good to me.  Try these two things and see if they make any difference:
1. put brackets around PartNumber ......PartNumber should be [PartNumber]
2.  Eliminate the space at the end of your group by clause:
             PartNumber "    >>>>>>>>>> [PartNumber]"

Let me know.

Nope, still getting the syntax error (missing operator).

I thought the only reason that Job Number and Project Name are in brackets because there is  space between them, there is not space between partnumber in the table.

Okay and it's not the statement in the cboPartNumber because I went through and took the AND out of the statement and ran it again, and the syntax error still appeared with the AND in it.  So it's got to be this one in cboJobNumber:

    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[PartNumber], ComtechSystemsIncConfigurationManagementData.[Project Name]  FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'And"
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[PartNumber], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name]"
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"

Yes it's the one in cboJobNumber.  I took the AND out and I still get the syntax error regarding a missing operator.

You are right about the braackets...I was just playing it safe.  You are also right about Combo#2 being the problem, although I could have sworn that we fixed this before......have you got little gremlins that sneak in and change the code after we have fixed it???

The problem with the missing operator is the "and" just before the Group by
change this:
 strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'And"
    strSQL = strSQL & " GROUP BY

To this:
 strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber"
    strSQL = strSQL & " GROUP BY


But I also noticed that the Select statement for Combo#2 is by PartNumber and ProjectName...
and it should be JobNumber and ProjectName.....so replace PartNumber with JobNumber.

Let me know.

When I change the SELECT statement to this "replace PartNumber with JobNumber" it puts the JobNumber in the cboPartNumber box.

Also I made the correction and I don't get the syntax error anymore BUT when I select a part number from cboPartNumber, combobox 1, 2 and subform goes blank.

Also the SHOWALL button only works once after replacing part number with job number.

I think I'm beginning to understand just don't know which end to start.

Darlene,
Don't  panic.  __________________________________________________________________________
if the combo box#2 is messed up then it can be only this:

1. PartNumber has to be added to the selection list in addition to ProjectNumber and JobNumber  (in other words, instead of telling you to replace, I should have told you to add it)
______________________________________________________________________________
It  makes sense when you think about it; the selection list should include all fields that are going to be displayed.  So, don't panic .....now that syntax error is gone, we have a minor correction.  Just go back to the selection lists for combo#1,2, and 3 and make sure that ProjectNumber, JobNumber, and PartNumber are on the selection lists for each combo.  If any are missing, add them to the selection list....but just to the selection list only.....don't make any changes to the where , group by, or order clauses.

Waiting to hear from you.
 

Okay I get this error when I add Part Number to combobox #1 Select statement:

You tried to execute a query that does not include a specified expression "Part Number" as part of an aggregated funtion.

This happens when I try to select from cboJobNumber.

Here's the whole codes I have now, I pray the gremlins hasn't struck again.

Option Compare Database
Option Explicit

Private Sub cboProjectName_AfterUpdate()

'COMBO BOX #1

     Dim strSQL As String
     Dim strSQLSF As String
         
     cboJobNumber = ""
     cboPartNumber = ""
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData "
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name] "
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"
     
     cboJobNumber.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
         
     Me!sfrmForm.LinkChildFields = "[Project Name]"
     Me!sfrmForm.LinkMasterFields = "[Project Name]"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = ""
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Project Name]  FROM ComtechSystemsIncConfigurationManagementData "
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'"
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.PartNumber, ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[Project Name]"
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And  "
    strSQLSF = strSQLSF & " qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
   
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

     Dim strSQL As String
     Dim strSQLSF As String
     
     cboProjectName = ""
     cboJobNumber = ""
     
     strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number],  ComtechSystemsIncConfigurationManagementData.[PartNumber] FROM ComtechSystemsIncConfigurationManagementData"
     strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "' And"
     strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[PartNumber] = '" & cboPartNumber & "'"
     strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Project Name], ComtechSystemsIncConfigurationManagementData.[Job Number], ComtechSystemsIncConfigurationManagementData.[PartNumber]"
     strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name],ComtechSystemsIncConfigurationManagementData.[Job Number];"

     
     cboProjectName.RowSource = strSQL
     
     strSQLSF = " SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
     strSQL = strSQL & " GROUP BY qryFilterInfo.[Project Name], qryFilterInfo.[Job Number], qryFilterInfo.[PartNumber]"
     strSQL = strSQL & " ORDER BY qryFilterInfo.[Project Name],qryFilterInfo.[Job Number];"


    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""

    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];PartNumber"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];PartNumber"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

     cboProjectName = ""
     cboJobNumber = ""
     cboPartNumber = ""
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     Me.RecordSource = "ComtechSystemsIncConfigurationManagementData"
     Me.Requery
         
exit_cmdShowAll_Click:
    Exit Sub
   
err_cmdShowAll_Click:
    MsgBox Err.Description
    Resume exit_cmdShowAll_Click
   
End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim strSQL As String
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name] FROM ComtechSystemsIncConfigurationManagementData ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"
     cboProjectName.RowSource = strSQL
   
End Sub

One more thing, should I start another question because this one is getting really long and reference it to the next question?

No, don't bother....I think we are almost there.  The message about the aggregate function means you need to add the ParNumber to the Group By Clause in combo# 1, which makes perfect sense if you want to display only PartNumbers related to the JProjectName you selected in combo #1.  To continue the logic, after you select the JobNumber in Combo#2, you want to see only those PartNumbers related to the ProjectName and JobNumber previously selected. And finally, after selecting PartNumber in Combo#3, you want to display only the specific Project,Job, and Part selected.

I think this is the big moment. Don't keep me in suspense too long!!

Okay sorry, when I add the part number to the group by clause in combobox #1 (cboProjectName) then the cboJobNumber (combobox #2) displays the part numbers.

This is a minor problem.Change the order they appear in the select statement and see if that does it. Change order to >>>ProjectName/JobNumber/PartNumber

The above refers to the Select clause, not the order by clause.

Let me know.

if that was insufficient, then change the order of fields in the group by clause to match.

Working on it now and yes send me the email address.

Email: *removed*

oops!
sorry-sent before notice recieved...please erase.  Will put in my profile.

Did you receive my e-mail?  I have to fiind a way to send you the attachment.  For me to send it from here is saying it's unsafe and not attaching it.

Thank you puppydogbuddy it works perfectly.  For those of you who are interested in the solution here it is:

Option Compare Database
Option Explicit

Private Sub cboProjectName_AfterUpdate()

'COMBO BOX #1

     Dim strSQL As String
     Dim strSQLSF As String
     
     cboJobNumber = ""
     cboPartNumber = ""
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     
     
    strSQL = "SELECT ComtechSystemsIncConfigurationManagementData.[Job Number]"
    strSQL = strSQL & " FROM ComtechSystemsIncConfigurationManagementData"
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "'"
    strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Job Number]"
    strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Job Number];"

    cboJobNumber.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM qryFilterInfo "
     strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"
     
     Me!sfrmForm.LinkChildFields = "[Project Name]"
     Me!sfrmForm.LinkMasterFields = "[Project Name]"
     Me.RecordSource = strSQLSF
     Me.Requery
         
End Sub

Private Sub cboJobNumber_AfterUpdate()

'COMBOBOX #2

    Dim strSQL As String
    Dim strSQLSF As String
       
    cboPartNumber = ""
    Me!sfrmForm.LinkChildFields = ""
    Me!sfrmForm.LinkMasterFields = ""
   
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[PartNumber]"
    strSQL = strSQL & " FROM ComtechSystemsIncConfigurationManagementData"
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "'"
   
    cboPartNumber.RowSource = strSQL
   
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And"
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "'"
   
     
    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number]"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub cboPartNumber_AfterUpdate()

'COMBO BOX #3

     Dim strSQL As String
     Dim strSQLSF As String
     
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     
   
     
    strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[PartNumber]"
    strSQL = strSQL & " FROM ComtechSystemsIncConfigurationManagementData"
    strSQL = strSQL & " WHERE ComtechSystemsIncConfigurationManagementData.[Project Name] = '" & cboProjectName & "' And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[Job Number] = '" & cboJobNumber & "' And"
    strSQL = strSQL & " ComtechSystemsIncConfigurationManagementData.[PartNumber] = '" & cboPartNumber & "'"

     cboPartNumber.RowSource = strSQL
     
    strSQLSF = "SELECT * FROM qryFilterInfo "
    strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "' And"
    strSQLSF = strSQLSF & " qryFilterInfo.[Job Number] = '" & cboJobNumber & "' And"
    strSQLSF = strSQLSF & " qryFilterInfo.[PartNumber] = '" & cboPartNumber & "'"


    Me!sfrmForm.LinkChildFields = "[Project Name];[Job Number];[PartNumber]"
    Me!sfrmForm.LinkMasterFields = "[Project Name];[Job Number];[PartNumber]"
    Me.RecordSource = strSQLSF
    Me.Requery
   
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

     cboProjectName = ""
     cboJobNumber = ""
     cboPartNumber = ""
     Me!sfrmForm.LinkChildFields = ""
     Me!sfrmForm.LinkMasterFields = ""
     Me.RecordSource = "ComtechSystemsIncConfigurationManagementData"
     Me.Requery
     Me.sfrmForm.Form.OrderBy = "[Project Name],[Job Number],[PartNumber]"
     Me.sfrmForm.Form.OrderByOn = True
         
exit_cmdShowAll_Click:
    Exit Sub
   
err_cmdShowAll_Click:
    MsgBox Err.Description
    Resume exit_cmdShowAll_Click
   
End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim strSQL As String
    Dim strSQLSF As String
   
    cboProjectName = ""
     cboJobNumber = ""
     cboPartNumber = ""


   
strSQL = " SELECT DISTINCT ComtechSystemsIncConfigurationManagementData.[Project Name]"
strSQL = strSQL & " FROM ComtechSystemsIncConfigurationManagementData"
strSQL = strSQL & " GROUP BY ComtechSystemsIncConfigurationManagementData.[Project Name]"
strSQL = strSQL & " ORDER BY ComtechSystemsIncConfigurationManagementData.[Project Name];"

Me!cboProjectName.RowSource = strSQL

strSQLSF = "SELECT * FROM qryFilterInfo "
strSQLSF = strSQLSF & " WHERE qryFilterInfo.[Project Name] = '" & cboProjectName & "'"

Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""

Me!sfrmForm.LinkChildFields = "[Project Name]"
Me!sfrmForm.LinkMasterFields = "[Project Name]"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

This is the form from Candice Trip Downloads

Again thank you puppydogbuddy for your patience and all your help.

You are welcome. Glad I could help.