GL,
Good. It sounds like your queries and the subforms are setup
properly so we are getting pretty close to having it working
the way that you want it to.
If you really focus on and think about what I said about the
critical issue being the need to get the CustomerID into the
CustomerID field from the contract table into the
contract/customer query that fuels your subform, you should
understand why you are having the current problem with the
combo box and the new customer not being in the list.
The combobox is looking up the existing customers and
putting them in the list. The combobox is displaying the
customer name and is allowing you to type that in to select
the customer, but it is actually being driven by the
CustomerID field which is hidden. No problem there when you
enter an existing one, just when you enter a new one.
If you enter a name not in the customer table, your
NotInList event will trigger which gives you the message
that you are getting now. You are right. Setting the
LimitToList property to No would normally allow you to type
in something that isn't in the list and save that value in
the table. This presents a couple of problems for you. 1) If
you just typed in a new customers name in and saved that in
the contract record there would be no corresponding record
entered into the customer table to reference later or to use
to add a second contract to that customer. There would also
be no opportunity for you to add any other specific customer
info such as address, phone, etc... 2) Now back to the
critical item. Although the combobox is allowing you to type
in a customer name, it is really dealing with the CustomerID
field exclusively, not the name fields. If you are trying to
enter a new customer there is no CustomerID yet for the
non-existant customer so there is no ID for the combobox to
save as that ID is only assigned when the customer is
entered into the Customer table! The error that you are now
receiving is saying in effect: "I can't enter the name you
typed into the ID field as it is the wrong data type".
Actually the error is truly being raised because you are
typing something into the 2nd or 3rd column of the combobox
and it is really only concerned with the first hidden column
which is the only one that it binds it to the table. To make
a long story short, turning the LimitToList off is only
really useful for a single column combobox where the source
field is not an Access autonumber ID field.
So... To add a new customer's contract the bottom line is
that you need to enter the new customer first so that they
are in the combobox and already have an ID when you need to
assign them to the contract. There are a couple of ways to
approach this.
1) Enter the Customer first before going to the Contracts
form. Easy to design (do nothing) but not totally user
friendly as they need to switch back and forth between
forms.
2) Put a button on the subform that would allow the user to
open the Customer form form entry if the user can't find
someone in the list. Better, but you still have to make sure
that the Contract form gets refreshed to show the new
customer after they have been entered in the other form.
3) Use the NotInList event that is raised when you enter a
name that is not there ( as long as LimitToList is set back
to Yes) to open up a form to open a form to enter the new
customer info. This can be done with a dialog style form
that can then signal the calling form that the new customer
info is now available for the list.
Here is some sample VBA code that assumes that you have a
customer entry form named frmAddCustomer to use to enter the
customer info and your combobox is named cboCustomerID. This
code would be entered into the NotInList event of your
combobox:
Private Sub cboCustomerID_NotInList(NewData As String,
Response As Integer)
Dim strMsg as String
' Verify that the user wants to enter a new customer
strMsg = "No matching entry in the Customer List. Would you
like to enter a new Customer now?"
' They say Yes, open the customer form
If msgbox(strMsg, vbYesNo,"Add New Customer?") = vbYes Then
DoCmd.OpenForm "frmAddCustomer",,,,,acDialog
Response = acDataErrAdded
Else
' They say No, don't add one. Cancel combobox entry
Response = acDataErrContinue
Me!cboCustomerID.Undo
End if
The acDataErrAdded response will trigger after your customer
form is closed and will tell the combobox that the new entry
should now be available and to take a fresh look for it. If
everything was entered correctly it should now be in the
list. This is a much more elegant approach.