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)

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 - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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)
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <(E-Mail Removed)> wrote in message
news:%23a8%(E-Mail Removed)...
> 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
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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)