Hi there,
I am very new to MS Access and to VBA. I am developing a database to keep track of adverse incidents in the workplace. I have a form that adds them to the database that works just great, but now I want to build a form that lets users look-up and update (for follow-up purposes) existing records. I found an example that seems to apply on:
http://allenbrowne.com/ser-62.html
I would like to modify this a little bit. I have pulled a query including all fields into the form, and added controls to enter "Incident number, Type of incident, a range of dates, and the name of the people that it affected". Correct me if I am wrong, but I think this constitutes a subform. Problem is that with the code I have now, no filtering seems to take place, and my command button just returns ALL records. H
Here is the code:
I am not sure where the problem might lay, and I would love some expert help.
-Thanks very much, Z
I am very new to MS Access and to VBA. I am developing a database to keep track of adverse incidents in the workplace. I have a form that adds them to the database that works just great, but now I want to build a form that lets users look-up and update (for follow-up purposes) existing records. I found an example that seems to apply on:
http://allenbrowne.com/ser-62.html
I would like to modify this a little bit. I have pulled a query including all fields into the form, and added controls to enter "Incident number, Type of incident, a range of dates, and the name of the people that it affected". Correct me if I am wrong, but I think this constitutes a subform. Problem is that with the code I have now, no filtering seems to take place, and my command button just returns ALL records. H
Here is the code:
Code:
Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\mm\/dd\/yyyy\#"
'******************************************************************************************
'look at each search box and build up the criteria string from all non-blank search boxes.
'******************************************************************************************
'ENCON number search
If Not IsNull(Me.cboENCON) Then
strWhere = strWhere & "([ENCON] = """ & Me.cboENCON & ") AND "
End If
'Search by name
If Not IsNull(Me.txtName) Then
strWhere = strWhere & "([Name] = Like ""*" & Me.txtName & "*"") AND "
End If
'Search by type of incident
If Not IsNull(Me.cboType) Then
strWhere = strWhere & "([Type] = """ & Me.cboType & ") AND "
End If
'Search by Resolved or Pending incidents
If Me.cboResolved = 1 Then
strWhere = strWhere & "([Resolved or Pending] = True) AND "
ElseIf Me.cboResolved = 2 Then
strWhere = strWhere & "([Resolved or Pending] = False) AND "
End If
'Search within dates
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If
If Not IsNull(Me.txtEndDate) Then
stWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
'**********************************************************************************************
'Chop off the trailing AND and use the resulting string as the filter for the form
'**********************************************************************************************
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Was nothing in the string.
MsgBox "Please select at least one criterion.", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
Risk_Data_Query_subform.Visible = True
End If
End SubI am not sure where the problem might lay, and I would love some expert help.
-Thanks very much, Z
