Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2012-03-21
10
Medium Priority
?
213 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:mytfein
ID: 37749452
bmp
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

606 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