Clear data entered

P

Penstar

I have the following code in a from where user is to enter New Ingredient.
It catches the error when a duplicate Ingredient ID is used. It works so
far, but what I want it to do is to clear the incorrect IngredientID they
have started to enter it. I can't figure out what code to put after the
message box

Private Sub IngredientID_BeforeUpdate(Cancel As Integer)
Dim varIngredientID As Variant
Dim strType As String, strWhere As String

strType = Me.IngredientID
strWhere = "[IngredientID]=""" & strType & """"

If IsNull(DLookup("IngredientID", "dbo_Ingredients", strWhere)) Then

Else
MsgBox "The Ingredient Code you have entered already exists. Please use
a Unique Code"

End If

End Sub
 
M

Marshall Barton

Penstar said:
I have the following code in a from where user is to enter New Ingredient.
It catches the error when a duplicate Ingredient ID is used. It works so
far, but what I want it to do is to clear the incorrect IngredientID they
have started to enter it. I can't figure out what code to put after the
message box

Private Sub IngredientID_BeforeUpdate(Cancel As Integer)
Dim varIngredientID As Variant
Dim strType As String, strWhere As String

strType = Me.IngredientID
strWhere = "[IngredientID]=""" & strType & """"

If IsNull(DLookup("IngredientID", "dbo_Ingredients", strWhere)) Then

Else
MsgBox "The Ingredient Code you have entered already exists. Please use
a Unique Code"

End If

End Sub


Add two lines after the MsgBox:

Cancel = True
Me.IngredientID.Undo
 
P

Penstar

Thank you so much. I thought there would be an easy solution.

Though, to clear the record I used
Cancel=True
Me.Undo
(when I just used Me.IngredientID.Undo I got a further error message)

Penny

Marshall Barton said:
Penstar said:
I have the following code in a from where user is to enter New Ingredient.
It catches the error when a duplicate Ingredient ID is used. It works so
far, but what I want it to do is to clear the incorrect IngredientID they
have started to enter it. I can't figure out what code to put after the
message box

Private Sub IngredientID_BeforeUpdate(Cancel As Integer)
Dim varIngredientID As Variant
Dim strType As String, strWhere As String

strType = Me.IngredientID
strWhere = "[IngredientID]=""" & strType & """"

If IsNull(DLookup("IngredientID", "dbo_Ingredients", strWhere)) Then

Else
MsgBox "The Ingredient Code you have entered already exists. Please use
a Unique Code"

End If

End Sub


Add two lines after the MsgBox:

Cancel = True
Me.IngredientID.Undo
 
M

Marshall Barton

Glad to able to help.

If the ingredient is essentially the record, then, yes, you
want to undo the whole record.
--
Marsh
MVP [MS Access]

Thank you so much. I thought there would be an easy solution.

Though, to clear the record I used
Cancel=True
Me.Undo
(when I just used Me.IngredientID.Undo I got a further error message)


Marshall Barton said:
Penstar said:
I have the following code in a from where user is to enter New Ingredient.
It catches the error when a duplicate Ingredient ID is used. It works so
far, but what I want it to do is to clear the incorrect IngredientID they
have started to enter it. I can't figure out what code to put after the
message box

Private Sub IngredientID_BeforeUpdate(Cancel As Integer)
Dim varIngredientID As Variant
Dim strType As String, strWhere As String

strType = Me.IngredientID
strWhere = "[IngredientID]=""" & strType & """"

If IsNull(DLookup("IngredientID", "dbo_Ingredients", strWhere)) Then

Else
MsgBox "The Ingredient Code you have entered already exists. Please use
a Unique Code"

End If

End Sub


Add two lines after the MsgBox:

Cancel = True
Me.IngredientID.Undo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top