NotInList making me crazy(er)

[FireMedic]

Hello all,
Having a bit of grief with a NotInList code block that has quit working properly.
I have set it as a public sub in a module so I can use it in multiple forms. I t worked ok in the form module but seems to be a bit glitchy in the public mode.

The new value does not seem to be taking and in spite of "acdataAdded" it continues to pop up the standard warning telling the user to pick an item from the list after the second message box closes. Ultimately the data doesnt get added.
Combo box source is:

SELECT [tbl_Manufacturers].[MfgrID], [tbl_Manufacturers].[MfgrName] FROM tbl_Manufacturers ORDER BY [tbl_Manufacturers].[MfgrName]; 

Limit To List is set to - Yes

Code is As Follows:

Public Sub MfgNIL()
 
On Error GoTo MfgNIL_Err
    Dim IntAnswer As Integer
    Dim StrSql As String
 
    IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "I-TEAM Utilities")
 
      If IntAnswer = vbYes Then
        StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSql
        DoCmd.SetWarnings True
        Response = acDataErrAdded
 
 
        MsgBox "The new Manufacturer has been added to the list." & _
        " Please remember to enter remaining contact information" & vbCrLf & _
        "as soon as possible to ensure complete records" & vbCrLf & _
        "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
            , vbInformation, "I-TEAM Utilities"
 
Else
        MsgBox "Please choose a Manufacturer from the list." _
            , vbInformation, "I-TEAM Utilities"
        Response = acDataErrContinue
 
    End If
 
MfgNIL_Exit:
    Exit Sub
MfgNIL_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume MfgNIL_Exit
End Sub

It is notable that the (New Data) is not appearing in the first message box string when it displays. It shows empty quotes instead where the new Mfg name should appear.

This ones been driving me crazier then I normally am. If someone could point out my blind spot it would be much appreciated.
Thanks in advance

[FireMedic]

Forgot to mention that the visible column in the cbo box is a text field while the bound column is an autonumber ID field.

Thanks again

[ADezii]

Hello all,
Having a bit of grief with a NotInList code block that has quit working properly.
I have set it as a public sub in a module so I can use it in multiple forms. I t worked ok in the form module but seems to be a bit glitchy in the public mode.

The new value does not seem to be taking and in spite of "acdataAdded" it continues to pop up the standard warning telling the user to pick an item from the list after the second message box closes. Ultimately the data doesnt get added.
Combo box source is:

SELECT [tbl_Manufacturers].[MfgrID], [tbl_Manufacturers].[MfgrName] FROM tbl_Manufacturers ORDER BY [tbl_Manufacturers].[MfgrName]; 

Limit To List is set to - Yes

Code is As Follows:

Public Sub MfgNIL()
 
On Error GoTo MfgNIL_Err
    Dim IntAnswer As Integer
    Dim StrSql As String
 
    IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "I-TEAM Utilities")
 
      If IntAnswer = vbYes Then
        StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSql
        DoCmd.SetWarnings True
        Response = acDataErrAdded
 
 
        MsgBox "The new Manufacturer has been added to the list." & _
        " Please remember to enter remaining contact information" & vbCrLf & _
        "as soon as possible to ensure complete records" & vbCrLf & _
        "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
            , vbInformation, "I-TEAM Utilities"
 
Else
        MsgBox "Please choose a Manufacturer from the list." _
            , vbInformation, "I-TEAM Utilities"
        Response = acDataErrContinue
 
    End If
 
MfgNIL_Exit:
    Exit Sub
MfgNIL_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume MfgNIL_Exit
End Sub

It is notable that the (New Data) is not appearing in the first message box string when it displays. It shows empty quotes instead where the new Mfg name should appear.

This ones been driving me crazier then I normally am. If someone could point out my blind spot it would be much appreciated.
Thanks in advance

Your Not-In-List code block is meant to be fired from the NotInList() Event of a Combo Box, and not in a Public Procedure. If you wish to make it somewhat portable and run it from a Public Sub-Routine, pass NewData as an Argument to the Sub-Routine, where it can now be accessed, as in:

Call MfgNIL(NewData)

To the best of my knowledge, you will not have control over the Response Parameter, since it is out of context. This means you will not be able to suppress the Default Access Error Message when an Item is entered when it is not in the List (LimitToList = True). You will also have to manually Requery the Combo Box since Response = acDataErrAdded will now be meaningless. You are probably better off leaving the code in the Event for which it was dsesigned.

[FireMedic]

Your Not-In-List code block is meant to be fired from the NotInList() Event of a Combo Box, and not in a Public Procedure. If you wish to make it somewhat portable and run it from a Public Sub-Routine, pass NewData as an Argument to the Sub-Routine, where it can now be accessed, as in:

Call MfgNIL(NewData)

To the best of my knowledge, you will not have control over the Response Parameter, since it is out of context. This means you will not be able to suppress the Default Access Error Message when an Item is entered when it is not in the List (LimitToList = True). You will also have to manually Requery the Combo Box since Response = acDataErrAdded will now be meaningless. You are probably better off leaving the code in the Event for which it was dsesigned.

Sigh... I was afraid of that...There are eighteen subforms and this routine will have to be repeated twice on each .. once for supplier and once for Mfg. Well so be it then. Onward and upward.
I've just gotten back to this after many months of busy work expanding our department responsibilities to include ARFF response at our new on-site aerodrome so I'm still behind the curve on where I left off. Hmmm did someone say commenting your code was a wise idea?

Thanks again ADezzi your assistance is as always much appreciated.

[ADezii]

Sigh... I was afraid of that...There are eighteen subforms and this routine will have to be repeated twice on each .. once for supplier and once for Mfg. Well so be it then. Onward and upward.
I've just gotten back to this after many months of busy work expanding our department responsibilities to include ARFF response at our new on-site aerodrome so I'm still behind the curve on where I left off. Hmmm did someone say commenting your code was a wise idea?

Thanks again ADezzi your assistance is as always much appreciated.

Always glad to help, especially a Fire Paramedic, since I was a professional Fire Fighter for 32+ years, 15 of which were in a HazMat Unit. Take care.

[FishVal]

To the best of my knowledge, you will not have control over the Response Parameter, since it is out of context. This means you will not be able to suppress the Default Access Error Message when an Item is entered when it is not in the List (LimitToList = True). You will also have to manually Requery the Combo Box since Response = acDataErrAdded will now be meaningless. You are probably better off leaving the code in the Event for which it was dsesigned.

Hi, all.

What about passing reference to Response variable to MfgNIL() procedure?
BTW ByRef is default for argument declaration, so in example below it is used for illustrative purpose only.

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
    .....
    MfgNIL(NewData As String, Response As Integer)
    .....
End Sub
 

Public Sub MfgNIL(NewData As String, ByRef Response As Integer)
    ....
    Response = acDataErrContinue
End Sub
 

Regards,
Fish

[ADezii]

Hi, all.

What about passing reference to Response variable to MfgNIL() procedure?
BTW ByRef is default for argument declaration, so in example below it is used for illustrative purpose only.

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
    .....
    MfgNIL(NewData As String, Response As Integer)
    .....
End Sub
 

Public Sub MfgNIL(NewData As String, ByRef Response As Integer)
    ....
    Response = acDataErrContinue
End Sub
 

Regards,
Fish

Excellent point FishVal, and a very interesting one. In the OP's specific circumstance, a Global NotInList Handler would probably be a good idea. One thought, wouldn't the value of Response have to be passed back to the NotInList() Event via the Argument? Another thought, I've never seen this functionality used anywhere except in the NotInList() Event, have you?

[FishVal]

Excellent point FishVal, and a very interesting one. In the OP's specific circumstance, a Global NotInList Handler would probably be a good idea.

Thanks, ADezii.

One thought, wouldn't the value of Response have to be passed back to the NotInList() Event via the Argument?

Both procedures - event handler and the public procedure, work with just the same variable.

Another thought, I've never seen this functionality used anywhere except in the NotInList() Event, have you?

Do you mean returning values in arguments passed by reference?
If so, then it is more likely to be a usual practice. E.g. all those events with "Cancel" argument. :)

Best regards,
Fish

[FireMedic]

Well Gents I don't know. I've put it back as a private sub in each subform and it still fails to add the data and requery itself. I get the default notInList warning right after my custom message box and no satisfaction. . Sigh

Private Sub MfgID_NotInList(NewData As String, Response As Integer)
 
On Error GoTo MfgID_NotInList_Err
    Dim IntAnswer As Integer
    Dim StrSql As String
 
 
    IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "I-TEAM Utilities")
 
      If IntAnswer = vbYes Then
        StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSql
        DoCmd.SetWarnings True
        Response = acDataErrAdded
 
 
        MsgBox "The new Manufacturer has been added to the list." & _
        " Please remember to enter remaining contact information" & vbCrLf & _
        "as soon as possible to ensure complete records" & vbCrLf & _
        "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
            , vbInformation, "I-TEAM Utilities"
 
    Else
        MsgBox "Please choose a Manufacturer from the list." _
            , vbInformation, "I-TEAM Utilities"
        Response = acDataErrContinue
    End If
 
MfgID_NotInList_Exit:
    Exit Sub
 
MfgID_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume MfgID_NotInList_Exit
End Sub

[ADezii]

Well Gents I don't know. I've put it back as a private sub in each subform and it still fails to add the data and requery itself. I get the default notInList warning right after my custom message box and no satisfaction. . Sigh

Private Sub MfgID_NotInList(NewData As String, Response As Integer)
 
On Error GoTo MfgID_NotInList_Err
    Dim IntAnswer As Integer
    Dim StrSql As String
 
 
    IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "I-TEAM Utilities")
 
      If IntAnswer = vbYes Then
        StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSql
        DoCmd.SetWarnings True
        Response = acDataErrAdded
 
 
        MsgBox "The new Manufacturer has been added to the list." & _
        " Please remember to enter remaining contact information" & vbCrLf & _
        "as soon as possible to ensure complete records" & vbCrLf & _
        "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
            , vbInformation, "I-TEAM Utilities"
 
    Else
        MsgBox "Please choose a Manufacturer from the list." _
            , vbInformation, "I-TEAM Utilities"
        Response = acDataErrContinue
    End If
 
MfgID_NotInList_Exit:
    Exit Sub
 
MfgID_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume MfgID_NotInList_Exit
End Sub

The code should work as stated, could there possibly be a Syntax Error on the Manufacturer Name Field?

[MfgrName] ==> [MfgName]

Is there any chance of you sending me the Database, or a subset of it, as an E-Mail Attachment so that I may have a look at it? If so, let me know, and I'll send you my E-Mail Address in a Private Message.

[sierra7]

Hi
I have noticed recently one of my applications started giving 'Not In List' errors even when the selection WAS in the list, because AutoComplete was not working (i.e. user typed "AL Enter or Tab" in a combo box holding list of metals which usually autocompleted and selected 'Aluminium')

Microsoft windged about ANSI 92 compatability (Yes, it had been changed in Tools>Options>Table/Queries) and how all Select statements for combo boxes had to be "SELECT DISTINCT..."

I'm sorry I don't have time to read the whole blog but it might have a bearing on your probs.

S7

[FireMedic]

The code should work as stated, could there possibly be a Syntax Error on the Manufacturer Name Field?

[MfgrName] ==> [MfgName]

Is there any chance of you sending me the Database, or a subset of it, as an E-Mail Attachment so that I may have a look at it? If so, let me know, and I'll send you my E-Mail Address in a Private Message.

Thanks ADezii.
I've parked that block of code for now due to time constraints and have gone to having the Suppliers or Manufacturers form open if the user opts too add the newData. Im using the New data as a variable and inserting it into the name field through the OpenArgs event of the Sup/Mfg forms. I did find that on some of the forms the data was actually added to the table numerous times but was not appearing in the cbo and the Default warning still continued to run after my sub. I'm sure it's just me but I don't have the time to keep troubleshooting this one right now. (I'm referring here to the original code that was giving me grief. The new code I described works fine.)

[FireMedic]

Hi
I have noticed recently one of my applications started giving 'Not In List' errors even when the selection WAS in the list, because AutoComplete was not working (i.e. user typed "AL Enter or Tab" in a combo box holding list of metals which usually autocompleted and selected 'Aluminium')

Microsoft windged about ANSI 92 compatability (Yes, it had been changed in Tools>Options>Table/Queries) and how all Select statements for combo boxes had to be "SELECT DISTINCT..."

I'm sorry I don't have time to read the whole blog but it might have a bearing on your probs.

S7

Hi sierra7
Thanks for the info. Unfortunately I work in a locked system with the exception of my work station in my office in our main firehall which is an unlocked developer machine. Any changes,installs,updates etc have to be pushed from a central authority and scripted for GI in many cases. This takes a long long time and requires the sacrifice of your firstborn and royal assent if you catch my drift.
Which brings me to my new thread I'm going to start now.
Thanks again.