Testing if record exists

H

How1

Can someone give me code for checking if a record exists then prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is not doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
 
H

How1

I've got it working. However I need it to check several field for matching
data. Can you tell me what code need to be repeated.

Thanks.
 
O

Ofer Cohen

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = " &
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = " &
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"
 
H

How1

Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a Numberic
field or TextBox?

Thanks.
--
How1


Ofer Cohen said:
If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = " &
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = " &
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


How1 said:
I've got it working. However I need it to check several field for matching
data. Can you tell me what code need to be repeated.

Thanks.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What is the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How1 said:
Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a Numberic
field or TextBox?

Thanks.
--
How1


Ofer Cohen said:
If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = "
&
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = "
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


How1 said:
I've got it working. However I need it to check several field for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
H

HOW1

Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE = '" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

Douglas J. Steele said:
You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What is the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How1 said:
Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a Numberic
field or TextBox?

Thanks.
--
How1


Ofer Cohen said:
If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = "
&
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField = "
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Any suggestions for what? Are you saying that that code doesn't do what you
want? If not, what happens? Do you get an error message? If so, what's the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE = '" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

Douglas J. Steele said:
You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How1 said:
Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
H

HOW1

The code is not working. It is displaying a run-time error 13. Type mismatch.

Thanks.

Douglas J. Steele said:
Any suggestions for what? Are you saying that that code doesn't do what you
want? If not, what happens? Do you get an error message? If so, what's the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE = '" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

Douglas J. Steele said:
You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

Douglas J. Steele said:
Any suggestions for what? Are you saying that that code doesn't do what
you
want? If not, what happens? Do you get an error message? If so, what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE =
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
H

HOW1

The stLinkCriteria is raising the error.

Thanks.

Douglas J. Steele said:
What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

Douglas J. Steele said:
Any suggestions for what? Are you saying that that code doesn't do what
you
want? If not, what happens? Do you get an error message? If so, what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE =
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the value,
Numeric field wont have anu thing and date field will have # before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox & "'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Your quotes appear to be incorrect, specifically

"'" And COMP_TYPE = '" &

You've got

" ' " And COMP_TYPE = ' " &

It should be

" ' And COMP_TYPE = ' " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
The stLinkCriteria is raising the error.

Thanks.

Douglas J. Steele said:
What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

:

Any suggestions for what? Are you saying that that code doesn't do
what
you
want? If not, what happens? Do you get an error message? If so, what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE
=
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What
is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the
value,
Numeric field wont have anu thing and date field will have #
before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox &
"'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field
for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being
entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists
then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it
is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
H

HOW1

That has overcome that error.

Can you tell me how to code the Dfunction to include the other fields.

Thanks again for all your help.

Douglas J. Steele said:
Your quotes appear to be incorrect, specifically

"'" And COMP_TYPE = '" &

You've got

" ' " And COMP_TYPE = ' " &

It should be

" ' And COMP_TYPE = ' " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
The stLinkCriteria is raising the error.

Thanks.

Douglas J. Steele said:
What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

:

Any suggestions for what? Are you saying that that code doesn't do
what
you
want? If not, what happens? Do you get an error message? If so, what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And COMP_TYPE
=
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No. What
is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the
value,
Numeric field wont have anu thing and date field will have #
before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox &
"'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several field
for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being
entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record exists
then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However it
is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What "other fields"?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
That has overcome that error.

Can you tell me how to code the Dfunction to include the other fields.

Thanks again for all your help.

Douglas J. Steele said:
Your quotes appear to be incorrect, specifically

"'" And COMP_TYPE = '" &

You've got

" ' " And COMP_TYPE = ' " &

It should be

" ' And COMP_TYPE = ' " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
The stLinkCriteria is raising the error.

Thanks.

:

What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

:

Any suggestions for what? Are you saying that that code doesn't do
what
you
want? If not, what happens? Do you get an error message? If so,
what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And
COMP_TYPE
=
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No.
What
is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is
this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the
value,
Numeric field wont have anu thing and date field will have #
before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox
&
"'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several
field
for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being
entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record
exists
then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However
it
is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
H

HOW1

I need to test the data entered into all the fields in my form and then
compare against existing records and give a warning if the record exists. At
the moment I have the code functioning when testing one field only. I need to
test all the fields. I have set the stLink Criteria to test multiple fields.
However it is now allowing me to add duplicate records.
I am not sure if the Dcount function needs to be changed also now that the
stLinkCriteria is testing multiple fields.


If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then

Do the above Dcount code remain the same if the stLinkCriteria has changed
to test multiple fields? If not why is the code now allowing me to add
duplicate records?

Douglas J. Steele said:
What "other fields"?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
That has overcome that error.

Can you tell me how to code the Dfunction to include the other fields.

Thanks again for all your help.

Douglas J. Steele said:
Your quotes appear to be incorrect, specifically

"'" And COMP_TYPE = '" &

You've got

" ' " And COMP_TYPE = ' " &

It should be

" ' And COMP_TYPE = ' " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The stLinkCriteria is raising the error.

Thanks.

:

What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

:

Any suggestions for what? Are you saying that that code doesn't do
what
you
want? If not, what happens? Do you get an error message? If so,
what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And
COMP_TYPE
=
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No.
What
is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is
this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the
value,
Numeric field wont have anu thing and date field will have #
before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox
&
"'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several
field
for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being
entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record
exists
then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However
it
is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already exists")
End If
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Try using DCount("*", ... rather than DCount("ORIG_CONT_", .... The
difference is using "*" will count all rows that meet the criteria, whereas
using "ORIG_CONT_" will only count those rows that meet the criteria and for
which ORIG_CONT_ is not null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
I need to test the data entered into all the fields in my form and then
compare against existing records and give a warning if the record exists.
At
the moment I have the code functioning when testing one field only. I need
to
test all the fields. I have set the stLink Criteria to test multiple
fields.
However it is now allowing me to add duplicate records.
I am not sure if the Dcount function needs to be changed also now that the
stLinkCriteria is testing multiple fields.


If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then

Do the above Dcount code remain the same if the stLinkCriteria has changed
to test multiple fields? If not why is the code now allowing me to add
duplicate records?

Douglas J. Steele said:
What "other fields"?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
That has overcome that error.

Can you tell me how to code the Dfunction to include the other fields.

Thanks again for all your help.

:

Your quotes appear to be incorrect, specifically

"'" And COMP_TYPE = '" &

You've got

" ' " And COMP_TYPE = ' " &

It should be

" ' And COMP_TYPE = ' " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The stLinkCriteria is raising the error.

Thanks.

:

What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code is not working. It is displaying a run-time error 13.
Type
mismatch.

Thanks.

:

Any suggestions for what? Are you saying that that code doesn't
do
what
you
want? If not, what happens? Do you get an error message? If so,
what's
the
error? If you don't get an error message, what do you get
instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And
COMP_TYPE
=
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field
Order_No.
What
is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField',
NumericTextBox.

For example the name of one of the fields. is Order_No. Is
this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after
the
value,
Numeric field wont have anu thing and date field will have
#
before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" &
AnotherTextBox
&
"'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several
field
for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being
entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record
exists
then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself.
However
it
is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Flag = Flag + 1
ElseIf Me.OTHER_SPEC <> Me.OTHER_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.O_RING_MAT <> Me.O_RING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.CASING_MAT <> Me.CASING_MAT.OldValue Then
Flag = Flag + 1
ElseIf Me.SEAL_TYPE <> Me.SEAL_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.PAINT_SPEC <> Me.PAINT_SPEC.OldValue Then
Flag = Flag + 1
ElseIf Me.EVAP_TEMP <> Me.EVAP_TEMP.OldValue Then
Flag = Flag + 1
ElseIf Me.COND_TEMP <> Me.COND_TEMP.OldValue Then
Flag = Flag + 1

If Flag > 0 Then resp = MsgBox("record already
exists")
End If
Click to expand...
Click to expand...
Click to expand...
 
H

HOW1

I have tried that and it still allows me to duplicate records meeting the
criteria.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String




SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & " 'And COMP_TYPE = ' " &
Me.COMP_TYPE.Value & "'"


'Check StudentDetails table for duplicate StudentNumber
If DCount("*", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning record has already been entered" _


End If


End Sub

Thanks.

HOW1 said:
I need to test the data entered into all the fields in my form and then
compare against existing records and give a warning if the record exists. At
the moment I have the code functioning when testing one field only. I need to
test all the fields. I have set the stLink Criteria to test multiple fields.
However it is now allowing me to add duplicate records.
I am not sure if the Dcount function needs to be changed also now that the
stLinkCriteria is testing multiple fields.


If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then

Do the above Dcount code remain the same if the stLinkCriteria has changed
to test multiple fields? If not why is the code now allowing me to add
duplicate records?

Douglas J. Steele said:
What "other fields"?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
That has overcome that error.

Can you tell me how to code the Dfunction to include the other fields.

Thanks again for all your help.

:

Your quotes appear to be incorrect, specifically

"'" And COMP_TYPE = '" &

You've got

" ' " And COMP_TYPE = ' " &

It should be

" ' And COMP_TYPE = ' " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The stLinkCriteria is raising the error.

Thanks.

:

What line is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code is not working. It is displaying a run-time error 13. Type
mismatch.

Thanks.

:

Any suggestions for what? Are you saying that that code doesn't do
what
you
want? If not, what happens? Do you get an error message? If so,
what's
the
error? If you don't get an error message, what do you get instead?

Incidentally, there's no need for the code

Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Set rsc = Nothing

since you're doing nothing else with rsc in that code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

All are text fields.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & "'" And
COMP_TYPE
=
'" &
Me.COMP_TYPE.Value & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("ORIG_CONT_", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Record exists" _

End If

Set rsc = Nothing
End Sub

Any suggestions?

Thanks again.

:

You can see your database: we can't.

Open your table in Design view and look at the field Order_No.
What
is
the
Data Type defined as?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Can you tell what to replace 'NumericField', NumericTextBox.

For example the name of one of the fields. is Order_No. Is
this a
Numberic
field or TextBox?

Thanks.
--
How1


:

If that the criteria from the example

stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

To add more fields to it you can use something like

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox

Note: Text field will have single quote before and after the
value,
Numeric field wont have anu thing and date field will have #
before
and
after the value

stLinkCriteria = "[strStudentNumber]= '" & SID & "' And
NumericField =
"
&
NumericTextBox & " And AnotherTextField = '" & AnotherTextBox
&
"'"

--
Good Luck
BS"D


:

I've got it working. However I need it to check several
field
for
matching
data. Can you tell me what code need to be repeated.

Thanks.
--
How1


:

Check this link on "Preventing Duplicates from being
entered"

http://www.databasedev.co.uk/duplicates.html

--
Good Luck
BS"D


:

Can someone give me code for checking if a record
exists
then
prompt the user
to let them know that the record exists.

Here is code that I have came up with myself. However
it
is
not
doing what I
need it to do.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Flag As Integer

Flag = 0

If Me.MK_NO_ <> Me.MK_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.COMP_TYPE <> Me.COMP_TYPE.OldValue Then
Flag = Flag + 1
ElseIf Me.ROTOR_SIZE <> Me.ROTOR_SIZE.OldValue Then
Flag = Flag + 1
ElseIf Me.L_D <> Me.L_D.OldValue Then
Flag = Flag + 1
ElseIf Me.BIPR <> Me.BIPR.OldValue Then
Flag = Flag + 1
ElseIf Me.SER_NO_ <> Me.SER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.SYSTEM_NO_ <> Me.SYSTEM_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.PART_NO <> Me.PART_NO.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_PREF <> Me.ORDER_PREF.OldValue Then
Flag = Flag + 1
ElseIf Me.ORDER_NO_ <> Me.ORDER_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.ORIG_CONT_ <> Me.ORIG_CONT_.OldValue Then
Flag = Flag + 1
ElseIf Me.UNIT_NO_ <> Me.UNIT_NO_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_SHIP_ <> Me.DATE_SHIP_.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_DUE <> Me.DATE_DUE.OldValue Then
Flag = Flag + 1
ElseIf Me.BOOKED <> Me.BOOKED.OldValue Then
Flag = Flag + 1
ElseIf Me.CLIENTS_OR <> Me.CLIENTS_OR.OldValue Then
Flag = Flag + 1
ElseIf Me.DATE_ISS_D <> Me.DATE_ISS_D.OldValue Then
Flag = Flag + 1
ElseIf Me.GAS_HANDLE <> Me.GAS_HANDLE.OldValue Then
Flag = Flag + 1
ElseIf Me.CUSTOMER <> Me.CUSTOMER.OldValue Then
Flag = Flag + 1
ElseIf Me.COUNTRY <> Me.COUNTRY.OldValue Then
Flag = Flag + 1
ElseIf Me.SITE <> Me.SITE.OldValue Then
Flag = Flag + 1
ElseIf Me.USER <> Me.USER.OldValue Then
Flag = Flag + 1
ElseIf Me.NOTE <> Me.NOTE.OldValue Then
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If that's your actual code, the problem is the spaces you've got near your
quotes.

stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & " 'And COMP_TYPE = ' " &
Me.COMP_TYPE.Value & "'"

introduces spaces into what's being searched. If SID was abc and COMP_TYPE
was xyz, your criteria would look something like

[ORIG_CONT_]='abc ' And COMP_TYPE = ' xyz'

In other words, it's looking for entries that have spaces at the end or at
the beginning, whereas what's stored likely doesn't have those spaces.

Get rid of the spaces:

stLinkCriteria = "[ORIG_CONT_]='" & SID & "' And " & _
"COMP_TYPE = '" & Me.COMP_TYPE.Value & "'"
 
H

HOW1

That worked. Thanks for all your help.

Douglas J. Steele said:
If that's your actual code, the problem is the spaces you've got near your
quotes.

stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & " 'And COMP_TYPE = ' " &
Me.COMP_TYPE.Value & "'"

introduces spaces into what's being searched. If SID was abc and COMP_TYPE
was xyz, your criteria would look something like

[ORIG_CONT_]='abc ' And COMP_TYPE = ' xyz'

In other words, it's looking for entries that have spaces at the end or at
the beginning, whereas what's stored likely doesn't have those spaces.

Get rid of the spaces:

stLinkCriteria = "[ORIG_CONT_]='" & SID & "' And " & _
"COMP_TYPE = '" & Me.COMP_TYPE.Value & "'"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HOW1 said:
I have tried that and it still allows me to duplicate records meeting the
criteria.

Here is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String




SID = Me.ORIG_CONT_.Value
stLinkCriteria = "[ORIG_CONT_]=" & "'" & SID & " 'And COMP_TYPE = ' " &
Me.COMP_TYPE.Value & "'"


'Check StudentDetails table for duplicate StudentNumber
If DCount("*", "comp", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning record has already been entered" _


End If


End Sub

Thanks.
Click to expand...
Click to expand...
 
Top