While normalizing some poorly designed tables today I discovered that I broke a form that had formerly been based on a query. Now, with my new junction table relationships, I couldn't have a query as the source any longer since it wasn't updateable. So, I dropped on the junction data into subtables and based the form off of its source table.
However, I had formerly offered a number of combo boxes to create where criteria to filter the form on load that were based on fields that were related through the query. Fields like FirstName LastName etc. Now these fields are found in a subform of the main form and so, I can't open the form using those combo/where filters any longer.
I can only imagine that I need to somehow create a recordset or query to apply the where criteria against and then based the form open on that new recordset. Unfortunately, I've not delved into recordset creation before so I'm only guessing that that is the solution and I have no idea where to start.
Help!?
Full Version: Build Where Criteria From Query/recordset
Although you've now included subforms, you should still be able to keep your combo box filters, but they will now filter the foreign key.
For example, if your main table may be:
tblContacts
ContactID
FirstName
LastName
your linked table may be
tblContactAddress
ContactAddressID
Address
City
StateProvince
ZipPostalCode
ContactID
On the main form, your "City" filter Control Source would be:
"SELECT DISTINCT City FROM tblContactAddress"
The WHERE Statement for your main form would be:
"...WHERE ContactID IN (SELECT ContactID FROM tblContactAddress WHERE City = " & cboCityFilter & ")"
I may not have the syntax 100% because the IN statement needs to be in a csv format, but hopefully it can get you started.
For example, if your main table may be:
tblContacts
ContactID
FirstName
LastName
your linked table may be
tblContactAddress
ContactAddressID
Address
City
StateProvince
ZipPostalCode
ContactID
On the main form, your "City" filter Control Source would be:
"SELECT DISTINCT City FROM tblContactAddress"
The WHERE Statement for your main form would be:
"...WHERE ContactID IN (SELECT ContactID FROM tblContactAddress WHERE City = " & cboCityFilter & ")"
I may not have the syntax 100% because the IN statement needs to be in a csv format, but hopefully it can get you started.
Hmm, I got the error
Here is the code that I'm using.
I also tried with strCriteria = "[GarageID] Like ' * ' AND PTPID IN (SELECT QRY with the same error resulting.
The GarageID factor functions properly. I've removed the plateNbr reference and successfully resolved my form. Any blaringly obvious mistakes in my syntax?
QUOTE
Enter Parameter Value HKH274
and QUOTE
Runtime error 2501
on my cancelling the event.Here is the code that I'm using.
CODE
Private Sub Cbo_SchPlate_GotFocus()
Dim strWhere As String
If IsNull(Cbo_SchFacility) Then
strWhere = "[GarageID] Like '*'"
Else
strWhere = "[GarageID] = " & Cbo_SchFacility & " "
End If
Cbo_SchPlate.RowSource = "SELECT DISTINCT QRY_PTP_Detail.PlateNbr " & _
"FROM QRY_PTP_Detail " & _
"WHERE " & strWhere & " " & _
"ORDER BY QRY_PTP_Detail.PlateNbr"
End Sub
Private Sub Cmd_SchPlate_Click()
Dim strCriteria As String
If IsNull(Cbo_SchFacility) Then
strCriteria = "[GarageID] Like ' * ' AND [PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = " & Cbo_SchPlate & ") "
Else
strCriteria = "[GarageID] = " & Cbo_SchFacility & " AND [PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = " & Cbo_SchPlate & ") "
End If
DoCmd.OpenForm "FRM_Input_PTP", , , strCriteria
End Sub
Dim strWhere As String
If IsNull(Cbo_SchFacility) Then
strWhere = "[GarageID] Like '*'"
Else
strWhere = "[GarageID] = " & Cbo_SchFacility & " "
End If
Cbo_SchPlate.RowSource = "SELECT DISTINCT QRY_PTP_Detail.PlateNbr " & _
"FROM QRY_PTP_Detail " & _
"WHERE " & strWhere & " " & _
"ORDER BY QRY_PTP_Detail.PlateNbr"
End Sub
Private Sub Cmd_SchPlate_Click()
Dim strCriteria As String
If IsNull(Cbo_SchFacility) Then
strCriteria = "[GarageID] Like ' * ' AND [PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = " & Cbo_SchPlate & ") "
Else
strCriteria = "[GarageID] = " & Cbo_SchFacility & " AND [PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = " & Cbo_SchPlate & ") "
End If
DoCmd.OpenForm "FRM_Input_PTP", , , strCriteria
End Sub
I also tried with strCriteria = "[GarageID] Like ' * ' AND PTPID IN (SELECT QRY with the same error resulting.
The GarageID factor functions properly. I've removed the plateNbr reference and successfully resolved my form. Any blaringly obvious mistakes in my syntax?
AH HA
I needed single quotes ... like
[PlateNbr] = '" & Cbo_SchPlate & "')
I needed single quotes ... like
[PlateNbr] = '" & Cbo_SchPlate & "')
Glad it's working for you Scot. 
I also noticed that you are checking for null in the combo and entering a "like '*'", but you could actually remove this from the where string altogether if you want. I'm assuming your number of records is fairly small at this point, but it will still run more efficiently without the Like operator. See below:
I also noticed that you are checking for null in the combo and entering a "like '*'", but you could actually remove this from the where string altogether if you want. I'm assuming your number of records is fairly small at this point, but it will still run more efficiently without the Like operator. See below:
CODE
Private Sub Cbo_SchPlate_GotFocus()
Dim strWhere As String
If IsNull(Cbo_SchFacility) Then
strWhere = ""
Else
strWhere = "WHERE [GarageID] = " & Cbo_SchFacility & " "
End If
Cbo_SchPlate.RowSource = "SELECT DISTINCT QRY_PTP_Detail.PlateNbr " & _
"FROM QRY_PTP_Detail " & _
strWhere & _
"ORDER BY QRY_PTP_Detail.PlateNbr"
End Sub
Private Sub Cmd_SchPlate_Click()
Dim strCriteria As String
If IsNull(Cbo_SchFacility) Then
strCriteria = "[PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = '" & Cbo_SchPlate & "') "
Else
strCriteria = "[GarageID] = " & Cbo_SchFacility & " AND [PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = '" & Cbo_SchPlate & "') "
End If
DoCmd.OpenForm "FRM_Input_PTP", , , strCriteria
End Sub
Dim strWhere As String
If IsNull(Cbo_SchFacility) Then
strWhere = ""
Else
strWhere = "WHERE [GarageID] = " & Cbo_SchFacility & " "
End If
Cbo_SchPlate.RowSource = "SELECT DISTINCT QRY_PTP_Detail.PlateNbr " & _
"FROM QRY_PTP_Detail " & _
strWhere & _
"ORDER BY QRY_PTP_Detail.PlateNbr"
End Sub
Private Sub Cmd_SchPlate_Click()
Dim strCriteria As String
If IsNull(Cbo_SchFacility) Then
strCriteria = "[PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = '" & Cbo_SchPlate & "') "
Else
strCriteria = "[GarageID] = " & Cbo_SchFacility & " AND [PTPID] IN (SELECT QRY_PTP_Detail.PTPID FROM QRY_PTP_Detail WHERE [PlateNbr] = '" & Cbo_SchPlate & "') "
End If
DoCmd.OpenForm "FRM_Input_PTP", , , strCriteria
End Sub
Thanks for the suggestion Darnel
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.