insert 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

 

[tina]

i take it the user is entering values into unbound
controls on the form? if so, how about just running the
following code on the command button:

DoCmd.RunSQL "INSERT INTO TableName( FirstFieldName,
SecondFieldName, ThirdFieldName )
SELECT " & Me!FirstControlName & ", " & Me!
SecondControlName & ", " & Me!ThirdControlName, False

if any of the fields are text rather than numbers,
remember to add the single quotes, as

'" & Me!FirstControlName & "', "....

hth

 

[bob]

But Tina My problem is that I am entering a
recordset....i.e like 25 values for each record i.e 6
fields are being entered for each record.

2 of the fields are bound to an another table called
tblstudents

Studentclocknum
shours

and the others are not bound

progcode
clocknum
login
exemptall

also in your code why do you write False after everything?

 

[tina]

i wasn't offering a solution to your recordset clone code,
just suggesting an alternative solution. using either one,
seems you have to write an SQL statement, so it doesn't
really matter how many field values you're inserting into
the table. just seems easier to me to dump that form
control values directly into the table, rather then using
a recordset clone. if writing a long SQL statement
troubles you, you could just build an append query and
save it in the db, then just call the query object from
the VBA code.

re False, see Help for an explanation of the RunSQL
statement parameters. if you're not wrapping the statement
in a transaction, setting the value to False may speed up
the query, per the Help documentation.

hth