Discussion Locked

Access 2000 - message boxes

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

The first field in my DB is a unique number, with no duplicates allowed (set in props.). But, the message that says "the record cant be saved as it will create duplicates", appears when the user has filled in 50 or more fields. Therefore I would like to attach a message box to the number field in question, to say "If [field]= *the same as a previous record* then flag a message box, but as you can see i'm unsure of the syntax!
Can anyone help me please...

7 replies

by john_wills

Attach a test in Visual Basic by using the On Lost Focus event under the Events tab of the property box for the first field. In the subroutine so created, write something like
if Dcount("[FirstField]","TableName", "[FirstField]='" & me.FirstField &"'")> 0 _
then
msgbox(whatever)
endif

by sej

I have typed this into the onlost focus event sub. If DCount("[lab no]", "data", "[lab no]='" & Me.[lab no] & "'") > 0 _
Then
MsgBox (whatever) and got a runtime error 3464 data type mismatch in criteria expression ???

by john_wills

What I gave you last time was under the assumption that FirstField was a text. If it's a number, do the same without the apostrophes(or the quotation mark pair surrounding the second apostrophe).

by sej

have tried this... If DCount("[lab no]", "data", "[lab no]=" & me.lab no & ) > 0_
Then
and get "Compile error: Expected: list separator or )"

by john_wills

Because you left the ampersand in: think of the meaning of what you are writing. That is not always easy in a new language, of course. The compiler expects something it can handle as a string after every "&", but you had a superfluous and therefore damaging "&". The third parameter of DCount is a string with the same format as the post-WHERE clause of an SQL SELECT statement. The parameter is composed of several parts, strung together by "&". The "&" unites separate items into a string. When the value being compared is a text, the text must be surrounded by apostrophes, so to put the second apostrophe in one needs an ampersand and a quotation of the second ampersand. When the value being compared is a number no apostrohe should be presentbefore or after, and hence no quotation and no linking ampersand after.

This discussion is closed to new replies.