By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

NotInList making me crazy(er)

FireMedic
P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub MfgNIL()
  2.  
  3. On Error GoTo MfgNIL_Err
  4.     Dim IntAnswer As Integer
  5.     Dim StrSql As String
  6.  
  7.     IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
  8.         Chr(34) & " is not currently listed." & vbCrLf & _
  9.         "Would you like to add it to the list now?" _
  10.         , vbQuestion + vbYesNo, "I-TEAM Utilities")
  11.  
  12.       If IntAnswer = vbYes Then
  13.         StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
  14.                  "VALUES ('" & NewData & "');"
  15.         DoCmd.SetWarnings False
  16.         DoCmd.RunSQL StrSql
  17.         DoCmd.SetWarnings True
  18.         Response = acDataErrAdded
  19.  
  20.  
  21.         MsgBox "The new Manufacturer has been added to the list." & _
  22.         " Please remember to enter remaining contact information" & vbCrLf & _
  23.         "as soon as possible to ensure complete records" & vbCrLf & _
  24.         "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
  25.             , vbInformation, "I-TEAM Utilities"
  26.  
  27. Else
  28.         MsgBox "Please choose a Manufacturer from the list." _
  29.             , vbInformation, "I-TEAM Utilities"
  30.         Response = acDataErrContinue
  31.  
  32.     End If
  33.  
  34. MfgNIL_Exit:
  35.     Exit Sub
  36. MfgNIL_Err:
  37.     MsgBox Err.Description, vbCritical, "Error"
  38.     Resume MfgNIL_Exit
  39. 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
Apr 13 '08 #1
Share this Question
Share on Google+
12 Replies


FireMedic
P: 18
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
Apr 13 '08 #2

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub MfgNIL()
  2.  
  3. On Error GoTo MfgNIL_Err
  4.     Dim IntAnswer As Integer
  5.     Dim StrSql As String
  6.  
  7.     IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
  8.         Chr(34) & " is not currently listed." & vbCrLf & _
  9.         "Would you like to add it to the list now?" _
  10.         , vbQuestion + vbYesNo, "I-TEAM Utilities")
  11.  
  12.       If IntAnswer = vbYes Then
  13.         StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
  14.                  "VALUES ('" & NewData & "');"
  15.         DoCmd.SetWarnings False
  16.         DoCmd.RunSQL StrSql
  17.         DoCmd.SetWarnings True
  18.         Response = acDataErrAdded
  19.  
  20.  
  21.         MsgBox "The new Manufacturer has been added to the list." & _
  22.         " Please remember to enter remaining contact information" & vbCrLf & _
  23.         "as soon as possible to ensure complete records" & vbCrLf & _
  24.         "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
  25.             , vbInformation, "I-TEAM Utilities"
  26.  
  27. Else
  28.         MsgBox "Please choose a Manufacturer from the list." _
  29.             , vbInformation, "I-TEAM Utilities"
  30.         Response = acDataErrContinue
  31.  
  32.     End If
  33.  
  34. MfgNIL_Exit:
  35.     Exit Sub
  36. MfgNIL_Err:
  37.     MsgBox Err.Description, vbCritical, "Error"
  38.     Resume MfgNIL_Exit
  39. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 13 '08 #3

FireMedic
P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 14 '08 #4

ADezii
Expert 5K+
P: 8,638
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.
Apr 14 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo0_NotInList(NewData As String, Response As Integer)
  2.     .....
  3.     MfgNIL(NewData As String, Response As Integer)
  4.     .....
  5. End Sub
  6.  
Expand|Select|Wrap|Line Numbers
  1. Public Sub MfgNIL(NewData As String, ByRef Response As Integer)
  2.     ....
  3.     Response = acDataErrContinue
  4. End Sub
  5.  
Regards,
Fish
Apr 14 '08 #6

ADezii
Expert 5K+
P: 8,638
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo0_NotInList(NewData As String, Response As Integer)
  2.     .....
  3.     MfgNIL(NewData As String, Response As Integer)
  4.     .....
  5. End Sub
  6.  
Expand|Select|Wrap|Line Numbers
  1. Public Sub MfgNIL(NewData As String, ByRef Response As Integer)
  2.     ....
  3.     Response = acDataErrContinue
  4. End Sub
  5.  
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?
Apr 14 '08 #7

FishVal
Expert 2.5K+
P: 2,653
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
Apr 14 '08 #8

FireMedic
P: 18
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub MfgID_NotInList(NewData As String, Response As Integer)
  2.  
  3. On Error GoTo MfgID_NotInList_Err
  4.     Dim IntAnswer As Integer
  5.     Dim StrSql As String
  6.  
  7.  
  8.     IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
  9.         Chr(34) & " is not currently listed." & vbCrLf & _
  10.         "Would you like to add it to the list now?" _
  11.         , vbQuestion + vbYesNo, "I-TEAM Utilities")
  12.  
  13.       If IntAnswer = vbYes Then
  14.         StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
  15.                  "VALUES ('" & NewData & "');"
  16.         DoCmd.SetWarnings False
  17.         DoCmd.RunSQL StrSql
  18.         DoCmd.SetWarnings True
  19.         Response = acDataErrAdded
  20.  
  21.  
  22.         MsgBox "The new Manufacturer has been added to the list." & _
  23.         " Please remember to enter remaining contact information" & vbCrLf & _
  24.         "as soon as possible to ensure complete records" & vbCrLf & _
  25.         "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
  26.             , vbInformation, "I-TEAM Utilities"
  27.  
  28.     Else
  29.         MsgBox "Please choose a Manufacturer from the list." _
  30.             , vbInformation, "I-TEAM Utilities"
  31.         Response = acDataErrContinue
  32.     End If
  33.  
  34. MfgID_NotInList_Exit:
  35.     Exit Sub
  36.  
  37. MfgID_NotInList_Err:
  38.     MsgBox Err.Description, vbCritical, "Error"
  39.     Resume MfgID_NotInList_Exit
  40. End Sub
Apr 20 '08 #9

ADezii
Expert 5K+
P: 8,638
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub MfgID_NotInList(NewData As String, Response As Integer)
  2.  
  3. On Error GoTo MfgID_NotInList_Err
  4.     Dim IntAnswer As Integer
  5.     Dim StrSql As String
  6.  
  7.  
  8.     IntAnswer = MsgBox("The Manufacturer " & Chr(34) & (NewData) & _
  9.         Chr(34) & " is not currently listed." & vbCrLf & _
  10.         "Would you like to add it to the list now?" _
  11.         , vbQuestion + vbYesNo, "I-TEAM Utilities")
  12.  
  13.       If IntAnswer = vbYes Then
  14.         StrSql = "INSERT INTO tbl_Manufacturers([MfgrName]) " & _
  15.                  "VALUES ('" & NewData & "');"
  16.         DoCmd.SetWarnings False
  17.         DoCmd.RunSQL StrSql
  18.         DoCmd.SetWarnings True
  19.         Response = acDataErrAdded
  20.  
  21.  
  22.         MsgBox "The new Manufacturer has been added to the list." & _
  23.         " Please remember to enter remaining contact information" & vbCrLf & _
  24.         "as soon as possible to ensure complete records" & vbCrLf & _
  25.         "Use the 'Find' control on the 'Manufacturers' form to locate this Manufacturer." _
  26.             , vbInformation, "I-TEAM Utilities"
  27.  
  28.     Else
  29.         MsgBox "Please choose a Manufacturer from the list." _
  30.             , vbInformation, "I-TEAM Utilities"
  31.         Response = acDataErrContinue
  32.     End If
  33.  
  34. MfgID_NotInList_Exit:
  35.     Exit Sub
  36.  
  37. MfgID_NotInList_Err:
  38.     MsgBox Err.Description, vbCritical, "Error"
  39.     Resume MfgID_NotInList_Exit
  40. End Sub
The code should work as stated, could there possibly be a Syntax Error on the Manufacturer Name Field?
Expand|Select|Wrap|Line Numbers
  1. [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.
Apr 20 '08 #10

Expert 100+
P: 446
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
Apr 20 '08 #11

FireMedic
P: 18
The code should work as stated, could there possibly be a Syntax Error on the Manufacturer Name Field?
Expand|Select|Wrap|Line Numbers
  1. [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.)
Apr 22 '08 #12

FireMedic
P: 18
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.
Apr 22 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.