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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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..
Forms don't store data. Tables store data.
If Me.Dirty = True Then
DoCmd.RunCommand acCmdSaveRecord
End If
Forms don't store data. Tables store data.
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial