Not in list warning

[CJ]

Hi Groupies

I have used Docmd Setwarnings False many times before but this time I need
some assistance.

My field is a combo box.
Limit to list is Yes. (bound field is not visible)
My code without the SetWarnings is as follows:

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

Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Me.Undo
Me.Dirty = False
DoCmd.OpenForm "frmModel"
DoCmd.GoToRecord , , acNewRec
Forms!frmModel!lngMakeID.Value = Forms!frmInventory.Make
Forms!frmModel!strModel.SetFocus
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

I have tried turning off the warnings everywhere but to no avail.
I have no other code running off of this field.

Any ideas?

 

[Graham Mandeno]

Hi CJ

You are not opening the form with WindowMode:=acDialog.

This means that the code in your NotInList proc continues to execute after
the form is opened. There is no error, so Response is set to
acDataErrAdded, which causes Access, after the proc exits, to requery the
combo box and try again. Of course, the new record has not yet been saved,
so the NotInList condition fires again.

You should do as I suggested in a previous post and open the form in dialog
mode, passing all the necessary information in OpenArgs. In your case, you
have two items to pass, so to do that in a single value you need to be
inventive.

I suggest you do it like this:

DoCmd.OpenForm "frmModel", WindowMode:=acDialog, _
OpenArgs:=Me!Make & vbNullChar & NewData

(I am assuming that frmInventory is the current form, so it's better to
refer to it as "Me" rather than "Forms!frmInventory")

This will pass a string made up of your two data items, separated by a
NullChar character - Chr(0).

In the Load event of frmModel, you need to pick this string apart and deal
with the individual parts:

Dim aArgs as Variant
aArgs = Split( Me.OpenArgs & vbNullChar, vbNullChar )
lngMakeID.DefaultValue = """" & aArgs(0) & """"
strModel.DefaultValue = """" & aArgs(1) & """"

This will set the default values of both make and model to the values
passed.

The user now just needs to fill in the other fields as required and close
the form. This will save the record and allow the NotInList code to
continue.

Further complications arise if the user either (a) closes the form without
creating the new record or (b) changes the spelling of Model so the item is
*still* not in the list. There are ways to handle this, but that's another
chapter of the story ;-)

 

[CJ]

Once again, your brilliance and complete explanations
are most appreciated!

Thanks Graham.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi CJ

You are not opening the form with WindowMode:=acDialog.

This means that the code in your NotInList proc continues to execute after
the form is opened. There is no error, so Response is set to
acDataErrAdded, which causes Access, after the proc exits, to requery the
combo box and try again. Of course, the new record has not yet been
saved, so the NotInList condition fires again.

You should do as I suggested in a previous post and open the form in
dialog mode, passing all the necessary information in OpenArgs. In your
case, you have two items to pass, so to do that in a single value you need
to be inventive.

I suggest you do it like this:

DoCmd.OpenForm "frmModel", WindowMode:=acDialog, _
OpenArgs:=Me!Make & vbNullChar & NewData

(I am assuming that frmInventory is the current form, so it's better to
refer to it as "Me" rather than "Forms!frmInventory")

This will pass a string made up of your two data items, separated by a
NullChar character - Chr(0).

In the Load event of frmModel, you need to pick this string apart and deal
with the individual parts:

Dim aArgs as Variant
aArgs = Split( Me.OpenArgs & vbNullChar, vbNullChar )
lngMakeID.DefaultValue = """" & aArgs(0) & """"
strModel.DefaultValue = """" & aArgs(1) & """"

This will set the default values of both make and model to the values
passed.

The user now just needs to fill in the other fields as required and close
the form. This will save the record and allow the NotInList code to
continue.

Further complications arise if the user either (a) closes the form without
creating the new record or (b) changes the spelling of Model so the item
is *still* not in the list. There are ways to handle this, but that's
another chapter of the story ;-)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand