Clear data entered

[Guest]

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

 

[Marshall Barton]

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

 

[Guest]

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

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

 

[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)

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