Data mismatch criteria error ?

[jshangkuan]

I have a really simple VBA code, and I have a "Data mismatch criteria
error" that I have no idea why it occurs. I have a table "MyTable"
where all the fields are text. When I run the code, it errors on the
last line when I set rs. I think this is because of the CurrentDb. I
have another error like this with another function, and I am not sure
why this occurs.

Your help would be appreciated. I also tried docmd.execute(mySQLstr),
which also doesn't work. What is the difference?

Private Sub cmdFindRecords_Click()
MySQLStr = "SELECT MyTable.* FROM MyTable WHERE ("

If Not IsNull(Me.Field1) Then
MySQLStr = MySQLStr & " ((MyTable.Field1) ='" &
[Forms]![MySearchForm]![Field1] & "') And"
End If
If Not IsNull(Me.Field2) Then
MySQLStr = MySQLStr & " ((MyTable.Field2) ='" &
[Forms]![MySearchForm]![Field2] & "') And"
End If

End If

MySQLStr = MySQLStr & "((MyTable.Field1) > ''));"

Set rs = CurrentDb.OpenRecordset(MySQLStr)

 

[Allen Browne]

You don't show the declaration line for rs, but try adding this line to the
top of your procedure:
Dim rs AS DAO.Recordset

If that fails also, you probably need to add a reference to:
Microsoft DAO 3.6 Library
It's no the Tools menu, under References.

More info on references here:
http://allenbrowne.com/ser-38.html

 

[Allen Browne]

You also asked about the difference between OpenRecordset and Execute.

OpenRecordset() is like an ordinary SELECT query, except that you don't see
the results in a query window. Instead, they exist only in memory, where you
can walk through the records, and examine or edit the fields.

Execute works like an action query (Append/Delete/Update/Make Table.) When
you run one of these queries you don't see any results: just information
about what records were changed. WIth DoCmd, you can use RunSQL, but not
Execute. For more info on the difference between RunSQL and Execute, see:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You don't show the declaration line for rs, but try adding this line to
the top of your procedure:
Dim rs AS DAO.Recordset

If that fails also, you probably need to add a reference to:
Microsoft DAO 3.6 Library
It's no the Tools menu, under References.

More info on references here:
http://allenbrowne.com/ser-38.html

I have a really simple VBA code, and I have a "Data mismatch criteria
error" that I have no idea why it occurs. I have a table "MyTable"
where all the fields are text. When I run the code, it errors on the
last line when I set rs. I think this is because of the CurrentDb. I
have another error like this with another function, and I am not sure
why this occurs.

Your help would be appreciated. I also tried docmd.execute(mySQLstr),
which also doesn't work. What is the difference?

Private Sub cmdFindRecords_Click()
MySQLStr = "SELECT MyTable.* FROM MyTable WHERE ("

If Not IsNull(Me.Field1) Then
MySQLStr = MySQLStr & " ((MyTable.Field1) ='" &
[Forms]![MySearchForm]![Field1] & "') And"
End If
If Not IsNull(Me.Field2) Then
MySQLStr = MySQLStr & " ((MyTable.Field2) ='" &
[Forms]![MySearchForm]![Field2] & "') And"
End If

End If

MySQLStr = MySQLStr & "((MyTable.Field1) > ''));"

Set rs = CurrentDb.OpenRecordset(MySQLStr)