Full Version: Save Field Error When Trying To Correct A Wrong Date Entered
UtterAccess Forums > Microsoft® Access > Access Forms
rmc123
The following Code determines if the date selected in a field is a Saturday for each week for up to three weeks, therefore, Me.Text101 is February 7th, Me.Text102 is February 14th, and Me.Text103 is February 21st. If the Appt Setter tries to set an appt on a Saturday, the msgbox will put up. Of course the ApptSetter will have advanced to the combo box next to the date and begin to set the Appt Time when the MsgBox pops up. This code is in the BeforeUpdate event. When I run the code without the RunCommand acCmdSave line, the error msg tells me that I need to save the field before the Setfocus line can run, but I do not know how to save a field. I need the ApptDate field to clear, and then the focus go back to the Appt Date field so that the Appt Setter can re-enter the correct Appt Date. With the following code I get the attached error msg. What do I do to correct this problem to get the msgbox to popup, the ApptDate field to clear, and then the focus to go back to the ApptDate field once the ApptSetter has advanced to the next combo box so that the ApptSetter can re-enter the correct ApptDate?

If (Me.ApptDate = Me.Text101 Or Me.ApptDate = Me.Text102 Or Me.ApptDate = Me.Text103) Then
MsgBox "You can't schedule appointments on Saturdays! Please choose another day.", vbOKOnly, "Day of the Week Error"
Me.ApptDate = ""
RunCommand acCmdSave
Me.ApptDate.SetFocus
End If

Your help is greatly appreciated!
Rose Mary
theDBguy
Hi. Which event are you using to call that code? You should try using the control's BeforeUpdate event.
rmc123
Hi theDBguy:

The code is in the Before Update event.

Rose Mary
theDBguy
That's good. Then, just make sure you "cancel" the event by using Cancel=True.
rmc123
Hi theDBguy:

Where does this go in the code? I still get the same error msg when I put it after the msgbox.

Thanks so much!
Rose Mary
theDBguy
Please copy and paste the entire code including the function stub, i.e. Private Sub...
rmc123
Private Sub ApptDate_BeforeUpdate(Cancel As Integer)
'If Me.Parent.CurrentDrID = 14 Then
If (Me.ApptDate = Me.Text101 Or Me.ApptDate = Me.Text102 Or Me.ApptDate = Me.Text103) Then
MsgBox "You can't schedule appointments on Saturdays! Please choose another day.", vbOKOnly, "Day of the Week Error" 'Add If Statement for NO SATURDAYS and then the rest of the appt criteria for Dr J.
'RunCommand acCmdSave A save form box pops up and I do not want this to occur
Cancel = True
Me.ApptDate = ""
Me.ApptDate.SetFocus
End If
'End If
End Sub

Rose Mary
theDBguy
Thanks. Try simplifying it to this:

Private Sub ApptDate_BeforeUpdate(Cancel As Integer)
If (Me.ApptDate = Me.Text101 Or Me.ApptDate = Me.Text102 Or Me.ApptDate = Me.Text103) Then
MsgBox "You can't schedule appointments on Saturdays! Please choose another day.", vbOKOnly, "Day of the Week Error" 'Add If Statement for NO SATURDAYS and then the rest of the appt criteria for Dr J.
Cancel = True
Me.ApptDate.Undo
End If
End Sub
GroverParkGeorge
I assume this is in the BeforeUpdate event of the field, n?

You are stopping the code on the error (good) before the control can update, but then trying to change it again and save it, which fires the same event again, and you are stuck in that loop.

What you need to do is Cancel the update and let the user reset the field. That's actually shown in the Header of the Sub, which didn't make it into the code you posted. I've highlighted it in red so it can be spotted.

Private Sub cboYourComboBoxNameGoesHere_BeforeUpdate(Cancel As Integer)
If (Me.ApptDate = Me.Text101 Or Me.ApptDate = Me.Text102 Or Me.ApptDate = Me.Text103) Then
Cancel = True
MsgBox "You can't schedule appointments on Saturdays! Please choose another day.", vbOKOnly, "Day of the Week Error"
Me.ApptDate = ""
' RunCommand acCmdSave -- You can't save here because you are already in an Update event that hasn't finished.
'Me.ApptDate.SetFocus -- you can't move focus until the error raised in this event is resolved
End If
End Sub

What I would suggest, is to tell the user which control(s) are not valid and let them correct them.

Give me a few minutes to put together an alternate sub for that purpose.
GroverParkGeorge
One more comment. I have occasionally found that Users prefer to see the previous, incorrect value in the control so they don't have to wonder what they put in there. It depends.
rmc123
Hi the DBGuy and GroverParkGeorge:

The DBguy: I still get the same error and it occurs at ApptDate = Null. Thank you for trying.

GroverParkGeorge: I understand a little of what you are trying to explain, but am not an expert. I would appreciate your help.

Kind regards,
Rose Mary
rmc123
Hi GroverParkGeorge:

The msgbox tells them that they scheduled the appt on a Saturday and there can't be any Saturday appts.

What I was suggesting that you add a bit to the message telling them which of the three text boxes caused the error, Text101 , Text102 or Text103, but if that's not useful additional information, it doesn't matter.


Rose Mary
theDBguy
Hi Rose Mary,

Sorry. I updated the code after I posted it. See if the update works any better for you. Hint: it's using Undo instead.
GroverParkGeorge
Correct, once you're in the Update event, you can't change the value in the control being updated until the first update is complete. My bad. I missed that.

Note, again, that I sometimes use .Undo and sometimes not, depending on whether the user wants to see their previous, incorrect entry to avoid repeating it.
rmc123
BRAVO! It worked beautifully. Something so simple too. Thank you so much!

Rose Mary
theDBguy
Hi Rose Mary. You're welcome! George and I were happy to assist. Good luck with your project.
rmc123
Hi theDBguy and George:

I had to leave right after my last post and am just now getting back and want to thank you both for your help. I read all your comments and with George's explanations, I understand a little better why I received the error. Thank you both so much!

Rose Mary
dmhzx
An extra thought at no charge ( hat_tip.gif )
As I read this all three dates are one week apart.
So either all three are right , or all three are wrong.
Therefore in the after update event of the first, the other 2 can be set simply with First +7 and First + 14.

Secondly, it really will make your life easier in future if you give your controls meaningful names as they are created.
When in the VBA window, scrolling down to txtApt1, is so much easier than txt137.

cmdValidate is easier than cmd234
rmc123
Hi dmhzx:

Thank you very much for the advice. I have made those corrections. That will greatly reduce the number of textboxes I am using in the form, plus I can increase the number of weeks if I need to.

Rose Mary
dmhzx
Pleased to help:
Hope it all goes smoothly from here on
thumbup.gif
rmc123
Thanks!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.