3022 error + null value error

[mseo]

hi,
I have developed a form for add new category using Ms-Access 2003 the first problem for me I got the error 3022 for no duplicating values and i have solved this one using this code:

Private Sub CategoryName_BeforeUpdate(Cancel As Integer)
 
Dim Answer As Variant
Answer = DLookup("[CategoryName]", "tbl_Category", "[CategoryName] = '" & Me.CategoryName & "'")
If Not IsNull(Answer) Then
MsgBox "Hi-Tech sysadmin reports that Duplicate Category Name Found, " & "Please press ok and try again. ", vbCritical + vbOKOnly + vbDefaultButton1, "Hi-Tech sysadmin, Duplicate Category Found"
Cancel = True
Me.CategoryName.Undo
End If
End Sub

but after I used this i found that I can get record with its ID number but the category name is null
so I set the categoryname field to null
I got the same error of no duplicating when I use save plus the error of null valued how can make this form for adding new category integrated
it is now 12 days to trap this error
so if anyone could provide any help for solving this please submit
thank you in advance for any help you may provide me

--------------------------------------------------------------------------------

[Delerna]

http://bytes.com/topic/visual-basic/...ry#post3520847

Identical question in 2 forums

[Delerna]

Really not sure what your question is....but

Do you have a key field (possibly categoryname) in the table and you are trying to insert a record with a value for that field that is already in the table ?

Duplicate values are not allowed in key fields


PS
Make sure when you post code that you use the code tags provided at the top of the posting window.

And if you can try and make it easily readable for us that would help us to help you

[Delerna]

something like this

Private Sub CategoryName_BeforeUpdate(Cancel As Integer)
   Dim Answer As Variant
   Answer = DLookup("[CategoryName]", _
                    "tbl_Category", _
                    "[CategoryName] = '" & Me.CategoryName & "'")
   If Not IsNull(Answer) Then
      MsgBox "Hi-Tech sysadmin reports that Duplicate Category Name  Found, " _
             & "Please press ok and try again. ",  _
             vbCritical + vbOKOnly + vbDefaultButton1, _
             "Hi-Tech sysadmin, Duplicate Category Found"
      Cancel = True
      Me.CategoryName.Undo
   End If
End Sub
 
 

You should write your own code something like that in your projects.
When trying to debug problems, readabilty can make all the difference
between a speedy fix and a hair pulling experience.

After reading the above code I see that you are trying to stop entry of duplicate categorynames, so I must have miss-understood your question with my first suggestion.

Don't forget, you can put breakpoints on each line of code.
That way, you can step through each line of code and watch what is happening.
Always helps me to find hard to find problems.

[NeoPa]

Let us know how you get on with Delerna's last post. In case of interest, check out Debugging in VBA.

[missinglinq]

mseo, you also need to understand that Visual Basic and Visual Basic for Applications are not the same thing at all, despite their similar names and lineage! Many functions are interchangeable between the two but many, many things are not, even though they share identical names.

Welcome to Bytes! ;0)>