Opening form in datasheet view

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to open a form in hidden mode, assign the record source
programmatically, and make the form visible but in _datasheet view. Access
will not open the form in datasheet view (I get a "You can't assign a value
to this object." error). The Default View and Views Allowed properties of
the form I'm trying to open are both set to Datasheet; but the form will only
open in Single Form view. (I have tried opening the form as a subform but am
using this code now because Access refuses to accept the code
Forms!frmFilterChoice!frmSecondFilter.Form.RecordSource = strSQL)

My code:

strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.startDate,
tblFeedbackCollection.endDate, tblFeedbackCollection.type,
tblFeedbackCollection.topic, tblFeedbackCollection.feedback,
tblFeedbackCollection.phase, tblFeedbackCollection.idtComments,
tblFeedbackCollection.transientSelect FROM tblFeedbackCollection WHERE
(((Left([screenID],3))='" & strCourseID & "') AND
((tblFeedbackCollection.dateSubmitted) Between #" & datStartDate & "# And #"
& datEndDate & "#) AND ((tblFeedbackCollection.status)='" & strStatus & "')
AND ((tblFeedbackCollection.phase)='" & strPhase & "'));"

'open form to be filtered
DoCmd.OpenForm "frmSecondFilter", acNormal, , , acFormPropertySettings,
acHidden

'assign recordsource
Forms!frmSecondFilter.RecordSource = strSQL

'make sure view is datasheet
'Forms!frmSecondFilter.ViewsAllowed = datasheet
'make form visible
Forms!frmSecondFilter.Visible = True
 
bjnova,
You can not set the views allowed property at run-time, so just delete that
statement, as you won't need it.
If your parent form's Default View and Views Allowed properties are set to
"Single Form" and "Form", respectively, and the subform is set to "Datasheet"
and "Both", respectively, everything should look fine when you run your code
(as long as you get rid of that line that causes the error) and you can
toggle the Subform from the menu if you like.
 
Thanks SFAxess. The line that is throwing the error has proven
insurmountable (one of the MVPs one here looked at it and exchanged with me
at length). I am forced now to open the form not as a subform but as a form
itself in datasheet view. One bizarre thing I noticed: when I open the form
initially, it opens in Single Form view; if I switch to Design view and then
back to Form view, the form appears in Datasheet view. If I try to do that
programmatically (switch back and forth between views), the form only appears
in form view.

SFAxess said:
bjnova,
You can not set the views allowed property at run-time, so just delete that
statement, as you won't need it.
If your parent form's Default View and Views Allowed properties are set to
"Single Form" and "Form", respectively, and the subform is set to "Datasheet"
and "Both", respectively, everything should look fine when you run your code
(as long as you get rid of that line that causes the error) and you can
toggle the Subform from the menu if you like.

bjnova said:
I am trying to open a form in hidden mode, assign the record source
programmatically, and make the form visible but in _datasheet view. Access
will not open the form in datasheet view (I get a "You can't assign a value
to this object." error). The Default View and Views Allowed properties of
the form I'm trying to open are both set to Datasheet; but the form will only
open in Single Form view. (I have tried opening the form as a subform but am
using this code now because Access refuses to accept the code
Forms!frmFilterChoice!frmSecondFilter.Form.RecordSource = strSQL)

My code:

strSQL = "SELECT tblFeedbackCollection.idFeedbackNumber, Left([screenID],3)
AS FirstThree, tblFeedbackCollection.screenID,
tblFeedbackCollection.dateSubmitted, tblFeedbackCollection.teamMember,
tblFeedbackCollection.status, tblFeedbackCollection.startDate,
tblFeedbackCollection.endDate, tblFeedbackCollection.type,
tblFeedbackCollection.topic, tblFeedbackCollection.feedback,
tblFeedbackCollection.phase, tblFeedbackCollection.idtComments,
tblFeedbackCollection.transientSelect FROM tblFeedbackCollection WHERE
(((Left([screenID],3))='" & strCourseID & "') AND
((tblFeedbackCollection.dateSubmitted) Between #" & datStartDate & "# And #"
& datEndDate & "#) AND ((tblFeedbackCollection.status)='" & strStatus & "')
AND ((tblFeedbackCollection.phase)='" & strPhase & "'));"

'open form to be filtered
DoCmd.OpenForm "frmSecondFilter", acNormal, , , acFormPropertySettings,
acHidden

'assign recordsource
Forms!frmSecondFilter.RecordSource = strSQL

'make sure view is datasheet
'Forms!frmSecondFilter.ViewsAllowed = datasheet
'make form visible
Forms!frmSecondFilter.Visible = True
Click to expand...
Click to expand...
 
Back
Top