VBA sequence of events in Access Form

220-221
220-221 used Ask the Experts™
on
I have an interactive access form.   User puts in a number which updates the overall market share.  The market share then searches a table to report back a number based on that market share.  

Everything’s fine except the Search of that table relies on the MS to be updated on the form in a timely manner.  If not, the form reports back invalid use of null because the MS number it’s referencing is blank.

I’ve tried a lot of things in the VBA to get things to pause to give the field time to refresh.  Sometimes I can get it so it works once but the next time it errors out.  

I don’t know what I’m asking except maybe to see if there is a way to ensure the MS calculated field on the form is “done” before it does the search.   I’ve tried every iteration of refresh, requery etc.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
When you want to save a record, save it.  Don't rely on other methods that have a different function to preform and as a side effect save the current record..

If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Open in new window


Forms don't store data.  Tables store data.

Author

Commented:
I appreciate that.
I have the user input number saving to the table (DDoses)
The marketshare  (DMS) is dynamic and is a function of that number - as changed on the form and saved to the table and the total units as summed on the footer of the form. (DTDoses)
DMS = DDoses / DTDoses

When we get to Varx2 and it's looking for DMS - which is to be the updated marketshare - presumably that is erroring out because in the interim the form is showing null for DMS. ....sometimes

I've tried to incorporate your suggested code perhaps in the wrong place.

xFoos = Me!Foos
xDMS = Me!DMS
XDDoses = Me!DDoses
xDMS2 = Me!DDoses
yDDose = Me.DDoses


If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If

DoCmd.RunSQL "Update PST1 Set PST1.DDoses = " & yDDose & " Where Foo='" & Foos & "'"

Forms!PST![PST1 Subform].Form.Requery

If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If

varx2 = DLookup("[Rebate]", "[Rebates]", "[Foo]='" & xFoos & "' and (" & xDMS & ") Between [MSH] And [MSL] And (" & XDDoses & ") Between [VH] And [VL]")

DoCmd.RunSQL "Update PST1 Set PST1.NR1 = " & varx2 & " Where Foo='" & Foos & "'"

Forms!PST![PST1 Subform].Form.Requery
Distinguished Expert 2017
Commented:
Isn't the calculated field in the table the form is bound to?  If it is, the calculation should be in the Form's BeforeUpdate event AND it should ensure that both DDoses and DTDoses have valid values.  Then when you save the record, the BeforeUpdate event runs and you can cancel the save if both values are not present.
If Me.DTDoses & "" = "" Then
    Me.DTDoses.SetFocus
    Cancel = True
    MsgBox "DTDoses is required.",vbOKOnly
    Exit Sub
End If
If Me.DDoses & "" = "" Then
    Me.DDoses.SetFocus
    Cancel = True
    MsgBox "DDoses is required.",vbOKOnly
    Exit Sub
End If

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
can you give us a screenshot

Author

Commented:
I incorporated Pat's comments which were a portion of the solution.  Part of it was on my end too in that the table that I was using to lookup marketshare was wrong.  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial