How to save new form records

[sud2017]

Agreed with your comments. No.Assembly in QuoteHeader is a Quantity multiplier for parts in Details section, meaning that it will multiply quantities of all parts by this number. Updated table structure attached. Thank you for directions

Excellent!

Quote type 3 - this was to cater for quotes where neither a job nor an opp is selected. I thought somewhere you had said this could be the case (needs clarification, affects form design only). Regardless, the ability to add additional part numbers to the quote will handle this.

The link from job to detail is extraneous, jobs do not have details - you will never enter details against a job ID.

Can you type in records into the details portion of the quote, or must it always be a valid part number? If the latter then there is no need to carry the description on the ItemsT.

Is "No. Assembly" on the QuoteHeader the quantity?

[aytee111]

Can you type in records into the details portion of the quote, or must it always be a valid part number? If the latter then there is no need to carry the description on the ItemsT.

Same applies to OppDetails.

Otherwise, this is great.

[sud2017]

I cant think of anything else at this moment that my application should do. For 3: Changes to existing quotes, I want this functionality for both Jobs and Opps. What would be the next steps?

I have quote forms in our company to refer for anything. Thanks very much.

The next thing is to write down the steps in the process of adding a new loan. We will use this process later on to design a form that will do all that you want.

Adding a quote:
1: (optional) user selects job ID
- show list of opps associated with this job
- run a query to add all details for this job to the Items table
- refresh the screen data
- do not allow entry of opp ID

2: if no job entered, user selects opp ID
- run a query to add all details for this opp to the Items table
- refresh the screen data
- do not allow entry of job ID

3: (optional) user changes quantity
- run an update query against ItemsT to change the quantities

4: Changes to existing quotes
- changes are allowed ....
- changes are not allowed ....

Add as much detail as you can think of so that when it comes to creating the form the decisions have already been made and you can just dive in and start doing it. You will find things missing at this stage (such as a global system table to store default values, e.g. markup pct, or a field to contain quote terms/duration) and may rearrange some things. This stage is getting deeper into the detail design. Hopefully your company already has a quote that you can use as a reference.

[aytee111]

Do post # 60. If you are confident with form design then start on it, otherwise write it out first.

[aytee111]

Oh, you can't design the form until you have entered the tables into the database, lol.

So add those tables to your database. Be aware of how you name fields,
- there is a lot of repetition with fields - the same field on multiple tables, such as quantity. Make sure each one has a different name. Some people have prefixes on their tables (Item_Qty, OppDet_Qty, etc.). All fields must have different names so that it is immediately apparent when in forms/queries/reports where that field is coming from
- EXCEPT all ID (primary key) fields must have the same name across all tables
- no spaces or special characters except for underscore (_)
- no reserved words, such as Date
- no lookups on tables, no formatting, no calculated fields

[sud2017]

Understood. I am gonna enter tables into database tonight. I believe all IDs should be autonumber? I haven't still thought what my ID numbering for Quotes, Opps and Jobs would look like. I wont enter any data into table for now, just in case if anything changes on table design. Just wanna tackle everything step-by-step to avoid making any mistakes. Thank you!

[ssanfu]

Instead of "No. Assenbly", I hope you will use something like qtyAssembly or "AssemblyQty".

Nice job, btw, in using pencil and paper to design your table structure before jumping into Access and creating tables..
I rarely see this happen... should save you from later on or tearing out your hair....

[aytee111]

Autonumber primary keys denote a meaningless number, a key where you don't have to concern yourself with what it contains, it is purely a way to identify and link records. Your part number could be a primary key, for instance, as it is unique and has meaning, altho some people prefer number keys to long text fields, also that field could change altho in this case it shouldn't.

If your company currently has a quote system then they may already have a numbering system set up, which you could use as the primary key. The others are basically meaningless so autonumbers would work well.

Not sure if you want to add an item number to the quote - item 1, item 2, etc. Then the primary key could be a composite of Quote and Item, again most people prefer single keys to multiple.

[aytee111]

Gosh, Steve, you're still around! That's real dedication, lol.

I totally agree with you, sud2017 has done an awesome job.

[sud2017]

Thanks for the clarification. Makes more sense to use single keys.

Autonumber primary keys denote a meaningless number, a key where you don't have to concern yourself with what it contains, it is purely a way to identify and link records. Your part number could be a primary key, for instance, as it is unique and has meaning, altho some people prefer number keys to long text fields, also that field could change altho in this case it shouldn't.

If your company currently has a quote system then they may already have a numbering system set up, which you could use as the primary key. The others are basically meaningless so autonumbers would work well.

Not sure if you want to add an item number to the quote - item 1, item 2, etc. Then the primary key could be a composite of Quote and Item, again most people prefer single keys to multiple.

[sud2017]

Thanks for your help and motivation Gurus, aytee111 and Steve. Paper and pencil was aytee111's idea

[sud2017]

Just finished entering tables into the database. Wondering if its advisable to enter dummy data to test my form design? I am hoping to prepare a prototype with few details that I can present to my manager, to show all functionalities. QuoteIDs might change afterwards once others are involved in my company. Please advise.

I'm thinking the form design would be something like this:-

1. Main Form will contain 2 comboboxes to give selection of 'jobs' and 'opps' for cost estimation
2. If an 'opp' is selected, a quote form will open up with Header and Details, and footer that contains TotalCost and TotalPrice
3. If a 'job' is selected from combobox, a quote that contains few opps will open up. There will be textboxes beside each opp names for Totalcost of each opp. User will open up opps one-by-one from the quote to complete estimating opp costs, and then come back to job form to get the Total Cost/Price of a job.

Could yo please share your thoughts on the above. Thank you very much.

[aytee111]

Brilliant! Hope you are having fun.

There are a few links missing, one repeated field, and one missing field. The links are important, the other things will be fixed as you go into form design.

1 and 2 will be a main form/subform setup - the main form containing everything to do with the quote header and the subform carrying everything to do with the items, linked by quote ID.

For 3 you may want to think about using the same main form/subform setup but adding a listbox which shows all opps for that job. The user will click on them one at a time and the subform will then show the details for that opp, same as for 2. There is nothing to do with jobs that the user is going to change or any value they need to enter, so a listbox display is sufficient.

My philosophy is to give the user as little as possible to complete their task - minimal clicking, scrolling, forms opening and closing. And to have as little repetition as possible in order to cut down on the maintenance and possibilities of things going wrong in future development. Believe me, you are going to forget everything in a couple of months, then you will have to come back and say - "Now why did I do that?". So much easier to keep things to a minimum.

Before you begin, make 100% sure that
1) user will ALWAYS select a job or an opp, never quote for purely parts
2) user will ALWAYS want all opp's associated with a job and will never want to remove one from the list or add another
3) user will ALWAYS want all parts associated with an opp and will never want to remove one from the list

These things are very important to get straight before you go into form design as they will totally change the way the form is designed and will be hard to change later. You are being very strict, which is good and necessary if that is the way your company operates, and also makes it far easier to develop. But it does tie you down to giving the user no choice and no flexibility.

[sud2017]

Great! Thank you for insight. Yes its fun, hope it was my fulltime job

I will be fixing the table today/tonight and putting our ideas to paper for form design as well, so it is straight in my head first.

I like the idea of minimal input from the user. If there is a need to modify job to have more opps in future (which might be the case), I will be the one doing it, so no user intervention required on that. I'm only 1 month experienced to everything in Access so form design is also part of learning. Thanks for your help.

[ssanfu]

A couple of my naming conventions is if the PK field is an Autonumber, I use "ID" in the name (you have) and I use the suffix "_PK".
For the foreign keys I have a suffix of "_FK". Again, for me, it makes it easier to know which are PK fields and which are FK fields when creating complex queries.
The order of the fields in tables is the PK field is the first (top) field, then FK fields, then other fields. Grouping the FK fields makes it easier to find them in the relationship window.

I find this especially helpful when writing/reading SQL....

So for the table OppheaderT, the fields would be
OppID_PK
JobID_FK
Opp_Desc

And OppDetailT would have
OppDetID_PK
OppID_FK
PartID_FK
OppDet_UnitCost
OppDet_Qty