Hi all, access noob checking in. So I have a database for a project that is simulating a used bookstore's database system.
My relationship table is attached below. Please forgive the lack of normalization on the 3 type of employees on the right side, it's a specific project requirement. I understand it's not good but I don't foresee an issue for this particular form.
I'm trying to create a form that will work for a student bringing in their books to sell to us. Each order can contain multiple books. 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.
You can assume all student_ID infomation, everything in the inventory table are pre-set up.
For example:
Student 1 brings in book A to sell. This is my second purchase.
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'm not sure where to start with this. I've always done simple forms by creating a bound form and just updating fields. My challenge is that I have to create a single record in 1 table, possibly multiple records in another, and then update a master table, all in 1 form. Is this do-able, or is this too ambitious for a access noob? From my classes I understand I'll need to use some subforms, but we've only learned theory and we were more or less thrown into this access project head first.
UPDATE 1: I'm working on the subform at the moment, and ISBN is a joint primary key. However, ISBN is pretty arbitrary to the end user and annoying to have to read and recognize even with a drop down. Is it possible for the form to pull the book names and when selected and submitted, the form would update the table with the ISBN?
Would I use this through an input mask or would it require a query?
UPDATE 2: I have the main form set up and the sub form inserted. However, I can't seem to make updates on the subform and the main form updates before i click the submit button. DataEntry is Yes on the subform, and no on the main form.
When I try to put something in the subform, it tells me that a record must be in the purchase_Detail table already, which doesn't make sense since I am trying create new records.
UPDATE 3: Talked to my professor today, she wouldn't tell me how to do it (obviously) but did mention that I should start with a query and have the subform pull from the query. I'm not sure what she meant though...
Thanks in advance for the help, please ask if you need clarification regarding my problem.
My relationship table is attached below. Please forgive the lack of normalization on the 3 type of employees on the right side, it's a specific project requirement. I understand it's not good but I don't foresee an issue for this particular form.
I'm trying to create a form that will work for a student bringing in their books to sell to us. Each order can contain multiple books. 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.
You can assume all student_ID infomation, everything in the inventory table are pre-set up.
For example:
Student 1 brings in book A to sell. This is my second purchase.
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'm not sure where to start with this. I've always done simple forms by creating a bound form and just updating fields. My challenge is that I have to create a single record in 1 table, possibly multiple records in another, and then update a master table, all in 1 form. Is this do-able, or is this too ambitious for a access noob? From my classes I understand I'll need to use some subforms, but we've only learned theory and we were more or less thrown into this access project head first.
UPDATE 1: I'm working on the subform at the moment, and ISBN is a joint primary key. However, ISBN is pretty arbitrary to the end user and annoying to have to read and recognize even with a drop down. Is it possible for the form to pull the book names and when selected and submitted, the form would update the table with the ISBN?
Would I use this through an input mask or would it require a query?
UPDATE 2: I have the main form set up and the sub form inserted. However, I can't seem to make updates on the subform and the main form updates before i click the submit button. DataEntry is Yes on the subform, and no on the main form.
When I try to put something in the subform, it tells me that a record must be in the purchase_Detail table already, which doesn't make sense since I am trying create new records.
UPDATE 3: Talked to my professor today, she wouldn't tell me how to do it (obviously) but did mention that I should start with a query and have the subform pull from the query. I'm not sure what she meant though...
Thanks in advance for the help, please ask if you need clarification regarding my problem.
