Before Update Event to check for duplicate data in a field?

  • Thread starter Thread starter jo
  • Start date Start date
J

jo

Hi I would like to enter an ID into a field and if this ID already exist a
msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID" and an
example of the ID: G1234B.
Can anyone help me please?
Thanks Jo
 
Hi I would like to enter an ID into a field and if this ID already
exist a msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID"
and an example of the ID: G1234B.
Can anyone help me please?
Thanks Jo
Click to expand...
First create an unique index on the gauge ID field in the table design
view, or better make it the primary key.

Then select the Gauge ID textbox on your data entry form,
set the data to event procedure
Put the following in the Gauge_ID_BeforeUpdate event

IF isnull(Dlookup("[Gauge ID]", _
& "GaugeInfo", _
& "[Gauge ID] = """ _
& me.Gauge_ID.text & """")) THEN
cancel = TRUE
msgbox "Gauge ID already exists", vbOKonly,"Warning"
me![gauge ID].undo
End If
 
I think you've got that backwards, Bob. If DLookup returns Null, that means
the value doesn't already exist in the table.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", & _
"GaugeInfo", & _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Quintal said:
Hi I would like to enter an ID into a field and if this ID already
exist a msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID"
and an example of the ID: G1234B.
Can anyone help me please?
Thanks Jo
Click to expand...
First create an unique index on the gauge ID field in the table design
view, or better make it the primary key.

Then select the Gauge ID textbox on your data entry form,
set the data to event procedure
Put the following in the Gauge_ID_BeforeUpdate event

IF isnull(Dlookup("[Gauge ID]", _
& "GaugeInfo", _
& "[Gauge ID] = """ _
& me.Gauge_ID.text & """")) THEN
cancel = TRUE
msgbox "Gauge ID already exists", vbOKonly,"Warning"
me![gauge ID].undo
End If
Click to expand...
 
Hi Bob thanks for the reply I copied yr code and it comes up with a complie
error? I am doing something wrong but not sure what??

Bob Quintal said:
Hi I would like to enter an ID into a field and if this ID already
exist a msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID"
and an example of the ID: G1234B.
Can anyone help me please?
Thanks Jo
Click to expand...
First create an unique index on the gauge ID field in the table design
view, or better make it the primary key.

Then select the Gauge ID textbox on your data entry form,
set the data to event procedure
Put the following in the Gauge_ID_BeforeUpdate event

IF isnull(Dlookup("[Gauge ID]", _
& "GaugeInfo", _
& "[Gauge ID] = """ _
& me.Gauge_ID.text & """")) THEN
cancel = TRUE
msgbox "Gauge ID already exists", vbOKonly,"Warning"
me![gauge ID].undo
End If
Click to expand...
 
HI Douglas I tried your way too and I get an syntax error? It must be me but
I dont know wot is wrong?? en more ideas?? please
jo

Douglas J. Steele said:
I think you've got that backwards, Bob. If DLookup returns Null, that means
the value doesn't already exist in the table.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", & _
"GaugeInfo", & _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Quintal said:
Hi I would like to enter an ID into a field and if this ID already
exist a msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID"
and an example of the ID: G1234B.
Can anyone help me please?
Thanks Jo
Click to expand...
First create an unique index on the gauge ID field in the table design
view, or better make it the primary key.

Then select the Gauge ID textbox on your data entry form,
set the data to event procedure
Put the following in the Gauge_ID_BeforeUpdate event

IF isnull(Dlookup("[Gauge ID]", _
& "GaugeInfo", _
& "[Gauge ID] = """ _
& me.Gauge_ID.text & """")) THEN
cancel = TRUE
msgbox "Gauge ID already exists", vbOKonly,"Warning"
me![gauge ID].undo
End If
Click to expand...
Click to expand...
 
Ooops. The ampersands aren't necessary:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", _
"GaugeInfo", _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub


Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jo said:
HI Douglas I tried your way too and I get an syntax error? It must be me
but
I dont know wot is wrong?? en more ideas?? please
jo

Douglas J. Steele said:
I think you've got that backwards, Bob. If DLookup returns Null, that
means
the value doesn't already exist in the table.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", & _
"GaugeInfo", & _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Quintal said:
Hi I would like to enter an ID into a field and if this ID already
exist a msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID"
and an example of the ID: G1234B.
Can anyone help me please?
Thanks Jo

First create an unique index on the gauge ID field in the table design
view, or better make it the primary key.

Then select the Gauge ID textbox on your data entry form,
set the data to event procedure
Put the following in the Gauge_ID_BeforeUpdate event

IF isnull(Dlookup("[Gauge ID]", _
& "GaugeInfo", _
& "[Gauge ID] = """ _
& me.Gauge_ID.text & """")) THEN
cancel = TRUE
msgbox "Gauge ID already exists", vbOKonly,"Warning"
me![gauge ID].undo
End If
Click to expand...
Click to expand...
Click to expand...
 
Hey Douglas that works a treat!!! thank you!!

Douglas J. Steele said:
Ooops. The ampersands aren't necessary:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", _
"GaugeInfo", _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub


Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jo said:
HI Douglas I tried your way too and I get an syntax error? It must be me
but
I dont know wot is wrong?? en more ideas?? please
jo

Douglas J. Steele said:
I think you've got that backwards, Bob. If DLookup returns Null, that
means
the value doesn't already exist in the table.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", & _
"GaugeInfo", & _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)



Hi I would like to enter an ID into a field and if this ID already
exist a msg will warn the user that ID already exist?
My table is called "GaugeInfo" and the field is called "Gauge ID"
and an example of the ID: G1234B.
Can anyone help me please?
Thanks Jo

First create an unique index on the gauge ID field in the table design
view, or better make it the primary key.

Then select the Gauge ID textbox on your data entry form,
set the data to event procedure
Put the following in the Gauge_ID_BeforeUpdate event

IF isnull(Dlookup("[Gauge ID]", _
& "GaugeInfo", _
& "[Gauge ID] = """ _
& me.Gauge_ID.text & """")) THEN
cancel = TRUE
msgbox "Gauge ID already exists", vbOKonly,"Warning"
me![gauge ID].undo
End If
Click to expand...
Click to expand...
Click to expand...
 
Ooops. The ampersands aren't necessary:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Dlookup("[Gauge ID]", _
"GaugeInfo", _
"[Gauge ID] = """ & Me.Gauge_ID.Text & """")) = False Then
Cancel = True
MsgBox "Gauge ID already exists", vbOKOnly,"Warning"
Me![gauge ID].Undo
End If

End Sub


Sorry about that.
Click to expand...
I'm the one who should be sorry, I'm the one who posted the original
code with the unnecessary ampersands. Not to mention the missing NOT
between If and IsNull.

I need to find a way to get intellisense working in my newsreader.
 
Back
Top