If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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... |
#12
|
|||
|
|||
Add data range
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... |
#13
|
|||
|
|||
Add data range
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] . . |
#14
|
|||
|
|||
Add data range
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] |
|
Thread Tools | |
Display Modes | |
|
|