Primary key cannot take NuLL Value

[fastmike]

hey guys. i have a field by the name of ISSUE. i am using this afterupdate event procedure
which is as follow:

Private Sub ISSUE_AfterUpdate()
If Not IsNull(ISSUE) Then
ISSUE = Format(ISSUE, "000000")
End If
End Sub

if a user enter like (23) in my ISSUE field my procedure will run and it will replace
the number by (000023) to make it a six digit. i wanted to create a primary key so then a user cannot enter the same number again
or cannot duplicate a record but i cannot because primary key cannot take a null value. Can you all tell me anything to add in my procedure so that once the number is entered like (23)
another user cannot enter (23) again in the record.
Thanks

 

[Banana]

1) I'd want to rename the textbox or whatever it is to something like txtISSUE so Access dont' get confused between textbox named ISSUE and field named ISSUE.

2) Is this an autonumber or integer data type?

3) I suspect that Format syntax is errornous, but I don't know for sure. Be sure to check help file that you have it all right.

 

[boblarson]

I think you may want to try BEFORE Update instead of After Update.

 

[fastmike]

i tried it in beforeupdate but it is giving me an error:
(The macro or function set to the before update or validation rule
property for this field is precenting ms office access from saving the data in the field)
my data type for my ISSUE field is Text. it is not autonumber or integer.

 

[The_Doc_Man]

If the field is going to be your prime key, you are right that it will not allow nulls. BUT that means that you must take steps to assure that it won't be null. If the field has any meaning other than "just a number to make the record unique" then you cannot use autonumber. Which means you must build something in which you place a value in the record beforehand. You MUST find a way to do a BEFORE_UPDATE because making a change in a field AFTER_UPDATE will cause an AFTER_UPDATE event loop. You don't want that.

 

[fastmike]

hey thanks Doc man. This really does not make no sense but it worked for me since i was in a hurry. i opened a new database and added a primary key in that issue field with the same onclick procedure and it worked. did not give me no error of null value or something like that. Wow i am happy but thanks alot for all your help. These forums really helped me out alot and have learned about access alot. spcial thanks to RURAL GUY(u'r the man) and KEITH.
Thanks