Small error in subform syntax

[Guest]

Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

 

[Marshall Barton]

Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Guest]

Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?

Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Marshall Barton]

No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.
--
Marsh
MVP [MS Access]

Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?

Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Guest]

many many thanks; I will try each of thes and post the result by tomorrow

No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.
--
Marsh
MVP [MS Access]

Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?

bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Guest]

Tried all of the above; results to breaks on each line: the line with the
reference to the subform is the one throwing the error.

Using the code you provided in your message, the following was the result:

?Forms!frmFilterChoice.Name
frmFilterChoice
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
frmFirstFilter_datasheet
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

Run-time error 2455: "You entered an expression that has an invalid
reference ..." (same error as before).

No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.
--
Marsh
MVP [MS Access]

Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?

bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Guest]

I tried changing the name to no avail . . .

No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.
--
Marsh
MVP [MS Access]

Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?

bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:

'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False

First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Marshall Barton]

Well at least we've narrowed it down to what we've suspected
all along. Unfortunately, I don't have any new ideas. I
just keep coming back to the name of the subform Control
being out of whack somehow, almost as if it were a text box
or something.

Straw grasping now. Let's try add ing another test to the
debug list:

?Forms!frmFilterChoice!frmFirstFilter_datasheet.SourceObject

If that generates an error, it would confirm that the
control is not a subform control.

If it generates a blank, then there is no form object to
display in the control.

If it displays the name of the form, then double check that
the form actually exists in the database window. The
underscore kind of bothers me, are you sure that it's not a
blank? I guess you could try enclosing it in square
brackets??
Forms!frmFilterChoice![frmFirstFilter_datasheet].Form....
--
Marsh
MVP [MS Access]

Tried all of the above; results to breaks on each line: the line with the
reference to the subform is the one throwing the error.

Using the code you provided in your message, the following was the result:

?Forms!frmFilterChoice.Name
frmFilterChoice
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
frmFirstFilter_datasheet
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

Run-time error 2455: "You entered an expression that has an invalid
reference ..." (same error as before).

No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.

Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?


bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:
'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False


:
First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Guest]

Marshall,
Thanks for all the help: this last series didn't work.

I changed the name of the subform as I noted earlier and the error is still
being thrown. I ran the code using the Immediate Window with the new
question and the the answer reurned was indeed the correct form, namely,
frmFirstFilter_datasheet (this is the name of the Source for the subform;
I've renamed the subform as subfrmFirstFilter). I then tried the brackets:
no cigar.

The form used as a subform, frmFirstFilter_datasheet, absolutely exists
(opened it). Two things I want to be sure of: the Recordsource property of
the form, frmFirstFilter_datasheet, is empty, while the sourceObject property
of the subform references the form name, frmFirstFilter_datasheet. I also
checked all the events for form and subform object; there are no stray events.

barry

Well at least we've narrowed it down to what we've suspected
all along. Unfortunately, I don't have any new ideas. I
just keep coming back to the name of the subform Control
being out of whack somehow, almost as if it were a text box
or something.

Straw grasping now. Let's try add ing another test to the
debug list:

?Forms!frmFilterChoice!frmFirstFilter_datasheet.SourceObject

If that generates an error, it would confirm that the
control is not a subform control.

If it generates a blank, then there is no form object to
display in the control.

If it displays the name of the form, then double check that
the form actually exists in the database window. The
underscore kind of bothers me, are you sure that it's not a
blank? I guess you could try enclosing it in square
brackets??
Forms!frmFilterChoice![frmFirstFilter_datasheet].Form....
--
Marsh
MVP [MS Access]

Tried all of the above; results to breaks on each line: the line with the
reference to the subform is the one throwing the error.

Using the code you provided in your message, the following was the result:

?Forms!frmFilterChoice.Name
frmFilterChoice
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
frmFirstFilter_datasheet
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

Run-time error 2455: "You entered an expression that has an invalid
reference ..." (same error as before).

No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.


bjnova wrote:
Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?


bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:
'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False


:
First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Marshall Barton]

I'm all out of ideas. Either I've hit a total blind spot
and just can't see what's in front of me, or this is beyond
anything I've ever run into before.

I guess we can always fall back on the usual scapegoat and
blame the unexplained behavior on corruption ....

This is absolutely my last gasp before going down for the
count. Try creating a new blank mdb file, set all the
references the same as you have now, make sure your options
are set properly (especially that the nasty option Name Auto
Correct is turned off) and then import everything from your
existing mdb to the new one.
--
Marsh
MVP [MS Access]

Thanks for all the help: this last series didn't work.

I changed the name of the subform as I noted earlier and the error is still
being thrown. I ran the code using the Immediate Window with the new
question and the the answer reurned was indeed the correct form, namely,
frmFirstFilter_datasheet (this is the name of the Source for the subform;
I've renamed the subform as subfrmFirstFilter). I then tried the brackets:
no cigar.

The form used as a subform, frmFirstFilter_datasheet, absolutely exists
(opened it). Two things I want to be sure of: the Recordsource property of
the form, frmFirstFilter_datasheet, is empty, while the sourceObject property
of the subform references the form name, frmFirstFilter_datasheet. I also
checked all the events for form and subform object; there are no stray events.

Well at least we've narrowed it down to what we've suspected
all along. Unfortunately, I don't have any new ideas. I
just keep coming back to the name of the subform Control
being out of whack somehow, almost as if it were a text box
or something.

Straw grasping now. Let's try add ing another test to the
debug list:

?Forms!frmFilterChoice!frmFirstFilter_datasheet.SourceObject

If that generates an error, it would confirm that the
control is not a subform control.

If it generates a blank, then there is no form object to
display in the control.

If it displays the name of the form, then double check that
the form actually exists in the database window. The
underscore kind of bothers me, are you sure that it's not a
blank? I guess you could try enclosing it in square
brackets??
Forms!frmFilterChoice![frmFirstFilter_datasheet].Form....

Tried all of the above; results to breaks on each line: the line with the
reference to the subform is the one throwing the error.

Using the code you provided in your message, the following was the result:

?Forms!frmFilterChoice.Name
frmFilterChoice
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
frmFirstFilter_datasheet
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

Run-time error 2455: "You entered an expression that has an invalid
reference ..." (same error as before).


:
No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.


bjnova wrote:
Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?


bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:
'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False


:
First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.

 

[Guest]

Since it's the Form property that is causing the problem, I am going to try
opening a new form and setting the record source that way

I'm all out of ideas. Either I've hit a total blind spot
and just can't see what's in front of me, or this is beyond
anything I've ever run into before.

I guess we can always fall back on the usual scapegoat and
blame the unexplained behavior on corruption ....

This is absolutely my last gasp before going down for the
count. Try creating a new blank mdb file, set all the
references the same as you have now, make sure your options
are set properly (especially that the nasty option Name Auto
Correct is turned off) and then import everything from your
existing mdb to the new one.
--
Marsh
MVP [MS Access]

Thanks for all the help: this last series didn't work.

I changed the name of the subform as I noted earlier and the error is still
being thrown. I ran the code using the Immediate Window with the new
question and the the answer reurned was indeed the correct form, namely,
frmFirstFilter_datasheet (this is the name of the Source for the subform;
I've renamed the subform as subfrmFirstFilter). I then tried the brackets:
no cigar.

The form used as a subform, frmFirstFilter_datasheet, absolutely exists
(opened it). Two things I want to be sure of: the Recordsource property of
the form, frmFirstFilter_datasheet, is empty, while the sourceObject property
of the subform references the form name, frmFirstFilter_datasheet. I also
checked all the events for form and subform object; there are no stray events.

Well at least we've narrowed it down to what we've suspected
all along. Unfortunately, I don't have any new ideas. I
just keep coming back to the name of the subform Control
being out of whack somehow, almost as if it were a text box
or something.

Straw grasping now. Let's try add ing another test to the
debug list:

?Forms!frmFilterChoice!frmFirstFilter_datasheet.SourceObject

If that generates an error, it would confirm that the
control is not a subform control.

If it generates a blank, then there is no form object to
display in the control.

If it displays the name of the form, then double check that
the form actually exists in the database window. The
underscore kind of bothers me, are you sure that it's not a
blank? I guess you could try enclosing it in square
brackets??
Forms!frmFilterChoice![frmFirstFilter_datasheet].Form....


bjnova wrote:
Tried all of the above; results to breaks on each line: the line with the
reference to the subform is the one throwing the error.

Using the code you provided in your message, the following was the result:

?Forms!frmFilterChoice.Name
frmFilterChoice
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
frmFirstFilter_datasheet
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

Run-time error 2455: "You entered an expression that has an invalid
reference ..." (same error as before).


:
No. that wouldn't make any difference.

I still don't see anything wrong with that line of code.
Let's see if you can pin things down a little closer. First
thing to try is to place breakpoints on each line to verify
that the error really happens on the line you "think" it
does.

Another thing to try is to look at the individual pieces in
the reference to verify which of its component parts causes
the proplem. When the code breaks on the line in question,
open the Immediate/Debug window (Ctrl+g) and type each of
these lines followed with Enter.
?Forms!frmFilterChoice.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Name
?Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.Name

This might provide a clue if you can see which parts of the
line works and which one fails.


bjnova wrote:
Getting the same error even after copying and pasting name from the
properties dialog box. Could the error arise due tot he fact that the
subform is hidden in the detail section of the mainform while the
recordsource assigment occurs on a button click from the visible header
section?


bjnova wrote:
Can anyone explain why I am getting the following error from the following
code? (the code looks long but it's there to give context; I've marked the
line which I think is causing the error)

error: "You entered an expression that has an invalid reference to the
property Form/Report."

code:
'construct recordsource for subform based on choice made in combobox
'first combobox value being used in filter is written to txtHiddenCourseID
since it is not the bound column of the combobox


strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.dateCompleted,
tblFeedbackCollection.type, tblFeedbackCollection.topic,
tblFeedbackCollection.feedback, tblFeedbackCollection.phase,
tblFeedbackCollection.idComments, tblFeedbackCollection.transientSelect FROM
tblFeedbackCollection WHERE (((Left([screenID],3))='" & [txtHiddenCourseID] &
"') AND ((tblFeedbackCollection.dateSubmitted) Between #" & [dteStartDate] &
"# And #" & [dteEndDate] & "#) AND ((tblFeedbackCollection.status)='" &
[cboStatusFilter] & "') AND ((tblFeedbackCollection.phase)='" &
[cboPhaseFilter] & "'));"

'**** the next line is producing the error, I think
Forms!frmFilterChoice!frmFirstFilter_datasheet.Form.RecordSource = strSQL
Forms!frmFilterChoice!frmFirstFilter_datasheet.Requery

Me.Detail.Visible = True
Me.FormHeader.Visible = False


:
First, get rid of the Requery line. Since assigning
something to the RecordSource property causes an automatic
Requery, it's a waste of time for you do do it again.

As for your error message, I don't see anything wrong. The
error implies that frmFirstFilter_datasheet is not the name
of the subform control displaying the subform. Double check
that it's the name of the control, which might be different
from the name of the (sub)form object.