DAO Recordset

[torb123]

I am working with products and systems. Some products are systems by themselves. I have set to a command button to make the product a system, but I want it to check and make sure it hasn't already been created as a system to eleminate duplicates. I am basing it off of the ProductName, and SystemName. If the products is a system as well, the name will be the same.

:Code Start:

Dim rstC As DAO.Recordset
Dim strSQL As String
DoCmd.RunCommand acCmdSaveRecord

strSQL = "SELECT tblSystem.SystemName FROM tblSystem WHERE SystemName=" & Forms!frm_Products!ProductName
Set rstC = CurrentDb.OpenRecordset(strSQL)

:End Code:

after that I will check to see if it brought up a record with
If rstC.RecordCount > 0 Then
MsgBox "There is already a System with this name." & vbCrLf & "Do you want to update it?"

or something like that. The problem I am having is that I get an error when I try to run the code.
I am assuming that it is an error with my sql statement, but when I paste the text into a query, it works great. I have tried coding it a few different ways that I know works, but with the same error.

The error is:
Runtime Error '3075':
Syntax error (missing operator) in query expression 'SystemName=Name of the product I am currently on'.

Anyone know how to fix this, or another way that I could check to see if there is a system with the same name as a product?

Thanks!

 

[PHV]

strSQL = "SELECT tblSystem.SystemName FROM tblSystem WHERE SystemName=[tt]'"[/tt] & Forms!frm_Products!ProductName & [tt]"'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[torb123]

Thanks PHV, works great.