Duplicate value

[Scott]

I have one field to accept one duplicate value, 8888 but not others. Can
someone advise some idea how to accomplish.

Thanks,

Scott

 

[JK]

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" & Me.YourField
_
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK

 

[Scott]

JK,

Thanks for your code. Regarding "YourTable", my form is based on a query so
I put the QueryName but it seems does not work with query. Is it the
restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK

I have one field to accept one duplicate value, 8888 but not others. Can
someone advise some idea how to accomplish.

Thanks,

Scott

 

[JK]

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of the
date instead of the date itself eg:

In my Numeric routine:

..... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code here
and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a query
so I put the QueryName but it seems does not work with query. Is it the
restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK

I have one field to accept one duplicate value, 8888 but not others. Can
someone advise some idea how to accomplish.

Thanks,

Scott

 

[Scott]

JK,

Thanks for your advice. The field is Case No and is of text type. The text
box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "' AND
[ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of
the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code here
and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a query
so I put the QueryName but it seems does not work with query. Is it the
restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[JK]

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e. Me.[Case
No]. This by itself is not a problem (it should work too) but it tells me
that your Control Name on the Form is not "Case No" and access picks up the
name from the query.

It is a good practice to name the control in the same name as the field
(unless you have 2 controls using the same field in the table or query). In
that case if there are spaces in the control name, in the Me. expression
*only* the spaces are replaced by underscores ( _ ) like this: Me.Case_No,
access will do it for you if you are using it drop down menu after typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK

JK,

Thanks for your advice. The field is Case No and is of text type. The
text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "' AND
[ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of
the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code here
and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query. Is
it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[David F Cox]

I am not expert in DLOOKUP, but this looks wrong to me:-

"[ID],"



"[ID]",

JK,

Thanks for your advice. The field is Case No and is of text type. The
text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "' AND
[ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of
the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code here
and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query. Is
it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[Scott]

JK,

Thanks for your continuous help. I have tried both methods but it produced
same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is based on
a query) is due to spaces used in between words. Now, understand it can use
underscore to fill the space instead.

I just want to clarify if I use the correct parameters for the code. ID is
the auto number field with primary key. Case No is a text field that I need
to control the data entry. The form is linked with a query, not directly to
a table. Any clues?

Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e. Me.[Case
No]. This by itself is not a problem (it should work too) but it tells me
that your Control Name on the Form is not "Case No" and access picks up
the
name from the query.

It is a good practice to name the control in the same name as the field
(unless you have 2 controls using the same field in the table or query).
In
that case if there are spaces in the control name, in the Me. expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK

JK,

Thanks for your advice. The field is Case No and is of text type. The
text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "' AND
[ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of
the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code
here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query. Is
it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[JK]

Scott,

no worries, glad to help.
comment below

JK,

Thanks for your continuous help. I have tried both methods but it
produced same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is based
on a query) is due to spaces used in between words. Now, understand it
can use underscore to fill the space instead.

Eventhough you have spaces in the table name still *no braket*, *no
underscores*.The quotation marks tells Access 2 things:

1. That it is a table or a query and
2. The name is *exactly* what appears between the quotation marks.



The square brackets tell Access that it is a *field*, This may well be the
source for the error you are getting.

*Dont* use the underscore for spaces for anything that is enclosed in
quoateion marks or square brakets, the underscor is used *only and strictly*
with the expression Me. for Control names.

I just want to clarify if I use the correct parameters for the code. ID
is the auto number field with primary key. Case No is a text field that I
need to control the data entry. The form is linked with a query, not
directly to a table. Any clues?

This is OK, no prolem here above

If still a problem, look at the property of "Case No" at the top and tell me
whay yoave under
1. Name
2. Control Source

Maybe there is some of confusion

Regards/JK

Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e. Me.[Case
No]. This by itself is not a problem (it should work too) but it tells me
that your Control Name on the Form is not "Case No" and access picks up
the
name from the query.

It is a good practice to name the control in the same name as the field
(unless you have 2 controls using the same field in the table or query).
In
that case if there are spaces in the control name, in the Me. expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK

JK,

Thanks for your advice. The field is Case No and is of text type. The
text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "'
AND [ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of
the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code
here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query.
Is it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[Scott]

JK,

The property of Case No is as below:-

Name: Case No
Control Source: Case No

Scott

Scott,

no worries, glad to help.
comment below

JK,

Thanks for your continuous help. I have tried both methods but it
produced same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is based
on a query) is due to spaces used in between words. Now, understand it
can use underscore to fill the space instead.

Eventhough you have spaces in the table name still *no braket*, *no
underscores*.The quotation marks tells Access 2 things:

1. That it is a table or a query and
2. The name is *exactly* what appears between the quotation marks.



The square brackets tell Access that it is a *field*, This may well be the
source for the error you are getting.

*Dont* use the underscore for spaces for anything that is enclosed in
quoateion marks or square brakets, the underscor is used *only and
strictly* with the expression Me. for Control names.

I just want to clarify if I use the correct parameters for the code. ID
is the auto number field with primary key. Case No is a text field that
I need to control the data entry. The form is linked with a query, not
directly to a table. Any clues?

This is OK, no prolem here above

If still a problem, look at the property of "Case No" at the top and tell
me whay yoave under
1. Name
2. Control Source

Maybe there is some of confusion

Regards/JK

Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e.
Me.[Case
No]. This by itself is not a problem (it should work too) but it tells
me
that your Control Name on the Form is not "Case No" and access picks up
the
name from the query.

It is a good practice to name the control in the same name as the field
(unless you have 2 controls using the same field in the table or query).
In
that case if there are spaces in the control name, in the Me. expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after
typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK


JK,

Thanks for your advice. The field is Case No and is of text type. The
text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "'
AND [ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that
if [YourFiled] is a Date field you have to match it against then
number of the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code
here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query.
Is it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[Scott]

JK,

I followed your suggested naming for the textbox of Case No and modified the
code as below:-

Private Sub txtCaseNo_BeforeUpdate(Cancel As Integer)

If Nz(DLookup("[ID]", "qry pro", "[CATS No]='" & Me.txtCaseNo & "' AND
[ID]<> " & Nz(Me.id), 0), 0) <> 0 And Me.txtCaseNo <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If
End Sub

The past error message has been changed to:-

Complie error:
Method or data member not found.

Me.txtCaseNo was highlighted.

Scott

JK,

The property of Case No is as below:-

Name: Case No
Control Source: Case No

Scott

Scott,

no worries, glad to help.
comment below

JK,

Thanks for your continuous help. I have tried both methods but it
produced same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is
based on a query) is due to spaces used in between words. Now,
understand it can use underscore to fill the space instead.

Eventhough you have spaces in the table name still *no braket*, *no
underscores*.The quotation marks tells Access 2 things:

1. That it is a table or a query and
2. The name is *exactly* what appears between the quotation marks.



The square brackets tell Access that it is a *field*, This may well be
the source for the error you are getting.

*Dont* use the underscore for spaces for anything that is enclosed in
quoateion marks or square brakets, the underscor is used *only and
strictly* with the expression Me. for Control names.

I just want to clarify if I use the correct parameters for the code. ID
is the auto number field with primary key. Case No is a text field that
I need to control the data entry. The form is linked with a query, not
directly to a table. Any clues?

This is OK, no prolem here above

If still a problem, look at the property of "Case No" at the top and tell
me whay yoave under
1. Name
2. Control Source

Maybe there is some of confusion

Regards/JK

Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e.
Me.[Case
No]. This by itself is not a problem (it should work too) but it tells
me
that your Control Name on the Form is not "Case No" and access picks
up the
name from the query.

It is a good practice to name the control in the same name as the field
(unless you have 2 controls using the same field in the table or
query). In
that case if there are spaces in the control name, in the Me.
expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after
typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK


JK,

Thanks for your advice. The field is Case No and is of text type.
The text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "'
AND [ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that
if [YourFiled] is a Date field you have to match it against then
number of the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code
here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query.
Is it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not
others. Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[JK]

Scott,

Miahooo! how many [CATS] do you have so you must keep a database for them?

(Have a look at your code again)

Ok, this is the correct code:


If Nz(DLookup("[ID]", "qry pro", "[CASE No]='" & Me.Case_No & "' AND
[ID]<> " & Nz(Me.ID), 0), 0) <> 0 And Me.Case_No <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

This should work

Regards/JK

JK,

I followed your suggested naming for the textbox of Case No and modified
the code as below:-

Private Sub txtCaseNo_BeforeUpdate(Cancel As Integer)

If Nz(DLookup("[ID]", "qry pro", "[CATS No]='" & Me.txtCaseNo & "' AND
[ID]<> " & Nz(Me.id), 0), 0) <> 0 And Me.txtCaseNo <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If
End Sub

The past error message has been changed to:-

Complie error:
Method or data member not found.

Me.txtCaseNo was highlighted.

Scott

JK,

The property of Case No is as below:-

Name: Case No
Control Source: Case No

Scott

Scott,

no worries, glad to help.
comment below


JK,

Thanks for your continuous help. I have tried both methods but it
produced same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is
based on a query) is due to spaces used in between words. Now,
understand it can use underscore to fill the space instead.

Eventhough you have spaces in the table name still *no braket*, *no
underscores*.The quotation marks tells Access 2 things:

1. That it is a table or a query and
2. The name is *exactly* what appears between the quotation marks.



The square brackets tell Access that it is a *field*, This may well be
the source for the error you are getting.

*Dont* use the underscore for spaces for anything that is enclosed in
quoateion marks or square brakets, the underscor is used *only and
strictly* with the expression Me. for Control names.


I just want to clarify if I use the correct parameters for the code.
ID is the auto number field with primary key. Case No is a text field
that I need to control the data entry. The form is linked with a
query, not directly to a table. Any clues?

This is OK, no prolem here above

If still a problem, look at the property of "Case No" at the top and
tell me whay yoave under
1. Name
2. Control Source

Maybe there is some of confusion

Regards/JK




Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e.
Me.[Case
No]. This by itself is not a problem (it should work too) but it tells
me
that your Control Name on the Form is not "Case No" and access picks
up the
name from the query.

It is a good practice to name the control in the same name as the
field
(unless you have 2 controls using the same field in the table or
query). In
that case if there are spaces in the control name, in the Me.
expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after
typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK


JK,

Thanks for your advice. The field is Case No and is of text type.
The text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "'
AND [ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that
if [YourFiled] is a Date field you have to match it against then
number of the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code
here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query.
Is it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not
others. Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[fae]

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e. Me.[Case
No]. This by itself is not a problem (it should work too) but it tells me
that your Control Name on the Form is not "Case No" and access picks up
the
name from the query.

It is a good practice to name the control in the same name as the field
(unless you have 2 controls using the same field in the table or query).
In
that case if there are spaces in the control name, in the Me. expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK

JK,

Thanks for your advice. The field is Case No and is of text type. The
text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] & "' AND
[ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention that if
[YourFiled] is a Date field you have to match it against then number of
the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your code
here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on a
query so I put the QueryName but it seems does not work with query. Is
it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not others.
Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[Scott]

Jk,

Many thanks for your effort. The code should work but not for my
application unfortunately. Anyway appreciate your patience.

Scott

Scott,

Miahooo! how many [CATS] do you have so you must keep a database for them?

(Have a look at your code again)

Ok, this is the correct code:


If Nz(DLookup("[ID]", "qry pro", "[CASE No]='" & Me.Case_No & "' AND
[ID]<> " & Nz(Me.ID), 0), 0) <> 0 And Me.Case_No <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

This should work

Regards/JK

JK,

I followed your suggested naming for the textbox of Case No and modified
the code as below:-

Private Sub txtCaseNo_BeforeUpdate(Cancel As Integer)

If Nz(DLookup("[ID]", "qry pro", "[CATS No]='" & Me.txtCaseNo & "' AND
[ID]<> " & Nz(Me.id), 0), 0) <> 0 And Me.txtCaseNo <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If
End Sub

The past error message has been changed to:-

Complie error:
Method or data member not found.

Me.txtCaseNo was highlighted.

Scott

JK,

The property of Case No is as below:-

Name: Case No
Control Source: Case No

Scott

Scott,

no worries, glad to help.
comment below


JK,

Thanks for your continuous help. I have tried both methods but it
produced same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is
based on a query) is due to spaces used in between words. Now,
understand it can use underscore to fill the space instead.

Eventhough you have spaces in the table name still *no braket*, *no
underscores*.The quotation marks tells Access 2 things:

1. That it is a table or a query and
2. The name is *exactly* what appears between the quotation marks.



The square brackets tell Access that it is a *field*, This may well be
the source for the error you are getting.

*Dont* use the underscore for spaces for anything that is enclosed in
quoateion marks or square brakets, the underscor is used *only and
strictly* with the expression Me. for Control names.


I just want to clarify if I use the correct parameters for the code.
ID is the auto number field with primary key. Case No is a text field
that I need to control the data entry. The form is linked with a
query, not directly to a table. Any clues?

This is OK, no prolem here above

If still a problem, look at the property of "Case No" at the top and
tell me whay yoave under
1. Name
2. Control Source

Maybe there is some of confusion

Regards/JK




Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks (my
fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed by
square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e.
Me.[Case
No]. This by itself is not a problem (it should work too) but it
tells me
that your Control Name on the Form is not "Case No" and access picks
up the
name from the query.

It is a good practice to name the control in the same name as the
field
(unless you have 2 controls using the same field in the table or
query). In
that case if there are spaces in the control name, in the Me.
expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after
typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK


JK,

Thanks for your advice. The field is Case No and is of text type.
The text box of Case No is also default to Case No and the code is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] &
"' AND [ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator
or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention
that if [YourFiled] is a Date field you have to match it against
then number of the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your
code here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on
a query so I put the QueryName but it seems does not work with
query. Is it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not
others. Can someone advise some idea how to accomplish.

Thanks,

Scott

 

[JK]

No problems Scott, any time
Hopefully better luck next time

Regards
Jacob

Jk,

Many thanks for your effort. The code should work but not for my
application unfortunately. Anyway appreciate your patience.

Scott

Scott,

Miahooo! how many [CATS] do you have so you must keep a database for
them?
(Have a look at your code again)

Ok, this is the correct code:


If Nz(DLookup("[ID]", "qry pro", "[CASE No]='" & Me.Case_No & "' AND
[ID]<> " & Nz(Me.ID), 0), 0) <> 0 And Me.Case_No <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

This should work

Regards/JK

JK,

I followed your suggested naming for the textbox of Case No and modified
the code as below:-

Private Sub txtCaseNo_BeforeUpdate(Cancel As Integer)

If Nz(DLookup("[ID]", "qry pro", "[CATS No]='" & Me.txtCaseNo & "' AND
[ID]<> " & Nz(Me.id), 0), 0) <> 0 And Me.txtCaseNo <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If
End Sub

The past error message has been changed to:-

Complie error:
Method or data member not found.

Me.txtCaseNo was highlighted.

Scott

JK,

The property of Case No is as below:-

Name: Case No
Control Source: Case No

Scott

Scott,

no worries, glad to help.
comment below


JK,

Thanks for your continuous help. I have tried both methods but it
produced same error message.

Complile error: Method or data number not found!

The reason to have brackets for the query or table (now the form is
based on a query) is due to spaces used in between words. Now,
understand it can use underscore to fill the space instead.

Eventhough you have spaces in the table name still *no braket*, *no
underscores*.The quotation marks tells Access 2 things:

1. That it is a table or a query and
2. The name is *exactly* what appears between the quotation marks.



The square brackets tell Access that it is a *field*, This may well be
the source for the error you are getting.

*Dont* use the underscore for spaces for anything that is enclosed in
quoateion marks or square brakets, the underscor is used *only and
strictly* with the expression Me. for Control names.


I just want to clarify if I use the correct parameters for the code.
ID is the auto number field with primary key. Case No is a text
field that I need to control the data entry. The form is linked with
a query, not directly to a table. Any clues?

This is OK, no prolem here above

If still a problem, look at the property of "Case No" at the top and
tell me whay yoave under
1. Name
2. Control Source

Maybe there is some of confusion

Regards/JK




Thanks,

Scott

Scott,

Ooops, I made a mistake but there are other points

The error you are getting is because of a missing quotation marks
(my fault)
in:

'Nz(DLookUp("[ID],
should be ' Nz(DLookUp("[ID]",

Further, no it yours ;-0 ,the table name should *not* be enclosed
by square
brackets ([]), just the quotation marks, like this:
Nz(DLookUp("[ID]","tbl Pro","[Case No]= '" etc

I see that you have square brackets in the Me. expression, i.e.
Me.[Case
No]. This by itself is not a problem (it should work too) but it
tells me
that your Control Name on the Form is not "Case No" and access
picks up the
name from the query.

It is a good practice to name the control in the same name as the
field
(unless you have 2 controls using the same field in the table or
query). In
that case if there are spaces in the control name, in the Me.
expression
*only* the spaces are replaced by underscores ( _ ) like this:
Me.Case_No,
access will do it for you if you are using it drop down menu after
typing
'me.' (without the quotation marks)

The expression is:

The control name is "Case No" :

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.Case_No & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.Case_No <> "8888" Then

Otherwise:

If Nz(DLookup("[ID]", "tble Pro", "[Case no]='" _
& Me.[Case No] & "' AND [ID]<> " & Nz(Me.id), 0), 0) _
<> 0 And Me.[Case No] <> "8888" Then

Note: No underscore added if sqare bracket are used

Please let me know how you go

Regards/JK


JK,

Thanks for your advice. The field is Case No and is of text type.
The text box of Case No is also default to Case No and the code
is:-

Private Sub Case_No_BeforeUpdate(Cancel As Integer)

If Nz(DLookUp("[ID],"[tbl Pro]","[Case No]= '" & Me.[Case No] &
"' AND [ID] <> " Nz(Me.ID,0),0) <> 0 AND Me.[Case No] <> "8888"
Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

The error message was: Compile errors: Expected: list separator
or ).

Your futher advice is highly appreciated.

Thanks,

Scott

Scott,

It should work in either tables or queries. I forgot to mention
that if [YourFiled] is a Date field you have to match it against
then number of the date instead of the date itself eg:

In my Numeric routine:

.... ,"[YourField]=" & Clng(Me.YourField) ...


If it does not work or "YourField" is not a Date field put your
code here and lets have a look. *Tell us the field type *it is

Regards
JK

JK,

Thanks for your code. Regarding "YourTable", my form is based on
a query so I put the QueryName but it seems does not work with
query. Is it the restriction?

Scott

Untested

Assuning YouyField is numreric

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=" &
Me.YourField _
& " AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> 8888 Then
msgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

End Sub

++++++

if YouFiled is a String:

Private Sub yrField_BeforeUpdate(Cancel As Integer)

If Nz(DllookUp("[YourTable_ID],"YourTable","[YourField]=' " &
Me.YourField _
& " ' AND [YourTable_ID]<>" Nz(Me.YourTable_ID,0),0) <> 0 AND
Me.YourField <> "8888" Then
MsgBox "Opps You cant' have that!"
DoCmd.CancelEvent
End If

'(remove spaces in ' " and " ')

End Sub

Regards/JK


I have one field to accept one duplicate value, 8888 but not
others. Can someone advise some idea how to accomplish.

Thanks,

Scott