how do i reset dcount?

[thewiseguy]

i'm using dcount for the first time to make sure that an ID Number cannot be repeated. however, after it occurs once, it will stop any further valid ID Numbers being entered. i'm guessing that i have to reset Dcount, but i am unsure as to how. i'm sure it's really easy to do but i'm a bit new to vb script.

code:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If DCount("[IDNumber]", "tbl: XQStock", "[IDNumber]='" & Me![IDNumber] & "'") Then
MsgBox "This ID Number already exists." & vbCrLf & "Please enter a different number.", vbExclamation, "Existing ID"
Cancel = True
Me.Undo
End If
End Sub

cheers

 

[Mile-O]

i'm a bit new to vb script.

It's Visual Basic for Applications (VBA) you are using and not VB Script. VB Script is something different - it's a scripting language for web pages.

Is IDNumber actually a number or is it really a text field?

 

[thewiseguy]

ok - vba it is

if i were to make IDNumber a number then i'm guessing i need to put a # instead of a '

 

[Mile-O]

No, the # is used to denote a date. Numeric values don't require any sort of parenthesis.

So, IDNumber I'm guessing is a texbox in which the user types in a value. Is it a text field at the table level or a numeric field at the table level?

 

[thewiseguy]

it's a text field in the main form. it's also the primary key that links all the tables together.

 

[Mile-O]

I'd advise against a text Primary Key and using an Autonumber - they take up more space, are slower when querying, etc.

Change your code to this below:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
    Dim lngTest As Long
    lngTest = DCount("[IDNumber]", "tbl: XQStock", "[IDNumber] = """ & Me.[IDNumber] & """")
    MsgBox lngTest
    If DCount("[IDNumber]", "tbl: XQStock", "[IDNumber] = """ & Me.[IDNumber] & """") > 0 Then
        MsgBox "This ID Number already exists." & vbCrLf & "Please enter a different number.", vbExclamation, "Existing ID"
        Cancel = True
        Me.Undo
    End If
End Sub

What's the value of the message box that pops up?

Or, is the database small enough to upload?

 

[thewiseguy]

it comes up as 7
i'm afraid i am not authroised to upload the db.

 

[Mile-O]

Well, if it's coming up with seven then that means you have seven records in your table with the IDNumber you are typing in. That's why you are getting the This ID Number already exists.

 

[thewiseguy]

Well, if it's coming up with seven then that means you have seven records in your table with the IDNumber you are typing in. That's why you are getting the This ID Number already exists.

but all these records have different ID Numbers and it doesn't matter what number I enter into the field, it still comes up with the messege.

eg if those seven records have ID Numbers as:
100, 101, ... 106

i could enter 987654321 and it will still seems to think that it's already a number used. that's why i initially thought that the dcount would have to reset somewhere.

 

[Mile-O]

Try compacting and repairing as I just tried this and my example works fine.

 

[thewiseguy]

thank you for the help and example mile-o-phile - i really appreciate it.
looking at your example i realised that my criteria was wrong.
it's all working like a charm now, thx to you!