Combo Box - Not in list Message

[LarryDeLaruelle]

I have created a combo box, cboAgency, on my frmContacts as follows:

Used first wizard option ". . . look up values in a table or query" and based it on my tblAgency

cboAgency has two columns:

Column 1 is AgencyID bound to frmContacts.AgencyID
Column 2 is AgencyName

Column widths are 0";1.0"

Limit to list is set to Yes; can't change it to No because "the first visible column width isn't equal to the bound column".

On the "On Not in List" event I set warnings to false and issue a prompt to the user: "This Agency is not in the Database do you wish to add it?" If the user responds OK I set the cboAgency value to null and then open the Add Agency form for data entry. If the user responds Cancel, I set the cboAgency value to null and set focus to cboAgency.

In either case, I get the message: "The text you entered isn't in the list. My question is how do I supress this message?

Or, is there a better way to handle the On Not In List process?
Larry De Laruelle
larry1de@yahoo.com

 

[braindead2]

if you look under the FAQ you will see 2 examples under forms for combobox
the solution involves
Response = acDataErrContinue

 

[LarryDeLaruelle]

Thanks Braindead2. I found them and will try it out.

I must be brain dead this afternoon also since I looked at the faq's and scrolled right past those.

Larry De Laruelle
larry1de@yahoo.com

 

[Quehay]

Larry,

With a lookup that shows a reference field but actually embeds an ID/Key value for future joins you have to put the record in the Parent/One side first to be able to follow the scheme (creating a flat table lookup that inserts the actual reference value is another option and this will allow "not in list entries" with no problem--this is good for simple lookup tables--not having a later join to perform can speed performance in some cases).

Here's a response posted for nearly the same question elsewhere today:

Have a button/hyperlink on your form that links to the parent records form. If a value isn't on the list click the button and enter a new record (on the One side). (This is a "if you don't see it add it in the source table rather than try to type it" mode of operation.)

In the Form_Close Event of the One side form have:

Forms!frm_YourForm.cboLookup.Requery

The new One record will be in the box.

 

[LarryDeLaruelle]

BrainDead:

I tried the code described in faq181-66 with only minor changes and it works fine except I'm still getting this message:

"The text you entered isn't an item in the list"
"select and item . . ." etc.

It's this message that I want to suppress. Any idea how to do that? The message appears immediately after clicking on the OK button.

Here is my code:

Private Sub cboAgency_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboAgency_NotInList

If MsgBox("This Agency is not in the database" & Chr(13) & Chr(13) & _
"If you wish to add it click OK", vbOKCancel, "TCC Contact Database&quot = vbOK Then

DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmAgencyAdd", acNormal, , , acFormAdd
Response = acDataErrAdded
Else
Response = acDataErrContinue
cboAgency = ""
cboAgency.SetFocus
End If

Exit_cboAgency_NotInList:
Exit Sub

Err_cboAgency_NotInList:
MsgBox Err.Description
Resume Exit_cboAgency_NotInList

End Sub

Quehay: Thanks for the post but I'm trying to idiot-proof this thing as much as possible. Knowing the way users do things they'll ignore the button and still try to enter an agency that isn't on the list.
Larry De Laruelle
larry1de@yahoo.com

 

[Quehay]

This is totally off the cuff, but what happens if you remove the "SetFocus" line. This method can often be problematic. The cbo = "" could be a problem also, as a zero length string is being entered and it interprets this as a selection (guessing again).

 

[rohalyla]

Try this code:

Sorry its not that easy to read -- when I pasted it in here, I lost the formatting


Private Sub sales_rep_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me![sales-rep]

' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Do you wish to add it?", vbOKCancel) = vbOK Then

' Set Response argument to indicate that data is being added. - THIS WILL SUPPRESS THE MESSAGE
Response = acDataErrAdded

' set up sql statement to add new data
strSQL = "INSERT INTO tblSalesReps ([sales-rep]) VALUES ('" & NewData & "');"

' turn off warnings
DoCmd.SetWarnings False

' run sql
DoCmd.RunSQL strSQL

' turn on warnings
DoCmd.SetWarnings True

Else

'THIS WILL SUPPRESS THE MESSAGE EVEN IF THE USER DOESN'T WANT TO ADD THE DATA
' If user chooses Cancel, suppress error message and undo changes.

Response = acDataErrContinue
ctl.Undo

End If

End Sub

 

[LarryDeLaruelle]

Rohalyla:

Your code looks pretty close to mine other than setting a control to the combo box. Is that the trick to this?

I need to open a form for the data entry since I am capturing address, phone and other information.

I already have the acDataErrAdded and acDataErrContinue in my code but the Message Box stills displays.

Thanks.
Larry De Laruelle
larry1de@yahoo.com

 

[LarryDeLaruelle]

Thanks all. I found the answer. I noticed that when I clicked on the Cancel option in the Message Box the message I want to suppress did not appear. So . . .

I replaced the line in the vbOK response part of the conditional:

Response = acDataErrAdded

with

Response = acDataErrContinue

Works just fine now. One additional question: While I see what the ac commands do, I'm not clear on how and why they work. Can you point me at any MS Help or other venues for information on this?

Thanks again to all who responded.
Larry De Laruelle
larry1de@yahoo.com