AyeSee
08-19-2010, 11:36 AM
I built a database in 2003 and I have to make a version in 97. Everything seems to be working fine except one bit of code. I created a function that filters a sub form based on four combo boxes that I have set up: cboSupplier, cboConsortium, cboFirstName, cboLastName. After the conversion, I got an error that says "Argument not optional". Why does the 97 version have this error? Thanks for any help in advance. Here is the code:
Private Function StockSearch()
On Error GoTo Error_StockSearch
Dim FilterClause As String, D As Long
'Hold whether we Use AND or OR in our Filter Criteria
D = Me.DirectionGrp.Value
'1st Combo - Supplier - Assuming the Table Field 'tblVendor.txtVendorName' is of a Text DataType
If Nz(Me.cboSupplier.Column, 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[tblVendor.txtVendorName]='" & Me.cboSupplier.Value & "'"
End If
'2nd Combo - Wood Treatment - Assuming the Table Field 'tblVendor_1.txtVendorName' is of a Text DataType
If Nz(Me.cboConsortium.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[tblVendor_1.txtVendorName]='" & Me.cboConsortium.Value & "'"
End If
'3rd Combo - Stock Location - Assuming the Table Field 'txtFirstName' is of a Text DataType
If Nz(Me.cboContactName.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[txtFirstName]='" & Me.cboContactName.Value & "'"
End If
'4th Combo - Wood Drying (kilned) - Assuming the Table Field 'txtLastName' is of a Text DataType
If Len(Me.cboLastName.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[txtLastName]='" & Me.cboLastName.Value & "'"
End If
'Fill this Form wide variable so that it can be used for
'the Report.
CurrentFilter = FilterClause: FilterClause = ""
'Place our created Filter Criteria into the Filter property of SubForm.
Forms("frmMainForm")("qryContactsBySupplier subform4").Form.Filter = CurrentFilter
'Turn on the Filter
Forms("frmMainForm")("qryContactsBySupplier subform4").Form.FilterOn = True
Exit_StockSearch:
Exit Function
Error_StockSearch:
MsgBox "StockSearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"Stock Search Error"
Resume Exit_StockSearch
End Function
Private Function StockSearch()
On Error GoTo Error_StockSearch
Dim FilterClause As String, D As Long
'Hold whether we Use AND or OR in our Filter Criteria
D = Me.DirectionGrp.Value
'1st Combo - Supplier - Assuming the Table Field 'tblVendor.txtVendorName' is of a Text DataType
If Nz(Me.cboSupplier.Column, 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[tblVendor.txtVendorName]='" & Me.cboSupplier.Value & "'"
End If
'2nd Combo - Wood Treatment - Assuming the Table Field 'tblVendor_1.txtVendorName' is of a Text DataType
If Nz(Me.cboConsortium.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[tblVendor_1.txtVendorName]='" & Me.cboConsortium.Value & "'"
End If
'3rd Combo - Stock Location - Assuming the Table Field 'txtFirstName' is of a Text DataType
If Nz(Me.cboContactName.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[txtFirstName]='" & Me.cboContactName.Value & "'"
End If
'4th Combo - Wood Drying (kilned) - Assuming the Table Field 'txtLastName' is of a Text DataType
If Len(Me.cboLastName.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[txtLastName]='" & Me.cboLastName.Value & "'"
End If
'Fill this Form wide variable so that it can be used for
'the Report.
CurrentFilter = FilterClause: FilterClause = ""
'Place our created Filter Criteria into the Filter property of SubForm.
Forms("frmMainForm")("qryContactsBySupplier subform4").Form.Filter = CurrentFilter
'Turn on the Filter
Forms("frmMainForm")("qryContactsBySupplier subform4").Form.FilterOn = True
Exit_StockSearch:
Exit Function
Error_StockSearch:
MsgBox "StockSearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"Stock Search Error"
Resume Exit_StockSearch
End Function