Add data range

Also just to say, your suggestion is basically what I have, there are two
tables, one for customers and their locations, and one for customers and
their orders, only a drop-down box won't work if they are trying to add new
customers (not already in the customer table), or are you suggesting that
they add the customers first elsewhere? Then choose the location? Then choose
the customer that they've just added and put in the order? I'm just not sure
where the customer #'s will be coming from, or how it would work...I may be
misunderstanding you though...

Locations needs to be in their own table.
Customers need to be in their own table.
Orders need to be in their own table.

Customer table has a primary key of CustomerID which is easier to manage if
you use an auto number primary key. You create a field for the custom
generated number for the customer.

The Order table has a primary key of OrderID, and it also has the CustomerID
as the foreign key.

The location table has a primary key of LocationID.
The customer table uses LocationID to keep track of which customer is at
which location.


To add new customers, put an add customer button next to the combo with
customers.
When user clicks to add customer, give them a dropdown of locations to
choose from.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"carrot_top" wrote in message
...
Also just to say, your suggestion is basically what I have, there are two
tables, one for customers and their locations, and one for customers and
their orders, only a drop-down box won't work if they are trying to add
new
customers (not already in the customer table), or are you suggesting that
they add the customers first elsewhere? Then choose the location? Then
choose
the customer that they've just added and put in the order? I'm just not
sure
where the customer #'s will be coming from, or how it would work...I may
be
misunderstanding you though...

See my answer to your next question.

We can supply code to give the next customer the next order number
automatically when entering customer's orders at a location. This will make
it fairly easy for users to enter a customer order.

I am afraid that your table setup is not normalized and this will cause
grief and frustration as you try to develop the database.

I have suggested tables in my answer to your next question.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



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



.

On Thu, 12 Nov 2009 15:16:28 +1100, "Jeanette Cunningham"
wrote:

To add new customers, put an add customer button next to the combo with
customers.
When user clicks to add customer, give them a dropdown of locations to
choose from.

and/or use the NotInList event of the Customer combo box to allow new
customers to be added using a popup form.
--

John W. Vinson [MVP]