I know I can do this with multivalue fields, but this causes other problems.

I have a main split-form with comboboxes to sort all kinds of different combinations. For example, I have a cbo_Customers, cbo_CustomerLocations, etc... (see VBA code at bottom)

The same CustomerLocation can have different Customers. So it is possible to have 2 different Customers which have the same CustomerLocation.

In my main split-form I have a combobox named cbo_CustomerLocations which looks up values from tbl_CustomerLocations. tbl_CustomerLocations consists of 4 fields. CustomerLocationID, LocationCompanyName, LocationCompanyPlace, CustomerID (which is linked to tbl_Customers)

I have 1 location named: TESTLOCATION,
I have 2 Customers named: CUSTOMER_1 and CUSTOMER_2

I tried to avoid multivalue fields, so therefore in tbl_CustomerLocations the record with value TESTLOCATION is twice in this table because the field CustomerID is linked to CUSTOMER_1 and the other record linked to CUSTOMER_2

Now in my main form in combobox cbo_CustomerLocations this TESTLOCATION is also shown twice (2 different CustomerLocationID)

I want this combobox to GROUP BY CustomerLocationName. And, if I select this (grouped) TESTLOCATION my form must show all records where string "TESTLOCATION" is found. (TESTLOCATION has different ID's)

This is my SQL where TESTLOCATION is shown twice:

SELECT tbl_CustomerLocations.CustomerLocationID, 
tbl_CustomerLocations.LocationCompanyName, 
tbl_CustomerLocations.LocationCompanyPlace
FROM tbl_CustomerLocations
ORDER BY tbl_CustomerLocations.LocationCompanyName;

I tried something like:

SELECT Count(tbl_CustomerLocations.CustomerLocationID) AS
CountOfCustomerLocationID, tbl_CustomerLocations.LocationCompanyName
FROM tbl_CustomerLocations
GROUP BY tbl_CustomerLocations.LocationCompanyName;

This does combine TESTLOCATION in the combobox, but my records do not show up in my split-form

I also tried this, found on Stack Overflow:

SELECT * FROM tbl_CustomerLocations e1, tbl_CustomerLocations e2 
WHERE e1.LocationCompanyName = e2.LocationCompanyName
AND e1.CustomerLocationID != e2.CustomerLocationID;

This is also a dead end.

In my main split-form, after I update combobox cbo_CustomerLocations the following VBA code is called:

Private Sub cbo_CustomerLocations_AfterUpdate()
Call SearchCriteria
End Sub


Function SearchCriteria()
Dim Customer, CustomerLocation as String
Dim task, strCriteria As String

If IsNull(Me.Cbo_Customers) Then
    Customer = "[CustomerID] like '*'"
Else
    Customer = "[CustomerID] = " & Me.Cbo_Customers
End If

If IsNull(Me.cbo_CustomerLocations) Then
    CustomerLocation = "[CustomerLocationID] like '*'"
Else
    CustomerLocation = "[CustomerLocationID] = " & cbo_CustomerLocations
End If

strCriteria = Customer & "And" & CustomerLocation

task = "Select * from qry_Administration where (" & strCriteria & ")"

Me.Form.RecordSource = task
Me.Form.Requery

So basically I want to select the (grouped) TESTLOCATION in cbo_CustomerLocations. Then function SearchCriteria is called and my form shows all records where string TESTLOCATION is found. TESTLOCATION has different ID's.

I guess I also have to edit these lines in some way?

strCriteria = Customer & "And" & CustomerLocation
task = "Select * from qry_Administration where (" & strCriteria & ")"

because strCriteria gives problems with this grouped field?

I know it is a lot of information, but I try to be as clearly as possible.

CodePudding user response：

If I'm understanding correctly, you want the cbo_CustomerLocations combobox in the main form to contain a list of distinct customer locations. Upon selecting a value, you want the subform to be filtered so that only customers in the selected location appear, yes? Here's what I would recommend:

Combobox: tbl_CustomerLocations contains data such as this:

You want the combobox to contain this list:

To achieve this, the data source for your combobox should be as follows:

SELECT LocationCompanyPlace
FROM tbl_CustomerLocations
GROUP BY LocationCompanyPlace

OR

SELECT DISTINCT LocationCompanyPlace
FROM tbl_CustomerLocations
GROUP BY LocationCompanyPlace

(Both of the above queries should return the same results.)

Now, you'll need to update your VBA code.

Sub cbo_CustomerLocations_AfterUpdate(): (No changes needed)

Private Sub cbo_CustomerLocations_AfterUpdate()
    Call SearchCriteria
End Sub

Function SearchCriteria():

Function SearchCriteria()
    Dim Customer, CustomerLocation as String
    Dim task, strCriteria As String
    Dim recordSource As string


    If IsNull(Me.Cbo_Customers) = False Then                    'If a Customer is selected, filter by selected Customer
        recordSource = "SELECT * FROM qry_Administration WHERE [CustomerID] = " & Me.Cbo_Customers
    Else                                                        'If a Customer is NOT selected, check to see if a CustomerLocation is selected
        If IsNull(me.cbo_CustomerLocations) = False Then        'If a Customer Location is selected, filter by selected CustomerLocation
            'Use this line if the CustomerLocationPlace field exists in qry_Administration
            recordSource = "SELECT * FROM qry_Administration WHERE [CustomerLocationPlace]='" & Me.Cbo_CustomerLocations & "'"
            
            'Use the recordSource below if the CustomerLocationPlace field DOES NOT exist in qry_Administration
            'This line joins qry_Administration to the tbl_CustomerLocations table so that you can filter on the CustomerLocationPlace field
            'By using "SELECT a.*", only records from qry_Administration are returned
            'recordSource = "SELECT a.* FROM qry_Administration a INNER JOIN dbo.tbl_CustomerLocations l ON a.CustomerID = l.CustomerID WHERE l.CustomerLocationPlace='" & Me.Cbo_CustomerLocations & "'"
        Else                                                    'If neither a Customer nor a Customer Location are selected, return all records in qry_Administration           
            recordSource = "SELECT * FROM qry_Administration"
        End If
    End If

    Me.Form.RecordSource = recordSource
    Me.Form.Requery
End Function

I have included comments to explain the code above, but please note that you may need to adjust the code in the If part of the nested if statement. If the CustomerLocationPlace field exists in qry_Administration, then you may use the code as is. However, if the CustomerLocationPlace field does NOT exist in qry_Administration, then you should comment out line 12 (recordSource = "SELECT * FROM qry_Administration WHERE [CustomerLocationPlace]='" & Me.Cbo_CustomerLocations & "'") & uncomment line 17 ('recordSource = "SELECT a.* FROM qry_Administration a INNER JOIN dbo.tbl_CustomerLocations l ON a.CustomerID = l.CustomerID WHERE l.CustomerLocationPlace='" & Me.Cbo_CustomerLocations & "'").

I believe this solution should address your problem. If you have any questions or concerns, let me know.

CodePudding user response：

With a little editing of your solution I managed to get it working.

The solution with grouped combobox works. You were also correct about CustomerLocationPlace being part of the query. I did not use the CustomerLocationPlace field, but I used the CustomerLocationName field instead. I also had to add the following line:

CustomerLocationPlace = "[LocationCompanyPlace] = '" & Me.cbo_CustomerLocations.Column(1) & "'"

I stripped my VBA so it was more easier for you guys. My script was a little bit more complicated then I posted earlier. I did use your solution, but I left my VBA script intact like I had before. Just for explaining purpose here is my full VBA script of function StrCriteria with the solution of David Buck implemented:

Function SearchCriteria()

    Dim Customer, CustomerLocation, CustomerLocationPlace, Protocol, SampleProvider, BRL, ProjectLeader, LabNumber, ExecutionDate, Classification, SampleProvider2, Material As String
    Dim Extern, Intern As String
    Dim strText, strSearch As String
    Dim task, strCriteria As String

    Me.FilterOn = True

    If IsNull(Me.txt_Search) Or Me.txt_Search = "" Then
        strText = "[DataID] like '*'"

    Else
        strSearch = Me.txt_Search.Value
            strText = "(LabNumberPrimary like  ""*" & strSearch & "*"")" & "Or" & _
            "(LabNumber_2_MH like  ""*" & strSearch & "*"")" & "Or" & _
            "(LabNumber_3_ASB like  ""*" & strSearch & "*"")" & "Or" & _
            "(LabNumber_4_CT like  ""*" & strSearch & "*"")" & "Or" & _
            "(LabNumber_5_LA like  ""*" & strSearch & "*"")" & "Or" & _
            "(LabNumber_6_CBR like  ""*" & strSearch & "*"")" & "Or" & _
            "(HerkeuringNumber like  ""*" & strSearch & "*"")" & "Or" & _
            "(Protocol like  ""*" & strSearch & "*"")" & "Or" & _
            "(BRL like  ""*" & strSearch & "*"")" & "Or" & _
            "(PrincipalCompanyName like  ""*" & strSearch & "*"")" & "Or" & _
            "(ProjectLeaderName like  ""*" & strSearch & "*"")" & "Or" & _
            "(Material like  ""*" & strSearch & "*"")" & "Or" & _
            "(Classification like  ""*" & strSearch & "*"")" & "Or" & _
            "(PrincipalContactName like  ""*" & strSearch & "*"")" & "Or" & _
            "(LocationContactName like  ""*" & strSearch & "*"")" & "Or" & _
            "(SampleProviderName like  ""*" & strSearch & "*"")" & "Or" & _
            "(QuotationNumber like  ""*" & strSearch & "*"")" & "Or" & _
            "(LocationCompanyName like  ""*" & strSearch & "*"")"
    End If

    If Me.chk_Ex = True Then
        Extern = "[AuditExID] = 2"
    Else
        Extern = "[AuditExID] like '*'"
    End If

    If Me.chk_In = True Then
        Intern = "[AuditInID] = 2"
    Else
        Intern = "[AuditInID] like '*'"
    End If

    If IsNull(Me.cbo_CustomerLocations) Then
        CustomerLocation = "[CustomerLocationID] like '*'"
    Else
        CustomerLocation = "[LocationCompanyName] = '" & Me.cbo_CustomerLocations.Column(0) & "'"
        CustomerLocationPlace = "[LocationCompanyPlace] = '" & Me.cbo_CustomerLocations.Column(1) & "'"
    End If

    If IsNull(Me.Cbo_Customers) Then
        Customer = "[CustomerID] like '*'"
    Else
        Customer = "[CustomerID] = " & Me.Cbo_Customers
    End If

    If IsNull(Me.cbo_Protocol) Or Me.cbo_Protocol = "" Then
        Protocol = "[ProtocolID] like '*'"
    ElseIf Me.cbo_Protocol = 5 Then
        Protocol = "[ProtocolID] in (" & TempVars!tempProtocol & ")"
    Else
        Protocol = "([ProtocolID] = " & Me.cbo_Protocol & ")"
    End If

    If IsNull(Me.cbo_Classification) Or Me.cbo_Classification = "" Then
        Classification = "[ClassificationID] like '*'"
    ElseIf Me.cbo_Classification = 5 Then
        Classification = "[ClassificationID] in (" & TempVars!tempClassification & ")"
    Else
        Classification = "([ClassificationID] = " & Me.cbo_Classification & ")"
    End If

    If IsNull(Me.cbo_SampleProviders) Or Me.cbo_SampleProviders = "" Then
        SampleProvider = "[SampleProviderPrimaryID] like '*'"
    ElseIf Me.cbo_SampleProviders = 6 Then
        SampleProvider = "[SampleProviderPrimaryID] in (" & TempVars!tempSampleProviders & ")"
    Else
        SampleProvider = "([SampleProviderPrimaryID] = " & Me.cbo_SampleProviders & ")"
    End If

    If IsNull(Me.cbo_SampleProviders2) Then
        SampleProvider2 = "[SampleProviderSecondaryID] like '*'"
    Else
        SampleProvider2 = "[SampleProviderSecondaryID] = " & Me.cbo_SampleProviders2
    End If

    If IsNull(Me.cbo_BRL) Or Me.cbo_BRL = "" Then
        BRL = "[BRLID] like '*'"
    ElseIf Me.cbo_BRL = 5 Then
        BRL = "[BRLID] in (" & TempVars!tempBRL & ")"
    Else
        BRL = "([BRLID] = " & Me.cbo_BRL & ")"
    End If

    If IsNull(Me.cbo_ProjectLeaders) Then
        ProjectLeader = "[ProjectLeaderID] like '*'"
    Else
        ProjectLeader = "[ProjectLeaderID] = " & Me.cbo_ProjectLeaders
    End If

    If IsNull(Me.txt_ExecutionDateTo) Then
        ExecutionDate = "[ExecutionDate] like '*'"
    Else
        If IsNull(Me.txt_ExecutionDateFrom) Then
            ExecutionDate = "[ExecutionDate] like '" & Me.txt_ExecutionDateTo & "'"
        Else
            ExecutionDate = "([ExecutionDate] >= #" & Format(Me.txt_ExecutionDateFrom, "mm/dd/yyyy") & "# And [ExecutionDate] <= #" & Format(Me.txt_ExecutionDateTo, "mm/dd/yyyy") & "#)"
        End If
    End If

    If IsNull(Me.cbo_Material) Or Me.cbo_Material = "" Then
        Material = "[MaterialID] like '*'"
    ElseIf Me.cbo_Material = 6 Then
        Material = "[MaterialID] in (" & TempVars!tempMaterial & ")"
    Else
        Material = "([MaterialID] = " & Me.cbo_Material & ")"
    End If

    strCriteria = Customer & "And" & CustomerLocation & "And" & CustomerLocationPlace & "And" & Protocol & "And" & SampleProvider & "And" & BRL & "And" & ProjectLeader & "And" _
            & ExecutionDate & "And" & Extern & "And" & Intern & "And" & Classification & "And" _
            & SampleProvider2 & "And" & Material & "And" & strText
                
    task = "Select * from qry_Administration where (" & strCriteria & ") order by ExecutionDate DESC"

    Debug.Print (task)
    
    Me.Form.RecordSource = task
    Me.Form.Requery

End Function