VBA Code still gives me an error (1 Viewer)

[jober]

Hello all, i have been fighting with this for a bit and just thought that id reach out. Anyway my code is as follows;

Private Sub RCB_Number_BeforeUpdate(cancel As Integer)

Dim Answer As Variant

Answer = DLookup("[RCB_Number]", "tblRCB", "[RCB_Number] = '" & Me.RCB_Number & "'")
If Not IsNull(Answer) Then
MsgBox "RCB Number already exists please enter a new number", , "Duplicate Value"
cancel = True
Me.RCB_Number.Undo
Else:
End If
End Sub

My problem that im having is when i enable the Active content, it always "no matter what i put in the text box says there is duplicate data." Is there something that i missing in my code? And if i dont enable to content of course the VBA dont run. Any help with this would be great!

:banghead:

 

[jdraw]

You are comparing a field called RCB_Number to a string/text.
You enclose string/text values with quotes. You do not need anything with numbers

Many people would use DCount() in BeforeUpdate to avoid creating duplicates.
If Dcount("field","table","criteria") >0 then that value for field already exists.

Here are some examples.

 

[Never Hide]

To elaborate on jdraw's answer

If DCount("RCB_Number","tblRCB","[RCB_Number=" & Me.RCB_Number)>0 Then
 MsgBox "RCB Number already exists please enter a new number", , "Duplicate Value"
 Canclet=True
 Me.RCB_Number.Undo
End If

I'm taking as a fact that RCB_Number's value is a number

 

[jober]

Yes its a number. So the Dlookup is only used for string/text? Let me put the code in and see is i still get the error message when i enable the activeX.

 

[Never Hide]

What jdraw meant in that the way you use the DLookup is used to compare strings.

You can search for the correct syntax to use with DLookup depending of the type of the field you want

 

[jober]

Ok put the code in that way but still give me the same error.Let me explain, when i enable the content i get the message box error of "RCB Number already exists please enter a new number"
And when i enter i brand new number will say 1 never been entered before still give me the error "RCB Number already exists please enter a new number". But oe has never been entered. So how can this be? Is something clicked or locked somewhere?

 

[Never Hide]

Can you give us a database copy so we can have a look? This could be a problem in your tables' relationships or some setting in a table or any other number of reasons and without having a look at it we could be going on a goose chase

 

[jdraw]

You may have to put your database into a zip file because of your post count.
Also, could you tell us more about

when i enable the content

 

[jober]

Ok here it is. Well at least it says it attached.

 

[Never Hide]

Ok I've looked at you database.
A few things:
It's not advised to use spaces in the name of fields, name of form controls (text boxes etc)
The code you posted wasn't working because the name of the field in your table is
"RCB Number" and you used "RCB_Number" and the same is for your text box. The reason it would work is that you've stored RCB Number as text.
I'd also advise you to reconsider your design and use an Auto Number for your tables ID and not allow the user to set it, you can avoid many loop holes that way

 

[jdraw]

Having looked at your tables, my guess is your table structure may be part of the problem.
Your tables contain fields with names like Field1, field2, field3 ... which is a red flag suggesting a normalization issue. May not be in your case, but quite likely.

I don't use macros so cannot offer suggestions.

 

[jober]

Yea this was the first one i did and the more i do and see i should redo alot over.As for the user to enter the numbers, that will be me and and me co-worker. But as soon as i set this up this is all its going to do. This just saves me and my parther like 5 to ten mins because the guys before me used to hand write everything!!! I have a few more datebases i have to do but will go bout them a difernemt way.

 

[jober]

So i just removed all the spaces in the table and code but as soon as i open Access i still get the Security Warning message " Some active content has been disabled . Clicick for more details." As soon as i hit that, no "matter what number i stick in the text box its a duplicate.