NotInList problem with 2003

[Stephen Haley]

In access 2003 I have a list box with 2 columns ID(hidden) & name. If the
user enters a new name I am using the Notinlist event to pop up a form, let
the users enter the details add the data to the source recordset then set
the notinlist event to acDataErrAdded and then try to set the control to the
newid. Fairly standard stuff but The problem comes if the user changes the
text passed from the combo box to the form. If they do change it then I get
an error message when I try to set the control to the new id (hidden column)
that it is not in the list. When I look at the list the new item has been
added with the ID that I tried to set it to. If I reiterate the cbbox.value=
id after the notinlist event completes it works fine.

What is going on? I am sure this used to work fine with 2002.

rgds
Stephen

 

[Graham Mandeno]

Hi Stephen

I'm afraid you are mistaken. This didn't work in Access 2002, and in fact
has *never* worked. The problem is that, after the user has changed the
text in the form, the new text is different from the text that was typed in,
so the item is *still* not in the list.

There is a way around this, but it is fiddly. The trick is to ascertain
what the new text value is and, if it is different from what the user typed,
then alter the Text property of the combobox to match.

However, there is a Catch-22 because setting the Text property also checks
the list for the new value, and the combo is not requeried until after
NotInList exits, so the new value will *still* not be in the list. This
causes NotInList to fire again.

To get around this, I set a static boolean variable before setting the Text
property, and my NotInList EP exits with minimal fuss is it finds the
variable is set.

I also set up my data entry dialog forms with Cancel and OK buttons. The
Cancel button does a Me.Undo and closes the form, and the OK button saves
the record and sets Me.Visible to False. That way, it is easy for the
calling code to extract the new text value to see if it has been modified.

Here is an outline of the code:

Static fIgnore As Boolean
Const cFormName = <your data input form>
If fIgnore Then
' we are recursing - just set Response and exit
Response = acDataErrAdded
Exit Sub
End If
If <user wants to add new record> Then
DoCmd.OpenForm cFormName, WindowMode:=acDialog, _
OpenArgs:=NewData
If CurrentProject.AllForms(cFormName).IsLoaded Then
' form is still open - get new text value from form
Response = acDataErrAdded
NewData = Forms(cFormName)("<name of text field>")
If MyCombo.Text <> NewData Then
' it has changed - alter .Text
fIgnore = True
MyCombo.Text = NewData
fIgnore = False
End If
DoCmd.Close acForm, cFormName
Else
' user closed form - assume cancelled
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If

 

[Stephen Haley]

Thanks - This has really been bugging me - been having so many probs with
2003 that I automatically attributed it to that especially as a google
turned up nothing beyond he routine I was using. And I could have sworn I
used to use it fine 18mths ago.
So far 2003 has crashed UA|E on opening a couple of my 2002dbs and ch7 of
the Developing Access 2002 2 book set.,
wont reference some controls until form has been saved,
rolled back previously saved code to what was previously compiled by the
looks of it,
had to manually reference accwiz.dll to get the linking wizard to work
Subform events not firing with attached classes unless a blank code esists
in the subform module for that event
Forms and controls not referencing events that actualy exist in the code
module for that form having previously been referenced.
Still have problems with compiler not picking up errors until runtime but
had that all the time with office since the beginning
etc etc
Havent been able to fid an up to date buglist anywhere - does one exist
apart from the list on mvps?

Begining to regret using it for this project but thought it might be stable
enough and wanted a few of the 2003 events.
But the adp tecnology is impressive - hadnt used it before this project
which is to be delivered three ways.

thks
Stephen

Hi Stephen

I'm afraid you are mistaken. This didn't work in Access 2002, and in fact
has *never* worked. The problem is that, after the user has changed the
text in the form, the new text is different from the text that was typed
in, so the item is *still* not in the list.

There is a way around this, but it is fiddly. The trick is to ascertain
what the new text value is and, if it is different from what the user
typed, then alter the Text property of the combobox to match.

However, there is a Catch-22 because setting the Text property also checks
the list for the new value, and the combo is not requeried until after
NotInList exits, so the new value will *still* not be in the list. This
causes NotInList to fire again.

To get around this, I set a static boolean variable before setting the
Text property, and my NotInList EP exits with minimal fuss is it finds the
variable is set.

I also set up my data entry dialog forms with Cancel and OK buttons. The
Cancel button does a Me.Undo and closes the form, and the OK button saves
the record and sets Me.Visible to False. That way, it is easy for the
calling code to extract the new text value to see if it has been modified.

Here is an outline of the code:

Static fIgnore As Boolean
Const cFormName = <your data input form>
If fIgnore Then
' we are recursing - just set Response and exit
Response = acDataErrAdded
Exit Sub
End If
If <user wants to add new record> Then
DoCmd.OpenForm cFormName, WindowMode:=acDialog, _
OpenArgs:=NewData
If CurrentProject.AllForms(cFormName).IsLoaded Then
' form is still open - get new text value from form
Response = acDataErrAdded
NewData = Forms(cFormName)("<name of text field>")
If MyCombo.Text <> NewData Then
' it has changed - alter .Text
fIgnore = True
MyCombo.Text = NewData
fIgnore = False
End If
DoCmd.Close acForm, cFormName
Else
' user closed form - assume cancelled
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

In access 2003 I have a list box with 2 columns ID(hidden) & name. If the
user enters a new name I am using the Notinlist event to pop up a form,
let the users enter the details add the data to the source recordset then
set the notinlist event to acDataErrAdded and then try to set the control
to the newid. Fairly standard stuff but The problem comes if the user
changes the text passed from the combo box to the form. If they do change
it then I get an error message when I try to set the control to the new
id (hidden column) that it is not in the list. When I look at the list
the new item has been added with the ID that I tried to set it to. If I
reiterate the cbbox.value= id after the notinlist event completes it
works fine.

What is going on? I am sure this used to work fine with 2002.

rgds
Stephen

 

[Stephen Haley]

Hmm
Tried your code must not be understanding some of it but this also works and
has the advantage that you only fire the requery on the form once.
(acDataErrAdded does an automatic requery) Blchng is a result of a strcomp
with vbbinarycomp as the combo sees changes as binary as opposed to text
(One of My probs as I Proper the names)

If BlChng Then
Response = acDataErrContinue
Me.ActiveControl.Undo
Me.ActiveControl.Requery
DoEvents ' Just to give the system a breather with external dbs
Me.ActiveControl.Text = RetStr
Else
Response = acDataErrAdded
Me.ActiveControl.Value = CLng(RetVal)
End If

Hi Stephen

I'm afraid you are mistaken. This didn't work in Access 2002, and in fact
has *never* worked. The problem is that, after the user has changed the
text in the form, the new text is different from the text that was typed
in, so the item is *still* not in the list.

There is a way around this, but it is fiddly. The trick is to ascertain
what the new text value is and, if it is different from what the user
typed, then alter the Text property of the combobox to match.

However, there is a Catch-22 because setting the Text property also checks
the list for the new value, and the combo is not requeried until after
NotInList exits, so the new value will *still* not be in the list. This
causes NotInList to fire again.

To get around this, I set a static boolean variable before setting the
Text property, and my NotInList EP exits with minimal fuss is it finds the
variable is set.

I also set up my data entry dialog forms with Cancel and OK buttons. The
Cancel button does a Me.Undo and closes the form, and the OK button saves
the record and sets Me.Visible to False. That way, it is easy for the
calling code to extract the new text value to see if it has been modified.

Here is an outline of the code:

Static fIgnore As Boolean
Const cFormName = <your data input form>
If fIgnore Then
' we are recursing - just set Response and exit
Response = acDataErrAdded
Exit Sub
End If
If <user wants to add new record> Then
DoCmd.OpenForm cFormName, WindowMode:=acDialog, _
OpenArgs:=NewData
If CurrentProject.AllForms(cFormName).IsLoaded Then
' form is still open - get new text value from form
Response = acDataErrAdded
NewData = Forms(cFormName)("<name of text field>")
If MyCombo.Text <> NewData Then
' it has changed - alter .Text
fIgnore = True
MyCombo.Text = NewData
fIgnore = False
End If
DoCmd.Close acForm, cFormName
Else
' user closed form - assume cancelled
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

In access 2003 I have a list box with 2 columns ID(hidden) & name. If the
user enters a new name I am using the Notinlist event to pop up a form,
let the users enter the details add the data to the source recordset then
set the notinlist event to acDataErrAdded and then try to set the control
to the newid. Fairly standard stuff but The problem comes if the user
changes the text passed from the combo box to the form. If they do change
it then I get an error message when I try to set the control to the new
id (hidden column) that it is not in the list. When I look at the list
the new item has been added with the ID that I tried to set it to. If I
reiterate the cbbox.value= id after the notinlist event completes it
works fine.

What is going on? I am sure this used to work fine with 2002.

rgds
Stephen

 

[Graham Mandeno]

Hi Stephen

I'm certain that combo boxes are not case sensitive, so the binary compare
should be unnecessary. Also, the line:
Me.ActiveControl.Value = CLng(RetVal)
is unnecessary, because acDataErrAdded causes access to re-search the list
after requerying it, and it will find the new value if it's there.

Yes, the Undo, Requery, DoEvents does work. I just prefer to let Access
take care of things where it can. You can avoid two requeries by returning
acDataErrContinue after a change has been detected.

Your BlChng variable is a good idea and it can be used for the dual purpose
by declaring it Static. Using your variables:

Dim RetStr as String
Static BlChng as boolean
If BlChng Then
Response = acDataErrAdded
Exit Sub
End If
....
' open form and get RetStr
....
With MyComboBox
BlChng = RetStr <> .Text
If BlChng Then
Response = acDataErrContinue ' requery only once
.Text = RetStr
BlChng = False
Else
Response = acDataErrAdded
End If
End With

I don't see this so much as a bug as an annoyance. However, what I would
really like is for NewData to be a two-way argument, so if you change its
value and return acDataErrAdded, then the *new* value of NewData is used as
the lookup value.

I suggested this back in Access 2 days ... still waiting :-(
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hmm
Tried your code must not be understanding some of it but this also works
and has the advantage that you only fire the requery on the form once.
(acDataErrAdded does an automatic requery) Blchng is a result of a strcomp
with vbbinarycomp as the combo sees changes as binary as opposed to text
(One of My probs as I Proper the names)

If BlChng Then
Response = acDataErrContinue
Me.ActiveControl.Undo
Me.ActiveControl.Requery
DoEvents ' Just to give the system a breather with external dbs
Me.ActiveControl.Text = RetStr
Else
Response = acDataErrAdded
Me.ActiveControl.Value = CLng(RetVal)
End If

Hi Stephen

I'm afraid you are mistaken. This didn't work in Access 2002, and in
fact has *never* worked. The problem is that, after the user has changed
the text in the form, the new text is different from the text that was
typed in, so the item is *still* not in the list.

There is a way around this, but it is fiddly. The trick is to ascertain
what the new text value is and, if it is different from what the user
typed, then alter the Text property of the combobox to match.

However, there is a Catch-22 because setting the Text property also
checks the list for the new value, and the combo is not requeried until
after NotInList exits, so the new value will *still* not be in the list.
This causes NotInList to fire again.

To get around this, I set a static boolean variable before setting the
Text property, and my NotInList EP exits with minimal fuss is it finds
the variable is set.

I also set up my data entry dialog forms with Cancel and OK buttons. The
Cancel button does a Me.Undo and closes the form, and the OK button saves
the record and sets Me.Visible to False. That way, it is easy for the
calling code to extract the new text value to see if it has been
modified.

Here is an outline of the code:

Static fIgnore As Boolean
Const cFormName = <your data input form>
If fIgnore Then
' we are recursing - just set Response and exit
Response = acDataErrAdded
Exit Sub
End If
If <user wants to add new record> Then
DoCmd.OpenForm cFormName, WindowMode:=acDialog, _
OpenArgs:=NewData
If CurrentProject.AllForms(cFormName).IsLoaded Then
' form is still open - get new text value from form
Response = acDataErrAdded
NewData = Forms(cFormName)("<name of text field>")
If MyCombo.Text <> NewData Then
' it has changed - alter .Text
fIgnore = True
MyCombo.Text = NewData
fIgnore = False
End If
DoCmd.Close acForm, cFormName
Else
' user closed form - assume cancelled
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

In access 2003 I have a list box with 2 columns ID(hidden) & name. If
the user enters a new name I am using the Notinlist event to pop up a
form, let the users enter the details add the data to the source
recordset then set the notinlist event to acDataErrAdded and then try to
set the control to the newid. Fairly standard stuff but The problem
comes if the user changes the text passed from the combo box to the
form. If they do change it then I get an error message when I try to set
the control to the new id (hidden column) that it is not in the list.
When I look at the list the new item has been added with the ID that I
tried to set it to. If I reiterate the cbbox.value= id after the
notinlist event completes it works fine.

What is going on? I am sure this used to work fine with 2002.

rgds
Stephen

 

[Stephen Haley]

If I fire a diff value in ie it has changed then I get the event refired and
have to resort to your static variable solution.
By using the acDataErrContinue, undo, requerey & then reset the combo box
this is avoided altogether.
I am afraid I have never liked static variables at module level they can be
a real pain to debug especially if you have multiple instances. When I need
them I tend to use gstatic global variables or use byref in calling subs &
functions but hey everyone has their own style.
rgds
Stephen

Hi Stephen

I'm certain that combo boxes are not case sensitive, so the binary compare
should be unnecessary. Also, the line:
Me.ActiveControl.Value = CLng(RetVal)
is unnecessary, because acDataErrAdded causes access to re-search the list
after requerying it, and it will find the new value if it's there.

Yes, the Undo, Requery, DoEvents does work. I just prefer to let Access
take care of things where it can. You can avoid two requeries by
returning acDataErrContinue after a change has been detected.

Your BlChng variable is a good idea and it can be used for the dual
purpose by declaring it Static. Using your variables:

Dim RetStr as String
Static BlChng as boolean
If BlChng Then
Response = acDataErrAdded
Exit Sub
End If
...
' open form and get RetStr
...
With MyComboBox
BlChng = RetStr <> .Text
If BlChng Then
Response = acDataErrContinue ' requery only once
.Text = RetStr
BlChng = False
Else
Response = acDataErrAdded
End If
End With

I don't see this so much as a bug as an annoyance. However, what I would
really like is for NewData to be a two-way argument, so if you change its
value and return acDataErrAdded, then the *new* value of NewData is used
as the lookup value.

I suggested this back in Access 2 days ... still waiting :-(
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hmm
Tried your code must not be understanding some of it but this also works
and has the advantage that you only fire the requery on the form once.
(acDataErrAdded does an automatic requery) Blchng is a result of a
strcomp with vbbinarycomp as the combo sees changes as binary as opposed
to text (One of My probs as I Proper the names)

If BlChng Then
Response = acDataErrContinue
Me.ActiveControl.Undo
Me.ActiveControl.Requery
DoEvents ' Just to give the system a breather with external
dbs
Me.ActiveControl.Text = RetStr
Else
Response = acDataErrAdded
Me.ActiveControl.Value = CLng(RetVal)
End If

Hi Stephen

I'm afraid you are mistaken. This didn't work in Access 2002, and in
fact has *never* worked. The problem is that, after the user has
changed the text in the form, the new text is different from the text
that was typed in, so the item is *still* not in the list.

There is a way around this, but it is fiddly. The trick is to ascertain
what the new text value is and, if it is different from what the user
typed, then alter the Text property of the combobox to match.

However, there is a Catch-22 because setting the Text property also
checks the list for the new value, and the combo is not requeried until
after NotInList exits, so the new value will *still* not be in the list.
This causes NotInList to fire again.

To get around this, I set a static boolean variable before setting the
Text property, and my NotInList EP exits with minimal fuss is it finds
the variable is set.

I also set up my data entry dialog forms with Cancel and OK buttons.
The Cancel button does a Me.Undo and closes the form, and the OK button
saves the record and sets Me.Visible to False. That way, it is easy for
the calling code to extract the new text value to see if it has been
modified.

Here is an outline of the code:

Static fIgnore As Boolean
Const cFormName = <your data input form>
If fIgnore Then
' we are recursing - just set Response and exit
Response = acDataErrAdded
Exit Sub
End If
If <user wants to add new record> Then
DoCmd.OpenForm cFormName, WindowMode:=acDialog, _
OpenArgs:=NewData
If CurrentProject.AllForms(cFormName).IsLoaded Then
' form is still open - get new text value from form
Response = acDataErrAdded
NewData = Forms(cFormName)("<name of text field>")
If MyCombo.Text <> NewData Then
' it has changed - alter .Text
fIgnore = True
MyCombo.Text = NewData
fIgnore = False
End If
DoCmd.Close acForm, cFormName
Else
' user closed form - assume cancelled
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


In access 2003 I have a list box with 2 columns ID(hidden) & name. If
the user enters a new name I am using the Notinlist event to pop up a
form, let the users enter the details add the data to the source
recordset then set the notinlist event to acDataErrAdded and then try
to set the control to the newid. Fairly standard stuff but The problem
comes if the user changes the text passed from the combo box to the
form. If they do change it then I get an error message when I try to
set the control to the new id (hidden column) that it is not in the
list. When I look at the list the new item has been added with the ID
that I tried to set it to. If I reiterate the cbbox.value= id after the
notinlist event completes it works fine.

What is going on? I am sure this used to work fine with 2002.

rgds
Stephen