runtime error 3219

[Rider570]

NSQL = "INSERT INTO tblOpeName (name)" & " values (" & MSQL & ")"

Error occurs here.
What i need to do is
1. Copy Unique Operator name(No duplicates) (OpeName1) from tblGage
2. Where Date equal to txtdate.text
3. Insert them into another field called Name in tblOpeName
Until this line prg works well.

OSQL = "SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
Set rss = dbs.OpenRecordset(OSQL)
MSQL = rss("OpeName1")

sqldate funtion will return txtdate.text as #txtdate.text # where suitable to sql.

OSQL vale looks like "Text"

Plz help to solve this
Thanx

Private Sub cmdsummery_Click()
Set wss = DBEngine.Workspaces(0)
Set dbs = wss.OpenDatabase("Gagedetails.mdb")
Set rss = dbs.OpenRecordset("tblGage")
 
Do While Not rss.EOF
OSQL = "SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
Set rss = dbs.OpenRecordset(OSQL)
MSQL = rss("OpeName1")
NSQL = "INSERT INTO tblOpeName (name)" & " values (" & MSQL & ")"
Set rss = dbs.OpenRecordset(NSQL)
rss.MoveNext
Loop
rss.Close
Set rss = Nothing
End Sub

When i tried to run this got a Error code "runtime error 3219"
plz help

[jzwp11]

The first issue I see is that the field "name", the word name is a reserved word in Access. It is best not to use reserved words, so I would suggest changing the field name to perhaps txtOperName; in the mean time you can try enclosing the field name in square brackets. Additionally, you need to set off a text field with single quotes


Now regarding the VALUES section of the SQL text, if the following query can return multiple distinct openames, the way you have your append query will at most append 1. You need to loop through the recordset and append each opeName (you don't have the loop in the right place in your code)

"SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)

BTW, "date" is also a reserved word in ACCESS

I assume that your SQLDate() function has been verified to give you the correct output. Where is txtdate.Text getting it's value? Is it a control on the same for as the command but that runs this code? If so, you can reference it as me.txtDate (assuming that the control name is txtDate).

Here is what I think the code should look like (not tested)

Private Sub cmdsummery_Click()
Set wss = DBEngine.Workspaces(0)
Set dbs = wss.OpenDatabase("Gagedetails.mdb")

OSQL = "SELECT DISTINCT OpeName1 FROM tblGage WHERE [Date] =" & SQLDate(txtdate.Text)
Set rss = dbs.OpenRecordset(OSQL) 
Do Until rss.EOF

NSQL = "INSERT INTO tblOpeName ([name])" & " values ('" & rss!OpeName1 & "')"

 CurrentDb.Execute NSQL, dbFailOnError
 
rss.MoveNext
Loop
rss.Close
Set rss = Nothing
End Sub

On an outside note, you do not need code to do what you are trying to do. You can use a query along these lines:

INSERT INTO tblOpeName ( [name] )
SELECT DISTINCT tblGage.OpeName1
FROM tblGage
WHERE (((tblGage.[Date])=[forms]![YourFormName]![txtDate]));

[Rider570]

Thank you very much for the help.
Problem has solved.

[jzwp11]

Great! I'm glad you got it worked out; good luck on your project.