Looking for data in a table

[Guest]

I have a Drawing Number field on a form and when the user fills out the
information on the form and clicks the Save button I want to check and see if
the Drawing Number already exists in the table. If it already exists then A
message box will appear telling the user that the Drawing Number is already
in the system. Here is the code I have to do the check.....

Me.MachineNum.SetFocus
temp = DLookup("[DrawingNum]", "tbl_DrawingsAndData", _
"[MachineNum] =" &
Me.MachineNum.Text)

Me.DrawingNum.SetFocus
If Me.DrawingNum.Text = temp Then
MsgBox "This Drawing Control Already Exists, Please See View Drawing
Controls"
Exit Sub
End If

When I click the Save button I am getting Run-time Error: '2001' You
cancelled the previous operation.

Does anyone know why this isn't working or what is wrong with the code???

Any help would be great..

 

[Steve Schapel]

Dave,

I would suggest doing your validation on the Before Update event of
DrawingNum. I don't understand MachineNum, so adjust accordingly, but
the code would be something like this...
Private Sub DrawingNum_BeforeUpdate(Cancel As Integer)
If DCount("*","tbl_DrawingsAndData","[DrawingNum]=" &
Me.DrawingNum) Then
Cancel=True
MsgBox "This Drawing Control Already Exists"
End If
End Sub

 

[Guest]

When I write this code I keep getting a "Data type mismatch error in criteria
expression"

Dave,

I would suggest doing your validation on the Before Update event of
DrawingNum. I don't understand MachineNum, so adjust accordingly, but
the code would be something like this...
Private Sub DrawingNum_BeforeUpdate(Cancel As Integer)
If DCount("*","tbl_DrawingsAndData","[DrawingNum]=" &
Me.DrawingNum) Then
Cancel=True
MsgBox "This Drawing Control Already Exists"
End If
End Sub

--
Steve Schapel, Microsoft Access MVP

I have a Drawing Number field on a form and when the user fills out the
information on the form and clicks the Save button I want to check and see if
the Drawing Number already exists in the table. If it already exists then A
message box will appear telling the user that the Drawing Number is already
in the system. Here is the code I have to do the check.....

Me.MachineNum.SetFocus
temp = DLookup("[DrawingNum]", "tbl_DrawingsAndData", _
"[MachineNum] =" &
Me.MachineNum.Text)

Me.DrawingNum.SetFocus
If Me.DrawingNum.Text = temp Then
MsgBox "This Drawing Control Already Exists, Please See View Drawing
Controls"
Exit Sub
End If

When I click the Save button I am getting Run-time Error: '2001' You
cancelled the previous operation.

Does anyone know why this isn't working or what is wrong with the code???

Any help would be great..

 

[Steve Schapel]

Playa,

So I guess DrawingNum is a text data type, and not a number? Therefore...
DCount("*","tbl_DrawingsAndData","[DrawingNum]='" & Me.DrawingNum & "'")

--
Steve Schapel, Microsoft Access MVP

When I write this code I keep getting a "Data type mismatch error in criteria
expression"

:

Dave,

I would suggest doing your validation on the Before Update event of
DrawingNum. I don't understand MachineNum, so adjust accordingly, but
the code would be something like this...
Private Sub DrawingNum_BeforeUpdate(Cancel As Integer)
If DCount("*","tbl_DrawingsAndData","[DrawingNum]=" &
Me.DrawingNum) Then
Cancel=True
MsgBox "This Drawing Control Already Exists"
End If
End Sub

--
Steve Schapel, Microsoft Access MVP

I have a Drawing Number field on a form and when the user fills out the
information on the form and clicks the Save button I want to check and see if
the Drawing Number already exists in the table. If it already exists then A
message box will appear telling the user that the Drawing Number is already
in the system. Here is the code I have to do the check.....

Me.MachineNum.SetFocus
temp = DLookup("[DrawingNum]", "tbl_DrawingsAndData", _
"[MachineNum] =" &
Me.MachineNum.Text)

Me.DrawingNum.SetFocus
If Me.DrawingNum.Text = temp Then
MsgBox "This Drawing Control Already Exists, Please See View Drawing
Controls"
Exit Sub
End If

When I click the Save button I am getting Run-time Error: '2001' You
cancelled the previous operation.

Does anyone know why this isn't working or what is wrong with the code???

Any help would be great..