asked on 5/19/2018

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.

Microsoft Access VBA

Last Comment

Neil Udovich

5/24/2018

PatHartman

5/19/2018

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.

Neil Udovich

5/19/2018

ASKER

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

ASKER CERTIFIED SOLUTION

PatHartman

5/20/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

John Tsioumpris

5/20/2018

can you give us a screenshot

Neil Udovich

5/24/2018

ASKER

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.