January 22nd, 2013, 09:46 AM
All products listed at once in a form to make an order
I am trying create an order form that looks like an Excel spreadsheet. The sheet has the following fields(Columns): product no, product name, product category all coming from Products table. Now I want to have more columns that will have :Trays(How many trays of the product is required). Pkts( How many packets required), Notes (Notes on the product).
Once the user enters all the quantities required(There may maybe products that will not be required), this datasheet needs to be saved to a OrdersDetails table with OrderID as the reference.
I have a main form with the following fields: OrderID, OrderDate, RequiredDate, ShopName, OrderStatus, OrderNotes that is Orders table. I have the OrderID in Orders table referenced to OrderID in the OrderDetails table.
What I don't know is how to create this specific Order form in Datasheet view and then to save it to my table OrderDetails.
I know how to use Form Wizard, and to create datasheets, but I am not able to do what I have described above. I am not sure if I will need a combination of VBA and SQl to solve this.
Any help to point me in the right direction will be much appreciated as I have been struggling to get this simple database running.
I don't have to have a datasheet but I don't know any other way to have the option to show all the data on screen. The order is placed by different shops for the products they need. If they have to go product by product they might miss one or it will be to slow. If they have all products in view all they have to do is put in the amounts they need near each product.
I have created a form and subform and I have a working database, but I have to go product by product.
I am not sure how to post my database for viewing in this forum.
January 22nd, 2013, 03:53 PM
Your main problem is that you're thinking in terms of excel. Access is not Excel, and thinking it is or trying to replicate things as they are in excel will inevitably cause you problems. Instead of listing all of the possible choices on every order form, why not do something like a combo box where you can choose only the products you want? use a query to list all of the possible choices then use the query as the record source for your combo box. You can even use multiple combo boxes to progressively filter the possible products to smaller numbers to make finding products easier.