Solved

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

Posted on 2012-03-21
10
198 Views
Last Modified: 2012-03-21
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
Comment
Question by:mytfein
  • 5
  • 5
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37749391
Try this instead:


With Forms!FRM_040_Totals_MAIN

     With sbf2_Students

          .SourceObject = "FRM_040_Totals_sub3_NRMP"
         
     End With

End With
0
 

Author Comment

by:mytfein
ID: 37749438
Hi mbizup,


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

tx, s
2012-02-23c.bmp
0
 

Author Comment

by:mytfein
ID: 37749443
bmp
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:mytfein
ID: 37749452
bmp
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37749470
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 37749489
You can also reduce it to one line:


         Me.Parent.sbf2_Students.SourceObject = "FRM_040_Totals_sub3_NRMP"
0
 

Author Comment

by:mytfein
ID: 37749591
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37749672
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37749813
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
 

Author Comment

by:mytfein
ID: 37749897
tx so much, mbizup!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question