How to save a set of forms with records

[sud2017]

Hi Expert,



My database project contains a Main Form and few subforms and it will be an application for Cost Estimation for different quote opportunities in my company. I have a main form with combo box that lists different 'opportunities' amongst which I select one for cost estimation. Upon selecting one opportunity, a main form and a set of subforms open up that let me do the opportunity cost estimation.

Question - I have been able to design this database to perform costing for me, but I now want to save all the estimation records once costing is completed for different opportunities. Therefore, when a user opens up the database, he/she will also be able to open up the previous costing (and could see main form for summary cost and subform for component cost).

Is it possible in Access to save these forms for future reference? These subforms contain values from tables, queries and they perform calculations in textboxes containing formulas.

Any guidance or approach would be greatly appreciated. Thanks!

[CJ_London]

you wouldn't save the forms, you would save the key data (customerID, QuoteID, quotedate in a 'quote header' table, QuoteID, productIDs/quantities/prices in a 'quote lines' table. Should be no need to save calculated values such as price*quantity since these would be calculated when required.

If you use these tables as your form and subform recordsource, the records will be updated automatically.

[sud2017]

Agreed. However, I have a user entered textbox in subforms to enter Mark-Up%, and also a provision to enter new items names, price, qty in subforms. Reason being that different quote opportunities are unique to some extent. Currently, we do this in excelsheets, where different tabs give component-wise costing and main sheet gives summary cost. These sheets are stored in shared drive for future reference for similar quotes. Therefore, I am hoping for a way that I could find such records in Access that we currently find thru' Excel. I can only substitute the Excel based costing in my company to Access based costing if I can both estimate new quotes and review old quotes records that we currently do with Excel.

what would be the best approach to achieve this? I was also thinking about exporting the whole record in excel (with main form and subforms) and store them in shared drive for future reference but I thought that accessing it thru' Access would even be of greater ease to end user.

you wouldn't save the forms, you would save the key data (customerID, QuoteID, quotedate in a 'quote header' table, QuoteID, productIDs/quantities/prices in a 'quote lines' table. Should be no need to save calculated values such as price*quantity since these would be calculated when required.

If you use these tables as your form and subform recordsource, the records will be updated automatically.

[CJ_London]

Generally speaking for data input and management, Access will beat Excel every time.

If you can design it on paper, you can do it in Access, just needs some though as to best way to do it. An access app consists of two components - a backend which will sit on your server and a front end which each user has on their own machine - in the same way you store your excel files on the server and each user has a copy of excel on their machine.

The backend contains the tables only, the front end everything else.

There is zero point in exporting your data to an excel file to sore the data.

[sud2017]

Great! If Access if so powerful, then it'll be fun exploring all those hidden features.

Need your opinion on this idea - if I declare my textboxes as global variables and include some code in Open () and Close () event of my form, I could possibly be able to store the data in textbox after my form is closed? Do you agree with it? I'm just exploring it so need validation of thought. if its possible, then I could SAVE AS my current cost estimation form with some other name with all values intact for a particular quote. Then I could add a combo box in my main menu that will allow to access all previous quotes costing with records intact.

Do you think this approach is workable and good? Thank you very much for your time and help.

Generally speaking for data input and management, Access will beat Excel every time.

If you can design it on paper, you can do it in Access, just needs some though as to best way to do it. An access app consists of two components - a backend which will sit on your server and a front end which each user has on their own machine - in the same way you store your excel files on the server and each user has a copy of excel on their machine.

The backend contains the tables only, the front end everything else.

There is zero point in exporting your data to an excel file to sore the data.

[CJ_London]

if I declare my textboxes as global variables and include some code in Open () and Close () event of my form, I could possibly be able to store the data in textbox after my form is closed? Do you agree with it?

No - you are miles off. As said before store in a table. You need to put your excel head in another room. Excel combines data storage, calculation and presentation on one sheet. Access stores data in tables and uses queries, forms and reports for calculation and presentation. Excel tends to store data 'wide and short', Access stores data 'tall and thin'

Take a look at some sample databases and see how they work

[sud2017]

Thanks for your quick advise. I am storing data in tables and queries with this database too, those fields are productID, unitprice, qty etc. However the only values I am not able to store in tables/queries is TotalPrice of a quote; reason being my totalprice is calculated based on margin% entered by the end user while estimating the cost.

Therefore, when I close and open the form, I see the tables values but the calculated values in 'totalprice' textbox is gone and I'm not sure if I can store value of totalprice in table because its a calculated value after manually entering the mark-up. In that case, how would I save this record to refer for future? Below screenshot with fictitious values will provide more clarity. Thanks again for your help. much appreciated.

No - you are miles off. As said before store in a table. You need to put your excel head in another room. Excel combines data storage, calculation and presentation on one sheet. Access stores data in tables and uses queries, forms and reports for calculation and presentation. Excel tends to store data 'wide and short', Access stores data 'tall and thin'

Take a look at some sample databases and see how they work

[CJ_London]

you need a 'quote header' table to store the markup, plus some means of identifying the quote.

[sud2017]

Ok thanks. I created a markup table and added the totalcost, markup$, totalprice etc. fields to the items table, which I previously had in text boxes. Now the records remain in the form when the form is closed. But now, I am trying to figure out how I can identify this form uniquely with a quoteID so I can open the form to view specific quote record from a find combobox in main page. Will have to do some digging in. Ideas would be appreciated. Thanks.

you need a 'quote header' table to store the markup, plus some means of identifying the quote.

[CJ_London]

not difficult - add a field for the user to complete - or auto populate with something meaningful like the date, who the quote was for, what the quote is about