[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Access 2003: Quest 2: Datasheets as a subform - to dynamically change data source of subf

Hi EE,

Want to learn how clicking on a column on a datasheet in the header section can
repopulate the datasheet in the detail section.

header section - datasheet - totals
detail   section - datsheet  - show actual rows

would like, if click on a column in totals sheet, would repopulate detail datasheet

i sort of have something working like this:

With Forms!FRM_040_Totals_MAIN.sbf2_Students.Form
     .RecordSource = "qp_050_Discoverer_SortByLNAME"
     
     .Requery
End With

BUT: i have queries using different tables,  so if i change a recordsource to use a query
whose fields are not in the datasheet design view, i get a #NAME error.

so i guess, that i have to create another datasheet for the other query's fields
and dynamically change the FORM name in additon to the data source

i am getting errors trying this below:

i am getting an error that: sbf2_Students  does not exist, yet it does.....

any help or ideas would be deeply appreciated.

the mdb is posted below....tx, sandra
=====================================

With Forms!FRM_040_Totals_MAIN

     With sbf2_Students.Form
     
          .SourceObject = "FRM_040_Totals_sub3_NRMP"
         
     End With

End With


With Forms!FRM_040_Totals_MAIN.sbf2_Students.Form
     
     .RecordSource = "qp_010d_No PGY1"
     
     .Requery
End With
EE-MatchM4-Upload-frontend.mdb
0
mytfein
Asked:
mytfein
  • 5
  • 5
2 Solutions
 
mbizupCommented:
Try this instead:


With Forms!FRM_040_Totals_MAIN

     With sbf2_Students

          .SourceObject = "FRM_040_Totals_sub3_NRMP"
         
     End With

End With
0
 
mytfeinAuthor Commented:
Hi mbizup,


tx so much for writing... still getting error.... posted screen shot below

tx, s
2012-02-23c.bmp
0
 
mytfeinAuthor Commented:
bmp
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mytfeinAuthor Commented:
bmp
0
 
mbizupCommented:
Sorry - this is the correct syntax (note the . before the subform):

With Forms!FRM_040_Totals_MAIN

     With .sbf2_Students
     
          .SourceObject = "FRM_040_Totals_sub3_NRMP"
         
     End With

End With
0
 
mbizupCommented:
You can also reduce it to one line:


         Me.Parent.sbf2_Students.SourceObject = "FRM_040_Totals_sub3_NRMP"
0
 
mytfeinAuthor Commented:
Hi mbizup,

tx so much!  it's working better now....

a) Is Parent a keyword?

b) going with the with syntax:

    is it possible to merge your WITH code and the
                                                 WITH code that i have below( to change record source)
     into one with WITH statement?


With Forms!FRM_040_Totals_MAIN

     With .sbf2_Students  'not the dot before subform name
     
          .SourceObject = "FRM_040_Totals_sub3_NRMP"
         
     End With
     
End With




With Forms!FRM_040_Totals_MAIN.sbf2_Students.Form
     
     .RecordSource = "qp_010d_No PGY1"
     
     .Requery
End With
0
 
mbizupCommented:
Hi -

a) Is Parent a keyword?

Yes.  Since your code is running from a subform, you can use Parent to refer to the main form.  So to refer to a 'sibling form' the syntax is:

Me.Parent.OtherChild
0
 
mbizupCommented:
Hmm..

This didnt get posted for some reason.

You can consolidate the two blocks like this:



With Forms!FRM_040_Totals_MAIN

     With .sbf2_Students  'not the dot before subform name
     
          .SourceObject = "FRM_040_Totals_sub3_NRMP"    
           .Form.RecordSource = "qp_010d_No PGY1"
         .Form.Requery
    End With
End With
0
 
mytfeinAuthor Commented:
tx so much, mbizup!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now