SURPRESS warning not in list

[lmv]

I hope someone can tell me why I get the additional "not in list error" and
how to surpress it. As my form after I have said yes, my form opens but then
the system error pops up... THANKS!

Private Sub cboPurchaser_NotInList(NewData As String, Response As Integer)
On Error GoTo cboPurchaser_NotInList_Err
Dim intAnswer As Integer
Dim stDocName As String

intAnswer = MsgBox("The NAME " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "BC")
If intAnswer = vbYes Then

stDocName = "frmPurchaser"
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Response = acDataErrAdded
Else
MsgBox "Please choose a name from the list." _
, vbInformation, "BC"
Response = acDataErrContinue
End If
cboPurchaser_NotInList_Exit:
Exit Sub
cboPurchaser_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume cboPurchaser_NotInList_Exit
End Sub

 

[Dirk Goldgar]

I hope someone can tell me why I get the additional "not in list
error" and how to surpress it. As my form after I have said yes, my
form opens but then the system error pops up... THANKS!

Private Sub cboPurchaser_NotInList(NewData As String, Response As
Integer) On Error GoTo cboPurchaser_NotInList_Err
Dim intAnswer As Integer
Dim stDocName As String

intAnswer = MsgBox("The NAME " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "BC")
If intAnswer = vbYes Then

stDocName = "frmPurchaser"
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Response = acDataErrAdded
Else
MsgBox "Please choose a name from the list." _
, vbInformation, "BC"
Response = acDataErrContinue
End If
cboPurchaser_NotInList_Exit:
Exit Sub
cboPurchaser_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume cboPurchaser_NotInList_Exit
End Sub

Because your form is not being opened in dialog mode, the code in the
NotInList event procedure continues immediately after the call to
DoCmd.OpenForm, and so it requeries the combo box before the new entry
has been made and saved from the form. I suggest you change the code
like this:

'----- start of code snippet -----

' ... preliminary code ...

If intAnswer = vbYes Then

stDocName = "frmPurchaser"

DoCmd.OpenForm stDocName, _
DataMode:=acFormAdd, WindowMode:=acDialog

Response = acDataErrAdded

Else
' ... rest of code ...

'----- end of code snippet -----

That will open the form in data entry mode (to add the new record), and
also in dialog mode so that the code in the event procedure won't
continue until the form is closed.

 

[lmv]

Thanks Dirk, I change it and it opens right but when I close it gives the not
in list now... :{ What I have is below.
do I need to requery on close ... I don't know how.

Private Sub cboPurchaser_NotInList(NewData As String, Response As Integer)
On Error GoTo cboPurchaser_NotInList_Err
Dim intAnswer As Integer
Dim stDocName As String

intAnswer = MsgBox("The NAME " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "BC")
If intAnswer = vbYes Then

stDocName = "frmPurchaser"
DoCmd.OpenForm stDocName, _
DataMode:=acFormAdd, WindowMode:=acDialog

Response = acDataErrAdded
Else
MsgBox "Please choose a name from the list." _
, vbInformation, "BC"
Response = acDataErrContinue
End If
cboPurchaser_NotInList_Exit:
Exit Sub
cboPurchaser_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume cboPurchaser_NotInList_Exit
End Sub
--------------------------------------------

 

[Dirk Goldgar]

Thanks Dirk, I change it and it opens right but when I close it gives
the not in list now...

Do you mean that it gives you the built-in "not in list" message even if
you use the form to add the new item? That seems very strange. The
line

Response = acDataErrAdded

tells Access to requery the combo box because the new item has been
added, and it should not give a message if it now finds the item in the
requeried list.

Or is it just that the message is displayed when you close frmPurchaser
without adding the item? That would be the expected behavior, and
working around that will be more complicated.

 

[lmv]

Hi

Do you mean that it gives you the built-in "not in list" message even if
you use the form to add the new item?

Yes...
I enter the info into the form clk close button. As the form closes I get
the error. Then when I say ok focus is on the cbo it drops down the list and
the new item is there. So, it did requery... any ideas?

 

[Dirk Goldgar]

Hi

Yes...
I enter the info into the form clk close button. As the form closes I
get the error. Then when I say ok focus is on the cbo it drops down
the list and the new item is there. So, it did requery... any ideas?

Is the form that adds the info a bound form, saving the info by letting
the user modify the bound controls and just letting Access save the
record automatically? Or is it an unbound form that uses some
user-written method for adding the new record to the table?

 

[lmv]

Is the form that adds the info a bound form, saving the info by letting

the user modify the bound controls and just letting Access save the
record automatically?

Yes it is bound to the tblpurchaser yes saves automatically...


------------------------------------------------

 

[Dirk Goldgar]

Yes it is bound to the tblpurchaser yes saves automatically...

Then it's very odd. I'd investigate along two lines:

(1) Something unexpected happening in the execution of the code.
To investigate this, I'd put a breakpoint at the start of the NotInList
event procedure, then trigger it by entering a value that is not in the
list. Make sure you note the *exact* value you entered. When the
NotInList event fires and the code stops at the breakpoint, step through
the code line by line to make sure it follows the path you expect.

(2) Something odd about the properties of the combo box. Please post
the following properties of that control:

Row Source (if a table, list the fields; if a stored query, give
the SQL)
Bound Column
Column Count
Column Widths

If there's any code in other events of the combo box, it might help to
post that, too.

 

[lmv]

You just helped me figure it out I was putting in initials that weren't in
the list... ie LT then while I was checking the code I put in the form CK not
thinking that it should be the same initials as I had been trying to put in
originally... DUH!! Then when I closed the form the LT was STILL not in the
list but CK was!! So of course the event triggered! Now I have to figure out
what to put in the open form event that will write what I had entered in the
cbo box so someone else doesn't do the same thing I did!! (Can you help me 1
more time?

Thanks!!

 

[Dirk Goldgar]

You just helped me figure it out I was putting in initials that
weren't in the list... ie LT then while I was checking the code I put
in the form CK not thinking that it should be the same initials as I
had been trying to put in originally... DUH!! Then when I closed the
form the LT was STILL not in the list but CK was!! So of course the
event triggered!

Now I have to figure out what to put in the open
form event that will write what I had entered in the cbo box so
someone else doesn't do the same thing I did!! (Can you help me 1
more time?

Since you have to open the form in dialog mode, the only way to
communicate with it from the calling code is to pass the new value via
the OpenArgs argument of the DoCmd.OpenForm method. Then in the form's
Current event, set the value of the appropriate control to the value of
the form's OpenArgs property (after making sure something was passed in
it).

The calling code:

DoCmd.OpenForm stDocName, _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData


Example code for the called form:

'----- start of example code -----
Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me!Initials = Me.OpenArgs
End If

End Sub
'----- end of example code -----

Of course you'd substitute the name of the appropriate control or field
for "Initials".

 

[lmv]

PERFECT!!! Now it's DONE! (at least 1 more itsy bitsy part!) Thanks VERY much!!