"Argument not optional" error after converting to access 97

[AyeSee]

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

[HiTechCoach]

There is a bug in your code;

This line:




'1st Combo - Supplier - Assuming the Table Field 'tblVendor.txtVendorName' is of a Text DataType
If Nz(Me.cboSupplier.Column, 0) > 0 Then



is missing the (0) after the Column

Try:

Me.cboSupplier.Column(0)

What line of code is generating the error?

[AyeSee]

Oh lord. Yes you are right, I inserted that error while trying to fix my initial problem: I get a type mismatch..... "Stock Search function error: 13- Type Mismatch"

That being said, I had forgotten about it, and I was stuck on the error I talked about in my first post: "Argument not optional". Thanks for pointing out that mistake from my part. But my inital problem that caused me to try things (and ultimately forget to put the (0) ) is the problem of mistatch... I'm gessing it has to do with different types of attributes, but that does not make sense, because they are both text.

One problem that is obvious though is that in my tables Access 97 displays the wording properly, but in the query, some special characters are not interpreted properly. ex: é è ², etc. I am in a french environment so their occurence is quite certain. That being said, in my combo box, they all appear, but in the subform I am trying to filter, they are not displayed properly.