| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 71409 | 2006-08-05 01:20:00 | MS Access Problem | Dannz (1668) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 476198 | 2006-08-05 01:20:00 | Hi All, Here is what i have: I have a MS Access database with _one_ table. Based around that table is a main form and about 6 other forms which all work from the same database. What i want to do is keep all the forms (which are seperate - NOT subforms) on the same record (ie when i click on the new record button on the main form i want all the other forms to go to that new record as well. Is this possible? If you want to see the database let me know and ill post a link. Thanks |
Dannz (1668) | ||
| 476199 | 2006-08-06 03:25:00 | Hi Daniel, Not really sure of what you're trying to do. Can I have a look and I might be able to offer some help. |
andy (473) | ||
| 476200 | 2006-08-06 06:27:00 | Hi Daniel If there is only one table, why are there 6 forms that would be open at the same time? Andrew |
andrew93 (249) | ||
| 476201 | 2006-08-06 06:53:00 | Hi Daniel If there is only one table, why are there 6 forms that would be open at the same time? Andrew Makes it easier to use.. there are loads of fields in the table anyway imagef1.net.nz |
Dannz (1668) | ||
| 476202 | 2006-08-06 09:58:00 | Hi Daniel The issue here is the database design. Until you have this right then you will grapple with Access and find it frustrating. Have a read of the following website : datamodel.org - this should help you with your database design. But in summation, to normalise the data you need a minimum of 4 tables, as follows: tblStudents Student_ID Student_Name Student_Class {debatable if this should be in this table but lets leave it here for simplicity purposes} tblItems Item_ID Item_Description Item_Price {you could have additional fields like 'category' etc to capture the type of food - with or without a link to another table} tblOrderHeaders Order_ID (autonumber, a unique ID for each order) Student_ID_Link {linked many to one to tblStudents} Pick_Up_Details {eg interval, lunch etc} Order_Date tblOrderItems Order_Item_ID {autonumber, a unique number for each order item} Order_ID_Link {linked many to one tblOrderHeaders} Item_ID_Link {linked many to one to tblItems} Order_Qty If you have your data in this sort of structure, then you are making use of the relational database, as opposed to having everything in the one flat file like a spreadsheet. This prevents the need to capture the name and order date for every single item ordered by every student. Furthermore, this sort of structure will allow you to use combo boxes on your main form, from which the student selects the products they want . Also, calculating an order total per student will be relatively simple - as opposed to your current structure which will make this difficult if you change any of your prices. Lastly, you can do the entry on just the one form for all products. With your current database, is the order total correctly updating where a student adds differing items to their order? Also, there is nothing stopping a student from entering multiple orders for the same day for the same pick up time. Is this something you want? That sort of thing can be easily controlled by using a joint primary key in the tblORderHeaders table. FYI there are also ways of locking down the form to prevent curious users from breaking into the database set-up. There were some suggestions as to how to do this in another thread on another forum here. (www.mrexcel.com) Lastly, the staff password is very easy to break. For a discussion on custom form security, have a read of this thread. (www.mrexcel.com) This involves storing the password within a database table (and one for each user), instead of hard-coding it into the VBA screen. Also, instead of using unusual fonts to hide the password, use the input mask 'Password' instead (without the quotes). HTH, Andrew |
andrew93 (249) | ||
| 1 | |||||