After Update Event Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
 
Use Nz function to replace a Null value with an empty string:

Private Sub Envelope_Number_AfterUpdate()
If DCount("*", "[Main Table]", "[Envelope Number]=" &
Nz(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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>



Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
 
Thanks for that Ken,
Have replaced with the following, but still getting the same error message.
Sorry to be a Pain.
Private Sub Envelope_Number_AfterUpdate()
If DCount("*", "[Main Table]", "[Envelope Number]=" & Nz(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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub


Ken Snell (MVP) said:
Use Nz function to replace a Null value with an empty string:

Private Sub Envelope_Number_AfterUpdate()
If DCount("*", "[Main Table]", "[Envelope Number]=" &
Nz(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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>



Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
Click to expand...
 
What do you want to happen if the Envelope number is null?

Private Sub Envelope_Number_AfterUpdate()
If IsNull(Me.EnvelopeNumber) = True Then
'Do nothing Null is acceptable

ElseIf DCount("*", "[Main Table]", "[Envelope Number]=" &
Me.Envelope_Number) > 0 Then
MsgBox "SORRY, THIS NUMBER IS ALREADY IN USE. " _
& vbcrlf & vbcrlf & "If you wish to allocate this number, " _
& "you MUST remove the number from the member to " _
& "whom it is currently allocated." _
& vbcrlf & vbcrlf & "Please note that you must not re-allocate
envelope numbers " _

& "during the planned giving cycle"", vbOKOnly, "ERROR! MESSAGE"


Cancel = True
Me.[Envelope Number] = Null


DoCmd.RunCommand acCmdSaveRecord
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
 
Thanks John for your help.
What I require is when the user enters a Duplicate Envelope Number, a
message appears to advse them of this. This is working.
However, sometimes they need to Remove the Envelope number completely, and
when the user deletes the Envelope Number, this error message appears:
Run Time Error '3075' Syntax error (missing operator) in query expression
'[Envelope Number]=',

What I would like is that any Envelope Number can be deleted as required.

I appreciate your help.

John Spencer said:
What do you want to happen if the Envelope number is null?

Private Sub Envelope_Number_AfterUpdate()
If IsNull(Me.EnvelopeNumber) = True Then
'Do nothing Null is acceptable

ElseIf DCount("*", "[Main Table]", "[Envelope Number]=" &
Me.Envelope_Number) > 0 Then
MsgBox "SORRY, THIS NUMBER IS ALREADY IN USE. " _
& vbcrlf & vbcrlf & "If you wish to allocate this number, " _
& "you MUST remove the number from the member to " _
& "whom it is currently allocated." _
& vbcrlf & vbcrlf & "Please note that you must not re-allocate
envelope numbers " _

& "during the planned giving cycle"", vbOKOnly, "ERROR! MESSAGE"


Cancel = True
Me.[Envelope Number] = Null


DoCmd.RunCommand acCmdSaveRecord
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
Click to expand...
 
What data type is Envelope Number field/control -- numeric? or text?

I concur with John's suggestion elsethread about skipping the code block if
the value in Envelope Number has no value. Except I usually test for length
of value insteaed of Null (code below assumes that Envelope Number is
numeric data type):

If Len(Me.Envelope_Number.Value & "") > 0 Then
If DCount("*", "[Main Table]", "[Envelope Number]=" &
Nz(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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Roger Bell said:
Thanks for that Ken,
Have replaced with the following, but still getting the same error
message.
Sorry to be a Pain.
Private Sub Envelope_Number_AfterUpdate()
If DCount("*", "[Main Table]", "[Envelope Number]=" &
Nz(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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub


Ken Snell (MVP) said:
Use Nz function to replace a Null value with an empty string:

Private Sub Envelope_Number_AfterUpdate()
If DCount("*", "[Main Table]", "[Envelope Number]=" &
Nz(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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>



Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing
Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope
Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
Click to expand...
Click to expand...
 
Did you try the modification I suggested?

Did it fail? With the same error?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Roger Bell said:
Thanks John for your help.
What I require is when the user enters a Duplicate Envelope Number, a
message appears to advse them of this. This is working.
However, sometimes they need to Remove the Envelope number completely, and
when the user deletes the Envelope Number, this error message appears:
Run Time Error '3075' Syntax error (missing operator) in query expression
'[Envelope Number]=',

What I would like is that any Envelope Number can be deleted as required.

I appreciate your help.

John Spencer said:
What do you want to happen if the Envelope number is null?

Private Sub Envelope_Number_AfterUpdate()
If IsNull(Me.EnvelopeNumber) = True Then
'Do nothing Null is acceptable

ElseIf DCount("*", "[Main Table]", "[Envelope Number]=" &
Me.Envelope_Number) > 0 Then
MsgBox "SORRY, THIS NUMBER IS ALREADY IN USE. " _
& vbcrlf & vbcrlf & "If you wish to allocate this number, " _
& "you MUST remove the number from the member to " _
& "whom it is currently allocated." _
& vbcrlf & vbcrlf & "Please note that you must not re-allocate
envelope numbers " _

& "during the planned giving cycle"", vbOKOnly, "ERROR!
MESSAGE"


Cancel = True
Me.[Envelope Number] = Null


DoCmd.RunCommand acCmdSaveRecord
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing
Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope
Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
Click to expand...
Click to expand...
 
Cancel = True

Does nothing in a AfterUpdate event (the Update has already happened, so
there isn't anything to Cancel)

However, it would have meaning in a BeforeUpdate event...

HTH,


Roger Bell said:
I have an After update Event procedure as follows:

Private Sub Envelope_Number_AfterUpdate()
If 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", vbOKOnly, "ERROR! MESSAGE"
Cancel = True
Me.[Envelope Number] = Null
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

This works fine, except when the user tries to deletes an existing Number,
the following message appears:

Run Time error '3075'
Syntax error (Missing operator) in query expression '[Envelope Number]=',

Is there a way I can adjust the code to prevent this?

Thanks for any help

"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

After Update_Event Pocedure 2
Go To Command 9
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Go To Next Record 1
Update Query 1
Word 2007 - Error Message for printing envelopes 2
Error Number 2645 1
Combo Box 2

Back
Top