Update Records in Current Table and another Table

[Confused]

I have a testing table to keep track of customers that test and need to
include the "specific" contact for the test, so I have name, email,
telephone. I want to have a combo box that selects the name (last name
mabye) from the Contacts table equivilant to the same record based on
Customer ID. (not sure how to do this part) And if name, email and telephone
in that record are not null, copy them and paste them into the Testing Form.

The other scenario would be if there is no Contact for that customer in the
contacts table, then could I type the name in the combo box, then the
Telephone field, and the email field etc. and have it update the Contacts
Table.

If this is feasible, how would I do it? Or an easier way to accomplish the
same thing?

 

[Confused]

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!

 

[Confused]

The pop up form is working great. That is exactly what I was needing, the
ability to add an existing customer to the CustomerSystemInventory Table.
Thanks!

As for the Contact Module, I have put the query of step one on the form. I
must be missing something basic. But I can only select names of customers
that are in the Contacts table from the Combo Box with the two columns that
list customer name and Contact Name...

How would I select a customer that is not on the contact list? I tried
selecting a customer out of my Customer Combo Box first, but that still did
not allow me to see the customer name in your combo list. And I tried it
using a name that was on the Contact List, but the two combo boxes didn't
sync up( I got an error about "the current field must match the join key?
etc.... So how would I pick a customer and have it all associate correctly?

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?

 

[Confused]

I always want a contact to go in the Testing TAble. So could I just set up
combo boxes on contact name, email, tn. And if there are contacts available
for that customer select them, and if not type the names in the combo. If
so how would I have the combo select from the appropriate customer that I am
testing with? How would I allow the combo to be updateable so that when I
type a new contact if one is not in the contacts table to be added to the
contacts table after I type it. Does this sound like a feasible alternative?

I'd assumed that each customer would have at least one contact. This was
remiss of me because what you've identified here is a generic problem, where
there is a missing link in a relationship chain. It frequently crops up with
geographical data where you might have a model:

Cities>---Regions>----Countries

but not all countries have a regional structure (I'd guess Lichtenstein and
Monaco are cases in point!).

In your case the absence of any contact for a customer is analogous to the
absence of any region for a country. The solution in both cases is to
provide a set of rows in the table in the middle of the chain which makes the
link between the tables to its left and right, so for each customer without
any actual contacts you'd have a row in contacts with a value such as N/A
instead of the usual name of the contact. Each N/A row would have a
different CustomerID value of course. Now when you join the tables there is
no break in the chain and each customer without contacts will be listed with
a N/A contact. Your Tests table would in these cases reference the N/A
contact for the company rather than a real contact, just as cities in
Lichtenstein or Monaco would reference a N/A region for the country.

Ken Sheridan
Stafford, England

The pop up form is working great. That is exactly what I was needing, the
ability to add an existing customer to the CustomerSystemInventory Table.
Thanks!

As for the Contact Module, I have put the query of step one on the form. I
must be missing something basic. But I can only select names of customers
that are in the Contacts table from the Combo Box with the two columns that
list customer name and Contact Name...

How would I select a customer that is not on the contact list? I tried
selecting a customer out of my Customer Combo Box first, but that still did
not allow me to see the customer name in your combo list. And I tried it
using a name that was on the Contact List, but the two combo boxes didn't
sync up( I got an error about "the current field must match the join key?
etc.... So how would I pick a customer and have it all associate correctly?

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

[quoted text clipped - 107 lines]

If this is feasible, how would I do it? Or an easier way to accomplish the
same thing?

 

[ashhad]

i want a latest version of outlook.
Ashhad

The pop up form is working great. That is exactly what I was needing,
the
ability to add an existing customer to the CustomerSystemInventory Table.
Thanks!

As for the Contact Module, I have put the query of step one on the form.
I
must be missing something basic. But I can only select names of customers
that are in the Contacts table from the Combo Box with the two columns
that
list customer name and Contact Name...

How would I select a customer that is not on the contact list? I tried
selecting a customer out of my Customer Combo Box first, but that still
did
not allow me to see the customer name in your combo list. And I tried
it
using a name that was on the Contact List, but the two combo boxes didn't
sync up( I got an error about "the current field must match the join key?
etc.... So how would I pick a customer and have it all associate
correctly?

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?

 

[Confused]

Ken,

Actually, this is working pretty nicely. But all I did was create the
option 2 for the combo box as per your instructions. I then have all of the
contact fields as updateable. I put a msgbox that says "if name not on list
proceed to select customer" They then can select the customer out of the
combo box and then proceed to the contact fields, and type in the contact
name etc. If there is a contact in the first combo it populates all of the
pertinent contact fields, which is really cool... Maybe your module
accomplishes a whole lot more! But for what I need I think this will work.

I never even added the module etc. and you may come back and tell me I
have flaws, but it seems to do what I wanted to accomplish. It didn't seem
like the combos were syncing up with each other the other day, but now it
does. Oh well...Thank you for all of the dialogue. This was a huge help!

If you are always going to map from tests to customers via a real contact,
then you have to have at least one 'real person' row in the contacts table
which references each customer. If not then you have to insert a row. You
do that by including code in the combo box's NotInList event procedure along
the lines of that which I posted for adding a new salesperson. In your case
the form which the code opens and passes the names to would be bound to the
Contacts table and would include a control in which to enter the customer
which the contact represents. This control would most likely be a combo box
to select a customer from a list. So when adding the new contact you'd
select the customer with whom you are currently testing. When you close the
contacts form and return to the testing form the new contact will be in the
combo box's list and associated with the customer in question.

Ken Sheridan
Stafford, England

I always want a contact to go in the Testing TAble. So could I just set up
combo boxes on contact name, email, tn. And if there are contacts available
for that customer select them, and if not type the names in the combo. If
so how would I have the combo select from the appropriate customer that I am
testing with? How would I allow the combo to be updateable so that when I
type a new contact if one is not in the contacts table to be added to the
contacts table after I type it. Does this sound like a feasible alternative?

I'd assumed that each customer would have at least one contact. This was
remiss of me because what you've identified here is a generic problem, where

[quoted text clipped - 42 lines]

If this is feasible, how would I do it? Or an easier way to accomplish the
same thing?