Sanity Check

[T_Access]

Hi There

This is the first time ever posting a question on a forum, so wish me luck.

Iam on the finishing touches of an access database and am running into an issue regarding a close button on my form. The code is meant to check the from for null criteria and if there is a null field, then give message and return user to form to complete missing information. Otherwise save and close form.

Here it is:


Private Sub TTL_Exit_Click()
On Error GoTo TTL_Exit_Err



If (IsNull([Invoice Complete Date])) Then
Beep
MsgBox "Incomplete Form: Invoice COMPLETE DATE Information Missing", vbExclamation, "Incomplete Form: Invoice Complete Date Information Missing"
DoCmd.CancelEvent
ElseIf (IsNull([Invoice #])) Then
Beep
MsgBox "Incomplete Form: INVOICE # Information Missing", vbExclamation, "Incomplete Form: Invoice # Missing"
DoCmd.CancelEvent
ElseIf (IsNull(Depth)) Then
Beep
MsgBox "Incomplete Form: DEPTH Information Missing", vbExclamation, "Incomplete Form: Depth Missing"
DoCmd.CancelEvent
ElseIf (IsNull([Final Total])) Then
Beep
MsgBox "Incomplete Form: FINAL TOTAL Information Missing", vbExclamation, "Incomplete Form: Final Total Missing Information"
DoCmd.CancelEvent
ElseIf (IsNull([Billable Total])) Then
Beep
MsgBox "Incomplete Form: BILLABLE TOTAL Information Missing", vbExclamation, "Incomplete Form: Billable Total Information Missing"
DoCmd.CancelEvent

End If


DoCmd.Close
TTL_Exit_Exit:
Exit Sub


TTL_Exit_Err:
MsgBox Error$
Resume TTL_Exit_Exit


End Sub



The issue is that if there is a blank field, it will recognize that and warn me, but will continue to close the form instead of cancelling the event and returning the user back to the form to fill in missing information.

Thank you for your help in advance and any advice would be appreciated.
T

 

[Joe4]

Welcome to the Board!

The DoCmd.CancelEvent doesn't do anything unless you are in an Event Procedure, and it looks to me like this is just code called by a Command Button click, not an Event.

If you place the line "Exit Sub" after your warning message box, it will exit the sub procedure and not run any more lines of code, (so it should never get to the line that closes the form (DoCmd.Close).

 

[SydneyGeek]

Welcome to MrExcel!

If you use the BeforeUpdate event of the form instead, you can use the event's Cancel parameter to stop the form from saving the record. That will prevent closing the form or navigating to another record.

Also, if certain fields are required every time you enter a record you can alter the table properties (set Allow Nulls to No on the required fields). The advantage of this approach is that any object using those fields will have the properties attached, so you create your error message in one place per field.

Denis

 

[T_Access]

Thank you for your feedback. I will be out of the office till after the weekend so I wont have time to play with it till then.

Joe4: Forgive me, but what would the code look like with your suggestion?
Denis: Unfortunately changing table properties is not an option this time, though I agree that would be the easiest. I will try the beforeupdate.

Thank you and I will let you know how I make out

T

 

[Joe4]

Replace each instance of "DoCmd.CancelEvent" with "Exit Sub".

 

[T_Access]

Thanks Joe4 - that worked brilliantly. However I thought this would solve the other issue that i had, but unfortunalty I can not get past this one.

Private Sub Save_Check_Update_Click()
On Error GoTo Save_Check_Update_Click_Err




If Me.Dirty Then
RunCommand acCmdSaveRecord


If (IsNull([Co Name])) Then
Beep
MsgBox "Incomplete Form: CUSTOMER Information Missing", vbExclamation, "Incomplete Form: Missing Customer Information"
Exit Sub

ElseIf (([JobType1] And [JobType2]) = 0) Then
Beep
MsgBox "Incomplete Form: JOB TYPE Iinformation Missing", vbExclamation, "Incomplete Form: JOB TYPE Iinformation Missing"
Exit Sub



RunCommand acCmdSaveRecord


If IsNull(Me![Snake #1]) Then
Me!Snake_Returned = 0
Else: Me!Snake_Returned = -1
End If

If IsNull(Me![Snake #2]) Then
Me!Snake2_Returned = 0
Else: Me!Snake2_Returned = -1
End If

Exit Sub
End If
Else: Exit Sub
End If


Save_Check_Update_Click_Exit:
Exit Sub


Save_Check_Update_Click_Err:
MsgBox Error$
Resume Save_Check_Update_Click_Exit



End Sub

For some reason this code is not reacting consistently, or does not react at all. I have deleted over 80% but they are repeats of the code that is there regarding specific fields and commands associated.

The goals are:
If form is dirty, then go through the rest of the code
If form is not dirty then close form without saving.

This way, if it is a new form that no information has been added, then I can just close it. If information has been added, then the form is Dirty and then go through the sanity check.

Sorry if this sounds a but confusing. You guys were a good help last time, so maybe you can help me with this one.

T

 

[T_Access]

Got if figured - The form was not staying dirty.

Thank you again for the help.

Question#2
Using VBA, how can I open a form and ask it to hide a particular tab out of the 4 that are there.

Thank you again

 

[Joe4]

Question#2
Using VBA, how can I open a form and ask it to hide a particular tab out of the 4 that are there.

Its best to ask brand new questions in a brand new thread. That way they appear as new unanswered questions, and will get more looks, and hopefully more attention.

The general rule of thumb is if your question is a directly related follow-up that is dependent on the original question, post it to the same thread. But if it is a new question, post it to a new thread.