Typing CustomerNumber in textbox fills in customer info in form.

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.

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.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"Ron Weaver" wrote in message
...
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.

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.

"Al Camp" wrote:

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.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"Ron Weaver" wrote in message
...
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.

On Mon, 20 Mar 2006 09:47:34 -0800, Ron Weaver
wrote:

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]

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.
"John Vinson" wrote:

On Mon, 20 Mar 2006 09:47:34 -0800, Ron Weaver
wrote:

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]

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.

"Al Camp" wrote:

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.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"Ron Weaver" wrote in message
...
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.

On Mon, 20 Mar 2006 12:11:28 -0800, Ron Weaver
wrote:

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]

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.

"John Vinson" wrote:

On Mon, 20 Mar 2006 12:11:28 -0800, Ron Weaver
wrote:

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,
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" wrote in message
...
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.

"John Vinson" wrote:

On Mon, 20 Mar 2006 12:11:28 -0800, Ron Weaver
wrote:

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]

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

"Al Camp" wrote:

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" wrote in message
...
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.

"John Vinson" wrote:

On Mon, 20 Mar 2006 12:11:28 -0800, Ron Weaver
wrote:

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]