I'm trying to create a form that will work for a student bringing in their books to sell to us. People cannot bring more than 1 of the same book nor more than 5 books.
I would need to create a record in purchase_inventory (I have been working on this part for a bit, so far I managed to autonumber purchase# through dmax 1 to avoid using the autonumber in the table itself) and also records in the junction table [purchase_detail] depending on how many book is brought in, and also update the inventory table. I need to create a record in purchase_inventory: Purchase#: 2 (Default value, populated through dmax) SR_ID: (Manual input based on employee serving customer) Student_ID: (Manual input, should validate against SAF_Student_List) Purchase_Date: Auto populate through default Purchase_Time: Auto populate through default [Purchase_Detail]: 1 record per book brought in, joint primary key of purchase# and ISBN Purchase_Price: Should be populated through a lookup in the inventory table Purchase_Quantity: Ignore, will auto populate as 1 Book_Condition: Drop-down menu with several options Sold_Status: Set to unsold automatically [Inventory]: Master file, must update quantity_on_hand based on detail given in junction table. I've always done simple forms by creating a bound form and just updating fields.
Is this do-able, or is this too ambitious for a access noob?
Or is a more practical approach 30 tables with 30 forms and then linking the tables for reporting?
30 is pushing it a bit for MSAccess but in theory you should be OK. Given the number of concurrent users I would be inclined not to use bound fields.
When creating forms, Currently Reviewer Name and Date are auto-input so the reviewer will not have to enter this information.
Client Name, Action Type are drop down boxes and Order Number is manual input.