Solved

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

Posted on 2012-03-21
10
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 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