Force Entry Based on Criteria

Let me start by saying that this is the first database that I have ever put Modules & VBA into. So I am REALLY new, so be gentle with me.

Also, I am using some code provided by my boss who has some knowledge of this, but is fairly new as well. So, if it is not on the right event type, have patience.

I have a form that is being used to track progress of work by our processors.

In the database, I have a combo field called status and a comment field. If the status = PENDING, I need to require something to be placed in the comment field.

I think that I am close, with the following code, however it will check once, give the message box and then let me save the record anyway without the comment.

Can anyone assist me with trying to button this down?

Thanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)

'test comments if status = pending

Dim stat As String
Dim Comment As Variant
Dim mycheck
Dim ID As Integer

ID = Me!ID

Comment = Me!Comment
stat = Me!Status
If stat = "PENDING" Then
mycheck = IsNull(Comment)
If mycheck = True Then
Response = MsgBox("Please add a comment for pending merchants!", 0)
DoCmd.GoToControl "Comment"
End If
End If

End Sub

you can add you code to the Exit event for the form. By doing so they cant exit the form until they fill in the required fields

Good Luck

hello penny-first-poster

after the response add
this will cancel the update and keep you where you are. hth.

bonekrusher,

you can add you code to the Exit event for the form. By doing so they cant exit the form until they fill in the required fields

Click to expand...

The Exit event is not the correct event to use for editing data. You need to use the form's BeforeUpdate event. The BeforeUpdate event fires just before Access updates the record. If you decide that there is an error, you simply use:
Cancel = True
to cancel the BeforeUpdate event and that will prevent the record from being saved. The problem with using other events such as the exit event is that there are many situations where Access will need to save a record and your exit code wouldn't capture the save and edit the data.

Woohoo!!!!!

This is a great suggestion.

I had to take it a step further, and had to cancel the do.cmd though.

At this point, I don't really need to force them to go to the comment section, as it will not allow them to save without updating.

Thanks!

OK, I spoke too soon.

When I add this line to my code, and try to save without entering a comment, it gives my response and I click ok.

Then it gives another response, The DoMenuItem action was canceled.

I removed the do.cmd item, so I am not sure what it is trying to call as a do.

Here is my code as it appears now...

Private Sub Form_BeforeUpdate(Cancel As Integer)

'test comments if status = pending

Dim stat As String
Dim Comment As Variant
Dim mycheck
Dim ID As Integer

ID = Me!ID

Comment = Me!Comment
stat = Me!Status
If stat = "PENDING" Then
mycheck = IsNull(Comment)
If mycheck = True Then
Response = MsgBox("Please add a comment for pending merchants!", 0)
Cancel = True
End If
End If

End Sub

OK, I figured this one out myself! Woohoo!

I modified the error response on my save command button to state, "Can not save record for pending merchant without a comment."

Works like a charm!

Thanks again guys!

The forms Before Update event is the key to data validation at the form level.

Check these links out for some working examples that will assist your project.

A Better Mouse Trap?

Enable/Disable The Control Box X Button

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

OK, my requirements were just changed by the end user for this field.

Now, it appears that not only if the status is pending, do they want to require comments.

They also want, if the status is complete for the following fields to be required:

TASQ Order #
Serial #
App

I put this code together, based on what you gave me before, but it will allow update if just one of those fields are filled. I need it to require all of the fields to be filled.

I feel like I am asking you guys to do my work for me, but I am learning, so I am hoping that someone will be able to assist...

Thanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)

'test comments if status = pending
'test tasq order #, serial # and app

Dim stat As String
Dim Comment As Variant
Dim mycheck 'pending
Dim mycheck2 'tasq order #
Dim mycheck3 'serial #
Dim mycheck4 'app
Dim ID As Integer

ID = Me!ID

stat = Me!Status
Comment = Me!Comment
Tasq = Me![TASQ Order #]
ser = Me![Serial #]
App = Me![App]

If stat = "PENDING" Then
mycheck = IsNull(Comment)
If mycheck = True Then
Response = MsgBox("Please add a comment for pending merchants!", 0)
Cancel = True
End If
End If

If stat = "COMPLETE" Then
mycheck2 = IsNull(Tasq)
If mycheck2 = True Then
Response = MsgBox("Please enter TASQ Order number.", 0)
Cancel = True
End If
End If

If stat = "COMPLETE" Then
mycheck3 = IsNull(Tasq)
If mycheck3 = True Then
Response = MsgBox("Please enter Serial number.", 0)
Cancel = True
End If
End If

If stat = "COMPLETE" Then
mycheck4 = IsNull(Tasq)
If mycheck4 = True Then
Response = MsgBox("Please enter Application type.", 0)
Cancel = True
End If
End If

End Sub

You need to merge your IF statements together and use ElseIf for each IF. Also, I think that you need to exit the sub in addition to canceling the event.

You know, I found out what I had done wrong with my last code example, I had the mycheck# isnull values all set to the same variable (tasq).

Changing that actually fixed that problem. Your code looks tighter though, so I tried it and it works as well.

Thanks to all for your assistance!

OK. Requirements changed again.

Now, not only do I need to check that comments are required when this field is in pending, and entry on TASQ, SER and App are complete, I now need to check that when this field is set to SENT_TO_AV that the AV_Comments is not null.

Sounds simple enough, but... my code is not recognizing that I want it to validate that field.

The AV_Comment field is on a separate tab control page, so I am not sure if that makes a difference in this scenario.

Below is my code, thanks for any assistance you can give!

Private Sub Form_BeforeUpdate(Cancel As Integer)

'test comments if status = pending
'test av_comment if status = SENT_TO_AV
'test tasq order #, serial # and app

Dim stat As String
Dim Comment As Variant
Dim AV_Comment As Variant
Dim mycheck 'pending
Dim mycheck2 'tasq order #
Dim mycheck3 'serial #
Dim mycheck4 'app
Dim mycheck5 'av comments
Dim ID As Integer

ID = Me!ID

stat = Me!Status
Comment = Me!Comment
Tasq = Me![TASQ Order #]
Ser = Me![Serial #]
App = Me![App]
Sent = Me!AV_Comment

If stat = "PENDING" Then 'check for comments when status is pending
mycheck = IsNull(Comment)
If mycheck = True Then
Response = MsgBox("Please add a comment for pending merchants!", 0)
Cancel = True
End If
End If

If stat = "COMPLETE" Then 'check for tasq order number when status is complete
mycheck2 = IsNull(Tasq)
If mycheck2 = True Then
Response = MsgBox("Please enter TASQ Order number.", 0)
Cancel = True

ElseIf stat = "COMPLETE" Then 'check for serial number when status is complete
mycheck3 = IsNull(Ser)
If mycheck3 = True Then
Response = MsgBox("Please enter Serial number.", 0)
Cancel = True

ElseIf stat = "COMPLETE" Then 'check for application when status is complete
mycheck4 = IsNull(App)
If mycheck4 = True Then
Response = MsgBox("Please enter Application type.", 0)
Cancel = True

ElseIf stat = "SENT_TO_AV" Then 'check for av_comments when status is sent to av
mycheck5 = IsNull(Sent)
If mycheck5 = True Then
Response = MsgBox("Please enter AV Comments on AV Info screen.", 0)
Cancel = True

End If
End If

End If
End If

End If

End Sub

It's much easier to read your code if you put

If my suggested code works, why go back to your own?
1. There is no need to create all those variables. They are not doing anything for you except confusing the code.
2. You are not using Option Explicit so it is quite easy to create errors by referencing non-existant variables.
3. Your code is not setting focus to the field with the error.
4. I can't be bothered to attempt to match all your If's to their coresponding else's because your code is unformatted. Especially since I have already cleaned it up once.

Mea Culpa

I actually had inadvertantly opened my old test database and pasted in the incorrect code. Then, I got to working on something else.... blah, blah, blah, yada, yada.

I just got back on here to state that I figured out my problem, when I noticed that I had put the wrong code in any way.

Thanks to everyone for their help!

ghudson said:

The "He" you are referring to is a her.

Click to expand...

I'm so sorry, Pat. My apologies.

No problem. Pat is non-gender specific. I actually find it amusing that most people assume me to be a male and I never correct them.