Avatar of Neil Udovich
Neil UdovichFlag for United States of America

asked on 

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 AccessVBA

Avatar of undefined
Last Comment
Neil Udovich
Avatar of PatHartman
PatHartman
Flag of United States of America image

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.
Avatar of Neil Udovich
Neil Udovich
Flag of United States of America image

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
Avatar of PatHartman
PatHartman
Flag of United States of America image

Blurred text
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
can you give us a screenshot
Avatar of Neil Udovich
Neil Udovich
Flag of United States of America image

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.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo