SetWarnings False but still showing "Not In List" message

[shuffine@hcreit.com]

I am working in Access 2010. A bound combo box, Not In List event. I have the following code. It opens up a form to add the new name in. The form opens and then the "Not in List" message pops up. I have tried adding the SetWarnings to both forms, created a SetWarning macro, to no available. The message keeps popping up. Please help! My code is below. I know I need to set the warnings back to true - but for now, I just want to get rid of the message before sticking it back into the code.

Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The acquirer/buyer " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Healthcare REIT")
If intAnswer = vbYes Then

DoCmd.SetWarnings False
DoCmd.OpenForm "frmASO", acNormal, , , acFormAdd, , acDialog
Else
MsgBox "Please choose an acquirer/buyer from the list." _
, vbInformation, "Healthcare REIT"
Response = acDataErrContinue
End If

 

[pr2-eugin]

Hello shuffine@hcreit.com, Welcome to AWF

I am unable to follow what you are actually trying to do (or) what is not working, can you please give another shot at explaining?

Dim strSQL As String

If MsgBox("The acquirer/buyer " & Chr(34) & NewData & Chr(34) & " is not currently listed." & vbCrLf & _
            "Would you like to add it to the list now?", vbQuestion + vbYesNo, "Healthcare REIT") = vbYes Then
    DoCmd.OpenForm "frmASO", acNormal, , , acFormAdd, , acDialog
Else
    MsgBox "Please choose an acquirer/buyer from the list.", vbInformation, "Healthcare REIT"
    Response = acDataErrContinue
End If

 

[shuffine@hcreit.com]

I apologize. The warning message "Not In List" is appearing even though I have the setwarnings to False. I can't seem to get rid of it. It happens right before the form is loaded/opened to add a new name. I don't understand why it keeps appearing when I have the warnings set to false.

 

[pr2-eugin]

So to get this right, you have handled this using the Not In List event available in the design view of the ComboBox?

 

[shuffine@hcreit.com]

Yes, the code I provided is Not In List event. But I have tried multiple things, one being a macro on the form open event, setwarnings to no, and that didn't work either.

 

[pr2-eugin]

Is the RowSourceType of the ComboBox "Value List"? Or a Query? I actually have never used this Not in List event. I normally use the List Items Edit Form. Same thing, but more flexible.

 

[shuffine@hcreit.com]

THe combo box is bound and it is a query. I have never heard of this List Items Edit Form - tell more, please.

 

[pr2-eugin]

Create a Form that is Bound to the Lookup Table, having controls and stuff that needs to be there.. Then save the Form Design by giving a Name (example lookupFrm), then select the ComboBox and in the Data tab of the Properties window, there is this property "List Item Edit Form", in which you simply use lookupFrm. Then when the item is not listed you will have the List edit button, which you can click to launch this Form.

PS : I just looked at the http://support.microsoft.com/kb/197526, it shows that you need to set the response

Response = acDataErrAdded

Maybe that would sort your problem? Not 100% sure though.

 

[shuffine@hcreit.com]

Thanks Paul - I added that back in, I forgot to do that. However, it does not affect my results - the popup message still comes up. I tried using the List Item Edit Form - the popup message changed a little bit, but same thing. I don't want the popup window/message at all. That's why I am using the SetWarnings to False. Thanks for trying though - I appreciate it!

 

[missinglinq]

The SetWarnings method turns system messages on or off, things such as 'you are about to delete N number of Records' or 'you are about to Append one Record,' etc. The NotInList event is triggered because you, as the developer, told Access to show it if the entry is not in the list! You, not the system, did this when you set the LimitToList Property to Yes.

Response = acDataErrAdded

doesn't work because NotInList does not trigger the Error Event.

Linq ;0)>

 

[shuffine@hcreit.com]

The system actually won't allow me to set the Limit to List to no, because the first visible column isn't equal to the bound column. I do not want to change the first visible column to show the primary key (bound column), it doesn't mean anything to the user.

I realize I am in error not the system, but I don't remember having this issue before. Your response was somewhat harsh.

 

[Foe]

I've dealt with the "first visible column isn't equal to the bound column" recently. If I recall correctly, my fix was to set the bound column to 0.5", make my changes and then set the bound column back to 0"

 

[shuffine@hcreit.com]

Foe - unfortunately, when you set it back to 0 the Limit to List sets back to yes. But thanks for trying to help me. I appreciate it!

 

[Foe]

How about Column Widths: 0.0007";1"

I just tried that and the first column is completely undetectable.

 

[shuffine@hcreit.com]

Unfortunately, that just makes it where they have to select the first column value or they receive a message "The value you entered isn't valid for this field".

 

[Foe]

Ah, I see that now.

I just played around a bit and this appears to work:

Change the order of the two fields in the Row Source so that your bound field is second and the one you want them to select is first.

Change the Bound Column to 2

Set the width of your first Column to match the width of the entire control. I left the second Column's width at 0.0007" and didn't end up with a horizontal scroll bar within the drop-down list. All I see when I drop the list down is my first column and that is what's get's selected. I didn't actually try to save anything to make sure the data is recorded properly since I'm using a live form and not actually saving the changes after looking at the result in the relevant combobox, but this looks promising.

 

[shuffine@hcreit.com]

I tried it - but when setting the LimitToList to No - I still get the same message - The first visible column isn't equal to the bound column. If I keep the LimitToList set to Yes - it still gives me the "The text you enterested isn't an item in the list. Do you want to edit the items in the list?"

I looked all over the web to try and find the solution. I found some saying to add the code:
Response = acDataErrContinue

which I have done and still the message comes up. Makes me feel good I am not the only one with this issue, but frustrated that I can't find the solution anywhere.

 

[Foe]

I tried Hope you find an answer

 

[shuffine@hcreit.com]

I know Foe - thank you very much, I really appreciate the effort!!

 

[Foe]

Just had another thought. Apparently your issue is stuck in my head.

Any possibility that you can populate that combobox using another source that doesn't have a separate primary key? Not sure what your whole setup looks like, but maybe the primary key can be dropped from that table or you can create a 'keyless' list on the fly to populate that combobox.

Maybe change the Row Source Type to Value List and generate it on the fly?