Access ADP sub-form recordsouce update dilemma

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

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
porkerjoeAuthor Commented:
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.
porkerjoeAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

porkerjoeAuthor Commented:
Any ideas why now there is a delay ??
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What type of database are you using?
porkerjoeAuthor Commented:
MS SQL 2000.
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.

All Courses

From novice to tech pro — start learning today.