After Update_Event Pocedure

Just to clarify, Dennins, Jet SQL will accept either single or double quotes;
however, there can be a danger using single quotes. That is in the case
where the data may contain a single quote. This most common situation is
name fields where you may run into an O'Reilly, for example.

I know that trying to get the right number of quotes when using double
quotes is daunting. I had a really hard time of it myself. I tried some
functions I downloaded and some constants, but nothing seemed to work in all
cases and frankly, obsfucated the code. So I came up with my own way of
doing it. First, the rule is that when you want to put a double quote in a
string, you use two double quotes ""
But then, you still have to use quotes to delimit the string and that is
where I would almost always get it wrong. When I got it right, it was purely
accidental. So here is what I did. I would first write the statement using
single quotes:

DCount("*", "[Main Table]", "[Text Field]= '" & Me.Text_Field & "'") > 0

Now since what I really want is a double quote everywhere there is a single
quote, I would just go back and replace all the single quotes with two double
quotes.

DCount("*", "[Main Table]", "[Text Field]= """ & Me.Text_Field & """") > 0

After a while, you don't even have to go through the first step. It becomes
natural where the quotes go.

And a note to the OP. When you only want to test for the existance of a
value in a field, the DLookup is usually much faster. The DCount has to
examine each value in the field, create an internal varialbe, and add 1 to it
each time it finds a match. The DLookup only looks for the value and stops
when it finds the first match. I would suggest, instead:

If Not IsNull(DLookup("[TextField]", "[Main Table]", "[Text Field]= """
& Me.Text_Field & """")) Then
'The Value was Found
Else
'The Value is not in the field
End If


--
Dave Hargis, Microsoft Access MVP


"Dennis" wrote:

> For text fields you need to surround your field with single quotes
>
> DCount("*", "[Main Table]", "[Text Field]= '" & Me.Text_Field & "'") > 0
>
> "Roger Bell" wrote:
>
> > I have a field in a Data Form called "Section Name" and this is a Text Field
> > where the user enters for example 1A, 1B etc.
> >
> > I would like an After Update event Procedure which prevents the user from
> > entering duplicates with an appropriate error message. I have been able to
> > do this with a number field but not with a text field.
> >
> > This is the code I used for a number field:
> > Private Sub Envelope_Number_AfterUpdate()
> > If IsNull(Me.Envelope_Number) = True Then
> > 'Do nothing NUll is acceptable
> > DoCmd.RunCommand acCmdSaveRecord
> > [Combo561].Requery
> >
> > ElseIf DCount("*", "[Main Table]", "[Envelope Number]=" &
> > Me.Envelope_Number) > 0 Then
> > MsgBox "SORRY, THIS NUMBER IS ALREADY IN USE. IF YOU WISH TO ALLOCATE THIS
> > NUMBER, YOU MUST REMOVE THE NUMBER FROM THE MEMBER TO WHOM IT IS CURRENTLY
> > ALLOCATED. PLEASE NOTE THAT YOU MUST NOT RE-ALLOCATE ENVELOPE NUMBERS DURING
> > THE PLANNED GIVING CYCLE AS THIS WILL AFFECT THE FINANCIAL REPORTING",
> > vbOKOnly, "ERROR! MESSAGE"
> > Cancel = True
> > Me.[Envelope Number] = Null
> > DoCmd.RunCommand acCmdSaveRecord
> > End If
> > End Sub
> >
> > Can the above code be modified for a text field?
> >
> > Thanks for any help
> >
> >