Add data range

[carrot_top]

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?

 

[Jeanette Cunningham]

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

 

[KARL DEWEY]

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

 

[carrot_top]

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?

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.

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?

 

[carrot_top]

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]

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

 

[John W. Vinson]

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

 

[carrot_top]

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.

 

[Jeanette Cunningham]

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]

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

 

[carrot_top]

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

 

[Jeanette Cunningham]

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

 

[Jeanette Cunningham]

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

 

[John W. Vinson]

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.