VBA sequence of events in Access Form

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.
220-221Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
220-221Author 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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John TsioumprisSoftware & Systems EngineerCommented:
can you give us a screenshot
220-221Author 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.