Find Records by selecting 3 fields in combo box

[Vanita]

Hi,
How to find records by selecting 3 fields in combo box, such that we can edit the data in table.
We are attaching Form Design and Query Picture .Please help me out.

[June7]

I have never used ApplyFilter. I set form Filter and FilterOn properties. Review: http://allenbrowne.com/ser-62.html

I don't think you have correct syntax for ApplyFilter. I cannot find an example using FilterName argument but I don't think it should be an SQL statement.

[Gicu]

Replace the Docmd.ApplyFilter task line with Me.recordsource=task (and add Me.recordset-"Select * FROM Production_Monitoring_Table" in the first part of the if statement if the fields are empty).

Cheers,
Vlad

[Vanita]

Hi,
Please check attachment and can u tell is this correct?

[June7]

Should copy/paste code in post between CODE tags, not attach screenshot.

Pick one control to SetFocus. As it is, cboShift will get focus.

Remove the Me.Recordset line. As is, that should give you a debug error and certainly won't execute.

[Vanita]

Hi,
As you mentioned i should copy/paste code in post CODE.
Code:

Private Sub BtnSearch_Click()
Call SearchBtn
End Sub
Sub SearchBtn()
Dim strCriteria As String
Dim task As String
'Me.Refresh


If IsNull(Me.cboOperatorName) Or IsNull(Me.cboProductionDate) Or IsNull(Me.cboShift) Then
MsgBox "Please Enter the Required Details", vbInformation, "Date Required"
Me.cboOperatorName.SetFocus
'Me.cboProductionDate.SetFocus
'Me.cboShift.SetFocus


Else
Me.Recordset - "Select * FROM Production_Monitoring_Table"
strCriteria = "(([Operator_Name])='" & Me.cboOperatorName & "' And ([Production_Date])='" & Me.cboProductionDate & "' And ([Shift])='" & Me.cboShift & "')"
task = "Select * from Production_Monitoring_Table where (" & strCriteria & ") OrderBy [Operator_Name]"
'Me.RecordSource = task


End If
End Sub


still its not working..

[Gicu]

try this:

Private Sub BtnSearch_Click()
Call SearchBtn
End Sub
Sub SearchBtn()
Dim strCriteria As String
Dim task As String
'Me.Refresh


If IsNull(Me.cboOperatorName) Or IsNull(Me.cboProductionDate) Or IsNull(Me.cboShift) Then
MsgBox "Please Enter the Required Details", vbInformation, "Date Required"
Me.cboOperatorName.SetFocus
'Me.cboProductionDate.SetFocus
'Me.cboShift.SetFocus


Else

strCriteria = "(([Operator_Name])='" & Me.cboOperatorName & "' And ([Production_Date])='" & Me.cboProductionDate & "' And ([Shift])='" & Me.cboShift & "')"
task = "Select * from Production_Monitoring_Table where (" & strCriteria & ") OrderBy [Operator_Name]"
'Me.RecordSource = task


End If
End Sub

[Gicu]

Try this:

Private Sub BtnSearch_Click()
Call SearchBtn
End Sub
Sub SearchBtn()
Dim strCriteria As String
Dim task As String
'Me.Refresh


If IsNull(Me.cboOperatorName) Or IsNull(Me.cboProductionDate) Or IsNull(Me.cboShift) Then
      Me.Recordsource = "Select * FROM Production_Monitoring_Table OrderBy [Operator_Name];"
      MsgBox "Please Enter the Required Details", vbInformation, "Date Required"
      Me.cboOperatorName.SetFocus
     'Me.cboProductionDate.SetFocus
     'Me.cboShift.SetFocus
Else
     strCriteria = "(([Operator_Name])='" & Me.cboOperatorName & "' And ([Production_Date])='" & Me.cboProductionDate & "' And ([Shift])='" & Me.cboShift & "')"
     task = "Select * from Production_Monitoring_Table where (" & strCriteria & ") OrderBy [Operator_Name]"
     Me.RecordSource = task

End If
End Sub

Cheers,
Vlad

[Vanita]

Error Message

[June7]

Text field parameters use apostrophe delimiter.
Date/time field parameters use # delimiter.
Number field parameters do not need delimiter.

Names are poor unique identifiers. Really should use OperatorID for search/filter.