Cancel event "not in list" if before update is not true

[Guest]

Hi,

i use this code in the Not in list event, but i want to cancel this if the before update event failes (checks that the text box is 9 digits of text). Any ideas

This is my code in the not in list event

Private Sub Organisasjonsnummer_NotInList(NewData As String, Response As Integer
'Formål- hvis verdien er lik 9 tegn og ikke finnes i listen fra før så skal den legges til som ny post
'selskaps tabellen og kontrollmelding oppdateres med den riktige verdie
Private Sub Organisasjonsnummer_NotInList(NewData As String, Response As Integer
Dim strSQL As String, X As Intege
Dim FindCriteria As Strin

X = MsgBox("Dette selskapet finnes ikke fra før - vil du legge det til som nytt selskap?", vbYesNo
If Len("NewData" & "") <> 9 The
Cancel = vbN
End I
If X = vbYes The
strSQL = "Insert into Selskap ([organisasjonsnummer]) values ('" & NewData & "')
'MsgBox strsq
CurrentDb.Execute strSQL, dbFailOnErro
FindCriteria = Me!Organisasjonsnummer.Tex

DoCmd.OpenForm "Selskap", , , , , , FindCriteri

Response = acDataErrAdde
Els
Response = acDataErrContinu
End I

End Su

And in the before update

Private Sub Organisasjonsnummer_BeforeUpdate(Cancel As Integer
If Len(Me.Organisasjonsnummer & "") <> 9 The

MsgBox "Du må skrive inn allle ni siffer i organisasjonsnummeret. Hvis du ikke har alle ni må du bruke feltet for ukjent objekt istedenfor!

Cancel = Tru

End I

End Su

 

[Dirk Goldgar]

Hi,

i use this code in the Not in list event, but i want to cancel this
if the before update event failes (checks that the text box is 9
digits of text). Any ideas?

This is my code in the not in list event:

Private Sub Organisasjonsnummer_NotInList(NewData As String, Response
As Integer) 'Formål- hvis verdien er lik 9 tegn og ikke finnes i
listen fra før så skal den legges til som ny post i 'selskaps
tabellen og kontrollmelding oppdateres med den riktige verdien
Private Sub Organisasjonsnummer_NotInList(NewData As String, Response
As Integer)
Dim strSQL As String, X As Integer
Dim FindCriteria As String

X = MsgBox("Dette selskapet finnes ikke fra før - vil du legge
det til som nytt selskap?", vbYesNo) If Len("NewData" & "") <> 9
Then Cancel = vbNo
End If
If X = vbYes Then
strSQL = "Insert into Selskap ([organisasjonsnummer]) values
('" & NewData & "')" 'MsgBox strsql
CurrentDb.Execute strSQL, dbFailOnError
FindCriteria = Me!Organisasjonsnummer.Text

DoCmd.OpenForm "Selskap", , , , , , FindCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

And in the before update:

Private Sub Organisasjonsnummer_BeforeUpdate(Cancel As Integer)
If Len(Me.Organisasjonsnummer & "") <> 9 Then

MsgBox "Du må skrive inn allle ni siffer i
organisasjonsnummeret. Hvis du ikke har alle ni må du bruke feltet
for ukjent objekt istedenfor!"

Cancel = True

End If


End Sub

The NotInList event fires before the BeforeUpdate event, so you can't
really wait to find out if that event has issued a Cancel. The simplest
thing to do is to make your own test in the NotInList event. I don't
really see why you would have a BeforeUpdate event test for data
validation in a combo box anyway, as the NotInList event does all the
validation you need. If the value entered is not in the list, either
it's not valid or you add it to the list, thereby asserting its
validity.

There's an error in your NotInList event procedure, at the point where
you appear to be trying to test for a NewData length other than 9. You
wrote:

If Len("NewData" & "") <> 9 Then
Cancel = vbNo
End If

If I am right about your intentions, that should be:

If Len(NewData) <> 9 Then
X = vbNo
MsgBox _
"Du må skrive inn allle ni siffer i " & _
"organisasjonsnummeret. Hvis du ikke har alle " & _
"ni må du bruke feltet for ukjent objekt istedenfor!"
End If

 

[Guest]

Dirk

I looked at your corresponse with interest because I saw the similar code and had several questions. Hopefully my questions won't take too much time from you or other experts

1. Where is Cancel from? Shouldn't it be Response

2. Unlike the parameters in calling procedures I saw in other language, VBA seems to allow passing parameters without calling procedure, or allow passing parameters by user's input. I guess NewData and Response are all user inputs. If so, how the procedure decides who is who? In this example, procedure may tell them from the data types (if the input is string, it is for NewData; if integar, it is for Response). If so, how the procedure tell if two parameters are of the same type

3. What is "Response = acDataErrAdded" and "Response = acDataErrContinue" programatically? The variables Response is assigned a value, but is not referred to by any later statement. Then, how it comes into play? My guess is: No matter what name of the variable is, if the system detects the word acDataErrAdded or acDataErrContinue in any place, it will take the appropriate action. If so, this is something I did not see in other languages and I wonder why no one indicates such peculiarity in the textbooks

I should be very grateful if those elementary questions could be answered

Gen

P.S.: Erik, I suggest you to omit everything not in English or translate them into English next time you ask the question

 

[Dirk Goldgar]

Answers inline ...

Dirk,

I looked at your corresponse with interest because I saw the similar
code and had several questions. Hopefully my questions won't take too
much time from you or other experts.

1. Where is Cancel from? Shouldn't it be Response?

Erik's use of "Cancel" in a NotInList event procedure was an error,
which I corrected in my suggested code. A BeforeUpdate event procedure
has a Cancel argument, but a NotInList event procedure doesn't; it has
arguments NewData and Response instead.

2. Unlike the parameters in calling procedures I saw in other
language, VBA seems to allow passing parameters without calling
procedure, or allow passing parameters by user's input. I guess
NewData and Response are all user inputs. If so, how the procedure
decides who is who? In this example, procedure may tell them from the
data types (if the input is string, it is for NewData; if integar, it
is for Response). If so, how the procedure tell if two parameters are
of the same type?

I think you have misunderstood this. Event procedures are special, in
that they are not normally called by the user's code (thought they can
be), but rather by Access itself. When the event fires, Access calls
the event procedure and passes it the arguments that are appropriate for
that event. The event procedure must be defined to accept these
parameters. So, for example, you have these event procedure signatures:

Private Sub txtMyTextBox_AfterUpdate()

Private Sub txtMyTextBox_BeforeUpdate(Cancel As Integer)

Private Sub cboMyComboBox_NotInList( _
NewData As String, Response As Integer)

Each event has its own signature, which any event procedure defined for
it must match. Although, as I said before, you can call these routines
directly from your code, they must be defined with parameters to match
the application's expectations, because those parameters are how the
application's event handler communicates with the event procedure.

It is not the case that VBA "interprets" the arguments you may pass to
the procedure as different parameters according to their data type. The
arguments may be passed positionally or by name, but the data type of
the argument must match (or be convertible to) the data type of the
corresponding parameter.

3. What is "Response = acDataErrAdded" and "Response =
acDataErrContinue" programatically? The variables Response is
assigned a value, but is not referred to by any later statement.
Then, how it comes into play? My guess is: No matter what name of the
variable is, if the system detects the word acDataErrAdded or
acDataErrContinue in any place, it will take the appropriate action.
If so, this is something I did not see in other languages and I
wonder why no one indicates such peculiarity in the textbooks.

"Response" is defined as a parameter to the NotInList event procedure.
It is provided as a means for the event procedure to communicate back to
Access how the program should follow up on the event after the NotInList
procedure has finished execution. The event procedure sets this
parameter to one of the following values as identified by defined
constants:

***** quoting from Access 97 help file *****

acDataErrDisplay
-------------------
(Default) Displays the default message to the user. You can use
this when you don't want to allow the user to add a new value to the
combo box list.

acDataErrContinue
-------------------
Doesn't display the default message to the user. You can use
this when you want to display a custom message to the user. For example,
the event procedure could display a custom dialog box asking if the user
wanted to save the new entry. If the response is Yes, the event
procedure would add the new entry to the list and set the Response
argument to acDataErrAdded. If the response is No, the event procedure
would set the Response argument to acDataErrContinue.

acDataErrAdded
-------------------
Doesn't display a message to the user but enables you to add
the entry to the combo box list in the NotInList event procedure. After
the entry is added, Microsoft Access updates the list by requerying the
combo box. Microsoft Access then rechecks the string against the combo
box list, and saves the value in the NewData argument in the field the
combo box is bound to. If the string is not in the list, then Microsoft
Access displays an error message.

***** end quote *****

I had to crib that from the Access 97 help file because I couldn't find
it in the #^&*@ Access 2002 help.

I should be very grateful if those elementary questions could be
answered.

I hope that clears things up.

 

[Guest]

Dirk

Many thanks for your lengthy reply! Very helpful!

Another question: When the event procedure is fired by the NotInList event, the NewData is the data input by the user. But where is Response from?

Now I understand "Response = acDataErrAdd" is for the next event procedure. Thanks again

Gene

 

[Dirk Goldgar]

Dirk,

Many thanks for your lengthy reply! Very helpful!!

Another question: When the event procedure is fired by the NotInList
event, the NewData is the data input by the user. But where is
Response from?

It doesn't come from any user code. It's an output parameter -- a
parameter that the event procedure can use to communicate its response
to Access and tell Access what to do next. It's up to the event
procedure to set the Response argument to one of the supported values.
If it doesn't, the default value is acDataErrDisplay.