General Design Help (1 Viewer)

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
Hopefully this is the correct forum to post this in.
I am new to Access, and have hit a kind of wall as far as design that I am struggling to overcome.

Let me preface this with a short summary of me:
New to Access (only been working with it for a little over a month).
Using Access 2016
Very little knowledge in coding, but have been learning VBA over the last few months and can understand basic stuff.

On to the main event!
I am designing a new system for quotes for my work using Access. I have designed all my tables and relationships, along with several forms for navigation and such. I have made the "first" form used in the process of making a quote (unceremoniously named "Create Quote") It pulls all the basic fields to "start" a quote, such as assigning a quote number, customers etc.

The problem I have run into is how to design the next form/step. After you fill out the first form and save it, it populates the "primary" table with that data. I then need to make a form that effectively goes "under" that ID. So the next form would tie in several other tables for data input (which I assume would be query based), but I cant figure out how all this would tie back to the databases "primary" table and link all the subsequent data under the Quote ID. I think it would involve parent/child records, but am unsure.

I am totally lost with how to go further since I can't figure out how to tie everything down the line back to that initial quote id/ quote number. Any design input/help would be greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

The usual approach to handle parent/child relationships is by using a form/subform configuration. You can even use a Tab Control to add as many subforms as you need.
 

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
Thank you!

That make some sense.

How would I implement that with my current setup where the quote data is created first? The reason behind me having the basic info created first, is that we have one person who initially starts the process, but other people are the ones who actually quote a job. So the first person would input all the jobs and assign them to various people who then take it from there. The person doing the quote then needs to have a form that is "under" that quote that the other person created.

My initial idea was to have my main form/dashboard have a sub-form that shows all jobs currently active (eventually it will only show jobs assigned to that person but that's for when I manage to implement a login system). That sub-form would populate as the first person creates the quote, then the person doing the quote would click the quote number to open the form to actually being quoting the job. There would be no need for the next person to open the "create quote" form.

I feel like I am wording this very poorly.
If it would help, I can attach what I have created thus far. It might give insight to my madness.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,358
Thank you!

That make some sense.

How would I implement that with my current setup where the quote data is created first? The reason behind me having the basic info created first, is that we have one person who initially starts the process, but other people are the ones who actually quote a job. So the first person would input all the jobs and assign them to various people who then take it from there. The person doing the quote then needs to have a form that is "under" that quote that the other person created.

My initial idea was to have my main form/dashboard have a sub-form that shows all jobs currently active (eventually it will only show jobs assigned to that person but that's for when I manage to implement a login system). That sub-form would populate as the first person creates the quote, then the person doing the quote would click the quote number to open the form to actually being quoting the job. There would be no need for the next person to open the "create quote" form.

I feel like I am wording this very poorly.
If it would help, I can attach what I have created thus far. It might give insight to my madness.
Hi. It might also help you to browse through the free templates available at MS site, just to get an idea of what you might be able to create or use. If you need more help, you might consider posting a small copy of your db with test data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Feb 19, 2002
Messages
42,983
Here's a sample database you can poke around in to see how subforms work.
 

Attachments

  • ManyToMany_A2016_20200909.zip
    1.5 MB · Views: 109

mike60smart

Registered User.
Local time
Today, 14:02
Joined
Aug 6, 2017
Messages
1,899
Thank you!

That make some sense.

How would I implement that with my current setup where the quote data is created first? The reason behind me having the basic info created first, is that we have one person who initially starts the process, but other people are the ones who actually quote a job. So the first person would input all the jobs and assign them to various people who then take it from there. The person doing the quote then needs to have a form that is "under" that quote that the other person created.

My initial idea was to have my main form/dashboard have a sub-form that shows all jobs currently active (eventually it will only show jobs assigned to that person but that's for when I manage to implement a login system). That sub-form would populate as the first person creates the quote, then the person doing the quote would click the quote number to open the form to actually being quoting the job. There would be no need for the next person to open the "create quote" form.

I feel like I am wording this very poorly.
If it would help, I can attach what I have created thus far. It might give insight to my madness.
Yes upload a zipped copy of the database so we can see your tables.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:02
Joined
Feb 28, 2001
Messages
27,003
@tmyers - you asked about how to link things together. That is a whole classroom lecture by itself, but I am willing to give you the overview and some advice on subjects to study. You said you are very new to Access, with about a month of experience. I will start by saying, "welcome to the rabbit hole" because all of us at one time or another have felt like everything was upside down and backwards. Each of us went through that Tweedledumb and Tweedledumber moment. But trust me, if you take it in reasonablly sized gulps, it slowly starts to make sense. I will give you some reading topics directed to help you in the question you asked.

Basic concept: Everything you know about your subject matter goes into your Access app file. Tables hold raw data. Sometimes two tables can relate to each other. Reading hint: Primary Key and Foreign Key. Significance: Some elements of your app will be free-standing or independent entities. Others will depend on those independent entities. Simple case in point: An invoice with multiple line-items is an independent item (the invoice) with dependent items (the line items of the invoice.) This is a parent/child relationship. Reading hint: Parent/child relationship. You typically implement relationships thus: For every new entry in a dependent table that is to be related to a particular independent entry, you copy the prime key to a field in the dependent table that becomes the Foreign Key. What Access then does is it looks at your defined relationships to note that you said a relationship occurs when dependent records' foreign keys match up with independent records' prime keys.

Basic concept: To keep your data properly organized so that you have tables broken out by purity of content or purpose, you need to study how to keep the independent items and dependent items separate from each other. Reading hint: Database Normalization. (See later note on this one.) Once you have your tables normalized, some data relationships will show up more or less naturally. Some will be obvious, like the invoice/line-item example. Some will be a bit more obscure. You are building a data model of your real-world process when you lay out your tables, queries, macros, and other elements. Just remember to never let the "tail wag the dog." If you build code to do something that should relate to your business but it doesn't work right, your business is not the one that is wrong.

You commented on how to tie things together. I told you about linking keys, but the question is how do you know which key goes where? You start by drawing out a data map. List the things you want to track. You obviously will track quotes. But what goes into quotes? How do you get quotes? To whom do you give quotes? What are the elements you put into a quote? These are all rhetorical questions that YOU need to answer in order to lay out what you are doing in a reasonable way. Since in this conversation, only YOU know where you want to go and how you want to get there, we can only offer non-specific advice unless you offer specific questions. Since YOU are the subject matter expert, you will have have to draw the lines between entities you want to relate. These lines might either become formal relationships or they might become some kind of action query / sequence. The point of this? Make a roadmap. Otherwise, how will you ever know that you have arrived at your destination? Trust me, your Garmin Navigational Aid isn't going to tell you.

When searching for Database Normalization, you can search two places: This forum or the general web. If you search this forum (Search is in the upper right of your screen), you can search for normalization. If you search the general internet, two bits of advice: Search for "Database Normalization" because there are other types of normalization. And for your initial reading, stick with articles originating from .EDU sites. Not that the .COM sites are no good, but they often have something to sell you that is proprietary rather than general. So start with .EDU and then sample the .COM sites later.
 

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
I think I figured it out (Thanks for everyone who helped).
But now I have run into a problem that I have yet to discover the cause/fix for.

I made a new form and added sub-forms to start pulling everything together, but now when i open the form up, several of the forms fields have somehow become read only. They are not locked, and none of the settings have changed, but I cant get them to unlock again.

I made a button that opens the form and goes to a new record ("start new quote" button), but whenever I seem to use that button, it locks half the fields. The bottom left corner says table is read only, but it shouldnt be. I think something somewhere else possibly broke.

The database is attached, and the particular form in question is the "SubDetails" form. I know my naming is bad, I plan to clean it up later.
 

Attachments

  • Active.accdb
    1.5 MB · Views: 105
Last edited:

plog

Banishment Pending
Local time
Today, 09:02
Joined
May 11, 2011
Messages
11,613
You're tables/relationships are incorrect, so there's no point discussing forms. You need to set up your tables before anything.

When I open you Relationship Tool I see that you have circular paths among your tables. For example, I can trace 4 paths from QuoteDetails to Product, there should only be one way to travel your tables between those 2 (or any) tables. I don't know your data well enough to know which 1 path is correct, but I know databases well enough that what you have is incorrect.

I suggest you clear out your Relationship Tool, leaving just QuoteDetails. Then, rebuild the relationships by adding 1 table back at a time linking that new table to just 1 table in the tool. When you're adding a new table that you feel should be linked to multiple existing tables you have found the issue and need to figure out how to reconfigure your tables such that new table links to just one existing one. It cannot be linked to 2 existing tables.

If you have trouble, post back here with the table you don't know how to relink and explain in simple terms what it does and why its related to 2 tables.
 

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
Looking back over the tables I see what you mean. I did not realize how poorly I had those structured. Will work on that tonight and clean it up. Thanks for the insight!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:02
Joined
Feb 28, 2001
Messages
27,003
Just to add a little explanation: When you have relationships that are multi-pathed, you confuse Access. Its goal is to figure out how to write back data to its tables based on the forms you have bound to the tables. But when you bind to queries that include JOINs (which IS legal), if there is something ambiguous in the relationship, Access doesn't understand how to manage the writeback because it doesn't have a unique path. So that makes anything that uses an invalid JOIN type of query to drive the form's data into a read-only query.
 

mike60smart

Registered User.
Local time
Today, 14:02
Joined
Aug 6, 2017
Messages
1,899
Looking back over the tables I see what you mean. I did not realize how poorly I had those structured. Will work on that tonight and clean it up. Thanks for the insight!
Hi
In the attached I have given an example of the Relationships between the Quote - the Revision and Sheets
 

Attachments

  • Example (1).zip
    122.9 KB · Views: 107

QMDirk

Member
Local time
Today, 07:02
Joined
Nov 16, 2019
Messages
52
Thank you!

That make some sense.

How would I implement that with my current setup where the quote data is created first? The reason behind me having the basic info created first, is that we have one person who initially starts the process, but other people are the ones who actually quote a job. So the first person would input all the jobs and assign them to various people who then take it from there. The person doing the quote then needs to have a form that is "under" that quote that the other person created.

My initial idea was to have my main form/dashboard have a sub-form that shows all jobs currently active (eventually it will only show jobs assigned to that person but that's for when I manage to implement a login system). That sub-form would populate as the first person creates the quote, then the person doing the quote would click the quote number to open the form to actually being quoting the job. There would be no need for the next person to open the "create quote" form.

I feel like I am wording this very poorly.
If it would help, I can attach what I have created thus far. It might give insight to my madness.
Hey bro. Here's a method I use and it's pretty easy to keep track of.

Create a query on the table that holds the data that is entered when the quote data is entered. In the Criteria field, type:

[Forms]![Form 1 name]![ID]

- this will look only for the record number indicated by whatever record number is displayed on Form1 when the button is clicked.)

Put a button on the 1st form that says something to the effect of "Continue"...

Create a 2nd form and use the same table as the quote data-table as its Record Source.

Back on the first form, on the 'Continue' button, put a macro to:

In the 1st row use Method: Close 'this will close the current form;
In the 2nd row use Method: OpenForm 'open the 2nd form

Withe the OpenForm row highlighted, at the bottom of the page set the button to open the 2nd form you created.

In the Filter field, type the name of the query. This is where the magic happens.
Now, on your 2nd form, place a couple of fields (in Design Mode, from the Available Fields screen) in the Header, like ID, and Order Number - something that is recognizable when you switch forms.

In the Details section of the 2nd form, draw a Subform and use another table that will store data you want linked to the quote. My forms will have 3-4 subforms on them and they all display the ID field so I know that everything is referencing correctly.

So what your doing is this"...OnClick, open Form2 where ID (on Form2) = the ID on Form1. Now form 2 is linked to form1. By drawing a subform on form2, and using the ID fields as the Master Link, the subform data will be linked to the data in the header, which is simply carried over from the previous form.

Are you following? Hope this helps.
 

Users who are viewing this thread

Top Bottom