Check if PartNumber (calculated) already exists

[DieterVisser]

Dear,

I store PartNumbers in a table called PartT. these Partnumbers are calculated from different fields (ex: EBAPOL-M25-00024-02)(system=M / XX_Number = 25 / Sequence = 00024 / Subsequence=02). All the different fields need to be filled in a form for new records.

I would like Access to check, after the information is filled in, whether the (caculated) partnumber already exists in the PartT table.

I created a button that saves the new reccord and I tried to at a Macro "Dcount()":

If
 DCount("PartNumber";"[PartT]";"PartNumber= '" & [Formulieren]![NewPartF]![PartNumber])>0
Then MsgBox(PartNumber already exists)
End If
 

But after testing and trying to add a existing Partnumber, No Msgbox shows up.

Can you help me?

Thanks!

[zmbd]

Your code as is - but stepped

If
  DCount("PartNumber"; _
    "[PartT]";
    "PartNumber= '" & _
        [Formulieren]![NewPartF]![PartNumber])
     >0
 Then 
    MsgBox(PartNumber already exists)
 End If 

Honestly, I am surprised this didn't toss all sorts of compiler errors - it should have, if not, then we need to know what programing language you are using.

Ok, to pull this apart more:

Is [PartNumber] numeric or text?

If text then you need to close the string thus: " ' ":

"PartNumber= '" & _
    [Formulieren]![NewPartF]![PartNumber] & _
       "'"

If numeric only, then remove the " ' " from the line

"PartNumber= " & _
    [Formulieren]![NewPartF]![PartNumber]

Next the semicolon is not used in this function, thus you should have for text:

DCount("PartNumber", _
    "[PartT]", 
    "PartNumber= '" & _
        [Formulieren]![NewPartF]![PartNumber]) & _
       "'"

Finally, I highly advise that you build your criteria string first:

'(...other code...)
DIM zWHERE as String
'(...other code...)
zWHERE = "PartNumber= '" & _
    [Formulieren]![NewPartF]![PartNumber] & _
       "'"
'
DCount("PartNumber", _
    "[PartT]", _
    zWHERE)
 
'(...other code...)

Finally the comparator (>) need not be used. If DCOUNT() returns anything except an error or zero, the true condition will be satisfied.

Next

MsgBox(PartNumber already exists)

Is not the correct syntax.

Try:

MsgBox "PartNumber already exists"

If you have the OPTION EXPLICIT set it's a simple catch to run the debug/compiler to find these types of syntax errors.

Finally, it could be that your domain function is also returning zero; however, I don't think the runtime reached that point.

If you will correct your code and repost we can tweak it more from that point.

:)

[DieterVisser]

Thanks A lot zmbd! The new code works!