Need help with SQL Insert statement

[Tom]

I have a main form which 3 sub forms:
- Main form has
- subform "frmSurveyKPILevel_1" which has
- subsubform "frmSurveyKPILevel_2" which has
- subsubsubform "frmSurveyKPILevel_3"...

On the 3rd level subform "frmSurveyKPILevel_3", I have a control (Score)
which value I need to insert into a table via command button on the main
form.

I used the 2 strSQLs below... they don't give me a syntax error, but I get
RunTime Error 438 "Object doesn't support this property of method".

Any ideas how I can insert the value from 3rd level subform in a table via
command button on the main form?



strSQL = "INSERT INTO tblKPIAnswers (Score) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyKPILevel_2].[Form].[RadioButton]
& "')"

strSQL = "INSERT INTO tblKPIAnswers (ScoreQ1) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyKPILevel_2].[Form].[RadioButton]
& "')"

 

[Dirk Goldgar]

I have a main form which 3 sub forms:
- Main form has
- subform "frmSurveyKPILevel_1" which has
- subsubform "frmSurveyKPILevel_2" which has
- subsubsubform "frmSurveyKPILevel_3"...

On the 3rd level subform "frmSurveyKPILevel_3", I have a control
(Score) which value I need to insert into a table via command button
on the main form.

I used the 2 strSQLs below... they don't give me a syntax error, but
I get RunTime Error 438 "Object doesn't support this property of
method".

Any ideas how I can insert the value from 3rd level subform in a
table via command button on the main form?



strSQL = "INSERT INTO tblKPIAnswers (Score) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

strSQL = "INSERT INTO tblKPIAnswers (ScoreQ1) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

Shouldn't that last subform name in both SQL statements be
"frmSurveyKPILevel_3", not "frmSurveyKPILevel_2"?

Your SQL statements refer to a control named "RadioButton". Is that the
correct name?

 

[Tom]

Dirk:

Yes, you're right,

Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyKPILevel_3].[Form].[RadioButton]

And yes, Radiobutton is correct... it's currently a combo box... but that
shouldn't make a difference right?

Any ideas how to fix the entire SQL statement w/o getting the RTE 438?

--
Thanks,
Tom

I have a main form which 3 sub forms:
- Main form has
- subform "frmSurveyKPILevel_1" which has
- subsubform "frmSurveyKPILevel_2" which has
- subsubsubform "frmSurveyKPILevel_3"...

On the 3rd level subform "frmSurveyKPILevel_3", I have a control
(Score) which value I need to insert into a table via command button
on the main form.

I used the 2 strSQLs below... they don't give me a syntax error, but
I get RunTime Error 438 "Object doesn't support this property of
method".

Any ideas how I can insert the value from 3rd level subform in a
table via command button on the main form?



strSQL = "INSERT INTO tblKPIAnswers (Score) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

strSQL = "INSERT INTO tblKPIAnswers (ScoreQ1) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

Shouldn't that last subform name in both SQL statements be
"frmSurveyKPILevel_3", not "frmSurveyKPILevel_2"?

Your SQL statements refer to a control named "RadioButton". Is that the
correct name?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk:

Yes, you're right,

Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_3].[Form].[RadioButton]

But you're saying that still isn't working?

And yes, Radiobutton is correct... it's currently a combo box... but
that shouldn't make a difference right?

Any ideas how to fix the entire SQL statement w/o getting the RTE 438?

Have you now posted the actual code, copied and pasted, not transcribed?
I would use bangs (!), not dots (.) in a couple of places there, but
based on the information you've given I would expect this to work:

'----- start of code -----
strSQL = _
"INSERT INTO tblKPIAnswers (Score) " & "VALUES ('" & _
Me![frmSurveyKPILevel_1].[Form]![frmSurveyKPILevel_2].[Form]![frmSurveyK
PILevel_3].[Form]![RadioButton] & "')"

'----- end of code -----

Note that the above will undoubtedly have been wrapped by the
newsreader, and you'll have to do the necessary "unwrapping".

If that doesn't work, check that the subform names in that rather
complex reference are all the names of the subform *controls* (on their
respective parent forms), which are not necessarily the same as the form
objects themselves.

 

[tina]

chances are you're referring to one or more of your subforms by the name of
the subform rather than the name of the subform *control*. example: i
tested a form with 3 nested subforms. i named the forms frmTest,
frmTestSub1, frmTestSub2, and frmTestSub3. in the first three forms, i named
the subform controls Child1, Child2, and Child3, respectively. added a
textbox to frmTestSub3, calling it Text3. i put a command button on frmTest,
which simply opens a message box displaying the value of control Text3 when
clicked. here's my syntax:

MsgBox Me!Child1!Child2!Child3!Text3

to make sure you get the correct name of a subform control, select the
subform within its' parent form's design view, click on the Other tab in the
Properties box, and look at the Name property.

hth

Dirk:

Yes, you're right,

Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyKPILe
vel_3].[Form].[RadioButton]

And yes, Radiobutton is correct... it's currently a combo box... but that
shouldn't make a difference right?

Any ideas how to fix the entire SQL statement w/o getting the RTE 438?

--
Thanks,
Tom

I have a main form which 3 sub forms:
- Main form has
- subform "frmSurveyKPILevel_1" which has
- subsubform "frmSurveyKPILevel_2" which has
- subsubsubform "frmSurveyKPILevel_3"...

On the 3rd level subform "frmSurveyKPILevel_3", I have a control
(Score) which value I need to insert into a table via command button
on the main form.

I used the 2 strSQLs below... they don't give me a syntax error, but
I get RunTime Error 438 "Object doesn't support this property of
method".

Any ideas how I can insert the value from 3rd level subform in a
table via command button on the main form?



strSQL = "INSERT INTO tblKPIAnswers (Score) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

strSQL = "INSERT INTO tblKPIAnswers (ScoreQ1) " & _
"VALUES ('" &
Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

Shouldn't that last subform name in both SQL statements be
"frmSurveyKPILevel_3", not "frmSurveyKPILevel_2"?

Your SQL statements refer to a control named "RadioButton". Is that the
correct name?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tom]

Tina:

I'm doing some step-by-step testing.

1a: I test for a value on the 1st subform (frmSurveyKPILevel_1)
1b: the textbox I'm testing is "TaskNo"
1c: I use the statement below and I get proper output of "1"

MsgBox "Value is: " & Me!frmSurveyKPILevel_1!TaskNo.Value


2a: Now, I tested for the next-layer subform (frmSurveyKPILevel_2) which
also has the field "TaskNo"
2b: I modified the MsgBox line to:

MsgBox "Value is: " &
Me!frmSurveyKPILevel_1!frmSurveyKPILevel_2!TaskNo.Value

Now, I get the error: "Runtime error 2455: You entered an expression that
has in invalid reference to the property Form/Report

To check the proper control names, I did the following:
- Opened up main form
- clicked on the 1st subform (frmSurveyKPILevel_1); then click on black
square in top left corner... the Properties
- both "Data" and "Other" tab have the value "frmSurveyKPILevel_1"
- I did the same for the 2nd subform (frmSurveyKPILevel_1)... Data and Other
tab have the subform's name listed in there


Any ideas what still might be missing?

Tom







--
Thanks,
Tom

chances are you're referring to one or more of your subforms by the name
of
the subform rather than the name of the subform *control*. example: i
tested a form with 3 nested subforms. i named the forms frmTest,
frmTestSub1, frmTestSub2, and frmTestSub3. in the first three forms, i
named
the subform controls Child1, Child2, and Child3, respectively. added a
textbox to frmTestSub3, calling it Text3. i put a command button on
frmTest,
which simply opens a message box displaying the value of control Text3
when
clicked. here's my syntax:

MsgBox Me!Child1!Child2!Child3!Text3

to make sure you get the correct name of a subform control, select the
subform within its' parent form's design view, click on the Other tab in
the
Properties box, and look at the Name property.

hth

Dirk:

Yes, you're right,

Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyKPILe
vel_3].[Form].[RadioButton]

And yes, Radiobutton is correct... it's currently a combo box... but that
shouldn't make a difference right?

Any ideas how to fix the entire SQL statement w/o getting the RTE 438?

--
Thanks,
Tom

I have a main form which 3 sub forms:
- Main form has
- subform "frmSurveyKPILevel_1" which has
- subsubform "frmSurveyKPILevel_2" which has
- subsubsubform "frmSurveyKPILevel_3"...

On the 3rd level subform "frmSurveyKPILevel_3", I have a control
(Score) which value I need to insert into a table via command button
on the main form.

I used the 2 strSQLs below... they don't give me a syntax error, but
I get RunTime Error 438 "Object doesn't support this property of
method".

Any ideas how I can insert the value from 3rd level subform in a
table via command button on the main form?



strSQL = "INSERT INTO tblKPIAnswers (Score) " & _
"VALUES ('" &

Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

strSQL = "INSERT INTO tblKPIAnswers (ScoreQ1) " & _
"VALUES ('" &

Me.[frmSurveyKPILevel_1].[Form].[frmSurveyKPILevel_2].[Form].[frmSurveyK
PILevel_2].[Form].[RadioButton]
& "')"

Shouldn't that last subform name in both SQL statements be
"frmSurveyKPILevel_3", not "frmSurveyKPILevel_2"?

Your SQL statements refer to a control named "RadioButton". Is that
the
correct name?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

To check the proper control names, I did the following:
- Opened up main form
- clicked on the 1st subform (frmSurveyKPILevel_1); then click on
black square in top left corner... the Properties
- both "Data" and "Other" tab have the value "frmSurveyKPILevel_1"
- I did the same for the 2nd subform (frmSurveyKPILevel_1)... Data
and Other tab have the subform's name listed in there


Any ideas what still might be missing?

I'm not sure whether you did this right or not. If you check the
property sheet of each subform control, making sure that the caption of
the property sheet states that you're looking at the properties of a
Subform/Subreport (and *not* Form), then you should find the Name
property on the Other tab showing "frmSurveyKPILevel_1" for one subform
control, "frmSurveyKPILevel_2" for the next lower subform control, and
"frmSurveyKPILevel_3" for the lowest subform control.