Weird NotInList behaviour

[Bryan]

I have a listbox that the user is able to add items to through and SQL
statement that is run on the NotInList event. At the end of the event,
I set Response = acDataErrAdded, telling access that the NewData
variable has been added to the underlying table. The recoord gets
added to the underlying table just fine. The problem is, after the
NotInList event, the afterupdate event is called by access. This is
also no problem. But after that, the Sub that is associated with the
default button on the form runs for some reason. It's like access is
pressing [enter] for some reason! Any insight.

 

[Dirk Goldgar]

I have a listbox that the user is able to add items to through and SQL
statement that is run on the NotInList event. At the end of the
event, I set Response = acDataErrAdded, telling access that the
NewData variable has been added to the underlying table. The recoord
gets added to the underlying table just fine. The problem is, after
the NotInList event, the afterupdate event is called by access. This
is also no problem. But after that, the Sub that is associated with
the default button on the form runs for some reason. It's like
access is pressing [enter] for some reason! Any insight.

Please post the code from all the relevant events. Have you verified
the sequence of code execution, by setting breakpoints and stepping
through the code?

 

[Bryan]

Here is my code...


Private Sub Job_NotInList(NewData As String, Response As Integer)

'ask user if they want to add job to list
Ans = MsgBox("Job not in list. Do you want to add it?", vbYesNo Or
vbQuestion, "Add Job to list?")

'if no, return them to list
If Ans = vbNo Then Response = acDataErrContinue: Exit Sub

'if yes, ask for a description of job before adding to Jobs table
tempdesc = InputBox("Enter a description for this job.", "Add job to
list", "Enter job description")

'if user presses cancel or enters blank, exit
If tempdesc = "" Then
Response = acDataErrContinue
Exit Sub
End If

Add new job to list
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("INSERT INTO Jobs ( JobNum, JobDesc ) SELECT " & NewData
& " AS JobNum, '" & tempdesc & "' AS JobDesc")
DoCmd.SetWarnings (True)

'tell access the new job has been added to the table
Response = acDataErrAdded

End Sub


***IT THEN GOES TO THE AfterUpdate EVENT OF THE SAME CONTROL


Private Sub Job_AfterUpdate()
'set permission and cost object to null to avoid fields that look blank
but
'contain info from previously selected jobs
Me.CostPermission = Null
Me.CostObject = Null
Refresh

'set default client #
Set rstemp = CurrentDb.OpenRecordset("SELECT * FROM CostPermissions
WHERE JobID = " & Me.Job & " AND DefaultPer = True")
If rstemp.EOF = True Then GoTo ChooseSingleCostCode
rstemp.MoveLast
If rstemp.RecordCount = 0 Then Exit Sub
Me.CostPermission = rstemp.Fields("PerID")

ChooseSingleCostCode:
'if only one cost code on job, select it for user
Set rstemp = CurrentDb.OpenRecordset("SELECT CostObjID FROM CostObjects
WHERE JobID = " & Me.Job)

***WITHOUT EXPLAINING MUCH ABOUT MY DB, THE FOLLOWING STATEMENT RETURNS
FALSE, AS IT SHOULD
***BUT THEN IT GOES STRAIGHT INTO THE SUB BELOW, WHICH IS THE SUB FOR
THE DEFAULT BUTTON OF THE FORM
If rstemp.EOF = True Then Exit Sub

rstemp.MoveLast
If rstemp.RecordCount = 1 Then
Me.CostObject = rstemp.Fields("CostObjID")
End If

rstemp.Close

Refresh
End Sub


***wHEN THE AfterUpdate SUB IS DONE, IT GOES INTO THE BELOW SUB,
***THIS IS THE SUB FOR THE DEFAULT BUTTON ON THE FORM


Private Sub butADD_Click()
'add new entry to log

'__/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\_
'-------------DATA VALIDATION--------------
'--\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~/-

'check if item is selected
If IsNull(Me.ChooseItem) = True Then
MsgBox "Please select " & Me.ItemTypeFilter & " from the
list.", vbInformation, "Make selection"
Exit Sub
End If

**THERE IS MORE BELOW THIS BUT THIS PROBLEM ENDS WITH THIS FIRST IF
STATEMENT


I don't understand how a control could call the sub of the default
control on a form without me coding it?
This sequence is strange

 

[TC]

Not to answer your question, but, it's really not a good idea to do all
those database updates "behind the scenes". What if the user selects an
entry from the combo box, and your code does all those updates, but
then the user presses the Esc key a few times to give up? I think
you'll find that all the updates that you did behind the scenes, have
gone on file! Oops.

HTH,
TC

 

[Bryan]

There is only one statement in the above code that modifies any data in
the DB, that being the one that adds a new job to the listbox. All the
other code simply fills in some controls for their convenience. The
people using this DB are not fast enough thinkers to press Esc before
the sub is done adding the new data, so i'm not afraid of them changing
their mind in the middle of the sub. Even if they did, they could just
press the delete button next to the data they just entered by accident.
I did just wake up though so I could be missing your point all
together.

 

[TC]

Sorry, you're quite right, there is only one such statement.

But my comment still holds for that one statement. In Access, as you
already know, new data, and changes to existing data, that are done
through a form, are not committed to the database, until the user does
something to cause a save to occur. For example, moving the form to a
new record. But in your form, if the user hits the escape key a few
times, the form will /seem/ to give up, and discard the unsaved new or
changed data, /but/, the update performed by the INSERT INTO statement
will still go on file, if I recall correctly.

I'm just mentioning that, in case you haven't thought of that case

HTH,
TC

 

[Dirk Goldgar]

Here is my code...


Private Sub Job_NotInList(NewData As String, Response As Integer)

'ask user if they want to add job to list
Ans = MsgBox("Job not in list. Do you want to add it?", vbYesNo Or
vbQuestion, "Add Job to list?")

'if no, return them to list
If Ans = vbNo Then Response = acDataErrContinue: Exit Sub

'if yes, ask for a description of job before adding to Jobs table
tempdesc = InputBox("Enter a description for this job.", "Add job to
list", "Enter job description")

'if user presses cancel or enters blank, exit
If tempdesc = "" Then
Response = acDataErrContinue
Exit Sub
End If

Add new job to list
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("INSERT INTO Jobs ( JobNum, JobDesc ) SELECT " & NewData
& " AS JobNum, '" & tempdesc & "' AS JobDesc")
DoCmd.SetWarnings (True)

'tell access the new job has been added to the table
Response = acDataErrAdded

End Sub


***IT THEN GOES TO THE AfterUpdate EVENT OF THE SAME CONTROL


Private Sub Job_AfterUpdate()
'set permission and cost object to null to avoid fields that look
blank but
'contain info from previously selected jobs
Me.CostPermission = Null
Me.CostObject = Null
Refresh

'set default client #
Set rstemp = CurrentDb.OpenRecordset("SELECT * FROM CostPermissions
WHERE JobID = " & Me.Job & " AND DefaultPer = True")
If rstemp.EOF = True Then GoTo ChooseSingleCostCode
rstemp.MoveLast
If rstemp.RecordCount = 0 Then Exit Sub
Me.CostPermission = rstemp.Fields("PerID")

ChooseSingleCostCode:
'if only one cost code on job, select it for user
Set rstemp = CurrentDb.OpenRecordset("SELECT CostObjID FROM
CostObjects WHERE JobID = " & Me.Job)

***WITHOUT EXPLAINING MUCH ABOUT MY DB, THE FOLLOWING STATEMENT
RETURNS FALSE, AS IT SHOULD
***BUT THEN IT GOES STRAIGHT INTO THE SUB BELOW, WHICH IS THE SUB FOR
THE DEFAULT BUTTON OF THE FORM
If rstemp.EOF = True Then Exit Sub

rstemp.MoveLast
If rstemp.RecordCount = 1 Then
Me.CostObject = rstemp.Fields("CostObjID")
End If

rstemp.Close

Refresh
End Sub


***wHEN THE AfterUpdate SUB IS DONE, IT GOES INTO THE BELOW SUB,
***THIS IS THE SUB FOR THE DEFAULT BUTTON ON THE FORM


Private Sub butADD_Click()
'add new entry to log

'__/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\_
'-------------DATA VALIDATION--------------
'--\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~/-

'check if item is selected
If IsNull(Me.ChooseItem) = True Then
MsgBox "Please select " & Me.ItemTypeFilter & " from the
list.", vbInformation, "Make selection"
Exit Sub
End If

**THERE IS MORE BELOW THIS BUT THIS PROBLEM ENDS WITH THIS FIRST IF
STATEMENT


I don't understand how a control could call the sub of the default
control on a form without me coding it?
This sequence is strange

Bryan, I see a few problems in that code, but I can't say that I see
anything that would force a call to the butADD_Click() routine. Are
there any other procedures in the form's module that explicitly call
that function? Maybe something you're doing in the above code is
triggering an event that, in turn, is calling that procedure.

If I were you, I'd set a breakpoint at the top of the NotInList event
proc and step through the code execution line by line to see exactly
what path it's taking.

 

[Bryan]

This is weird, I just created a new unbound form to troubleshoot this
problem. I have on list box, and one button. The button is set as the
default control. I have messages set up in the afterupdate event of
the list box, and in the sub for the default button. When the
notInList event is triggered, the afterUpdate message appears first,
then the default buttons message appears. It looks like when you
return Response = acDataErrAdded to the NotInList event, access
requerys the control, runs that controls afterUpdate event, and then
runs the default control's sub for some reason. Can anybody else
confirm this. It is a quick form to set up, I would appreciate it if
somebody ran the test on their machine to see if this is true. Thanks
in advance.

 

[Dirk Goldgar]

This is weird, I just created a new unbound form to troubleshoot this
problem. I have on list box, and one button. The button is set as
the default control. I have messages set up in the afterupdate event
of the list box, and in the sub for the default button. When the
notInList event is triggered, the afterUpdate message appears first,
then the default buttons message appears. It looks like when you
return Response = acDataErrAdded to the NotInList event, access
requerys the control, runs that controls afterUpdate event, and then
runs the default control's sub for some reason. Can anybody else
confirm this. It is a quick form to set up, I would appreciate it if
somebody ran the test on their machine to see if this is true. Thanks
in advance.

I presume that, though you say "list box", you really mean "combo box".
It's not clear what you mean by "default control", but I'm assuming you
mean that the button's Default property is set to Yes. Also, by "the
default control's sub", I take it you mean the command button's Click
event procedure.

I created a form like this, and no, I can't reproduce the behavior.
Yes, the combo box's AfterUpdate event fires after te NotInList event,
as it's supposed to, but the command button's Click event does not fire.

 

[Bryan]

Thank you for your results.

 

[Bryan]

Is there any way I can post an example database that is showing this
behaviour? It is a simple one-form database with a combobox and a
default button.

 

[Dirk Goldgar]

Thank you for your results.

Have you found the cause of your problem? If you can create a sample
database that demonstrates it, less than 1MB in size when zipped, you
may e-mail it to me at the address you get by removing NO SPAM from the
reply address of this message. I'll have a look at it, time permitting.