Need help with SQL Insert statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 29 Apr 2005 05:36 GMT

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]
& "')"

Signature

Thanks,
Tom

Reply to this Message

Dirk Goldgar - 29 Apr 2005 05:45 GMT

> I have a main form which 3 sub forms:
> - Main form has
[quoted text clipped - 15 lines]
> 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?

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Tom - 29 Apr 2005 05:59 GMT

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?

Signature

Thanks,
Tom

>> I have a main form which 3 sub forms:
>> - Main form has
[quoted text clipped - 32 lines]
> Your SQL statements refer to a control named "RadioButton". Is that the
> correct name?

Reply to this Message

Dirk Goldgar - 29 Apr 2005 06:15 GMT

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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

tina - 29 Apr 2005 07:55 GMT

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?
[quoted text clipped - 37 lines]
> > Your SQL statements refer to a control named "RadioButton". Is that the
> > correct name?

Reply to this Message

Tom - 29 Apr 2005 16:21 GMT

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

Signature

Thanks,
Tom

> chances are you're referring to one or more of your subforms by the name
> of
[quoted text clipped - 67 lines]
>> > the
>> > correct name?

Reply to this Message

Dirk Goldgar - 29 Apr 2005 22:11 GMT

> To check the proper control names, I did the following:
> - Opened up main form
[quoted text clipped - 5 lines]
>
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message