Problems with forms

[Guest]

I am creating a database for customers who have returns. I have an autonumber
set up for each return linked to a form.
How do I set up the form so if a customer calls in say today to return
something so the form auto generates a confirmation number for that return.
Then when the same customer two months later to return a different item I
want the database to autogenerate a new confirmation number but when I type
in the customer number/name I want the data base to remember the customers
address and phone number and other contact information, but not the previous
return.

Please help!

 

[Guest]

Hi, Sarah.

I'm not clear as to the structure of your database (tables and their
relationships).

One of the absolute requirements to any good database is that the structure
of your data be normalized.

What tables have you defined? At the simplest possible form of what you are
describing, I would say you would definantly need more than one table. You
will need a table to store the return information, a table for your
customers, possibly a table for your items and perhaps even other tables.

You indicate that you will need to locate existing customer information. I
would suggest that one of the easiest ways to lookup an existing customer
would be by their phone number, but that might not be the only way your uses
would want or need to try to find an existing customer.

Post your data structure and then perhaps we can assist you and getting
where you want to go.

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

 

[Guest]

I have three tables, a customer table (which contains all the customer
information. with the confirmation number as the primary key) a products
table (which contains all the information related to the products being
returned. with the product id as the primary key) and I have a Existing
Customer Search table (which is linked to the Customers table to search for
an existing customer. with the customer id as the primary key).
I have an Add New Return Form, an Edit Existing Return Form, An Existing
Customer Query, and Product Subform (which displays in the Add and Exit
Return form).

The way I want it to work is when a customer calls in to return an item, I
want the user to go into the data base and enter the customer information and
return info etc. and for the database to give that return an unique
confirmation number (which all works). I also want any other use to be able
open the database and search for that confirmation number and signoff that
the item has actually be returned and recieved in the warehouse (which also
already works). The thing that I can't get to work is when a customer calls
in to return an item, the user enters the info and gives a confirmation
number. Then say that same customer calls in a month later to return a
different item, there information should already be in the database. But that
new return has a new confirmation number. What I want to happen is for the
user to not have to retype all the customer information (name address, phone,
fax etc). I want the database to recognize that the customer already exists
in the system and display the address info when the customer id is typed into
the customer id textbox.

 

[Guest]

Sarah,

I think you may need to re-think your database design a little.

You will use your Customers table for the customer's data. You mentioned a
"Products" table that you say will have all the information about your
products that are being returned. All this is just fine, however, you need to
realize that you have a "many-to-many" relationship between the Items and the
customers.

In your case you may have customers returning may many customers retuning
one or more of the same items that are being returned by many customers. So,
you get the idea as to why you have to have some way to link many customers
to many items being returned.

Any time you have a "many-to-many" situation, you must have an intermediate
table.

To accomidate this, you not only need your Products table and Customes table
but you also need a third table: call it "tblProductsReturned". I assume
that your Products table has an AutoNumber field as the Primary Key field
(maybe ProductID) and that your Customer table has an AutoNumber field as the
Primary Key field (maybe CustomerID). Here note that I did notice that your
current Customer table has the confirmation number as the Primary Key. The
confirmation number field will need to be moved to a third table which would
consist of at least three fields. ItemID and CutomerID (both as a Long
Integer type fields used as a Foreign Key to their respective tables) and the
confirmation number field for one specific return.

As for forms, you will need a form where the new Confirmation Number is
generated. (you can cross that bridge after the get the rest of the structure
fixed) in this form you can include a combo box or list box, which ever is
appropriate to allow users to find the Product being returned and the
Customer that is wanting to return the Product. Each of these controls
;would have the appropriate ID field as its bound field, thus storiing the
confirmation number, the ProductID and the CustomerID in the third table that
I described above.

In yet another form, you could display all of the detail info about the
Product and the customer relative to a specific return based on the
confirmation number. You would accomplish this by having a form
(tblProductsReturned) as its record source and two sub forms. One for the
Product information linked to the main form by the ProductID field and one
for the Customer information linked to the main form by the CustomerID field,
using the "Master/Child Link fields" for the sub forms.

Then when someone calls in, you would have your application to open your
Product Being Returned form to a new record. This is where the new
confirmation number would be created (VBA code required) and the user would
locate the Customere and the Product as described above.

There are actually many other things that you could incorporate into this
type of application but if you can use this info to get you moving along with
the development of your application, then you can always add more
functionality later.

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com