Opening a Single Form directly to a specific record in the recordsource
I’m working on a form in a legacy Access 2013 application. The form can be used to add a new record or update an existing record.
The recordsource of the form is ‘tblInstallPay_Main’
When adding a new record the form is opened
DoCmd.OpenForm "frmInstallPay_Main", , , , acFormAdd
Currently when opening to an existing record, the form is currently opened passing the ID as OPenArgs
The logic in the form load event looks like this:
'
If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[ID] = " & OpenArgs
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
'End If
I am trying to speed up the opening of the form opening with a Where Clause and commenting the filtering logic.
I commented the filtering logic and used the following to open the form.
DoCmd.OpenForm "frmInstallPay_Main", , , "[ID] = " & gCurrInstallPlanID
It works but when I trace the events triggered on the form prior to showing the selected record I see the form ‘Current’ event is triggered 4 times. The first two times the ‘NewRecord’ logic is triggered. The next two times the ‘existing record’ logic is triggered. This seems very inefficient but I don’t understand why those events are being triggered.
This is the logic In the Form Current event
This is the logic in the Form_Load Event
Can anyone spot what is causing the 'Form_Current' event to execute 4 times?
How can I make the opening of the form to a specific record more efficient?
The recordsource of the form is ‘tblInstallPay_Main’
When adding a new record the form is opened
DoCmd.OpenForm "frmInstallPay_Main", , , , acFormAdd
Currently when opening to an existing record, the form is currently opened passing the ID as OPenArgs
The logic in the form load event looks like this:
'
If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[ID] = " & OpenArgs
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
'End If
I am trying to speed up the opening of the form opening with a Where Clause and commenting the filtering logic.
I commented the filtering logic and used the following to open the form.
DoCmd.OpenForm "frmInstallPay_Main", , , "[ID] = " & gCurrInstallPlanID
It works but when I trace the events triggered on the form prior to showing the selected record I see the form ‘Current’ event is triggered 4 times. The first two times the ‘NewRecord’ logic is triggered. The next two times the ‘existing record’ logic is triggered. This seems very inefficient but I don’t understand why those events are being triggered.
This is the logic In the Form Current event
Private Sub Form_Current()
'
If Me.NewRecord Then
'
prevPlanStatusID = 0
prevPlanStatusStr = ""
'
thisIsAnewPlan = True
Me.AccountID.DefaultValue = gCurrAccountID
Me.PayPlanTypeID.DefaultValue = eInstallPlanType.eRegular
Me.StatusOfPlanID.DefaultValue = eInstallPlanStep.eProposed
'
Me.btnPrintAll.Visible = False
Me.btnDelete.Visible = False
'
' balance due cannot be calculated until the start date and number of months is entered
'
Else
'
prevPlanStatusID = Nz(Me.StatusOfPlanID, 0)
prevPlanStatusStr = Me.StatusOfPlanID.Column(1)
'
If Nz(Me.StatusOfPlanID, 0) = eInstallPlanStep.eProposed Then
btnGenerateInstallPlan.Visible = True
btnRefreshAssociatedPayments.Visible = False
Else
btnGenerateInstallPlan.Visible = False
btnRefreshAssociatedPayments.Visible = True
End If
'
thisIsAnewPlan = False
'Me.btnGenerateInstallPlan.Visible = False commented when we gave the ablity to regenerate the plan
Me.btnPrintAll.Visible = True
Me.btnDelete.Visible = True
'
End If
'
' 11/16/14 Certain users can alter the date established on any plan at any time.
'
'
If UserIsAllowedToDoThis(eSecurityEvent.ePayPlanAlterDateEstablished) Then
'
Me.DateEstablished.Locked = False
'
End If
'
Me.PeriodInMonths.SetFocus
'
End SubThis is the logic in the Form_Load Event
Private Sub Form_Load()
'
'gSaveInstallPlanIDForReOpen = 0
Me.Caption = "Installment Plan Detail" '/// Set for every form //////'
Me.lblHeader.Caption = Me.Caption
'
gCurrUserName = Trim(Nz(GimmeUserName, "unknown"))
'
wkTable = "tblInstallPay_Main"
'
Me.RecordSource = "Select * from " & wkTable
'
' Use this to position
'
'If Not IsNull(Me.OpenArgs) Then
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[AutoNumID] = " & OpenArgs
' Me.Bookmark = rs.Bookmark
'End If
'
' Use this to filter /// NOt anymore, form i s opened with 'Where clause' 02/15/19
'
'If Not IsNull(Me.OpenArgs) Then
' Me.Filter = "[ID] = " & OpenArgs
Me.FilterOn = True
'Else
'' Me.Filter = ""
' Me.FilterOn = False
'End If
'
generateButtonHasBeenPressed = False
'
Update_Form_Controls Me
'
' Added 01/04/16 Per Mandi
'
Me.AccountID.Locked = True
'
If UserIsAllowedToDoThis(eSecurityEvent.ePayPlanEdit) Then
'
Else
'
Me.btnExitNoSave.SetFocus
Lock_All_Form_Controls Me
'
End If
'
If UserIsAllowedToDoThis(eSecurityEvent.ePayPlanDelete) Then
'
Me.btnDelete.Visible = True
Me.btnDelete.Enabled = True
'
Else
'
Me.btnDelete.Visible = False
'
End If
End SubCan anyone spot what is causing the 'Form_Current' event to execute 4 times?
How can I make the opening of the form to a specific record more efficient?
You have a lot of unnecessary code. All you need to do is to use a where argument in the Openform method. If you don't know the id before you open the form, then, you can add a combo or a textbox to the form itself. The selection criteria would reference the combo or textbox. When the form opens, the textbos or combo would be empty so no record would be found. That would cause the form to open ready to add a record. After the form is open, the user can enter the value in the textbox or combo and using either a "find" button or the AfterUpdate event of the textbox or combo, requery the form. This will cause the form's RecordSource query to run using the specified criteria and find the record.
This method uses the same technique for opening the form whether you are adding or updating so you would never use the acFormAdd argument. This method uses a single line of code:
Me.Requery
plaaced in either of the events I mentioned, plus a query with selection criteria.
You can't get more simple than that.
Just FYI, I never use form filters because most of my apps use SQL Server as the BE rather than Jet/ACE and using form filters completely eliminates any benefit you might derive from using a server based database engine because the filtering method causes Access to request ALL rows from the bound table or query so that filtering can be done locally whereas when you use a query with criteria, the criteria causes the server to select ONLY the requested records so instead of downloading thousands or hundreds of thousands of rows, you download only one. When the BE is Jet/ACE the behind the scenes process is somewhat different due to the close integration of Access with Jet/ACE and the filter technique is no where near as inefficient with Jet/ACE as it is with a "big" RDBMS.
This method uses the same technique for opening the form whether you are adding or updating so you would never use the acFormAdd argument. This method uses a single line of code:
Me.Requery
plaaced in either of the events I mentioned, plus a query with selection criteria.
You can't get more simple than that.
Just FYI, I never use form filters because most of my apps use SQL Server as the BE rather than Jet/ACE and using form filters completely eliminates any benefit you might derive from using a server based database engine because the filtering method causes Access to request ALL rows from the bound table or query so that filtering can be done locally whereas when you use a query with criteria, the criteria causes the server to select ONLY the requested records so instead of downloading thousands or hundreds of thousands of rows, you download only one. When the BE is Jet/ACE the behind the scenes process is somewhat different due to the close integration of Access with Jet/ACE and the filter technique is no where near as inefficient with Jet/ACE as it is with a "big" RDBMS.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the input. I prefer a bound form. I commented (''''''''''''') what I thought was all of the extraneous code in my Load event.
Everything else stayed the same.
The revised Load event looked like this:
The result was that when the form opened the 'New Record' path in the Current event was triggered once. The form opened in add mode.
When I uncommented the line
The new record logic in the Current event was triggered twice and the form opened in Add mode.
When I then uncommented the line
The newrecord logic in 'Current' event was triggered once, then the Existing record logic in the current event triggered 3 times and the form opened the the exact record it should have.
I'm obviously missing something obvious here but I don't know what it is.
The recordsource of the form is 'tblInstallPay_Main'
''''''''''Me.RecordSource = "Select * from " & wkTable
'
'''''''''Me.FilterOn = TrueEverything else stayed the same.
The revised Load event looked like this:
Private Sub Form_Load()
'
'gSaveInstallPlanIDForReOpen = 0
Me.Caption = "Installment Plan Detail" '/// Set for every form //////'
Me.lblHeader.Caption = Me.Caption
'
gCurrUserName = Trim(Nz(GimmeUserName, "unknown"))
'
wkTable = "tblInstallPay_Main"
'
''''''''''Me.RecordSource = "Select * from " & wkTable
'
'''''''''Me.FilterOn = True
'
generateButtonHasBeenPressed = False
'
Update_Form_Controls Me
'
' Added 01/04/16 Per Mandi
'
Me.AccountID.Locked = True
'
If UserIsAllowedToDoThis(eSecurityEvent.ePayPlanEdit) Then
'
Else
'
Me.btnExitNoSave.SetFocus
Lock_All_Form_Controls Me
'
End If
'
If UserIsAllowedToDoThis(eSecurityEvent.ePayPlanDelete) Then
'
Me.btnDelete.Visible = True
Me.btnDelete.Enabled = True
'
Else
'
Me.btnDelete.Visible = False
'
End If
End SubThe result was that when the form opened the 'New Record' path in the Current event was triggered once. The form opened in add mode.
When I uncommented the line
Me.RecordSource = "Select * from " & wkTableThe new record logic in the Current event was triggered twice and the form opened in Add mode.
When I then uncommented the line
Me.FilterOn = TrueThe newrecord logic in 'Current' event was triggered once, then the Existing record logic in the current event triggered 3 times and the form opened the the exact record it should have.
I'm obviously missing something obvious here but I don't know what it is.
The recordsource of the form is 'tblInstallPay_Main'
After the Set Recordsource it becomes bound.
ASKER
Thanks You
In the Form_Load try moving the FilterOn section in lines 22-30 to be just before the setting of the RecordSource at line 12.
That’ll likely cut it down the On_Current event occurrence by half.