After Update_Event Pocedure

  • Thread starter Thread starter Roger Bell
  • Start date Start date
R

Roger Bell

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
 
For text fields you need to surround your field with single quotes

DCount("*", "[Main Table]", "[Text Field]= '" & Me.Text_Field & "'") > 0
 
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 said:
For text fields you need to surround your field with single quotes

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

Roger Bell said:
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
Click to expand...
Click to expand...
 
Back
Top