Before/afterUpdates :: Expensive on Memory?

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

Do you get a big performance hit when you utilize
before and afterupdates in a FORM....?

I am finding that it takes a few seconds for a user to scroll to a new user
when he clicks the scroll button....

Appreciate advice
Jason
 
jason said:
Do you get a big performance hit when you utilize
before and afterupdates in a FORM....?

I am finding that it takes a few seconds for a user to scroll to a new user
when he clicks the scroll button....
Click to expand...

That would not trigger the Before and After Update events (unless he had made
changes), but it would fire the Current event once per record.

It really isn't a matter of memory usage for an event. What matters is what the
code is doing. If the Current event is doing a lot of stuff then a pause as you
navigate is to be expected. Requiring a lot of lookups and sources for
ListBoxes and ComboBoxes for example.
 
Rick, I am new to VBA and managed to create a user-friendly form which
conditionally presents the user with form control items depending on choices
in an eg: combo box.

But, I am not quite sure what parts of the code are causing the delay - you
say Current Event.... could I post some of the key subs and you briefly scan
for a problem area.....there is not a lot - I just need a quick eyeball on
where I am getting this problem...

- Jason
 
jason said:
Rick, I am new to VBA and managed to create a user-friendly form which
conditionally presents the user with form control items depending on choices
in an eg: combo box.

But, I am not quite sure what parts of the code are causing the delay - you
say Current Event.... could I post some of the key subs and you briefly scan
for a problem area.....there is not a lot - I just need a quick eyeball on
where I am getting this problem...
Click to expand...

If you see slow transitions from one record to the next when you are not making
edits, the Current event is the only one I can think of that would be running.
Do you have code in the Current event? If so, does the form navigate faster
when you temporarily disable that code? Do you have any sub-forms? These get
requeried for each record change in the main form and could also be causing the
delay.
 
Hi Rick,

I am simultaenousuly updating my base table as well as a secondary "AUDIT"
table which allows me to track edits. This useful piece of vba code and
underlying audit table was provided by:
Allen Browne at bigpond.net.au, which is essentially an Audit trail, to
track Deletes, Edits, and Inserts.

Most of my actions are tied into the Before_Update events of the control eg:
combo_box but also CALLED from the Sub Form CURRENT event to complete the
exercise and ensure universal application of my conditions. I understand
that you say this could be the problem area - but what choice do I have as
it appears to be the only way to force my conditions to come true.

For instance, if a user selects an item in a combo box then make VISIBLE
another item etc.

I mean, I cannot believe that simple conditions like this could cause the
FORM scrolling (record to record) to slow down so much. I realise I am
doing double updates in my underlying tables but surely Access is strong
enough to handle this...

I know you are busy but here is the basic code....can you give any advice to
increase speed - is it actually possible?

- Jason



Dim bWasNewRecord As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpListings", "audListings", Status)
End Sub

Private Sub Form_AfterUpdate()
Call AuditEditEnd("tblListings", "audTmpListings", "audListings",
"ListingsID", Nz(Me!ListingsID, 0), bWasNewRecord)
Model.SetFocus
record_alert = "Administrator has UPDATED the following record " &
Model.Value
'//Call SendMessage(record_alert)
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
bWasNewRecord = Me.NewRecord
End Sub

Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
Call Status_AfterUpdate
End Sub





Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("tblListings", "audTmpListings", "ListingsID",
Nz(Me.ListingsID, 0))
End Sub







Private Sub Status_Price_Reduced_AfterUpdate()
On Error GoTo Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
'//MsgBox (bShow)

Price_Reduction_frame.Visible = bShow
'//Current_Price.Visible = bShow

If bShow = False Then
With Me.Current_Price
.Value = Null
End With
End If
Current_Price.Visible = bShow

Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[ListingID].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub



Private Sub Status_AfterUpdate()
On Error GoTo Err_Handler


Me.Status.SetFocus

If Me.Status.Text = "Sold" Then

Closing_Date.Visible = True
Closing_Details_Frame.Visible = True

Else
Closing_Date.Visible = False
Closing_Details_Frame.Visible = False

End If

If Me.Status.Text = "Vessel Under Offer" Then

Selling_Broker_VUO_ID.Visible = True

Selling_Broker_VUO_Question.Visible = True

Else

'Me.Selling_Broker_VUO_ID.Text = "None"

With Me.Selling_Broker_VUO_ID
.Value = Null
.Visible = False
End With
Me.Selling_Broker_VUO_ID.Visible = False
Me.Selling_Broker_VUO_Question.Visible = False


End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[ListingID].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub



Private Sub Year_BeforeUpdate(Cancel As Integer)
If Len(Me.Year) <> 4 Then
MsgBox "You must enter a four-digit year.", vbInformation,
"Enter 4-Digit Year"
Cancel = True
End If
End Sub
 
I fail to see why you need to call the AfterUpdate events in your Current
event also.
Doesn't this create an audit entry as you navigate even though the records are not
being changed?
Click to expand...

Hi Rick,

Let me be more specific. The audit trail is actually controlled by the
following form events

'--------------------- * audit *---------------------------------
Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("tblListings", "audTmpListings", "ListingsID",
Nz(Me.ListingsID, 0))
End Sub
etc
Private Sub Form_AfterDelConfirm(Status As Integer)
etc
Private Sub Form_AfterUpdate()
etc
Private Sub Form_BeforeUpdate(Cancel As Integer)


'--------------- * FORM CURRENT conditionals * -------------------
But, my conditional control of form values is controlled here (see below
example). It may be that I am duplicating the same action
but I cannot be sure as I was aided in getting these conditions set up by
other VBA Developers. Is my scenario clearer now - I am not sure
if I can elminate any of the following class


Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
Call Status_AfterUpdate
End Sub


'--------------- * CONTROL conditionals - also called
above* -------------------

Private Sub Status_AfterUpdate()
On Error GoTo Err_Handler


Me.Status.SetFocus

If Me.Status.Text = "Sold" Then

Closing_Date.Visible = True
Closing_Details_Frame.Visible = True

Else
Closing_Date.Visible = False
Closing_Details_Frame.Visible = False

End If

If Me.Status.Text = "Vessel Under Offer" Then

Selling_Broker_VUO_ID.Visible = True

Selling_Broker_VUO_Question.Visible = True

Else

'Me.Selling_Broker_VUO_ID.Text = "None"

With Me.Selling_Broker_VUO_ID
.Value = Null
.Visible = False
End With
Me.Selling_Broker_VUO_ID.Visible = False
Me.Selling_Broker_VUO_Question.Visible = False


End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[ListingID].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub
 
jason said:
I fail to see why you need to call the AfterUpdate events in your Current event also.
Doesn't this create an audit entry as you navigate even though the records are not
being changed?
Click to expand...

Hi Rick,

Let me be more specific. The audit trail is actually controlled by the
following form events

'--------------------- * audit *---------------------------------
Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("tblListings", "audTmpListings", "ListingsID",
Nz(Me.ListingsID, 0))
End Sub
etc
Private Sub Form_AfterDelConfirm(Status As Integer)
etc
Private Sub Form_AfterUpdate()
etc
Private Sub Form_BeforeUpdate(Cancel As Integer)


'--------------- * FORM CURRENT conditionals * -------------------
But, my conditional control of form values is controlled here (see below
example). It may be that I am duplicating the same action
but I cannot be sure as I was aided in getting these conditions set up by
other VBA Developers. Is my scenario clearer now - I am not sure
if I can elminate any of the following class


Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
Call Status_AfterUpdate
End Sub


'--------------- * CONTROL conditionals - also called
above* -------------------

Private Sub Status_AfterUpdate()
On Error GoTo Err_Handler


Me.Status.SetFocus

If Me.Status.Text = "Sold" Then

Closing_Date.Visible = True
Closing_Details_Frame.Visible = True

Else
Closing_Date.Visible = False
Closing_Details_Frame.Visible = False

End If

If Me.Status.Text = "Vessel Under Offer" Then

Selling_Broker_VUO_ID.Visible = True

Selling_Broker_VUO_Question.Visible = True

Else

'Me.Selling_Broker_VUO_ID.Text = "None"

With Me.Selling_Broker_VUO_ID
.Value = Null
.Visible = False
End With
Me.Selling_Broker_VUO_ID.Visible = False
Me.Selling_Broker_VUO_Question.Visible = False


End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[ListingID].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub
Click to expand...

It seems to me that perhaps in an effort to avoid duplication someone went a bit too
far. I can see why your current event needs to duplicate the lines of code in
Status_AfterUpdate that involve *display* attributes (hiding/showing various
controls), but it seems incorrect to me to be having the form change *data* every
time you navigate.

Since the code in Status_AfterUpdate is not that long I would stop calling it from
the Current event and just duplicate the lines necessary for the proper showing and
hiding. Otherwise you are not only dirtying and saving the record every time you
navigate, but I would think that this is causing non-changes to be written to your
audit table.
 
Back
Top