Solved Message Box -Clear field when "OK" Is clicked

[Teri Bridges]

Guys, I have this code to display a message box if the field entry is a duplicate. I would like to add code to clear the field entry if OK is clicked. I don't know how to do that.

Private Sub CatalogID_AfterUpdate()
If DCount("[CatalogID]", "Course_tbl", "[CatalogID]= '" & Me![CatalogID] & "'") > 0 Then
MsgBox "Course Catalog ID Already Exists.!"
End If
End Sub

 

[theDBguy]

Just add another line like

Me.CatalogID = Null

 

[gemma-the-husky]

This give you a msgbox with option buttons, and retrieves your response according to the button you press.

There are assorted options to provide the buttons you want, so it's all pretty easy to programme.


If msgbox("your message",vbyesno+vbquestion+vbdefaultbutton2),=vbno then
msgbox "no clicked"
Else
msgbox "yes clicked"
End if

 

[LarryE]

If you are looking for duplicates, the the test should be:
If DCount("[CatalogID]", "Course_tbl", "[CatalogID]= '" & Me![CatalogID] & "'") >1

You can clear the field as the DBguy said ASSUMING CatalogID is NOT a Primary Key field in your table. And you might get an error message if you try to set it to Null. You can use Me.CatalogID="".

 

[Teri Bridges]

Just add another line like

Me.CatalogID = Null

I am not sure how to add this to my existing code? Catalog ID is Not a Primary Key in my table.

 

[Teri Bridges]

If you are looking for duplicates, the the test should be:
If DCount("[CatalogID]", "Course_tbl", "[CatalogID]= '" & Me![CatalogID] & "'") >1

You can clear the field as the DBguy said ASSUMING CatalogID is NOT a Primary Key field in your table. And you might get an error message if you try to set it to Null. You can use Me.CatalogID="".

No, I am not looking for duplicates. The user enters a new course in the catalog, I just want a popup to display letting them know if they have made a duplicate entry. If they have made a duplicate entry I wanted the field to clear once they clicked the ok button.

 

[Pat Hartman]

You need to use the correct tool for your objective. The AfterUpdate event is the wrong event to use for validation. You need to use the BeforeUpdate event which includes the ability to be cancelled. Using Cancel = true leaves the field dirty but tells Access to NOT save the data. You almost NEVER want to just obliterate data entered by the user even when it is wrong so the only time I would ever clear the control is when the user does not have authorization to do any updating so no matter what he types, I'm going to erase it. If it is just wrong, you should leave it and let him see what he entered. He will not thank you for erasing what he typed.

Private Sub CatalogID_BeforeUpdate(ByRef Cancel as Integer)
    If DCount("[CatalogID]", "Course_tbl", "[CatalogID]= '" & Me![CatalogID] & "'") > 0 Then
        MsgBox "Course Catalog ID Already Exists.!"
        Cancel = True
        Exit Sub
    End If
End Sub

 

[Pat Hartman]

If they have made a duplicate entry I wanted the field to clear once they clicked the ok button.

You really don't want to erase what the user typed. What if it is only a couple of characters off?

You should also have a unique index to prevent a duplicate from being saved.

@LarryE checking for > 0 is the correct condition. The record you are updating has not yet been saved and so is not included in the count. Only existing records would be considered and you don't want to find any of them.

 

[Teri Bridges]

You need to use the correct tool for your objective. The AfterUpdate event is the wrong event to use for validation. You need to use the BeforeUpdate event which includes the ability to be cancelled. Using Cancel = true leaves the field dirty but tells Access to NOT save the data. You almost NEVER want to just obliterate data entered by the user even when it is wrong so the only time I would ever clear the control is when the user does not have authorization to do any updating so no matter what he types, I'm going to erase it. If it is just wrong, you should leave it and let him see what he entered. He will not thank you for erasing what he typed.

Private Sub CatalogID_BeforeUpdate(ByRef Cancel as Integer)
    If DCount("[CatalogID]", "Course_tbl", "[CatalogID]= '" & Me![CatalogID] & "'") > 0 Then
        MsgBox "Course Catalog ID Already Exists.!"
        Cancel = True
        Exit Sub
    End If
End Sub

Thanks Pat, but in this case, I do want to clear the data so the user can make a new entry without having to delete what they have previously typed. It is not a big deal to have them delete the entry. I was hoping to automate the process.

 

[Teri Bridges]

You really don't want to erase what the user typed. What if it is only a couple of characters off?

You should also have a unique index to prevent a duplicate from being saved.

@LarryE checking for > 0 is the correct condition. The record you are updating has not yet been saved and so is not included in the count. Only existing records would be considered and you don't want to find any of them.

I do have the field in the table indexed, but it wont notify them that it is a duplicate record until they have completed all fields on the form. I was trying to make a notification happen after the first field entry, which is the field that cannot be duplicated.

 

[Pat Hartman]

That's fine. Both are necessary. The index is the line of last defense and will catch an update that happens via a query. Your dcount() will work in the BeforeUpdate event of the control and warn the user before he moves off the control. You just need to get your code into the correct event and than use the correct method to stop the update which is --- Cancel = True, NOT setting the field value to null.

 

[Teri Bridges]

That's fine. Both are necessary. The index is the line of last defense and will catch an update that happens via a query. Your dcount() will work in the BeforeUpdate event of the control and warn the user before he moves off the control. You just need to get your code into the correct event and than use the correct method to stop the update which is --- Cancel = True, NOT setting the field value to null.

Thanks Pat I am new to VBA and have mostly worked by copying existing code and changing it to fit my needs. I think I am having a hard time combining codes.

If this is my code:

Private Sub CatalogID_AfterUpdate()
If DCount("[CatalogID]", "Course_tbl", "[CatalogID]= '" & Me![CatalogID] & "'") > 0 Then
MsgBox "Course Catalog ID Already Exists.!"
End If
End Sub

I do not know what statement or test needs to be added, to reset the field.

 

[Pat Hartman]

If you go back and look at #7, you will see the EXACT code you need. You need to delete the AfterUpdate event and replace it with the BeforeUpdate event code I posted.

 

[Teri Bridges]

If you go back and look at #7, you will see the EXACT code you need. You need to delete the AfterUpdate event and replace it with the BeforeUpdate event code I posted.

#7 did not work either. It did not clear the field. Thanks tho

 

[Pat Hartman]

I explained to you why I think that clearing the field is WRONG. Clearing the field is NOT the way you prevent Access from saving the duplicate. Using the Cancel = True is the method that keeps the user from leaving the field when It contains an invalid value. The users should also be taught to use the esc key if they want to undo typing that they did.

If you still want to clear the field, add the line of code that does it AFTER the Cancel = True line. But this is Draconian. You don't want to erase anything the user types EXCEPT for the reasons I mentioned earlier. I don't know what these strings look like. What if they can be only a couple of characters different? Why would you want to force a user to retype the entire string if he only needed to change a couple of characters? That's just mean and not user friendly.

You can use more complicated code that asks if the user also wants to erase the duplicate value but I also avoid spurious prompts to the user. I don't want to train the users to ignore my prompts so I save them for communications that I need him to pay attention to.

 

[Teri Bridges]

I explained to you why I think that clearing the field is WRONG. Clearing the field is NOT the way you prevent Access from saving the duplicate. Using the Cancel = True is the method that keeps the user from leaving the field when It contains an invalid value. The users should also be taught to use the esc key if they want to undo typing that they did.

If you still want to clear the field, add the line of code that does it AFTER the Cancel = True line. But this is Draconian. You don't want to erase anything the user types EXCEPT for the reasons I mentioned earlier. I don't know what these strings look like. What if they can be only a couple of characters different? Why would you want to force a user to retype the entire string if he only needed to change a couple of characters? That's just mean and not user friendly.

You can use more complicated code that asks if the user also wants to erase the duplicate value but I also avoid spurious prompts to the user. I don't want to train the users to ignore my prompts so I save them for communications that I need him to pay attention to.

That all makes sense. I now understand what you are saying. I will not clear the field.

 

[Pat Hartman]

Great