After update

[Jeanke]

Hello

I have following code behind the after update of a button.

Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Problem is that it will indeed give me the pop up saying that the order was
not found, but I click OK, then it will open a form anyway in stead of going
back to the field where I need to enter the number.
How do I solve this ?

 

[tina]

well, first, you can't cancel the AfterUpdate event. as the name implies,
the event runs *after* the update has occurred. and you'll notice that the
procedure does not contain a Cancel argument. next, there is nothing in the
code you posted that opens a form. i also notice that the If statement as
posted is incomplete - there's no End If line. as well as leaving it out,
did you leave out an Else clause that contains the OpenForm action? in order
to trouble-shoot your code effectively, we need to see the *complete*
procedure.

hth

 

[Chris]

Jenkie

We need more info to sort this for you, for example what is the button
trying to do? What opens the form you are concerned about, is it on the oen
event, or is there another function that opens it, or is there more code in
this routine?

Setting the Cancel = True will have no effect unless you have set it in a
Event that has Cancel as one of its parameters such as the beforeupdate,
where you can use the Cancel to stop the update happening. If you are trying
to do this elsewhere then you will have to code for it (setting a global
variable in the routine to False for example and then checking it before you
invoke the command to open the next form

 

[Jeanke]

Well, I have an unbound text box in which I would need to enter an order
number. After that, I have to press a button and button needs to check 2
things before moving to the display order form :
1. is the order number 10 digits long ?
Private Sub Text0_BeforeUpdate(Cancel As Integer)

If Len(Me.Text0) <> 10 Or Not IsNumeric(Me.Text0) Then
MsgBox "Must be 10 digits", vbExclamation
Cancel = True
Exit Sub
End If

End Sub
2. check whether the order exists in the order table and if not go back to
the pop-up where you need to enter the order number
Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "Order entry form"

stlinkcriteria = "[SAPnr]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stdocname, , , stlinkcriteria
DoCmd.Close acForm, "change order prompt", acSaveNo

last part is the code to go to the form.
But once the button is pressed, I get the message that the order does not
exist, but it still moves on to the form with a blank record.

tia

Jenkie

We need more info to sort this for you, for example what is the button
trying to do? What opens the form you are concerned about, is it on the oen
event, or is there another function that opens it, or is there more code in
this routine?

Setting the Cancel = True will have no effect unless you have set it in a
Event that has Cancel as one of its parameters such as the beforeupdate,
where you can use the Cancel to stop the update happening. If you are trying
to do this elsewhere then you will have to code for it (setting a global
variable in the routine to False for example and then checking it before you
invoke the command to open the next form

--
HTH

Chris

Hello

I have following code behind the after update of a button.

Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Problem is that it will indeed give me the pop up saying that the order was
not found, but I click OK, then it will open a form anyway in stead of going
back to the field where I need to enter the number.
How do I solve this ?

 

[Chris]

Jeanke

I am assuming that your routine calls firstly the Text0_BeforeUpdate and
then Text0_AfterUpdate?

If so, you will get the problem you are describing as you are not telling
your calling routine that there is a problem and not to open the next form.
Setting the Cancel = True in the second routine will not achieve anything as
the AfterUpdate event does not support it.

If you want to do it this way, then I would suggest the following:

1. Remove the code you have in the AfterUpdate Event and place it in a
function that returns a boolean value.

In the code you have behind the button, remove the first check (as this will
be invoked automatically when you try to leave the field and setting CANCEL =
TRUE will stop the user leaving the field until they have entered 10 digits
(be aware that as you have it coded, once they enter something in the field
they will not be able to exit until they have entered 10 digits)

Then call your new function and decide whether to open the form based on the
response. Eg.

If CheckOrderExists then
code to open order form
endif

Function CheckOrderExists as Boolean

If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
CheckOrderExists = False
me.text0.setcursor ' set cursor back to this field
Else
CheckOrderExists = True
End If

End Function
--
HTH

Chris

Well, I have an unbound text box in which I would need to enter an order
number. After that, I have to press a button and button needs to check 2
things before moving to the display order form :
1. is the order number 10 digits long ?
Private Sub Text0_BeforeUpdate(Cancel As Integer)

If Len(Me.Text0) <> 10 Or Not IsNumeric(Me.Text0) Then
MsgBox "Must be 10 digits", vbExclamation
Cancel = True
Exit Sub
End If

End Sub
2. check whether the order exists in the order table and if not go back to
the pop-up where you need to enter the order number
Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "Order entry form"

stlinkcriteria = "[SAPnr]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stdocname, , , stlinkcriteria
DoCmd.Close acForm, "change order prompt", acSaveNo

last part is the code to go to the form.
But once the button is pressed, I get the message that the order does not
exist, but it still moves on to the form with a blank record.

tia

Jenkie

We need more info to sort this for you, for example what is the button
trying to do? What opens the form you are concerned about, is it on the oen
event, or is there another function that opens it, or is there more code in
this routine?

Setting the Cancel = True will have no effect unless you have set it in a
Event that has Cancel as one of its parameters such as the beforeupdate,
where you can use the Cancel to stop the update happening. If you are trying
to do this elsewhere then you will have to code for it (setting a global
variable in the routine to False for example and then checking it before you
invoke the command to open the next form

--
HTH

Chris

Hello

I have following code behind the after update of a button.

Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Problem is that it will indeed give me the pop up saying that the order was
not found, but I click OK, then it will open a form anyway in stead of going
back to the field where I need to enter the number.
How do I solve this ?

 

[tina]

okay. you say that you enter an order number in a textbox, and then "have to
press a button to check two things". that doesn't make sense because the
first thing you listed is validation code that *automatically* runs on the
textbox's BeforeUpdate event. don't get me wrong, the code itself, and its'
placement, is fine - but it has nothing to do with any code that may be
running from a command button on your form.

same issue with the second thing you listed: the code runs on the textbox's
AfterUpdate event (where it does no good, as Chris and i have already
noted). it has nothing to do with any code that may be running from a
command button on your form. the other problem with the code you posted for
#2 is that the "open form" code you posted is *outside of the sub
procedure*. (it comes *after* the End Sub line.) this doesn't make sense to
me, because code that's outside a procedure will not run (not to mention
causing an error during compile).

suggest you change the code in the textbox's BeforeUpdate event to:

Private Sub Text0_BeforeUpdate(Cancel As Integer)

If Len(Me.Text0) <> 10 Or Not IsNumeric(Me.Text0) Then
MsgBox "Must be 10 digits", vbExclamation
Cancel = True
ElseIf IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, _
"PO number not found"
Cancel = True
Else
Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "Order entry form"

stlinkcriteria = "[SAPnr]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stdocname, , , stlinkcriteria
DoCmd.Close acForm, "change order prompt", acSaveNo
End If

End Sub

if you *are* clicking a command button on your form, that you expect to do
something related to checking the PO number and opening the form, suggest
you post it here, so we can figure out how it's involved in all this.

btw, if the SAPnr field, in the Order data table, is a Number data type,
then you need to change both the DLookup() criteria and the stlinkcriteria,
as

DLookup("[SAPnr]", "Order data", "[SAPnr] = " & Me.Text0)
stlinkcriteria = "[SAPnr]=" & Me![Text0]

hth

Well, I have an unbound text box in which I would need to enter an order
number. After that, I have to press a button and button needs to check 2
things before moving to the display order form :
1. is the order number 10 digits long ?
Private Sub Text0_BeforeUpdate(Cancel As Integer)

If Len(Me.Text0) <> 10 Or Not IsNumeric(Me.Text0) Then
MsgBox "Must be 10 digits", vbExclamation
Cancel = True
Exit Sub
End If

End Sub
2. check whether the order exists in the order table and if not go back to
the pop-up where you need to enter the order number
Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "Order entry form"

stlinkcriteria = "[SAPnr]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stdocname, , , stlinkcriteria
DoCmd.Close acForm, "change order prompt", acSaveNo

last part is the code to go to the form.
But once the button is pressed, I get the message that the order does not
exist, but it still moves on to the form with a blank record.

tia

Jenkie

We need more info to sort this for you, for example what is the button
trying to do? What opens the form you are concerned about, is it on the oen
event, or is there another function that opens it, or is there more code in
this routine?

Setting the Cancel = True will have no effect unless you have set it in a
Event that has Cancel as one of its parameters such as the beforeupdate,
where you can use the Cancel to stop the update happening. If you are trying
to do this elsewhere then you will have to code for it (setting a global
variable in the routine to False for example and then checking it before you
invoke the command to open the next form

--
HTH

Chris

Hello

I have following code behind the after update of a button.

Private Sub Text0_AfterUpdate()
If IsNull(DLookup("[SAPnr]", "Order data", "[SAPnr] = '" & _
Me.Text0 & "'")) Then
MsgBox "Did Not Find PO " & Me.Text0, vbExclamation, "PO number not
found"
Cancel = True
Exit Sub
End If

End Sub

Problem is that it will indeed give me the pop up saying that the order was
not found, but I click OK, then it will open a form anyway in stead of going
back to the field where I need to enter the number.
How do I solve this ?