This is great Ken, I don't know why i did not work that out for myself. Thank
you so much for your help.
Regards Ray C
"Ken Sheridan" wrote:
[Quoted Text] > On Dec 28, 5:52 pm, Ray C <R...[ at ]discussions.microsoft.com> wrote:
> > Can anyone help please?
> > I use a Combo Box as a "Find Box" to list all available (non duplicated)
> > entries in a table. Great, but I also want the user to input the name that
> > they want to find and if the name is not in the Table, I want to trap out the
> > error (The Item does not exist in the List) and tell the user that the name
> > does not exist in the Table giving them the option "Do you want to create a
> > new record in that name?"
> > I can do this if I run the required string through the Record Set but I can
> > not seem to trap out the error generated by the Combo Box not being able to
> > find what has been entered.
> > Any help appreciated
> > Ray C
>
> As Tina says, use the control's NotInList event procedure. Here's an
> example which adds a City name to a Cities table:
>
> Private Sub cboCities_NotInList(NewData As String, Response As
> Integer)
>
> Dim cmd As ADODB.Command
> Dim ctrl As Control
> Dim strSQL As String, strMessage As String
>
> Set ctrl = Me.ActiveControl
> strMessage = "Add " & NewData & " to list?"
>
> strSQL = "INSERT INTO Cities(City) VALUES(""" & _
> NewData & """)"
>
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandType = adCmdText
>
> If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
> cmd.CommandText = strSQL
> cmd.Execute
> Response = acDataErrAdded
> Else
> Response = acDataErrContinue
> ctrl.Undo
> End If
>
> End Sub
>
> And here's one which does the same, but in this case opens a frmCities
> form so that other data than the city name, e.g. the county or state
> in which the city is in can also be added to the new record:
>
> Private Sub cboCities_NotInList(NewData As String, Response As
> Integer)
>
> Dim ctrl As Control
> Dim strMessage As String
>
> Set ctrl = Me.ActiveControl
> strMessage = "Add " & NewData & " to list?"
>
> If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
> DoCmd.OpenForm "frmCities", _
> DataMode:=acFormAdd, _
> WindowMode:=acDialog, _
> OpenArgs:=NewData
> ' ensure frmCities closed
> DoCmd.Close acForm, "frmCities"
> ' ensure city has been added
> If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
> NewData & """")) Then
> Response = acDataErrAdded
> Else
> strMessage = NewData & " was not added to Cities table."
> MsgBox strMessage, vbInformation, "Warning"
> Response = acDataErrContinue
> ctrl.Undo
> End If
> Else
> Response = acDataErrContinue
> ctrl.Undo
> End If
>
> End Sub
>
> With this second example the following code also goes in the frmCities
> form's Open event procedure to set the DefaultValue property of the
> City control on the form to the name of the city added in the combo
> box:
>
> Private Sub Form_Open(Cancel As Integer)
>
> If Not IsNull(Me.OpenArgs) Then
> Me.City.DefaultValue = """" & Me.OpenArgs & """"
> End If
>
> End Sub
>
> Ken Sheridan
> Stafford, England
>
>
|