problem with INSERT INTO code

  • Thread starter Thread starter JulieD
  • Start date Start date
J

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


Ken Snell said:
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>

JulieD said:
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 = '" &
Click to expand...
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
Click to expand...
Click to expand...
 
You're welcome....good luck!

--
Ken Snell
<MS ACCESS MVP>

JulieD said:
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


Ken Snell said:
Assuming that your setup will work the way you want, the error that I
Click to expand...
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 = '" &
Click to expand...
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>

JulieD said:
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 = '" &
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top