Combo 'NotInList' > add record > refresh combo problem

[Fjordur]

Hi,
I have a job form with a subform 'assign' which shows people assigned to the
job. The subform has a combo based on a query based on a 'person' table.
Adding a record to the sublist is done by selecting a person's name in the
combo. The property 'LimitToList' of the combo is true.

I want to be able to type a new name in the combo; in that case I want
the user to fill a "personForm" form and then go back to the assignment
form - subform. What I did is this: the property 'onNotInList' of the combo
is a Sub that does the following:
MsgBox("do you want to add a new name")
if answer is Yes then
DoCmd.RunSQL "INSERT INTO [personTable] ( personName) " & _
"VALUES ('" & NewData & "');"
intAnswer = Nz(DMax("[personID]", "[personTable]"), 0)
stLinkCriteria = "[personID]=" & intAnswer '& "'"
DoCmd.OpenForm "personForm", , , stLinkCriteria
The user can then fill the "personForm", then close it and find himself back
where he was, with the new name types in the combo, focus on the combo.

Problem is, I can't find how to refresh the combo list. It doesn't contain
the new name and therefore will not let you move the focus out of the combo
(it will ask again and again if you want to add the name). Ideally, I would
like the query of the combo to be re-run.

How can I do that?

 

[Douglas J Steele]

Ensure that you have a line of code

Response = acDataErrAdded

in your NotInList event after you've added the new value.

 

[Fjordur]

Ensure that you have a line of code
Response = acDataErrAdded
in your NotInList event after you've added the new value.

It is there. Actual code is
If intAnswer = vbYes Then
strSQL = "INSERT INTO [personTable] ( personName) " & _
"VALUES ('" & NewData & "');"
DoCmd.RunSQL strSQL
intAnswer = Nz(DMax("[personID]", "[personTable]"), 0)
MsgBox "The new name" & NewData & " was appended." _
, vbInformation, "Win-Flex"
Response = acDataErrAdded
stLinkCriteria = "[personID]=" & intAnswer '& "'"
DoCmd.OpenForm "PersonForm", , , stLinkCriteria
Else
What happens is:
1) in the combo type xxx then tab
2) message box "do you want to add a new name" > click yes
3) message "you are about to append 1 row", are you sure > click yes
4) message "The new name xxx was appended" > click OK
5) form personForm opens, with nameField = xxx
6) ***message "the text you entered isn't an item in the list" > click OK
*** I don't want this message
7) now I can input data in the person form, close window
8) back in the combo box with value xxx (see step 1), combo list doesn't
contain xxx
I seem to understand the combo list should be updated but how?
Maybe the combo idea is not the proper user interface for this?

 

[Douglas J. Steele]

While I wouldn't have expected it to be required, try putting a Requery for
the combo box before you open PersonForm.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ensure that you have a line of code
Response = acDataErrAdded
in your NotInList event after you've added the new value.

It is there. Actual code is
If intAnswer = vbYes Then
strSQL = "INSERT INTO [personTable] ( personName) " & _
"VALUES ('" & NewData & "');"
DoCmd.RunSQL strSQL
intAnswer = Nz(DMax("[personID]", "[personTable]"), 0)
MsgBox "The new name" & NewData & " was appended." _
, vbInformation, "Win-Flex"
Response = acDataErrAdded
stLinkCriteria = "[personID]=" & intAnswer '& "'"
DoCmd.OpenForm "PersonForm", , , stLinkCriteria
Else
What happens is:
1) in the combo type xxx then tab
2) message box "do you want to add a new name" > click yes
3) message "you are about to append 1 row", are you sure > click yes
4) message "The new name xxx was appended" > click OK
5) form personForm opens, with nameField = xxx
6) ***message "the text you entered isn't an item in the list" > click OK
*** I don't want this message
7) now I can input data in the person form, close window
8) back in the combo box with value xxx (see step 1), combo list doesn't
contain xxx
I seem to understand the combo list should be updated but how?
Maybe the combo idea is not the proper user interface for this?