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

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
 
B

Bob Quintal

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
 
D

Douglas J. Steele

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...
 
J

jo

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...
 
J

jo

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...
 
D

Douglas J. Steele

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...
 
J

jo

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...
 
B

Bob Quintal

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.
 

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

Similar Threads

Update table field by content in another field in the same table 2
Data Entry Form and Auto Number ID 2
color of field in each record of datasheet depends on other field 1
Identify and display duplicate records 0
Validation of a field 3
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Validating Field data within a Table 9
How to build a "reach around" query? 5

Top