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.
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
Part of query with solution:
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
And for the adjustsments on strCriteria:
strCriteria = Customer & "And" & CustomerLocation & "And" & CustomerLocationPlace & .........