Access ADP sub-form recordsouce update dilemma

Posted on 2007-08-01
Medium Priority
Last Modified: 2013-12-05
I have a problem with an Access ADP that I am trying to modify. I did not write the app originally and I'm learning as I go.

The problem I'm having is that I need a subform within a form to have a different recordsource depending on whether the record number in the main table is above or below a certain number. The actual event procedure is pasted below.

The code below works perfectly when I go directly to a record. However, the main form also has some forward and backward buttons. So when starting with an older record, I get the old recordsource and all the fields hidden, which is correct for older records. But when advancing to a newer record, the subform never picks up the newer record source and visible fields because the above code only runs on form load.

I need to figure out an event to run the above code in that will make the subform update after advancing to a new record. I've tried a ton of stuff but I can't quite figure it out.

Private Sub Form_Load()
    'changes recordsource of RADiag_subform to new format
    'if RAID is greater than 36354. Also makes new fields visible.
    '(i.e. data linked to table RAItemsDetail.)
    Dim strSQL As String
    strSQL = "SELECT RADiag.RADiagRANumber, RADiag.Tier1, RADiag.Tier2, RADiag.Tier3, RADiag.Tier4, RADiag.Tier5, RADiag.Tier6, RADiag.Tier7, RADiag.Tier8, RADiag.Tier9, RADiag.Tier10, RADiag.Responsible, RADiag.RADiag_RAItemsDetailID, RAItemsDetail.RAItemsSerialNum, RAItemsDetail.RAItemsQuantity, RAItemsDetail.RAItemsPartNum, RADiag.RADiagID, Proteam_dbo_IMA.IMA_ItemName FROM RAItemsDetail INNER JOIN RADiag ON RAItemsDetail.RAItemsID = RADiag.RADiag_RAItemsDetailID INNER JOIN Proteam_dbo_IMA ON RAItemsDetail.RAItemsPartNum = Proteam_dbo_IMA.IMA_ItemID"
    If Parent.RAID > 36354 Then
        Me.RecordSource = strSQL
        lblNewNormalView.Visible = True
        lblLegacyWarning.Visible = False
        lblRAItemsPartNum.Visible = True
        txtRAItemsPartNum.Visible = True
        lblDescription.Visible = True
        txtDescription.Visible = True
        lblSerialNumber.Visible = True
        txtSerialNumber.Visible = True
        lblQuantity.Visible = True
        txtRAItemsQuantity.Visible = True
        Me.AllowAdditions = False
        lblNewNormalView.Visible = False
        lblLegacyWarning.Visible = True
        lblRAItemsPartNum.Visible = False
        txtRAItemsPartNum.Visible = False
        lblDescription.Visible = False
        txtDescription.Visible = False
        lblSerialNumber.Visible = False
        txtSerialNumber.Visible = False
        lblQuantity.Visible = False
        txtRAItemsQuantity.Visible = False
        Me.AllowAdditions = True
    End If
End Sub

Question by:porkerjoe
  • 4
  • 2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 19615572
So the Recordsource changes depending on a value in each record? If that's the case then the Form_Current event would be better in this situation.

Author Comment

ID: 19616474
The recordsouce of the Subform needs to change based on a value in the parent form.

"If Parent.RAID > 36354 Then..."

I'll try Form_Current Event and post back.

Author Comment

ID: 19618906
That works. However, now the form takes about 15 seconds to load. When moving between records with the original criteria, it moves normally between records.

When I move between records that apply the new criteria, it takes about 15 seconds for the new form to load. The data in the fields seem to flicker a little in the subform, as if being repeatedly refreshed.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 19621988
Any ideas why now there is a delay ??
LVL 85
ID: 19624126
What type of database are you using?

Author Comment

ID: 19626088
MS SQL 2000.

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!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

864 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