NotInList property

  • Thread starter Thread starter Janna
  • Start date Start date
J

Janna

I'm trying to use a combo box for the user to look up and
populate customer information on a form (name, address,
phone#). I have the following code (see below)in the
NotInList property of the combo box and it seems to work
fine except.....the users also have one-time customers.
That is, they want the flexibility to enter a new
customer's name and save it with that one record but not
have the customer's name added permanently to the table
from which the combo box gets its data. Is this possible
with what I've got or do I need to approach this in an
entirely different way.

Appreciate any help!!!!



Private Sub cboCustID_NotInList(NewData As String,
Response As Integer)
'Adds a Customer that is not in the list to the list, if
the user wishes to do so.
Dim strMessage As String
Dim intAnswer As Integer
strMessage = "'" & [NewData] & "' is currently not in
your list. Do you wish to add it?"
intAnswer = MsgBox(strMessage, vbOKCancel +
vbQuestion)
If intAnswer = 1 Then
Set dbsVBA = CurrentDb
Set rstKeyWord = dbsVBA.OpenRecordset
("tblCustomerData")
rstKeyWord.AddNew
rstKeyWord!NAME = NewData
rstKeyWord.Update
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If
End Sub
 
Janna,

What you are trying to do is a big mistake!

Presuming your users are entering orders, your tables should look like:
TblCustomer
CustomerID
CustomerName
etc

TblOrders
OrderID
CustomerID
OrderDate
Etc.

TblOrderDetails
OrderDetailsID
OrderID
ItemID
Quantity
Price
etc

As you can see, when each order record is entered in TblOrders, you enter the
CustomerID and not the CustomerName. The customer combobox is used for this.
This way, in other forms and reports where you need to display the customer for
the order, you join TblCustomer and TblOrders on CustomerID in a query to get
the customer name.
 
Back
Top