Query not combining and filtering values based on a form

Okay, so then the two Combo Boxes you posted are the only ones you have correct?
 
Okay, give me a second, I have to type it up and then I will post it...
 
Is this Madeup code the name of the feild in the query in the Record Source of femSupplierDescriptionCodeqry?
 
"Is this Madeup code the name of the feild in the query in the Record Source of femSupplierDescriptionCodeqry? "

Madeup Code is the name of a field in Test code Query, but the Record Source of the form is empty. And the Madeup Code is just that it only exists in the query, it's not an actual field in any table.
 
Wait, this Search will not work without a Form Record Source because that what the code looks to in order to Filter.
 
I looked on the form you created (frmMultiSelectqry) and the form Record Source is set to qryMultiSelect. So does that mean I should set the Record Source for the new form (frmSupplierDescriptionCodeqry) to point to the new query I created (qrySupplierDescriptionCode)?
 
Yes, it does. And added it as the Record Source to the form property.
 
Add the below to the [Event Procedure] of the Command Button that says Set Filter.

Code: 
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboEquipment) Then
        strWhere = strWhere & "([Madeup Code] = """ & Me.cboEquipment & """) AND "
    End If
 
    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboSupplierID) Then
        strWhere = strWhere & "([Supplier_Name] = " & Me.cboSupplierID & ") AND "
    End If
 
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
 
Wait, I don’t have any command buttons on the form yet. Should I copy and paste the three that you have on frmMultiSelectqry onto the new form?
And add the new code to the event procedure of Set Filter command button?
 
Yes, you should, going to need that button and the others. You can cipy the other code from the other Command Buttons as well. That code should be fine as is.
 
Just to make sure I'm doing the right thing:
I copy the three command buttons you have on the frmMultiSelectqry and paste them on to the frmSupplierDescriptionCodeqry. Then I copy the code you just posted into the Event Procedure of the Set Filter command button in frmSupplierDescriptionCodeqry. Am I correct so far? I'm a little confused after that. The Clear and Close Form command buttons have Event procedures that I need to copy over as well?
 
Yes, you are correct so far. The Close button has nothing to copy so no worries there. You will only need to copy the code from the Clear buttons Event Procedure section.
 
Copied over the Clear button command. But I'm not sure I have the code correct for the Set Filter button. I tried running the query and I'm getting a "Run-time error '3464': Data type mismatch in criteria expression".
I click on the debug button and it takes me to the code and highlights "Me.Filter = strWhere"

This is what the all the code looks like under the Set Filter command button Event Procedure:

'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
'Note: Only records matching ALL of the criteria are returned.
'Author: Allen Browne (allen@allenbrowne.com), June 2006.
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboEquipment) Then
strWhere = strWhere & "([Madeup Code] = """ & Me.cboEquipment & """) AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboSupplier) Then
strWhere = strWhere & "([Supplier_Name] = " & Me.cboSupplier & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Close()
Me.Filter = ""
Me.FilterOn = False
End Sub
Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub
Click to expand...
 
Sorry I selected the wrong button to insert code :o Hope this is easier to read.

Code: 
'Purpose:   This module illustrates how to create a search form, _
            where the user can enter as many or few criteria as they wish, _
            and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne ([EMAIL="allen@allenbrowne.com"]allen@allenbrowne.com[/EMAIL]), June 2006.
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboEquipment) Then
        strWhere = strWhere & "([Madeup Code] = """ & Me.cboEquipment & """) AND "
    End If
 
    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboSupplier) Then
        strWhere = strWhere & "([Supplier_Name] = " & Me.cboSupplier & ") AND "
    End If
 
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at [URL]http://allenbrowne.com/bug-06.html[/URL]
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Close()
    Me.Filter = ""
    Me.FilterOn = False
End Sub
Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    'Me.Filter = "(False)"
    'Me.FilterOn = True
End Sub
 
Can you please post the SQL of the query that is the Record Source of the Form...
 
This is the SQL of the query in the Record Source of the form.

Code: 
SELECT Equipment.Project_Name, Project.Material_Month, Project.Material_Year, Equipment.Unit_Cost, Equipment.Quantity_Purchase, [unit_cost]*[quantity_purchase] AS Total, Equipment.[Location/Affiliate_Name], Equipment_Spec.Description, [Test code Query].[Madeup code], Supplier.Supplier_Name
FROM Supplier INNER JOIN (Project INNER JOIN ((Equipment_Spec INNER JOIN [Test code Query] ON Equipment_Spec.ID = [Test code Query].ID) INNER JOIN Equipment ON Equipment_Spec.ID = Equipment.Equipment_ID) ON Project.Project_Name = Equipment.Project_Name) ON Supplier.Supplier_ID = Equipment.Supplier_Name;
 
Hmm, my mistake :( just replace the cboSupplier section with this...

Code: 
If Not IsNull(Me.cboSupplier) Then
     strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplier & """) AND "
End If
 
Okay changed that. Now when I select a Supplier and push Set Filter nothing shows. Even if I pick the Supplier that I know is in the table.

But when I select an Equipment the query works.
 
You must log in or register to reply here.

Similar threads

F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
439
Faoineag
F
M
Open ComboBox Drop-down at current value - possible?
Replies
16
Views
619
MajP
MajP
H
Pull a data from Customer Table based on a combo box
Replies
13
Views
494
hrdpgajjar
H
M
Please I need Urgent Assistant
Replies
5
Views
557
Pat Hartman
P
AOB
Solved Parameterised query requires me to re-cast parameter in order to run (?)
Replies
6
Views
392
AOB
AOB

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom