inset query error

[bob]

Can some one tell me what I am doing wrong here? creating
a clone of a form which is getting half the values from a
table and the other half are entered by the user on the
form. This recordset is then suppose to insert the records
in to the table.

The error is a syntax error wnd is the last line in the
insert query code.

Private Sub Command46_Click()

Dim strsql As String
Dim rs As DAO.Recordset
Dim db As DAO.Database ' for insert queries

Set db = CurrentDb
Set rs = Me.RecordsetClone

With rs

If .RecordCount <> 0 Then .MoveFirst

Do Until .EOF

'try entering only one value

strsql = _
"insert into tblattendance" & _
"(tblattendance.programcode)"
"VALUES("!progcode & ") ' error here

db.Execute strsql, dbFailOnError

.MoveNext
Loop
End With

Set rs = Nothing
Set db = Nothing
End Sub

 

[Gary Miller]

Bob,

Not sure if this is your only problem, but you need to add
some spaces in your SQL string. You have...

strsql = _
"insert into tblattendance" & _
"(tblattendance.programcode)"
"VALUES("!progcode & ") ' error here

Try...

strsql = _
"insert into tblattendance" & _
" (tblattendance.programcode)"
" VALUES("!progcode & ") ' error here

It's always helpful to use a message box displaying your SQL
when you are troubleshootingj problems. This would have
shown the words running together.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[bob]

No tthis is not working.

Do you think I am having a problem in the syntax of my
Insert query somewhere?

 

[Gary Miller]

Two things. Depending on your version of Access !progcode
may be better off with the Me predicator... Me!progcode.
Also, now I see a missing " after your last parenthesis and
a missing ampersand...

"VALUES("!progcode & ")
should be
" VALUES(" & !progcode & ")"
or
" VALUES(" & Me!progcode & ")"
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________