Forms that update/create records in multiple tables, including junction tables

excal

New member
Local time
Today, 15:14
Joined
Jul 12, 2011
Messages
3
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.
 

Attachments

  • Relationship.png
    Relationship.png
    64.8 KB · Views: 1,125
I think you should start by reviewing...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

...because starting with, tables not normalized properly is like building your 2,000 square foot house on a 1,000 square foot foundation. Both (the house and your databse) will eventually fall...

After you finish reviewing the above go look at some sample databases...
http://databases.about.com/od/accessdatabasetemplates/Microsoft_Access_Database_Templates.htm

To answer your UPDATES (without telling you how)...

UPDATE 1 - Yes, it is possible to do that...
http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/combo.pdf

http://office.microsoft.com/en-ca/a...bo-box-or-drop-down-list-box-HP005240581.aspx

UPDATE 2 - You cannot enter a record in a subform if there is no main/related record in the main form...
http://www.techonthenet.com/access/subforms/

UPDARTE 3 - Forms can be BOUND or UNBOUND. You can use a TABLE or QUERY as the RecordSource for a FORM making it BOUND. In the above tutorial by Crystal you will get more information on that.
 
Thank you, I'll be going through that shortly. Do you have any specific suggestion as to where to start with regards to my problem?

And I'm pretty sure my database is 3NF, which is sufficient for Access, am I incorrect? I think I can achieve 4NF but again, having the employee table separated is a specific requirement by the professor, not sure why.

I'm going to get reading and working, thanks for the resources.

EDIT 1: Regarding Update 1, I have changed the SQL within the row source to reflect book_name, and the control source remains as ISBN, and I also changed the sorting of the box. Is that complete, or am I missing something? (can't really test functionality as it's a subform and my main form is incomplete.
 
Last edited:
I don't understand why you need the employees in a seprate table as opposed to a field in one table that determines what type if employee they are; which is the way 99.9% of developers create a table. The way you have it now creates ALOT more work.

As for where to start... well, I always start at the beginning and skip the parts I know. For you if you feel your tables ar correct (and I don't) I would go to Forms and Reports...
 
Thank you, much appreciated.

I have a subform set up, but I'm not sure how to input things into the junction table. This is what I have so far. I managed to use a SQL query to fix what i wanted in edit 1, and just to confirm, for row source, i now have:

SELECT Inventory.Book_Title
FROM Inventory
ORDER BY Inventory.Book_Title;

and control source remains unchanged as ISBN. Is that correct? Due to the fact that my forms aren't functional yet, i can't really test it.

I have attached the form I'm having trouble with below, and what I have so far. I have the purchase number autopopulated using dmax+1, and date and time is also autopopulated. Sales rep and student id are drop downs, and as far as I can see, it's more or less complete (the main form)

The only issue with the main form is that when i click into purchase detail, it updates immediately, even though i didn't click submit, and that i can't enter things in purchase detail as it says that a record is required, which doesn't make sense to me as i'm attempting to create a record.

Reiterating, my prof said I should use a query, but being limited in my SQL skills (was only covered in 1 class, the class is primarly for database design in general) I am pretty lost.

Can you help me, conceptually, how I should about implementing this (sub)form? I'm sure I can work out the SQL code and the query/form once I can wrap my head around it, but I just dont know where to start.
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    86.9 KB · Views: 575
You cannot add records to the detail until you *create* a record on the Main form. It looks like you are using Autonumber for the Purchase Order ID well, it does not commit until you actually create the record.

Read the tutorials by Crystal they will help with form/subform creation...

You say this class is Database Design, was there a class prior that dealt with tables and queries?
 

Users who are viewing this thread

Back
Top Bottom