hoochiepoos
New member
- Local time
- Today, 10:27
- Joined
- Dec 12, 2018
- Messages
- 8
Hi,
I am new to access and have been thrown in at the deep end.
I am making a search form using a form with subform. The subform is based on a query.
The form has a number of combo boxes which allow the user to select or type in a value. Stringing these together means that you can refine you search easily.
The following code is working for me but................. for some reason I just cannot work out how to return records that have blank fields. Some of the fields are optional and don't have to be filled. (of course I could force a default value but this seems clumsy)
I would appreciate any pointers and input.
Thanks,
Hooch
I am new to access and have been thrown in at the deep end.
I am making a search form using a form with subform. The subform is based on a query.
The form has a number of combo boxes which allow the user to select or type in a value. Stringing these together means that you can refine you search easily.
The following code is working for me but................. for some reason I just cannot work out how to return records that have blank fields. Some of the fields are optional and don't have to be filled. (of course I could force a default value but this seems clumsy)
Code:
Function SearchCriteria()
Dim EnquiryNo, strSparesStatus, strDescription As String
Dim strCriteria As String
'This field is an integer
If IsNull(Me.cboEnquiryNo) Then
EnquiryNo = "[EnquiryNo] like '*'"
Else
EnquiryNo = "[EnquiryNo] = " & Me.cboEnquiryNo
End If
'The next two fields are strings
If IsNull(Me.cboSparesStatus) Then
strSparesStatus = "[SparesStatus] like '*'"
Else
strSparesStatus = "[SparesStatus] = '" & Me.cboSparesStatus & "'"
End If
If IsNull(Me.cboPartDes) Then
strDescription = "[Description] like '*'"
Else
strDescription = "[Description] = '" & Me.cboPartDes & "'"
End If
'Concatenating the data
strCriteria = EnquiryNo & "And" & strSparesStatus & "And" & trDescription
'Feeding the string to my query
task = "select * from qrySparesDetailSearch where " & strCriteria
'Feeding the task to my subform
Me.frmSubSparesSearch.Form.RecordSource = task
'Updating my subform
Me.frmSubSparesSearch.Form.Requery
End Function
Thanks,
Hooch
Last edited: