Typing CustomerNumber in textbox fills in customer info in form.

[Ron Weaver]

This is what I am trying to accomplish:
Customer Order Form
Fill in the Customer Number field and hit tab. If there is a match in the
system, the customer information fills in on the form. If there is no match,
then a new Customer Number can be created at this point.
I know how some of you feel about creating
Customer Numbers, but that is what I have to work with. If someone knows a
way to make one customer unique from another without having to create a
unique number/letter, I would appreciate your sharing it with me. These
customers have many repeat orders.
As you can tell, I am new at this and do appreciate all the help everyone
has given me.

 

[Al Camp]

Ron,
I'd suggest a Combobox, based on all your legitimate existing CustNos.
Set LimitToList = Yes. If you enter a CustNo that doesn't already exist,
use the NotInList event to trigger a message box (Add new CustNo?), and send
the form to a new record to allow adding the new CustNo.

When entering a legitimate CustNo, it's not necessary to save the
ancillary information (Name, Address, City... etc) to bound fields on the
form... just display those values.
Given a combo box (ex. name cboCustNo), bound to the CustNo field, with
these columns...
CustNo CustName Cust Address CustCity etc...
Now, a calculated field on your form with a ControlSource of...
= cboCustNo.Column(1)
would "display" the CustName.
=cboCustNo.Column(2)
would display the CustAddress etc.. etc.. for your other Cust info.

Since you're capturing the CustNo, you don't need to capture/save all the
other customer information... just display it for the user. With the saved
CustNo, you can always "re-derive" the ancillary values, at any time, in any
subsequent query, form, or report.

 

[Ron Weaver]

Carl
This is what I want, but I'm having problems making it happen. I am very
limited in programming skills. I have created the combo box with the fields
in it that I need, named it cboCustNo, tied it to the CustomerNumber Source
and with the wizard created the msg box. I don't know how to send it to a new
record source. I also don't know how to put the code you gave me into the
record source on the form. Following is the form's record source code:
SELECT Customer.CustomerID AS Customer_CustomerID, Customer.FirstName,
Customer.LastName, Customer.Address, Customer.City, Customer.State,
Customer.ZipCode, Customer.Phone, Customer.Fax, Orders.OrderID,
Orders.CustomerID AS Orders_CustomerID, Orders.OrderDetailID, Orders.Room,
Orders.TodaysDate, Orders.StartDate, Orders.EndDate, Orders.ArriveTime,
Orders.StartTime, Orders.EndTime, Orders.Notes, Customer.CustomerNumber
FROM (Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;
I don't know if this helps, but I sure would like to get this working.
Thanks so much for your help.

 

[John Vinson]

I know how some of you feel about creating
Customer Numbers, but that is what I have to work with. If someone knows a
way to make one customer unique from another without having to create a
unique number/letter, I would appreciate your sharing it with me. These
customers have many repeat orders.
As you can tell, I am new at this and do appreciate all the help everyone
has given me.

Ron, could you please post the names and relevant fields of the Tables
that you're using?

NOBODY has told you that you shouldn't have unique customer ID's. You
seem to be misinterpreting our advice - and our advice may be
confusing because we're not visualizing your actual database
structure!

John W. Vinson[MVP]

 

[Ron Weaver]

John
I may have missunderstood, but I did get the feeling that using unique
letters/numbers was a bad way to go. All I want to do is to be able to link
all of a customers orders to that customer. As far as Tables and relevant
fields go: Customers Table: CustomerID, CustomerName, FirstName, LastName,
Phone.
Orders Table: OrderID, CustomerID, Room, Dates and Times
Products Table: ProducdID, Products (List)
Thanks for helping, John. Sorry if I offended anyone.

 

[Ron Weaver]

Al
I see what you mean by using list boxes and putting the combo code in the
source row of each field. I just need to know how to send the form to a new
record.

 

[John Vinson]

John
I may have missunderstood, but I did get the feeling that using unique
letters/numbers was a bad way to go.

It's simply not NECESSARY. If you have a unique customer ID, generated
by autonumber, then you already can *do* what you're asking. Creating
a Text field with customer names and special codes simply is extra
work for no benefit.

All I want to do is to be able to link
all of a customers orders to that customer. As far as Tables and relevant
fields go: Customers Table: CustomerID, CustomerName, FirstName, LastName,
Phone.

And the datatype of CustomerID is...?? I'd suggest an Autonumber. You
said in another thread that you get a new customer ID for each order;
is that in fact not the case?

Orders Table: OrderID, CustomerID, Room, Dates and Times

If CustomerID in Customers is Autonumber then you would use a Long
Integer here... right?

Products Table: ProducdID, Products (List)
Thanks for helping, John. Sorry if I offended anyone.

No offense whatsoever - just confusion on my part, and trying to
resolve it!

IF - and again, I'm not certain about the IF - my guesses above are
correct then you can use the Combo Box wizard to solve your problem.
Open the Form in design view. On the toolbox make sure that the magic
wand icon is selected. Click the Combo Box icon, and place a combo on
the form where you want it. Choose the option "Use this combo to find
a record" and base it on the Customers table.

This will create an unbound combo box which will present the customer
names (you may need to tweak its properties a bit) and let you pick
one; when you select a customer it will open the Form to the first
record for that customer. Or you can use it to Filter the form to show
just that customer's records. Will that meet your needs?

John W. Vinson[MVP]

 

[Ron Weaver]

Ok John,

I just don't understand. If every time I enter an order for Joe Smith, the
autonumber creates a new CustomerID, how do I go about querying ALL of Joe
Smith's orders to a form via listbox or combobox?? What is there that ties
all of his orders together? I am trying to grasp this. Thanks for you
patience.

 

[Al Camp]

Ron,
Your Orders table design is in error...
You shouldn't be creating multiple CustomerIDs for each order. You
should create a unique value for each Order (autonumber OrderID), but your
Orders table should have a field to allow you to place an "existing"
CustomerID in it.
If you're creating a new CustometID for each order, you're destrying any
relationship between that CustomerID and your Customer table.
Let Access create your OrderID autonumber, but NOT your CustomerID...
that must be selected from your Customer table.

Sample Orders table
(auto) (long) (text)
OrderID CustID CustomerName
123 23 Big Industies
124 16 Custom Widgets
125 23 Big Industries
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Ron Weaver]

Al
My CustomerID field is autonumber. That is why I created the CustomerNumber
field. The OrderID field is autonumber also.

 

[John Vinson]

Ok John,

I just don't understand. If every time I enter an order for Joe Smith, the
autonumber creates a new CustomerID, how do I go about querying ALL of Joe
Smith's orders to a form via listbox or combobox?? What is there that ties
all of his orders together? I am trying to grasp this. Thanks for you
patience.

No. In a properly designed form, the system DOES NOT create a new
entry or a new ID for Joe Smith.

Have you looked at the Orders form in the Northwind sample database?
It shows how this can be done. You do not create a new Customer
record; you create a new Orders record, and use a Combo Box to
*SELECT* an ID from the existing Customers table. The form is not
based on the Customers table; it's based on the Orders table, and all
you're doing is adding the CustomerID to the Orders record.


John W. Vinson[MVP]

 

[John Vinson]

My CustomerID field is autonumber. That is why I created the CustomerNumber
field. The OrderID field is autonumber also.

A Table can have one and only one autonumber field.

What is the Recordsource for your form????


John W. Vinson[MVP]

 

[Al Camp]

Ron,
You wrote...
It should be an Autonumber field in your Customer table, so that when a
new customer is added, it is given it's own unique key identifier.
It should NOT be an AutoNumber on the Order form.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Ron Weaver]

The CustomerNumber field is a Text field. Here is the recordsource for my
'Orders' form:
SELECT Customer.CustomerID AS Customer_CustomerID, Customer.FirstName,
Customer.LastName, Customer.Address, Customer.City, Customer.State,
Customer.ZipCode, Customer.Phone, Customer.Fax, Orders.OrderID,
Orders.CustomerID AS Orders_CustomerID, Orders.OrderDetailID, Orders.Room,
Orders.TodaysDate, Orders.StartDate, Orders.EndDate, Orders.ArriveTime,
Orders.StartTime, Orders.EndTime, Orders.Notes, Customer.CustomerNumber
FROM (Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;

 

[Ron Weaver]

Al
I know I'm being really dense here. Looking back at your previous reply, I
think I see what you mean with the example you gave me. Right now my OrderID
is (autonumber), Customer name is (Text), and I have to change my CustomerID.
to (Long). How does the CustomerID field populate. I know I'm trying your
patience, but I'm just trying to understand.

 

[Ron Weaver]

Al
I just answered your last reply in the wrong place. It's in your previous
reply.

 

[Al Camp]

Ron,
Usually, on an Order type form, you would set up a combobox, "bound" to
your Orders/CustomerID field. The query behind the combo will list all the
customers in your customer table, from which you will select a CustomerID.
To keep things simple for now, make the combo 2 columns...
CustomerID Customer Name

Combo Properties...
ColumnCount = 2
ColumnWidths = 0" ; 2"
ListWidth = 2"

Now... with this setup, the user selects a CustomerName from the combo
list (The ID field is hidden by the first 0" columnWidth) This makes it
very easy for the user to determine the correct customer... by name rather
than by a number.
BUT...
The CustomerName will "display" in the combobox, but what's really stored
in the combo's bound CustomerID field is the CustomerID value from the 0"
width hidden column. That's it... you've captured the CustomerID associated
with this order.

I have a sample 97 and 2003 downloadable file on my website below (in
Access Tips) which demostrates how this works.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Ron Weaver]

John
I didn't realize I was coming at this from the wrong direction. I think I
see where you and Al are coming from. I will take everyones advise and make
necessary changes to my tables. I will also take a look at the Northwind
database and with the insight you all have given me, it will probably make
more sense. I want the table structure to be correct.
Thanks very much.

 

[Ron Weaver]

Thanks Al

I appreciate everything. I am going to work on my tables. This has been a
great learning experence for me.

 

[John Vinson]

The CustomerNumber field is a Text field. Here is the recordsource for my
'Orders' form:
SELECT Customer.CustomerID AS Customer_CustomerID, Customer.FirstName,
Customer.LastName, Customer.Address, Customer.City, Customer.State,
Customer.ZipCode, Customer.Phone, Customer.Fax, Orders.OrderID,
Orders.CustomerID AS Orders_CustomerID, Orders.OrderDetailID, Orders.Room,
Orders.TodaysDate, Orders.StartDate, Orders.EndDate, Orders.ArriveTime,
Orders.StartTime, Orders.EndTime, Orders.Notes, Customer.CustomerNumber
FROM (Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;

THAT is the problem. Thank you.

I see elsethread that you're reevaluating your table structure. When
you do so, consider using *TWO* forms - one for the customer, one for
the order - since they are different entities with different
requirements! If you base your Orders form *just* on the Orders table,
you can put a combo box bound to Orders.CustomerID displaying the
customer name, and storing the customerID; you can even put VBA code
into that combo's NotInList event to pop open a Customer form so that
you can enter the information for new customers.

John W. Vinson[MVP]