SURPRESS warning not in list

G

Guest

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
 
D

Dirk Goldgar

lmv said:
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
Click to expand...

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.
 
G

Guest

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
--------------------------------------------
 
D

Dirk Goldgar

lmv said:
Thanks Dirk, I change it and it opens right but when I close it gives
the not in list now...
Click to expand...

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
Click to expand...

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.
 
G

Guest

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?
Click to expand...

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?
 
D

Dirk Goldgar

lmv said:
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?
Click to expand...

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?
 
G

Guest

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?
Click to expand...

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


------------------------------------------------
 
D

Dirk Goldgar

lmv said:
Yes it is bound to the tblpurchaser yes saves automatically...
Click to expand...

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.
 
G

Guest

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!!
 
D

Dirk Goldgar

lmv said:
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!
Click to expand...

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? :)
Click to expand...

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".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Not In List Cbo Help 5
acDialog - Form Invisible? 1
NotInList Event 1
NotInList INSERT INTO 2 Fields HOW? 3
code not working 1
Jet Engine Error Message 2
On Not in List even question 4
Not in List Event 1

Top