Access ADP sub-form recordsouce update dilemma

Posted on 2007-08-01
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
    LVL 84

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Comment

    Any ideas why now there is a delay ??
    LVL 84
    What type of database are you using?
    LVL 1

    Author Comment

    MS SQL 2000.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    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…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    761 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