I created a query in Access 2000 that runs perfectly. I then copied the
"sql" version of the same query and set it equal the variable "sql" in the
code below. When I run the code below I get an "Type Mismatch" error. Can
someone explain how to fix this?

Thanks,
TD


Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As Database
Dim rst As Recordset
Dim sql As String

[Forms]![frmVendorSetup]![Name].SetFocus

sql = "SELECT tblVendors.Name FROM tblVendors WHERE (((tblVendors.Name)
Like [Forms]![frmVendorSetup]![Name].[Text] & " * "));"


Set db = OpenDatabase("adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

If rst.RecordCount > 0 Then
MsgBox "This vendor may already exist, check again!", vbOKOnly,
WAIT!: GoTo Exit_cmdSave_Click:
GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cboName.Requery
GoTo Exit_cmdSave_Click:
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

You're embedding a " character within a text string, so ACCESS doesn't know
whether that " character is the end of the string or a real character. So it
interprets it as the end of the string. To embed a " character, double it:

sql = "SELECT tblVendors.Name FROM tblVendors WHERE (((tblVendors.Name)
Like [Forms]![frmVendorSetup]![Name].[Text] & "" * ""));"
--

Ken Snell
<MS ACCESS MVP>

"TD" <tdlee (AT) mindspring (DOT) com> wrote

Thanks Ken. I made the changes you suggested but now I get this error
message "Too few parameters. Expect 1" Any suggestions?

Thanks again
TD

Ken, I tried what you suggested but I get the "Type mismatch" error again.
Here is the sql statement, all on one line of course:

sql = "SELECT tblVendors.Name FROM tblVendors WHERE (((tblVendors.Name) Like
'" & [Forms]![frmVendorSetup]![Name].[text] & "*'));"


Thanks in advance for your help!!!


"Ken Snell" <kthsneisllis9 (AT) ncoomcastt (DOT) renaetl> wrote

You'll need to resolve the reference to the form's control by concatenating
it outside the text string.

sql = "SELECT tblVendors.Name FROM tblVendors WHERE (((tblVendors.Name)
Like '" & [Forms]![frmVendorSetup]![Name].[Text] & "*'));"
--

Ken Snell
<MS ACCESS MVP>

"TD" <tdlee (AT) mindspring (DOT) com> wrote

ACCESS may be confused by your use of Name as a field name. That is a
reserved word in ACCESS.

Try this:

sql = "SELECT tblVendors.[Name] FROM tblVendors WHERE (((tblVendors.[Name])
Like
'" & [Forms]![frmVendorSetup]![Name].[text] & "*'));"


Are you sure you need to use the Text property of the Name control? Usually,
Value is better.
--

Ken Snell
<MS ACCESS MVP>


"TD" <tdlee (AT) mindspring (DOT) com> wrote