Combo Box - Getting around limit to list error and acDialog

[Grounded]

I have two forms - one enters info about people. One enters info about organizations. when entering a person you choose an organization for them. If you type in on that isn't on the list then i fire this code:

MsgBox "Organization not in list - would you like to add it?", vbOKCancel, "Not on list"

If Response = vbOK Then
Me.cmbOrg_ID.Text = ""
Me.cmbOrg_ID.Value = Null


DoCmd.OpenForm "frmOrganization", acDesign, , , acFormAdd, acDialog


Response = acDataErrAdded
Response = acDataErrContinue

End If

This successfully moves me to the other form to enter a new org without triggering that damn item on list error that makes me insane. I enter the new org and close the form, returning to my blank combo which is waiting for me to choose the new org.

PROBLEM: i can not get the form to requery so the new org is not in the list. i used acDialog to avoid the Not in List error but when i close the org form it doesn't trigger the activate event in frmPeople. I have read i need to use an OK button instead of a close button but can't figure out how to set one up.

 

[Eupher]

Hi, Grounded,

Your code has a number of mistakes which are bound to cause problems. If you want I will explain them, but it might be more helpful to refer you to a good working example:

http://msdn.microsoft.com/library/d...us/vbaac11/html/acevtNotInList_HV05187720.asp

Good luck and please post back if you need more help.

Ken S.

 

[Grounded]

Ken, that was helpful for a code cleanup but it didn't solve my problem - infact it is worse now.

New Code:

If MsgBox("Organization not in list - would you like to add it?", vbOKCancel, "Not on list") = vbOK Then

Response = acDataErrAdded
DoCmd.OpenForm "frmOrganization", acDesign, , , acFormAdd, acDialog

Else

Response = acDataErrContinue
ctl.Undo

End If

Now i get the infamous "text you entered isn't an item in the list" error before i get access to frmOrg And when i close frmOrg i return to the list with the typed in value which still isn't on the list.

I realize also that i would rather use one of the values entered in the org form - not what was initially punched in (i ask for a full name and an abbreviation and want to make sure the dropdown uses the abbreviation). This secondary at this point though.

 

[Grounded]

This worked well in the end:

Private Sub cmbOrg_ID_NotInList(NewData As String, Response As Integer)
On Error GoTo Error_Check

'ask if they want to enter a new value
If MsgBox("Organization not in list - would you like to add it?", vbOKCancel, "Not on list") = vbOK Then

'clear out the entered values
Me.cmbOrg_ID.Text = ""
Me.cmbOrg_ID.Value = Null

'tell it to go through the error
Response = acDataErrContinue

'use acDialog to halt code until you return to this form
DoCmd.OpenForm "frmOrganization", acNormal, , , acFormAdd, acDialog

'refresh when you return to get the new value into the list
Me.Refresh

Else

'clear the value
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue

End If

Error_Check:

End Sub