multifield recordset 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

 

[Michel Walsh]

Hi,



change

rs.Fields("StudentClockNum", "progcode", "cnum")

into

rs.Fields("StudentClockNum") & ", " & rs.Fields( "progcode") & ", " &
rs.Fields("cnum")


where I assumed all the values are numerical. If some are alpha, then
you have to add the required delimiters.

Hoping it may help,
Vanderghast, Access MVP

 

[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

 

[Michel Walsh]

Hi,

You can try something like

INSERT INTO tableName( f1, f2, f3, f4, f5) SELECT g1, 55, g3, g4,
444 FROM otherTable WHERE pk=102


or, if you prefer, maybe more illustrative of the concept:


"INSERT INTO tableName(f1, f2, f3, f4, f5) SELECT g1, " &
someConstant & ", g3, g4, " & someConstantForF5 & " FROM otherTable WHERE
itsPk=" & pkValueIdentifyingTheRecord


if some values come from known constants (55 and 444) while other come from
a record of another table, g1, g3 and g4, where you know the primary key
value that refer to that record.


Technically, the "other table" can be the same table than the one into which
we insert records.



Hoping it may help,
Vanderghast, Access MVP