Solved

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

Posted on 2012-03-21
10
193 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
Comment Utility
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
Comment Utility
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
Comment Utility
bmp
0
 

Author Comment

by:mytfein
Comment Utility
bmp
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
Comment Utility
You can also reduce it to one line:


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

Author Comment

by:mytfein
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
tx so much, mbizup!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now