Typing CustomerNumber in textbox fills in customer info in form.

On Mon, 20 Mar 2006 15:07:28 -0800, Ron Weaver
wrote:

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]

On Mon, 20 Mar 2006 16:23:27 -0800, Ron Weaver
wrote:

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]

Ron,
You wrote...
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??

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

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;


"John Vinson" wrote:

On Mon, 20 Mar 2006 16:23:27 -0800, Ron Weaver
wrote:

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
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.

"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]

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

"Al Camp" wrote:

Ron,
You wrote...
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??

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

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

"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]

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.

"John Vinson" wrote:

On Mon, 20 Mar 2006 15:07:28 -0800, Ron Weaver
wrote:

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]

Thanks Al

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

"Al Camp" wrote:

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

"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]

On Mon, 20 Mar 2006 19:39:27 -0800, Ron Weaver
wrote:

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]