To add a completely new customer to the database you have to insert a new row
(record) into the Customers table. All you should have to do is move to a
new record in the main form and enter the new customer record.
The fact that the main form is based on a query which restricts it to those
customers currently with systems makes no difference when adding a new
customer. If, however, you were to close the form without adding any systems
for the customer and reopen it the new customer would not be shown in the
form as its based on a query which only returns customers with systems.
This brings us to another scenario, where you might want to add systems to a
customer who's already in the database but not shown in the main form because
they don't as yet have any systems. There are various ways you could do that
but one way would be to pop up a form in dialogue mode, bound to the
CustomerSystemInventory table and then requery the main form when the new
form is closed. As well as having controls bound to the column(s) containing
the system data this form would have a combo box bound to the CustomerID
foreign key column, set up in the same way as I described for ContactID in my
last post. You'd open this new form in dialogue mode from a button on your
main form like so, passing the name of your main form to it:
' open customer system inventory form in dialogue mode
' for adding a new record
DoCmd.OpenForm "frmCustomerSystemInventory", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.Name
where frmCustomerSystemInventory is the name of the new form.
You now need your main form to be requeried, and to move to the record for
the customer for whom you've inserted a row or rows in the dialogue form, so
in the new form's AfterInsert event procedure put:
Dim rst As Object
Dim frm as Form
' return a reference to your main form
Set frm = Forms(Me.OpenArgs)
' requery main form
frm.Requery
' navigate to customer by finding customer in clone
' of main form's recordset and then synchronizing
' form's bookmark with recordset's
Set rst = frm.Recordset.Clone
With rst
.FindFirst "CustomerID = " & Me.CustomerID
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With
As regards adding a new contact there's no reason why you can't use option 2.
You would not lock/disable the combo box bound to ContactID, only the other
controls with the other data like email, phone number etc.
Similarly you'd only need code in the ContactID combo box's NotInList event
procedure. This code box would be set up in the way I described and the code
would be very similar to the example for salespeople (which was actually
written by for a colleague in California). What would happen when you enter
a new contact in the combo box in the format Ken Sheridan, i.e. firstname
<space> lastname, is that, once you've answered yes at the conformation
message box, the form to add the new contact would open in dialogue mode.
You'd then add their details and which customer they represent in the
dialogue form. When you close the dialogue form the new name would be added
to the combo box and the other details you entered in the dialogue form would
appear in the locked/disabled controls.
You will also need to add the other module to the database to pass the values
between the form as its this module which allows you to pass the first and
last names as multiple arguments via the OpenArgs mechanism.
Ken Sheridan
Stafford, England
Your first paragraph about which fields to use was invaluable. Thank you!
I do have a related question/problem. I set up a form/subform for
Customers/CustomerSystemInventory. I wanted to see the customers inventory
like this. Customer A (Next Record) Customer B
Systems 3 System 2
System 4 System 3
I queried customers and Customers system INventory for the main form. ( I
only wanted to show customers that had systems. But at the same time have a
way to add new customers to systems. But when I try to add new customers
by Select Distinct From Customers, I get a message, field can't be edited,
it's bound to an auto number field. So I changed to select the CLEC ID from
the CustomerSystemInventory. The only problem with that is when I am
scrolling through records it shows the same customer numerous times (like
it's caught in a loop.) Northwind's Orders form was set up like this, but it
doesn't do the same thing!
I'm intrigued by your module. I played around with option 2, but that only
allows me to select customers that have contacts. Most of my records are yet
to have contacts, so would I only be able to use option 1? And wasn't sure
about setting all of the contact fields to enable = false, because I would of
course need to type the contact info if not on the list. I see the
notinlist event property, so would I need to add something to that on the
other fields as well?
Thank you very much for your detail in the first reply!
Firstly you should not insert the names etc into the table underlying the
Testing Form; that would introduce redundancy and the possibility of
[quoted text clipped - 208 lines]
If this is feasible, how would I do it? Or an easier way to accomplish the
same thing?
