Form Filtering Problems Please Help!!

**emilyrogers** · 02-07-2011, 05:41 AM

This is the code I am using to filter records on a form
there are 3 combo boxes, 3 textboxes, two date boxes and one check box.

this code is used to filter and then display the results of the records that match all of the criteria.

however it doesnt work.
what happens is for example I enter a criteria and then it displays "NAME?" in all the form fields and then I close the form and the next time i open it the correct filtered fields are there. but it seems as if the query is retaining the filter information as I than have to go into the query and delete the parameters before the form will display all records again!

Please Please Please help me!! I have been working on this for weeks!

here is my code:

Code:

Option Compare Database
Dim whichCabinet As Integer
Const csfixedSQL = "SELECT tblLog.Name, tblLog.[SAP Number], tblLog.[Job Number], tblLog.Site, " & _
      "tblLog.[# ID], tblLog.Date, tblLog.[Incident reference], tblLog.[Cause of trip], " & _
      "tblLog.[Contact with ABS], tblLog.Comment, tblLog.[Start Time], tblLog.[Finish Time], " & _
      "tblLog.[Additional comment], tblLog.Report FROM tblLog "
Private Sub Chk_log_comment_Click()
   Txt_log_comment.Enabled = Chk_log_comment
End Sub
Private Sub Chk_log_date_Click()
'DT_log_date1.Locked = Not Chk_log_date
'DT_log_date2.Locked = Not Chk_log_date
End Sub
Private Sub Chk_log_id_Click()
   Cmb_TLA.Enabled = Chk_log_id
End Sub
Private Sub Chk_log_incident_Click()
   Txt_log_incident.Enabled = Chk_log_incident
End Sub
Private Sub Chk_log_job_Click()
   Txt_log_job.Enabled = Chk_log_job
End Sub
Private Sub Chk_log_name_Click()
   Cmb_name.Enabled = Chk_log_name
End Sub
Private Sub Chk_log_report_Click()
   Txt_log_report.Enabled = Chk_log_report
End Sub
Private Sub Chk_log_sap_Click()
   Txt_log_sap.Enabled = Chk_log_sap
End Sub
Private Sub Chk_log_cause_Click()
   Txt_log_cause.Enabled = Chk_log_cause
End Sub
Private Sub Cmd_apply_Click()
   Dim strSQL As String
   Dim strWhere As String
   Dim qdf As QueryDef
   strSQL = csfixedSQL
   If ((Chk_log_comment) And Not (IsNull(Txt_log_comment.Value))) Then
      strWhere = " AND tblLog.[Additional comment] Like """ & "*" & Txt_log_comment.Value & "*"""
   End If
   If ((Chk_log_cause) And Not (IsNull(Txt_log_cause.Value))) Then
      strWhere = strWhere & " OR tblLog.[Cause of trip] Like """ & "*" & Txt_log_cause.Value & "*"""
   End If
   If ((Chk_log_name) And Not (IsNull(Cmb_name.Value))) Then
      strWhere = " AND tblLog.Name = """ & Cmb_name.Value & """"
   End If
   If ((Chk_log_id) And Not (IsNull(Cmb_TLA.Value))) Then
      strWhere = strWhere & " AND tblLog.[# ID] = """ & Cmb_TLA.Value & """"
   End If
   If ((Chk_log_sap) And Not (IsNull(Txt_log_sap.Value))) Then
      strWhere = strWhere & " AND tblLog.[SAP Number] Like """ & "*" & Txt_log_sap.Value & "*"""
   End If
   If ((Chk_log_job) And Not (IsNull(Txt_log_job.Value))) Then
      strWhere = strWhere & " AND tblLog.[Job Number] Like """ & "*" & Txt_log_job.Value & "*"""
   End If
   If ((Chk_log_incident) And Not (IsNull(Txt_log_incident.Value))) Then
      strWhere = strWhere & " AND tblLog.[Incident reference] Like """ & "*" & Txt_log_incident.Value & "*"""
   End If
   If ((Chk_log_report) And Not (IsNull(Txt_log_report.Value))) Then
      strWhere = strWhere & " AND tblLog.Report Like """ & "*" & Txt_log_report.Value & "*"""
   End If
   If (strWhere <> "") Then strWhere = "(" & Mid(strWhere, 6) & ")"
   If (Chk_log_date) Then
      If Not (IsNull(DT_log_date1.Value)) Then
         strDate = " AND tblLog.Date >= " & _
                   "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
      End If
      If Not (IsNull(DT_log_date2.Value)) Then
         strDate = strDate & " AND tblLog.Date <= " & _
                   "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
      End If
   End If
   If (strWhere <> "") Then
      strWhere = strWhere & strDate
   Else
      strWhere = Mid(strDate, 6)
   End If
   If (strWhere <> "") Then strSQL = strSQL & "WHERE " & strWhere
   If Frm_sort.Value = 1 Then
      strSQL = strSQL & " ORDER BY tblLog.[# ID] ASC, tblLog.Date DESC"
   Else
      strSQL = strSQL & " ORDER BY tblLog.Date DESC, tblLog.[# ID] ASC"
   End If
   'If (Chk_log_date) Then
   ' If Not (IsNull(DT_log_date1.Value)) Then
   ' strWhere = strWhere & " AND tblLog.Date >= " & _
     ' "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
   ' End If
   ' If Not (IsNull(DT_log_date2.Value)) Then
   ' strWhere = strWhere & " AND tblLog.Date <= " & _
     ' "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
   ' End If
   'End If
   'If (strWhere <> "") Then strSQL = strSQL & "WHERE " & Mid(strWhere, 6)
   Set qdf = CurrentDb.QueryDefs("qryLog")
   qdf.SQL = strSQL
   qdf.Close
   Forms!frmSiteLog!frmLog.Form.RecordSource = ""
   Forms!frmSiteLog!frmLog.Form.RecordSource = "qryLog"
End Sub
Private Sub Cmd_clear_Click()
   Chk_log_name.Value = 0
   Cmb_name.Enabled = False
   Chk_log_id.Value = 0
   Cmb_TLA.Enabled = False
   Chk_log_sap.Value = 0
   Txt_log_sap.Enabled = False
   Chk_log_job.Value = 0
   Txt_log_job.Enabled = False
   Chk_log_date.Value = 0
   'DT_log_date1.Locked = False
   'DT_log_date2.Locked = False
   Chk_log_incident.Value = 0
   Txt_log_incident.Enabled = False
   Chk_log_comment.Value = 0
   Txt_log_comment.Enabled = False
   Chk_log_report.Value = 0
   Txt_log_report.Enabled = False
   Chk_log_cause.Value = 0
   Txt_log_cause.Enabled = False
   Cmd_apply_Click
End Sub
Private Sub Form_Open(Cancel As Integer)
   Cmd_clear_Click
End Sub
Private Sub Cmd_save_Click()
   temp = -2147483643
   Cmd_save.ForeColor = temp
   Cmb_name.BackColor = temp
   With Forms!frmSiteLog!frmLog
      !Cmb_name.BackColor = temp
      !Cmb_ID.BackColor = temp
      !Txt_sap.BackColor = temp
      !Txt_job.BackColor = temp
      !Txt_date.BackColor = temp
      !Txt_start.BackColor = temp
      !Txt_finish.BackColor = temp
      !Txt_incident.BackColor = temp
      !Txt_trip.BackColor = temp
      !Txt_comment.BackColor = temp
      !Txt_report.BackColor = temp
      !Txt_trip.BackColor = temp
   End With
   Opt_change.Value = False
   UpdateLocks
End Sub
Private Sub Form_Load()
   Opt_change.Value = False
   UpdateLocks
   DT_log_date1 = Now
   DT_log_date2 = Now
End Sub
Private Sub UpdateLocks()
   frmLog.Locked = Not Opt_change.Value
End Sub
Private Sub Frm_sort_AfterUpdate()
   Cmd_apply_Click
End Sub
Private Sub Opt_change_Click()
   UpdateLocks
End Sub
Private Sub Cmd_close_Click()
   On Error GoTo Err_Cmd_close_Click

   DoCmd.Close
Exit_Cmd_close_Click:
   Exit Sub
Err_Cmd_close_Click:
   MsgBox Err.Description
   Resume Exit_Cmd_close_Click
End Sub

**jzwp11** · 02-07-2011, 08:00 AM

Set qdf = CurrentDb.QueryDefs("qryLog")
qdf.SQL = strSQL
qdf.Close
Forms!frmSiteLog!frmLog.Form.RecordSource = ""
Forms!frmSiteLog!frmLog.Form.RecordSource = "qryLog"

Since you are storing the query and assigning it as the record source for the form with the above part of your code, then the next time you open the form, you will see the filtered results.

What form setup are you using? Is the main form (with the search controls) unbound while the subform shows the results and is bound to the query qrylog?

**emilyrogers** · 02-07-2011, 08:06 AM

Originally Posted by jzwp11

Since you are storing the query and assigning it as the record source for the form with the above part of your code, then the next time you open the form, you will see the filtered results.

What form setup are you using? Is the main form (with the search controls) unbound while the subform shows the results and is bound to the query qrylog?

Yes- thank you for replying!
The main form and controls are unbound and then the subform is bound to the query qrylog

**jzwp11** · 02-07-2011, 08:58 AM

What I have done in the past is to present the user with an option to view all records or to filter the records (a simple option group would work). I usually keep the search controls and subform hidden when the form opens. If they choose to see all records, just assign the query (less the WHERE clause) to the record source of the subform and then requery (I think you may need to do this and that is why you might be getting the "NAME?" issue) and unhide the subform. If they choose to filter the records, unhide the search controls and then when they click on the search button, assign the filtered query to the record source, requery and then unhide the subform.

I generally don't store the query, but rather just build it dynamically within the code.

The code may look like the following for the after update of the option group assuming that optChoice is my option group and option 1 is to view all records and option 2 is to view filtered records:

Code:

 
Dim qryLog as string
qryLog="SELECT tblLog.Name, tblLog.[SAP Number], tblLog.[Job Number], tblLog.Site, " & _
      "tblLog.[# ID], tblLog.Date, tblLog.[Incident reference], tblLog.[Cause of trip], " & _
      "tblLog.[Contact with ABS], tblLog.Comment, tblLog.[Start Time], tblLog.[Finish Time], " & _
      "tblLog.[Additional comment], tblLog.Report FROM tblLog "

IF optChoice=1 THEN
  Forms!frmSiteLog!frmLog.Form.RecordSource = "qryLog" 
  Forms!frmSiteLog!frmLog.Form.Requery
  Forms!frmSiteLog!frmLog.Form.Visible=true
ELSE
 'unhide your search controls
END IF

You would user your same code as before for your search button, but I would just remove the part where you store the query. You will need to requery and then make the subform visible with these two lines of code following your assignment of qryLog to the recordsource.

Forms!frmSiteLog!frmLog.Form.Requery
Forms!frmSiteLog!frmLog.Form.Visible=true

**emilyrogers** · 02-07-2011, 09:04 AM

Originally Posted by jzwp11

What I have done in the past is to present the user with an option to view all records or to filter the records (a simple option group would work). I usually keep the search controls and subform hidden when the form opens. If they choose to see all records, just assign the query (less the WHERE clause) to the record source of the subform and then requery (I think you may need to do this and that is why you might be getting the "NAME?" issue) and unhide the subform. If they choose to filter the records, unhide the search controls and then when they click on the search button, assign the filtered query to the record source, requery and then unhide the subform.

I generally don't store the query, but rather just build it dynamically within the code.

The code may look like the following for the after update of the option group assuming that optChoice is my option group and option 1 is to view all records and option 2 is to view filtered records:

Code:

 
Dim qryLog as string
qryLog="SELECT tblLog.Name, tblLog.[SAP Number], tblLog.[Job Number], tblLog.Site, " & _
      "tblLog.[# ID], tblLog.Date, tblLog.[Incident reference], tblLog.[Cause of trip], " & _
      "tblLog.[Contact with ABS], tblLog.Comment, tblLog.[Start Time], tblLog.[Finish Time], " & _
      "tblLog.[Additional comment], tblLog.Report FROM tblLog "

IF optChoice=1 THEN
  Forms!frmSiteLog!frmLog.Form.RecordSource = "qryLog" 
  Forms!frmSiteLog!frmLog.Form.Requery
  Forms!frmSiteLog!frmLog.Form.Visible=true
ELSE
 'unhide your search controls
END IF

You would user your same code as before for your search button, but I would just remove the part where you store the query. You will need to requery and then make the subform visible with these two lines of code following your assignment of qryLog to the recordsource.

Forms!frmSiteLog!frmLog.Form.Requery
Forms!frmSiteLog!frmLog.Form.Visible=true

OK Thank You- I must admit I am very new to Access and havent spent much time in it, so some of what you said doesn't make sense to me. But I will try and work through it.

EDIT- I have replaced
Set qdf = CurrentDb.QueryDefs("qryLog")
qdf.SQL = strSQL
qdf.Close
Forms!frmSiteLog!frmLog.Form.RecordSource = ""
Forms!frmSiteLog!frmLog.Form.RecordSource = "qryLog"

With

Forms!frmSiteLog!frmLog.Form.Requery

Forms!frmSiteLog!frmLog.Form.Visible=true

BUt as far as the option groups-I have created them but in teh properties
there is no "On Click" section so I am unsure where to put this code

Dim qryLog as string
qryLog="SELECT tblLog.Name, tblLog.[SAP Number], tblLog.[Job Number], tblLog.Site, " & _
"tblLog.[# ID], tblLog.Date, tblLog.[Incident reference], tblLog.[Cause of trip], " & _
"tblLog.[Contact with ABS], tblLog.Comment, tblLog.[Start Time], tblLog.[Finish Time], " & _
"tblLog.[Additional comment], tblLog.Report FROM tblLog "

IF optChoice=1 THEN
Forms!frmSiteLog!frmLog.Form.RecordSource = "qryLog"
Forms!frmSiteLog!frmLog.Form.Requery
Forms!frmSiteLog!frmLog.Form.Visible=true
ELSE
'unhide your search controls

**jzwp11** · 02-07-2011, 09:15 AM

Since you say that you are new to Access, did you inherit this database (and the code you presented earlier) from someone else?

**emilyrogers** · 02-07-2011, 09:25 AM

Originally Posted by jzwp11

Since you say that you are new to Access, did you inherit this database (and the code you presented earlier) from someone else?

Yes I have created this database from scratch but I am trying to re-use this code from a database created by someone else who has now left the office (otherwise i would ask them!). Do you think it will work for what I need?

I have looked everywhere and can't find any suggestions for a filter which combines the criteria from multiple controls of different types.

If you have any sugestions they would be much appreciated!

Thank You!!!!!

**jzwp11** · 02-07-2011, 09:55 AM

Yes I have created this database from scratch but I am trying to re-use this code from a database created by someone else who has now left the office (otherwise i would ask them!). Do you think it will work for what I need?

You will probably need to modify it somewhat to suit your needs which means you will have to understand what it is doing. I would probably simplify it somewhat. For example, I'm not sure what the purpose of this section of the code is used for:

Code:

Private Sub Chk_log_comment_Click()
   Txt_log_comment.Enabled = Chk_log_comment
End Sub
Private Sub Chk_log_date_Click()
'DT_log_date1.Locked = Not Chk_log_date
'DT_log_date2.Locked = Not Chk_log_date
End Sub
Private Sub Chk_log_id_Click()
   Cmb_TLA.Enabled = Chk_log_id
End Sub
Private Sub Chk_log_incident_Click()
   Txt_log_incident.Enabled = Chk_log_incident
End Sub
Private Sub Chk_log_job_Click()
   Txt_log_job.Enabled = Chk_log_job
End Sub
Private Sub Chk_log_name_Click()
   Cmb_name.Enabled = Chk_log_name
End Sub
Private Sub Chk_log_report_Click()
   Txt_log_report.Enabled = Chk_log_report
End Sub
Private Sub Chk_log_sap_Click()
   Txt_log_sap.Enabled = Chk_log_sap
End Sub
Private Sub Chk_log_cause_Click()
   Txt_log_cause.Enabled = Chk_log_cause
End Sub

Are there some sort of check boxes next to each control that is used for search purposes?

I have looked everywhere and can't find any suggestions for a filter which combines the criteria from multiple controls of different types.

I have actually done some exactly as you have described above. I would check each control and if it has a value/not null then add it to the WHERE clause. This part of your code does just that (but has the additional functions of which purpose I don't completely understand)

Code:

   
 
If ((Chk_log_comment) And Not (IsNull(Txt_log_comment.Value))) Then
      strWhere = " AND tblLog.[Additional comment] Like """ & "*" & Txt_log_comment.Value & "*"""
   End If
   If ((Chk_log_cause) And Not (IsNull(Txt_log_cause.Value))) Then
      strWhere = strWhere & " OR tblLog.[Cause of trip] Like """ & "*" & Txt_log_cause.Value & "*"""
   End If
   If ((Chk_log_name) And Not (IsNull(Cmb_name.Value))) Then
      strWhere = " AND tblLog.Name = """ & Cmb_name.Value & """"
   End If
   If ((Chk_log_id) And Not (IsNull(Cmb_TLA.Value))) Then
      strWhere = strWhere & " AND tblLog.[# ID] = """ & Cmb_TLA.Value & """"
   End If
   If ((Chk_log_sap) And Not (IsNull(Txt_log_sap.Value))) Then
      strWhere = strWhere & " AND tblLog.[SAP Number] Like """ & "*" & Txt_log_sap.Value & "*"""
   End If
   If ((Chk_log_job) And Not (IsNull(Txt_log_job.Value))) Then
      strWhere = strWhere & " AND tblLog.[Job Number] Like """ & "*" & Txt_log_job.Value & "*"""
   End If
   If ((Chk_log_incident) And Not (IsNull(Txt_log_incident.Value))) Then
      strWhere = strWhere & " AND tblLog.[Incident reference] Like """ & "*" & Txt_log_incident.Value & "*"""
   End If
   If ((Chk_log_report) And Not (IsNull(Txt_log_report.Value))) Then
      strWhere = strWhere & " AND tblLog.Report Like """ & "*" & Txt_log_report.Value & "*"""
   End If
   If (strWhere <> "") Then strWhere = "(" & Mid(strWhere, 6) & ")"
   If (Chk_log_date) Then
      If Not (IsNull(DT_log_date1.Value)) Then
         strDate = " AND tblLog.Date >= " & _
                   "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
      End If
      If Not (IsNull(DT_log_date2.Value)) Then
         strDate = strDate & " AND tblLog.Date <= " & _
                   "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
      End If
   End If
   If (strWhere <> "") Then
      strWhere = strWhere & strDate
   Else
      strWhere = Mid(strDate, 6)
   End If
   If (strWhere <> "") Then strSQL = strSQL & "WHERE " & strWhere
   If Frm_sort.Value = 1 Then
      strSQL = strSQL & " ORDER BY tblLog.[# ID] ASC, tblLog.Date DESC"
   Else
      strSQL = strSQL & " ORDER BY tblLog.Date DESC, tblLog.[# ID] ASC"
   End If
   'If (Chk_log_date) Then
   ' If Not (IsNull(DT_log_date1.Value)) Then
   ' strWhere = strWhere & " AND tblLog.Date >= " & _
     ' "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
   ' End If
   ' If Not (IsNull(DT_log_date2.Value)) Then
   ' strWhere = strWhere & " AND tblLog.Date <= " & _
     ' "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
   ' End If
   'End If

Going back to you edit of your previous post:

BUt as far as the option groups-I have created them but in teh properties
there is no "On Click" section so I am unsure where to put this code

When you set up an option group, Access usually calls it a frame, it is the frame that has certain events including the After Update event. So after someone selects an option, the After Update event will fire. That is where you want the code.

To help further I will need to know the query you are working with as well as the data types of the field names for which there are corresponding controls on the unbound form.

**emilyrogers** · 02-07-2011, 10:14 AM

Thank You So Much!
You have been soo helpful! I have been trying to modify it.
I have a main form called frmSiteLog which has the unbound controls on it
- the controls are
- Customer - Combobox
-Location Country- COmbobox
-Location City- Textbox
-Customer Enquiry Reference- Textbox
-Enquiry Date- which is 2 date boxes to search for a record within a set range of dates.
-Bid Required By- same as enquiry date
- WMB Enquiry ID- textbox
- Sales Responsibility- combobox
-Customer has order to place- check box.

Then there is a subform called "New Enquiry" which has all of the above fields plus a few more. the record source query for the sub form is "Opportunities Extended"
SELECT Opportunities.*
FROM Opportunities;

The aim of the filter section in the main form is that there are 9 fields and when each check box is clicked that field becomes enabled and then the user will enter a value. they can do this for as many or as few fields as they like and then they click apply which applies those criteria to the records stored in the table " Opportunities"

Thank You for explaining option groups!
For some reason I have implemented the below code and when I click apply nothing happens.

HTML Code:

Option Compare Database
Dim whichCabinet As Integer
Const csfixedSQL = "SELECT Opportunities.*" & _
"FROM Opportunities "


Private Sub Check201_Click()
   Check205.Enabled = Check201
End Sub
Private Sub Chk_log_date_Click()
'DT_log_date1.Locked = Not Chk_log_date
'DT_log_date2.Locked = Not Chk_log_date
End Sub
Private Sub Check191_Click()
'ActiveXCtl194.Locked = Not Check191
'ActiveXCtl195.Locked = Not Check191
End Sub
Private Sub Chk_log_id_Click()
   Cmb_TLA.Enabled = Chk_log_id
End Sub
Private Sub Chk_log_incident_Click()
   Txt_log_incident.Enabled = Chk_log_incident
End Sub
Private Sub Chk_log_job_Click()
   Txt_log_job.Enabled = Chk_log_job
End Sub
Private Sub Chk_log_name_Click()
   Cmb_name.Enabled = Chk_log_name
End Sub
Private Sub Chk_log_report_Click()
   Combo198.Enabled = Chk_log_report
End Sub
Private Sub Chk_log_sap_Click()
   Txt_log_sap.Enabled = Chk_log_sap
End Sub


Private Sub Cmd_apply_Click()
Dim strSQL As String
    Dim strWhere As String
    Dim qdf As QueryDef
    strSQL = csfixedSQL
    

   
    If ((Chk_log_name) And Not (IsNull(Cmb_name.Value))) Then
        strWhere = " AND [Opportunities].[Location Country] = """ & Cmb_name.Value & """"
    End If
       If ((Chk_log_report) And Not (IsNull(Combo198.Value))) Then
        strWhere = " AND [Opportunities].[Sales Responsibility] = """ & Combo198.Value & """"
   End If
    
       If ((Check201) And Not (IsNull(Check205.Value))) Then
        strWhere = " AND [Opportunities].[Customer has order to place] = """ & Check205.Value & """"
    End If
    
    If ((Chk_log_id) And Not (IsNull(Cmb_TLA.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Customer] = """ & Cmb_TLA.Value & """"
    End If
    If ((Chk_log_sap) And Not (IsNull(Txt_log_sap.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Location City] Like """ & "*" & Txt_log_sap.Value & "*"""
    End If
    If ((Chk_log_job) And Not (IsNull(Txt_log_job.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Opportunities.ID] Like """ & "*" & Txt_log_job.Value & "*"""
    End If
    If ((Chk_log_incident) And Not (IsNull(Txt_log_incident.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Customer Enquiry Reference] Like """ & "*" & Txt_log_incident.Value & "*"""
    End If
   
    If (strWhere <> "") Then strWhere = "(" & Mid(strWhere, 6) & ")"
        
    If (Chk_log_date) Then
        If Not (IsNull(DT_log_date1.Value)) Then
            strDate = " AND [Opportunities].[Bid Required By] >= " & _
            "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
        End If
        If Not (IsNull(DT_log_date2.Value)) Then
            strDate = strDate & " AND [Opportunities].[Bid Required By] <= " & _
            "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
        End If
        
        
         If (Check191) Then
        If Not (IsNull(ActiveXCtl194.Value)) Then
            strDate = " AND [Opportunities].[Enquiry Date] >= " & _
            "#" & Format$(ActiveXCtl194.Value, "mm/dd/yyyy") & "#"
        End If
        If Not (IsNull(ActiveXCtl195.Value)) Then
            strDate = strDate & " AND [Opportunities].[Enquiry Date] <= " & _
            "#" & Format$(ActiveXCtl195.Value, "mm/dd/yyyy") & "#"
        End If
    End If
    
    If (strWhere <> "") Then
      strWhere = strWhere & strDate
    Else
      strWhere = Mid(strDate, 6)
    End If
    If (strWhere <> "") Then strSQL = strSQL & "WHERE " & strWhere
    
    
    
Forms!frmSiteLog![New Enquiry].Form.Requery
Forms!frmSiteLog![New Enquiry].Form.Visible = True
End If
End Sub
Private Sub Cmd_clear_Click()

   Chk_log_name.Value = 0
   Cmb_name.Enabled = False
   
   Chk_log_id.Value = 0
   Cmb_TLA.Enabled = False
   
   Chk_log_sap.Value = 0
   Txt_log_sap.Enabled = False
   
   Chk_log_job.Value = 0
   Txt_log_job.Enabled = False
   
   Chk_log_date.Value = 0
   DT_log_date1.Locked = False
   DT_log_date2.Locked = False
   
   Check191.Value = 0
   ActiveXCtl194.Locked = False
   ActiveXCtl195.Locked = False
   
   Chk_log_incident.Value = 0
   Txt_log_incident.Enabled = False
   
   Check201.Value = 0
   Check205.Enabled = False
   
   Chk_log_report.Value = 0
   Combo198.Enabled = False

   Cmd_apply_Click
End Sub
Private Sub Form_Open(Cancel As Integer)
   Cmd_clear_Click
End Sub
Private Sub Cmd_save_Click()
 
End Sub
Private Sub Form_Load()
   Opt_change.Value = False
   UpdateLocks
   ActiveXCtl194 = Now
   ActiveXCtl195 = Now
End Sub
Private Sub UpdateLocks()
   [New Enquiry].Locked = Not Opt_change.Value
End Sub
Private Sub Frm_sort_AfterUpdate()
   Cmd_apply_Click
End Sub
Private Sub Opt_change_Click()
   UpdateLocks
End Sub
Private Sub Cmd_close_Click()
   On Error GoTo Err_Cmd_close_Click

   DoCmd.Close
Exit_Cmd_close_Click:
   Exit Sub
Err_Cmd_close_Click:
   MsgBox Err.Description
   Resume Exit_Cmd_close_Click
End Sub

**jzwp11** · 02-07-2011, 10:43 AM

You still need to assign the strSQL to the record source of the form; I've shown it in red in your code below. I also found it helpful for troubleshooting purposes to include debug.print statement so that the strSQL text is shown in the immediate window. You can then make sure all of the syntax is correct by copying and pasting the actual strSQL to a new query & test it. You would put this statement after the WHERE clause is added (I've shown the placement in green within the code as well).

debug.print strSQL

I also noticed that you had and END IF that might have been in the wrong place. I show what I think is the correct place in purple.

Code:

Option Compare Database
Dim whichCabinet As Integer
Const csfixedSQL = "SELECT Opportunities.*" & _
"FROM Opportunities "


Private Sub Check201_Click()
   Check205.Enabled = Check201
End Sub
Private Sub Chk_log_date_Click()
'DT_log_date1.Locked = Not Chk_log_date
'DT_log_date2.Locked = Not Chk_log_date
End Sub
Private Sub Check191_Click()
'ActiveXCtl194.Locked = Not Check191
'ActiveXCtl195.Locked = Not Check191
End Sub
Private Sub Chk_log_id_Click()
   Cmb_TLA.Enabled = Chk_log_id
End Sub
Private Sub Chk_log_incident_Click()
   Txt_log_incident.Enabled = Chk_log_incident
End Sub
Private Sub Chk_log_job_Click()
   Txt_log_job.Enabled = Chk_log_job
End Sub
Private Sub Chk_log_name_Click()
   Cmb_name.Enabled = Chk_log_name
End Sub
Private Sub Chk_log_report_Click()
   Combo198.Enabled = Chk_log_report
End Sub
Private Sub Chk_log_sap_Click()
   Txt_log_sap.Enabled = Chk_log_sap
End Sub


Private Sub Cmd_apply_Click()
Dim strSQL As String
    Dim strWhere As String
    Dim qdf As QueryDef
    strSQL = csfixedSQL
    

   
    If ((Chk_log_name) And Not (IsNull(Cmb_name.Value))) Then
        strWhere = " AND [Opportunities].[Location Country] = """ & Cmb_name.Value & """"
    End If
       If ((Chk_log_report) And Not (IsNull(Combo198.Value))) Then
        strWhere = " AND [Opportunities].[Sales Responsibility] = """ & Combo198.Value & """"
   End If
    
       If ((Check201) And Not (IsNull(Check205.Value))) Then
        strWhere = " AND [Opportunities].[Customer has order to place] = """ & Check205.Value & """"
    End If
    
    If ((Chk_log_id) And Not (IsNull(Cmb_TLA.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Customer] = """ & Cmb_TLA.Value & """"
    End If
    If ((Chk_log_sap) And Not (IsNull(Txt_log_sap.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Location City] Like """ & "*" & Txt_log_sap.Value & "*"""
    End If
    If ((Chk_log_job) And Not (IsNull(Txt_log_job.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Opportunities.ID] Like """ & "*" & Txt_log_job.Value & "*"""
    End If
    If ((Chk_log_incident) And Not (IsNull(Txt_log_incident.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Customer Enquiry Reference] Like """ & "*" & Txt_log_incident.Value & "*"""
    End If
   
    If (strWhere <> "") Then strWhere = "(" & Mid(strWhere, 6) & ")"
        
    If (Chk_log_date) Then
        If Not (IsNull(DT_log_date1.Value)) Then
            strDate = " AND [Opportunities].[Bid Required By] >= " & _
            "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
        End If
        If Not (IsNull(DT_log_date2.Value)) Then
            strDate = strDate & " AND [Opportunities].[Bid Required By] <= " & _
            "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
        End If
        
        
         If (Check191) Then
        If Not (IsNull(ActiveXCtl194.Value)) Then
            strDate = " AND [Opportunities].[Enquiry Date] >= " & _
            "#" & Format$(ActiveXCtl194.Value, "mm/dd/yyyy") & "#"
        End If
        If Not (IsNull(ActiveXCtl195.Value)) Then
            strDate = strDate & " AND [Opportunities].[Enquiry Date] <= " & _
            "#" & Format$(ActiveXCtl195.Value, "mm/dd/yyyy") & "#"
        End If
    End If
    
    If (strWhere <> "") Then
      strWhere = strWhere & strDate
    Else
      strWhere = Mid(strDate, 6)
    End If

    If (strWhere <> "") Then 
       strSQL = strSQL & "WHERE " & strWhere
    End IF
 
 
debug.print strSQL
    
Forms!frmSiteLog![New Enquiry].Form.Recordsource=strSQL    
Forms!frmSiteLog![New Enquiry].Form.Requery
Forms!frmSiteLog![New Enquiry].Form.Visible = True

End Sub
 

Private Sub Cmd_clear_Click()

   Chk_log_name.Value = 0
   Cmb_name.Enabled = False
   
   Chk_log_id.Value = 0
   Cmb_TLA.Enabled = False
   
   Chk_log_sap.Value = 0
   Txt_log_sap.Enabled = False
   
   Chk_log_job.Value = 0
   Txt_log_job.Enabled = False
   
   Chk_log_date.Value = 0
   DT_log_date1.Locked = False
   DT_log_date2.Locked = False
   
   Check191.Value = 0
   ActiveXCtl194.Locked = False
   ActiveXCtl195.Locked = False
   
   Chk_log_incident.Value = 0
   Txt_log_incident.Enabled = False
   
   Check201.Value = 0
   Check205.Enabled = False
   
   Chk_log_report.Value = 0
   Combo198.Enabled = False

   Cmd_apply_Click
End Sub
Private Sub Form_Open(Cancel As Integer)
   Cmd_clear_Click
End Sub
Private Sub Cmd_save_Click()
 
End Sub
Private Sub Form_Load()
   Opt_change.Value = False
   UpdateLocks
   ActiveXCtl194 = Now
   ActiveXCtl195 = Now
End Sub
Private Sub UpdateLocks()
   [New Enquiry].Locked = Not Opt_change.Value
End Sub
Private Sub Frm_sort_AfterUpdate()
   Cmd_apply_Click
End Sub
Private Sub Opt_change_Click()
   UpdateLocks
End Sub
Private Sub Cmd_close_Click()
   On Error GoTo Err_Cmd_close_Click

   DoCmd.Close
Exit_Cmd_close_Click:
   Exit Sub
Err_Cmd_close_Click:
   MsgBox Err.Description
   Resume Exit_Cmd_close_Click
End Sub

The aim of the filter section in the main form is that there are 9 fields and when each check box is clicked that field becomes enabled and then the user will enter a value.

Why do you have this? It is an extra step for your users.

Also, for your combo boxes, is there only 1 field in the row source i.e. the actual name of the item? Do these combo boxes refer to tables?

**emilyrogers** · 02-07-2011, 11:09 AM

Thank You!
The reason I am using the check boxes is simply because they were being used in the previous version and I am not good enough in Access to be able to filter out the irrelevant code.
You were right with the misspalced End IF and thankyou for the code corrections! I am still having no luck.
I have attached a copy of the database that maybe you could have a look at??
Thank You!

**jzwp11** · 02-07-2011, 11:47 AM

Out of curiosity, how far along are you with your re-creation of this database? I ask because I see some things in your current table structure that do not follow normalization rules and best practices.

**emilyrogers** · 02-07-2011, 12:41 PM

well i had to strip the database to the bare minimum to get it to fit in the attachemnt file.
i am quite far along this was the last part of my project but if you have a better idea then i would be quite willling to go backa few stages!

**jzwp11** · 02-07-2011, 02:00 PM

I would recommend that you totally restructure your tables which would make any forms/reports/queries that you have thus far created worthless. So basically, you would have to restart. If you are willing to do that, I would be willing to help.

**emilyrogers** · 02-08-2011, 02:14 AM

If you would be willing to help- I m willing to re-start!

thank you

Form Filtering Problems Please Help!!

Thread Tools

Form Filtering Problems Please Help!!

Similar Threads

Filtering a continuous form

Query (Filtering?) in a Form

Filtering a Continuous Form

remove filtering of a form

Question on filtering a form

Tags for this Thread

Posting Permissions