multi field recordset insert error

[bob]

I am having a problem when I am running this code. It
works fine with one field but when I am putting all the
three fields at a time it is creating a problem...

the error is pointing to the statement...insert into...

and the message is..
Compile error:
Wrong number of argumentsor invalid property assignment.


Private Sub Command46_Click()


Dim db As Database
Dim rs As Recordset
Dim strsQl As String

Set rs = Me.RecordsetClone
Set db = CurrentDb

rs.MoveFirst

Do Until rs.EOF

strsQl = _
"Insert into tblattendance
(StudentClockNum,programcode,coursenum) values (" & _
rs.Fields("StudentClockNum", "progcode", "cnum") & ");"

db.Execute strsQl, dbFailOnError

rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

 

[Dirk Goldgar]

I am having a problem when I am running this code. It
works fine with one field but when I am putting all the
three fields at a time it is creating a problem...

the error is pointing to the statement...insert into...

and the message is..
Compile error:
Wrong number of argumentsor invalid property assignment.


Private Sub Command46_Click()


Dim db As Database
Dim rs As Recordset
Dim strsQl As String

Set rs = Me.RecordsetClone
Set db = CurrentDb

rs.MoveFirst

Do Until rs.EOF

strsQl = _
"Insert into tblattendance
(StudentClockNum,programcode,coursenum) values (" & _
rs.Fields("StudentClockNum", "progcode", "cnum") & ");"

db.Execute strsQl, dbFailOnError

rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

You can only index your recordset by one field at a time, so you must
write your statement like this:

strsQl = _
"Insert into tblattendance " & _
"(StudentClockNum,programcode,coursenum) " & _
"Values (" & _
rs.Fields("StudentClockNum") & ", " & _
rs.Fields("progcode") & ", " & _
rs.Fields("cnum") & _
");"

Unless I made a typo somewhere, that will work so long as these are all
numeric fields.

 

[bob]

Thanks!!

I figures this one out but only partly.


I have 2 fields which are coming froma table and I am
writing a query and showing it on a form and then
inserting them in to an anotther table. Simultaneously on
the same form I have 3 fields called Progcode,coursenum
and logintime which I am having the user enter in
textboxes and then for each record in the recordset I will
be inserting these values on to the new table.

Essentailly my question is how would I enter these unbound
fields in to the new table.

The code that I have devleoped so far worsk fine..and that
is... Please note that the Thours afield and the
Studentclocknum fields are bound fields. The other three
fields called Programcode,cnum and logintime are unbound
fields. Should I use them as variables and also add them
to my insert statement or what?





Private Sub Command46_Click()


Dim db As Database
Dim rs As Recordset
Dim strsQl As String

Set rs = Me.RecordsetClone
Set db = CurrentDb

rs.MoveFirst

Do Until rs.EOF

'begin of the insert statement to insert the records

strsQl = _
"Insert into tblattendance " & _
"(StudentClockNum,shours) " & _
"Values (" & _
rs.Fields("StudentClockNum") & ", " & _
rs.Fields("thours") & _
");"

db.Execute strsQl, dbFailOnError

rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

 

[Dirk Goldgar]

Thanks!!

I figures this one out but only partly.


I have 2 fields which are coming froma table and I am
writing a query and showing it on a form and then
inserting them in to an anotther table. Simultaneously on
the same form I have 3 fields called Progcode,coursenum
and logintime which I am having the user enter in
textboxes and then for each record in the recordset I will
be inserting these values on to the new table.

Essentailly my question is how would I enter these unbound
fields in to the new table.

The code that I have devleoped so far worsk fine..and that
is... Please note that the Thours afield and the
Studentclocknum fields are bound fields. The other three
fields called Programcode,cnum and logintime are unbound
fields. Should I use them as variables and also add them
to my insert statement or what?





Private Sub Command46_Click()


Dim db As Database
Dim rs As Recordset
Dim strsQl As String

Set rs = Me.RecordsetClone
Set db = CurrentDb

rs.MoveFirst

Do Until rs.EOF

'begin of the insert statement to insert the records

strsQl = _
"Insert into tblattendance " & _
"(StudentClockNum,shours) " & _
"Values (" & _
rs.Fields("StudentClockNum") & ", " & _
rs.Fields("thours") & _
");"

db.Execute strsQl, dbFailOnError

rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

Try this:

'----- start of suggested code -----
Private Sub Command46_Click()

Dim db As DAO.Database
Dim strsQl As String

Set db = CurrentDb

With Me.RecordsetClone

.MoveFirst

Do Until .EOF

strsQl = _
"Insert into tblattendance " & _
"(StudentClockNum,shours," & _
"Programcode,cnum,logintime) " & _
"Values (" & _
.Fields("StudentClockNum") & ", " & _
.Fields("thours") & ", " & _
Me!Programcode & ", " & _
Me!cnum & ", " & _
Me!logintime & _
");"

db.Execute strsQl, dbFailOnError

.MoveNext

Loop

End With

Set db = Nothing

End Sub
'----- end of suggested code -----

Note that the above code assumes

(a) the controls on the form and the fields in the table have the
same names. If they don't, you'll have to change the names in the
"strsQl=" statement where appropriate.

(b) the fields are all numeric. If they aren't (which seems quite
likely), you'll have to insert the appropriate delimiters -- quotes for
text, '#' for date/time -- around the embedded values in the SQL
statement.

 

[bob]

I am getting closer. Now the error I am getting is
that ....at line db.execute...

and the error is runtime'3061'
Too few parameters . Expected 1.


Also, my question is I am not doing anything wrong here in
creating the recordset right? I mean I can createa
Recordset from a form where some fields are bound and the
others are unbound.

For my fields....

Studentclocknum is text
Shours is numbers
programcode is text
cnme is text
logintime is date
logout is date
exempt is a text...a check box?


my syntax is right?



Private Sub Command46_Click()


Dim db As DAO.Database
Dim strsql As String

Set db = CurrentDb

With Me.RecordsetClone

..MoveFirst

Do Until .EOF

'begin of the insert statement to insert the records

strsql = _
"Insert into tblattendance " & _
"(StudentClockNum,programcode,coursenum,exempt,
shours) " & _
"Values (" & _
.Fields("StudentClockNum") & ", " & _
Me!ProgCode & ", " & _
Me!CNum & ", " & _
Me!Check60 & "," & _
.Fields("thours") & _
");"

db.Execute strsql, dbFailOnError

..MoveNext
Loop
End With


Set db = Nothing

End Sub

 

[Dirk Goldgar]

I am getting closer. Now the error I am getting is
that ....at line db.execute...

and the error is runtime'3061'
Too few parameters . Expected 1.


Also, my question is I am not doing anything wrong here in
creating the recordset right? I mean I can createa
Recordset from a form where some fields are bound and the
others are unbound.

For my fields....

Studentclocknum is text
Shours is numbers
programcode is text
cnme is text
logintime is date
logout is date
exempt is a text...a check box?


my syntax is right?



Private Sub Command46_Click()


Dim db As DAO.Database
Dim strsql As String

Set db = CurrentDb

With Me.RecordsetClone

.MoveFirst

Do Until .EOF

'begin of the insert statement to insert the records

strsql = _
"Insert into tblattendance " & _
"(StudentClockNum,programcode,coursenum,exempt,
shours) " & _
"Values (" & _
.Fields("StudentClockNum") & ", " & _
Me!ProgCode & ", " & _
Me!CNum & ", " & _
Me!Check60 & "," & _
.Fields("thours") & _
");"

db.Execute strsql, dbFailOnError

.MoveNext
Loop
End With


Set db = Nothing

End Sub

I see from the multitude of other threads you've started on this issue
that you've moved on from the questions you're asking here, so I have
nothing to add -- except to tell you that you are not "creating a
recordset"; you're just executing a query after collecting values from
the form's recordset.