Interesting Combo Box question

Hello all,
Here is my situation. I have a combo box on a form. The combo box is
linked to a qry (qryCustomers) and the form is linked to tblOrders.
The way I currently use the combo box is to place the CustID number in
to the table tblOrders. My question arises in that I would like to
know if there is a way to initiate a new record (in my customers
table) when a user types a value in the combo box that isn't in the
list. Let me explain if that question isn't clear:

My combo box (cboFullName) is linked to qryCustomers.
If the customer's name isn't in the list, they would type in their
name (Last, First)
Access will not like this at the moment (obviously because the value
isn't in the list)
I would like the onEnter event to open a form (called frmCustomers)
which would allow the customer to input their information which would
put them in the customers table and therefore into the qry.
This would allow for better ease of use you might say.

Is this possible?

Thanks

re: Interesting Combo Box question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. You're talking about the "Not In List" event. Read the Access
Help about the event, or, search the Google newsgroup archive for the
NotInList event. There are many examples in comp.databases.ms-access
archives.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRm7K4echKqOuFEgEQLJyQCgviWdjVtE3fVhWvbXLb6TpR twa3MAn1hZ
4CC1qz+XzY5cC4CX9GfwdKOf
=a7ML
-----END PGP SIGNATURE-----


Marcus Canales wrote:[color=blue]
> Hello all,
> Here is my situation. I have a combo box on a form. The combo box is
> linked to a qry (qryCustomers) and the form is linked to tblOrders.
> The way I currently use the combo box is to place the CustID number in
> to the table tblOrders. My question arises in that I would like to
> know if there is a way to initiate a new record (in my customers
> table) when a user types a value in the combo box that isn't in the
> list. Let me explain if that question isn't clear:
>
> My combo box (cboFullName) is linked to qryCustomers.
> If the customer's name isn't in the list, they would type in their
> name (Last, First)
> Access will not like this at the moment (obviously because the value
> isn't in the list)
> I would like the onEnter event to open a form (called frmCustomers)
> which would allow the customer to input their information which would
> put them in the customers table and therefore into the qry.
> This would allow for better ease of use you might say.
>
> Is this possible?[/color]

re: Interesting Combo Box question

Hi,

For the combobox - set Limit To List = yes and use the On Not In List event.
This Microsoft article provides code:

http://support.microsoft.com/?kbid=197526

HTH -Linda


"Marcus Canales" <Magno101@Aol.com> wrote in message
news:7069ce6f.0408102128.795def67@posting.google.c om...[color=blue]
> Hello all,
> Here is my situation. I have a combo box on a form. The combo box is
> linked to a qry (qryCustomers) and the form is linked to tblOrders.
> The way I currently use the combo box is to place the CustID number in
> to the table tblOrders. My question arises in that I would like to
> know if there is a way to initiate a new record (in my customers
> table) when a user types a value in the combo box that isn't in the
> list. Let me explain if that question isn't clear:
>
> My combo box (cboFullName) is linked to qryCustomers.
> If the customer's name isn't in the list, they would type in their
> name (Last, First)
> Access will not like this at the moment (obviously because the value
> isn't in the list)
> I would like the onEnter event to open a form (called frmCustomers)
> which would allow the customer to input their information which would
> put them in the customers table and therefore into the qry.
> This would allow for better ease of use you might say.
>
> Is this possible?
>
> Thanks[/color]

re: Interesting Combo Box question

Hi!
Here's a routine I use which works:

(Put it in your Combo's NotInList_event)

On Error GoTo Err_KIDNotINLIST

Dim lngCustID As Long

Response = acDataErrContinue
If IsNull(Me![CustID]) Then
Me![CustID].Text = ""
Else
lngCustID = Me![CustID]
Me![CustID] = Null
End If
DoCmd.OpenForm "frmRegCustFromOrders", acNormal, , , acFormAdd, , NewData
Me![CustID].Requery
If lngCustID <> 0 Then Me![CustID] = lngCustID

Exit_KIDNotINLIST:
Exit Sub
Err_KIDNotINLIST:
MsgBox Err.Number & " " & "ORD/KIDNotINLIST" & Chr(13) _
& Err.Description, vbOKOnly
Resume Exit_KIDNotINLIST