problem with INSERT INTO code

[JulieD]

Hi All

i've a form (Classes) with a subform (Units) - and in the subform is another
subform (Assignments). When the user picks the unit that the student is
going to do, i want to populate the assignments subform with the standard
assignments that are available for that unit and then they can enter due
dates & other information.

i am using the ONENTER event of the assignments subform to run the following
code:

Dim dbs
Dim rst
Dim strsql, txtunit As String

'append assignments relevent to unit into jtbl_ClassAssignment
txtunit = Me![UnitID]
Set dbs = CurrentDb
dbs.Execute = "INSERT INTO jTBL_ClassAssignment ( Assignment, Marks )
SELECT ztbl_Assignment.Assignment, ztbl_Assignment.Marks" _
& " FROM ztbl_Assignment WHERE ztbl_Assignment.unit = '" & txtunit &
"'"

However, i'm getting a run-time error 3251 "Operation is not supported for
this type of object"
and it doesn't work.

Please help.

Regards
JulieD

 

[Ken Snell]

Assuming that your setup will work the way you want, the error that I see in
your code is the = sign after dbs.Execute.

dbs.Execute "INSERT INTO jTBL_ClassAssignment ( Assignment, Marks )
SELECT ztbl_Assignment.Assignment, ztbl_Assignment.Marks" _
& " FROM ztbl_Assignment WHERE ztbl_Assignment.unit = '" & txtunit &
"'"

Also, note that your Dim of strsql is making strsql a variant variable type,
not a string type. In VBA code, you must explicitly declare each variable
type or else ACCESS will default it to Variant.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String, txtunit As String

 

[JulieD]

Hi Ken

thanks - i knew it was something simple (as i was sure i had it working
yesterday before my system crashed) - and yes, it's working fine now and
almost doing everything i want.

and thanks for the advice re variable data types.

Cheers
JulieD

Assuming that your setup will work the way you want, the error that I see in
your code is the = sign after dbs.Execute.

dbs.Execute "INSERT INTO jTBL_ClassAssignment ( Assignment, Marks )
SELECT ztbl_Assignment.Assignment, ztbl_Assignment.Marks" _
& " FROM ztbl_Assignment WHERE ztbl_Assignment.unit = '" & txtunit &
"'"

Also, note that your Dim of strsql is making strsql a variant variable type,
not a string type. In VBA code, you must explicitly declare each variable
type or else ACCESS will default it to Variant.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String, txtunit As String

--
Ken Snell
<MS ACCESS MVP>

Hi All

i've a form (Classes) with a subform (Units) - and in the subform is another
subform (Assignments). When the user picks the unit that the student is
going to do, i want to populate the assignments subform with the standard
assignments that are available for that unit and then they can enter due
dates & other information.

i am using the ONENTER event of the assignments subform to run the following
code:

Dim dbs
Dim rst
Dim strsql, txtunit As String

'append assignments relevent to unit into jtbl_ClassAssignment
txtunit = Me![UnitID]
Set dbs = CurrentDb
dbs.Execute = "INSERT INTO jTBL_ClassAssignment ( Assignment, Marks )
SELECT ztbl_Assignment.Assignment, ztbl_Assignment.Marks" _
& " FROM ztbl_Assignment WHERE ztbl_Assignment.unit = '" &

txtunit

&
"'"

However, i'm getting a run-time error 3251 "Operation is not supported for
this type of object"
and it doesn't work.

Please help.

Regards
JulieD

 

[Ken Snell]

You're welcome....good luck!

--
Ken Snell
<MS ACCESS MVP>

Hi Ken

thanks - i knew it was something simple (as i was sure i had it working
yesterday before my system crashed) - and yes, it's working fine now and
almost doing everything i want.

and thanks for the advice re variable data types.

Cheers
JulieD

Assuming that your setup will work the way you want, the error that I

see

in
your code is the = sign after dbs.Execute.

dbs.Execute "INSERT INTO jTBL_ClassAssignment ( Assignment, Marks )
SELECT ztbl_Assignment.Assignment, ztbl_Assignment.Marks" _
& " FROM ztbl_Assignment WHERE ztbl_Assignment.unit = '" &

txtunit

&
"'"

Also, note that your Dim of strsql is making strsql a variant variable type,
not a string type. In VBA code, you must explicitly declare each variable
type or else ACCESS will default it to Variant.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String, txtunit As String

--
Ken Snell
<MS ACCESS MVP>

Hi All

i've a form (Classes) with a subform (Units) - and in the subform is another
subform (Assignments). When the user picks the unit that the student is
going to do, i want to populate the assignments subform with the standard
assignments that are available for that unit and then they can enter due
dates & other information.

i am using the ONENTER event of the assignments subform to run the following
code:

Dim dbs
Dim rst
Dim strsql, txtunit As String

'append assignments relevent to unit into jtbl_ClassAssignment
txtunit = Me![UnitID]
Set dbs = CurrentDb
dbs.Execute = "INSERT INTO jTBL_ClassAssignment ( Assignment, Marks )
SELECT ztbl_Assignment.Assignment, ztbl_Assignment.Marks" _
& " FROM ztbl_Assignment WHERE ztbl_Assignment.unit = '" &

txtunit

&
"'"

However, i'm getting a run-time error 3251 "Operation is not supported for
this type of object"
and it doesn't work.

Please help.

Regards
JulieD