Questioning my form design.

[Guest]

I am questioning my design of the form. I am doing a call tracking form for
customers. I have it set up to search for the customer in a couple of ways 1)
a combo box with all the phone numbers 2) a combo box with Last Names. The
Combo Boxes are recordset cloned on AfterUpdate.

My problem is that my main form is based on my customer table and I don't
want to add a record to that form only filter it. So I can't put a subform on
it because of the Master and Child Links.

So how do I filter the customer form and add the cust info to another form
that is not connected? Any thoughts appreciated.

Thanks Dustin

 

[Guest]

Your question is not quite clear, Dustin. Do you mean you have one form for
finding a customer, but you want to be able to add a new customer using a
different form?
Or is it you want to record call info on an existing customer with a
different form?
Can you describe in more detail, please.

 

[John W. Vinson]

I am questioning my design of the form. I am doing a call tracking form for
customers. I have it set up to search for the customer in a couple of ways 1)
a combo box with all the phone numbers 2) a combo box with Last Names. The
Combo Boxes are recordset cloned on AfterUpdate.

My problem is that my main form is based on my customer table and I don't
want to add a record to that form only filter it. So I can't put a subform on
it because of the Master and Child Links.

So how do I filter the customer form and add the cust info to another form
that is not connected? Any thoughts appreciated.

Well, first off - you DON'T want to "add the cust info" to any other table.
The name, phone, etc. should be stored once and once only in the customer
table.

Could you explain what you mean by "recordset cloned on AfterUpdate"?

You can put unbound combo boxes (say in the form header) to filter the form.
The Combo Box toolbox wizard will set this up for you - just choose the option
"use this combo to find a record".

John W. Vinson [MVP]

 

[Guest]

I want to record the call info on an existing customer. I want to be able to
filter the customer info so my coworkers can see the what options he selected
for his machine. When they find him I want to display the customer info on
the Call info form. Any suggestions? Thanks for responding.

 

[Guest]

I will explain myself better. I have a form based off my customer table with
the search combo boxes you suggested. I wanted to add a subform with the call
information on it. I wanted to display (not record) some of the customer info
from the main form on to the subform after the customer was selected. But I
realized a problem. If I insert a subform there has to be master and child
links that match each other. Which means records on the main form would have
to be added. But I don't want to do that. I just want to search the main
form.

If there is a better way please let me know. I hope this cleared it up.

Thanks for responding, Dustin

 

[John W. Vinson]

I will explain myself better. I have a form based off my customer table with
the search combo boxes you suggested. I wanted to add a subform with the call
information on it. I wanted to display (not record) some of the customer info
from the main form on to the subform after the customer was selected. But I
realized a problem. If I insert a subform there has to be master and child
links that match each other. Which means records on the main form would have
to be added. But I don't want to do that. I just want to search the main
form.

I don't understand.

If you search for a record on the mainform, and use the customer ID as the
master/child link field so that the subform is displaying calls for that
customer, you do NOT need to add any records on the main form. You *find* the
customer's record on the main form - that establishes the master link field;
the subform will automatically display just that customer's calls.

Why do you feel that "records on the main form would have to be added"? Or do
you want to record calls made by a customer, before any record for that
customer exists? If so, how on Earth can you tell what customer it is?

And why do you need to display the customer name on BOTH the mainform and also
the subform? They're both on screen at the same time - the name is *right
there in plain sight*.

John W. Vinson [MVP]

 

[Guest]

Thanks for responding.

Ok, I understand what you are saying. See, I thought that a new record had
to be made on the new form from my previous experience with subforms. But now
I understand that they don't. I will try what you said with the subform.

To answer your question of why do I want display customer info in the
subform. Is because I want to be able to query the subform table and be able
to identify whether certain size machines are having problems. If they are
how many and so forth.

I would appreciate any other comments or ideas. Sorry for the confusion I am
pretty new at this.

Thanks Dustin

 

[John W. Vinson]

Thanks for responding.

Ok, I understand what you are saying. See, I thought that a new record had
to be made on the new form from my previous experience with subforms. But now
I understand that they don't. I will try what you said with the subform.

To answer your question of why do I want display customer info in the
subform. Is because I want to be able to query the subform table and be able
to identify whether certain size machines are having problems. If they are
how many and so forth.

I would appreciate any other comments or ideas. Sorry for the confusion I am
pretty new at this.

If you need a query with criteria from both tables, base the query on both
tables.

Storing data redundantly in the child table is NOTHING BUT TROUBLE and will be
of *no* benefit.

You have the customerID in the child table, so you can create a Query joining
it to the customer table. This gives you all of the fields in both tables,
available for display, searching, or sorting. Copying the field from the main
table into the child table just opens a huge opportunity for having WRONG data
in one table or the other.

John W. Vinson [MVP]

 

[Guest]

Thanks again John

Dustin

If you need a query with criteria from both tables, base the query on both
tables.

Storing data redundantly in the child table is NOTHING BUT TROUBLE and will be
of *no* benefit.

You have the customerID in the child table, so you can create a Query joining
it to the customer table. This gives you all of the fields in both tables,
available for display, searching, or sorting. Copying the field from the main
table into the child table just opens a huge opportunity for having WRONG data
in one table or the other.

John W. Vinson [MVP]