Add data range

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then be
added to the relevant table.

Is this possible?

Hi carrot_top,
Usually, a customer can order more than one thing at a time, which means
that you need to allow for multiple rows for each order.
To handle this it will be easier if user selects a location and then opens a
form where there is a drop down showing all the customers for that location.
User can select a customer, enter the order and then move on to the next
customer at that location.

I would expect to see 4 tables
Locations
Customers
Orders
OrderDetails

It's easier to use an autonumber for the Customer Primary Key in the
customers table.
Use a separate field (usually text) for the customer number. You can put a
unique indes on the customer number field.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"carrot_top" wrote in message
...
I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for
example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then
be
added to the relevant table.

Is this possible?

Try something like this --
Create table named CountNumber with field CountNUM containing numbers from 0
(zero) through say 100.
Substitute your table and field names in query below --
INSERT INTO YourTable ( CustomerID, Field2)
SELECT [Enter starting number] + [CountNUM] AS CustomerID, [Enter data] AS
Field2
FROM CountNumber
WHERE CountNUM [Enter quanity of records] + [Enter starting number];

--
Build a little, test a little.


"carrot_top" wrote:

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then be
added to the relevant table.

Is this possible?

Thanks, I think this is heading in the right direction, but not in the right
order and I think I have too many buttons now. How can I make the same button
do the two actions - ie: open the subform, with the range already filled in,
input the data, THEN append to the table after the user closes the subform?


"KARL DEWEY" wrote:

Try something like this --
Create table named CountNumber with field CountNUM containing numbers from 0
(zero) through say 100.
Substitute your table and field names in query below --
INSERT INTO YourTable ( CustomerID, Field2)
SELECT [Enter starting number] + [CountNUM] AS CustomerID, [Enter data] AS
Field2
FROM CountNumber
WHERE CountNUM [Enter quanity of records] + [Enter starting number];

--
Build a little, test a little.


"carrot_top" wrote:

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then be
added to the relevant table.

Is this possible?

Thanks, I think this is heading in the right direction but its still not
quite right, there are too many buttons, and its happening in the wrong
order. Now it is trying to append the data before I can input it in the
subform. How can I get one button to perform the two tasks? It would be
easier if the user could put in the range, then click one button which will
bring up the subform, with the range already filled in, fill in the details
for the order, THEN append the data to the table by closing the subform
window. This is mainly to save time - the form is working now but I need to
fill in each customer # at the next window: time consuming and not useful if
a mistake is made.

Jeanette: I still need a way to enter multiple new customers (also: new
customers only need one order, so I don't need to input multiple orders at
that point). I could show current customers with a drop down if I wanted
(there are hundreds though so it doesn't strike me as a useful way of doing a
second order - I think I would need a search function of some sort for
that...but haven't got there yet, that is the next step, just trying to take
it one bit at a time!). The customers are identified with a unique number,
which is what relates the two tables. I can probably make another table for
orders as you suggest as well, but again, that is the next stage to be
tackled. I have a vague vision of perhaps having a switchboard where one
chooses to enter a new customer or enter another order.

Am I perhaps making it too complicated? Any suggestions of how I can better
accomplish any of this would be welcome as well ...this is just what I've
worked out through trying to understand the way my users think (not
logically)...
I need to keep this super simple - my users are not the type to listen to
instructions so it needs to be very automated as well!



"Jeanette Cunningham" wrote:

Hi carrot_top,
Usually, a customer can order more than one thing at a time, which means
that you need to allow for multiple rows for each order.
To handle this it will be easier if user selects a location and then opens a
form where there is a drop down showing all the customers for that location.
User can select a customer, enter the order and then move on to the next
customer at that location.

I would expect to see 4 tables
Locations
Customers
Orders
OrderDetails

It's easier to use an autonumber for the Customer Primary Key in the
customers table.
Use a separate field (usually text) for the customer number. You can put a
unique indes on the customer number field.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"carrot_top" wrote in message
...
I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for
example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then
be
added to the relevant table.

Is this possible?


.

A question - if you choose multiple customers, are they all ordering the
same thing in the same quantity at the same price?

Perhaps you wish to give users a listbox where they can select as many
customers as they need, and then you can write some code to add the dame
order to each of the selected customers?
Does this sound like a useful idea for your database?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"carrot_top" wrote in message
...
Thanks, I think this is heading in the right direction but its still not
quite right, there are too many buttons, and its happening in the wrong
order. Now it is trying to append the data before I can input it in the
subform. How can I get one button to perform the two tasks? It would be
easier if the user could put in the range, then click one button which
will
bring up the subform, with the range already filled in, fill in the
details
for the order, THEN append the data to the table by closing the subform
window. This is mainly to save time - the form is working now but I need
to
fill in each customer # at the next window: time consuming and not useful
if
a mistake is made.

Jeanette: I still need a way to enter multiple new customers (also: new
customers only need one order, so I don't need to input multiple orders at
that point). I could show current customers with a drop down if I wanted
(there are hundreds though so it doesn't strike me as a useful way of
doing a
second order - I think I would need a search function of some sort for
that...but haven't got there yet, that is the next step, just trying to
take
it one bit at a time!). The customers are identified with a unique
number,
which is what relates the two tables. I can probably make another table
for
orders as you suggest as well, but again, that is the next stage to be
tackled. I have a vague vision of perhaps having a switchboard where one
chooses to enter a new customer or enter another order.

Am I perhaps making it too complicated? Any suggestions of how I can
better
accomplish any of this would be welcome as well ...this is just what
I've
worked out through trying to understand the way my users think (not
logically)...
I need to keep this super simple - my users are not the type to listen to
instructions so it needs to be very automated as well!



"Jeanette Cunningham" wrote:

Hi carrot_top,
Usually, a customer can order more than one thing at a time, which means
that you need to allow for multiple rows for each order.
To handle this it will be easier if user selects a location and then
opens a
form where there is a drop down showing all the customers for that
location.
User can select a customer, enter the order and then move on to the next
customer at that location.

I would expect to see 4 tables
Locations
Customers
Orders
OrderDetails

It's easier to use an autonumber for the Customer Primary Key in the
customers table.
Use a separate field (usually text) for the customer number. You can put
a
unique indes on the customer number field.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"carrot_top" wrote in message
...
I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second
table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to
be
able to input a range of customer numbers (ie: from # 300 to 310 for
example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so
on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making
it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will
then
be
added to the relevant table.

Is this possible?


.

On Mon, 9 Nov 2009 23:19:01 -0800, carrot_top
wrote:

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then be
added to the relevant table.

Is this possible?

Are you trying to create empty "placeholder" records to be filled in later? If
so, DON'T!

It sounds to me like a better solution might be to have a subform with a combo
box to select only valid customer IDs (the combo based on a Query selecting
only the ones you want).
--

John W. Vinson [MVP]

No, I am not creating empty records, I just have a seperate table to fill in
the order, and would like to do this on the subform as often there are
multiple customers at the same location, so that the user does not have to
keep inputting the same location over and over. The customers may be ordering
different things. What happens is each customer is given a unique ID #, the
#s will be sequential for any one location as the salesperson will be filling
them out in order. I just thought it would be easier to input the data if we
could just select the range of customers which was used there, and then fill
in the details for each one.



"John W. Vinson" wrote:

On Mon, 9 Nov 2009 23:19:01 -0800, carrot_top
wrote:

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to be
able to input a range of customer numbers (ie: from # 300 to 310 for example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will then be
added to the relevant table.

Is this possible?

Are you trying to create empty "placeholder" records to be filled in later? If
so, DON'T!

It sounds to me like a better solution might be to have a subform with a combo
box to select only valid customer IDs (the combo based on a Query selecting
only the ones you want).
--

John W. Vinson [MVP]
.

Then, my first response is the setup that will work for what you want to do.
Here it is again.

To handle this it will be easier if user selects a location and then opens a
form where there is a drop down showing all the customers for that location.
User can select a customer, enter the order and then move on to the next
customer at that location.

Use a form based on location to select the correct location.
After the user selects the location, the main form opens with the combo
showing only the customers for that location.

The main form is based on the customer table.
The subform is based on the order table.

For each new order at the same location, user selects the customer from the
drop down in the main form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia




"carrot_top" wrote in message
...
No, I am not creating empty records, I just have a seperate table to fill
in
the order, and would like to do this on the subform as often there are
multiple customers at the same location, so that the user does not have to
keep inputting the same location over and over. The customers may be
ordering
different things. What happens is each customer is given a unique ID #,
the
#s will be sequential for any one location as the salesperson will be
filling
them out in order. I just thought it would be easier to input the data if
we
could just select the range of customers which was used there, and then
fill
in the details for each one.



"John W. Vinson" wrote:

On Mon, 9 Nov 2009 23:19:01 -0800, carrot_top
wrote:

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second
table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to
be
able to input a range of customer numbers (ie: from # 300 to 310 for
example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so
on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making
it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will
then be
added to the relevant table.

Is this possible?

Are you trying to create empty "placeholder" records to be filled in
later? If
so, DON'T!

It sounds to me like a better solution might be to have a subform with a
combo
box to select only valid customer IDs (the combo based on a Query
selecting
only the ones you want).
--

John W. Vinson [MVP]
.

My main concern is that this will take ages to enter dozens of new customers.
No one will want to bother doing that much work and will never use the
database. Right now everyone just uses excel, which fills the numbers down
automatically. However, the problem with this is no one has followed any
conventions whatsoever (even though they were given templates), and have made
seperate spreadsheets everytime, now we have so many different spreadsheets
around, all called different things, no one knows where to go to find their
data, whether its been input correctly, "accidently" edited, or what. It's
frankly a mess *sigh*...and I have been given the task to sort it all out. My
main problem actually appears to be my user group, not the data. I can go
back to the original files and reassemble the data, but I want a better
system that will be useful (and that they can't muck with) than just a bunch
of spreadsheets scattered throughout the system. I figured that a central
access database where everyone can go to input and export their data would
solve some of these issues, perhaps I am wrong about that? Any suggestions of
how I could better arrange this would be most welcome if I seem to be going
about it in the wrong way...I just need something that is completely
idiot-proof, I'm just about at my wits end with this mob!!


"Jeanette Cunningham" wrote:

Then, my first response is the setup that will work for what you want to do.
Here it is again.

To handle this it will be easier if user selects a location and then opens a
form where there is a drop down showing all the customers for that location.
User can select a customer, enter the order and then move on to the next
customer at that location.

Use a form based on location to select the correct location.
After the user selects the location, the main form opens with the combo
showing only the customers for that location.

The main form is based on the customer table.
The subform is based on the order table.

For each new order at the same location, user selects the customer from the
drop down in the main form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia




"carrot_top" wrote in message
...
No, I am not creating empty records, I just have a seperate table to fill
in
the order, and would like to do this on the subform as often there are
multiple customers at the same location, so that the user does not have to
keep inputting the same location over and over. The customers may be
ordering
different things. What happens is each customer is given a unique ID #,
the
#s will be sequential for any one location as the salesperson will be
filling
them out in order. I just thought it would be easier to input the data if
we
could just select the range of customers which was used there, and then
fill
in the details for each one.



"John W. Vinson" wrote:

On Mon, 9 Nov 2009 23:19:01 -0800, carrot_top
wrote:

I have been searching for ages, and have not been able to find anything
exactly like I want, any suggestions would be greatly appreciated!

I have a data entry form based on table: Locations, which shows the
locations of a number of customers, with a subform based on a second
table
which contains the detailed information regarding the order. To input
multiple new customers (at the same location), I would like the user to
be
able to input a range of customer numbers (ie: from # 300 to 310 for
example)
on the main form, and then have these ID #s (ie: 300, 301, 302, and so
on)
show up in the subform (uneditable: the numbers are actually much more
complicated than this, so I want to avoid typos and save time by making
it
automatic), where they will then input in the relevant data for each.
Ideally, the new customers, their locations, and the information will
then be
added to the relevant table.

Is this possible?

Are you trying to create empty "placeholder" records to be filled in
later? If
so, DON'T!

It sounds to me like a better solution might be to have a subform with a
combo
box to select only valid customer IDs (the combo based on a Query
selecting
only the ones you want).
--

John W. Vinson [MVP]
.



.