Post Holding thread for my own use
Thread: help with complicated formula
Briefly, these are the steps that IMO are required to develop an App like yours:
I: Develop the database
- Create a list of every possible data point you can think of. See my "Company Database" sheet and your "VDO" Sheet. Name this sheet "Data Points List"
- Use two columns, the first for the name of the data point and the second for a brief description of it. The second is mostly for me, but you will find it useful, too.
- The Data Point "Date" should be at the top of the list.
- Its description is "The Calendar date of which all records are pertinent to."
- It is the only Data Point (DP) with the simple name "Date." All other Date names should be uniquely descriptive such as "VDOPrintDate," "ShiftStartDate," ShiftEndDate." etc. I'm not saying that you will have ShiftDates, that's just an example.
- Using the description column, insure there are no duplicate data points. Indicate in the description which different place this DP is used.
- Using a different sheet for each group, organize the DPs into related groups like the "Company Database" and "VDO" sheets.
- Name each sheet according to its group
- Put the lists in row 5 or 6 on each DP group sheet.
- Using the Descriptions, make sure that each group sheet has its own copy of the relevant duplicated DP.
- DP name (calendar) "Date" should be at the top of each groups sheet's list.
- Review all the groups to insure that the requirements in steps 1 and 2 are met.
- On each Group sheet, copy the list of Names and in cell "A1" PasteSpecial >>Transpose.
- From now on, we will no longer refer to DPs and Groups, They have now transformed in to Field Names and Database Tables.
- Format each Date and Time Field (Entire Column) to suit.
- Save the workbook with the name "Database Descriptions."
- Anytime in the future when you modify a database, edit this workbook to match, adding notes to describe what, when, why.
- Keep Data Points List up to date.
Normally I would allocate several days for all the above, but you (and I helped a little bit with the list of DP Names,) have already done most of it. By the time you complete the above, you will have a very good feel for what your App can and should provide any truck driver and most important, what Form(s) you will need. The completed "Database Descriptions" is half the work of developing the UserForm(s), so do not think that it is not necessary.
Each of the below major steps will also take a few days. Only a few because of all the preparatory work done in step I. As you get to each step below, I will be expanding the minor step lists, so you will know what to expect.
II: Lay out the Userform(s)
- Save book as "TDR.v.01." Suggested name.
- Use the Data Points List to know what controls you'll need.
- Open "Database Descriptions" and add the sheet "vbaList" to the end.
- Delete all DP lists, leaving only Field Names.
- Complete "vbaLists."
- Make Lists of Suggested Form Control Names
- Add database type code to Database sheets and "vbaLists."
- I already have this code, that only needs localization to work in any DB type sheet.
III: Code the Form(s)
- Increment the Version number to .02
IV: Add Reports Sheets, Increment Version number
V: Repeat II, III, & IV as needed.
VI: Beta test.
VII: Repeat step V as needed.
VIII: Sell the Application :)
IX: Repeat Step VII as needed
You will have noted that with each major step, the work book gets saved with a new name. It is not until the end of step V that it actually gets a working name. Be sure and keep a copy of each workbook with a different name, even if it's just a minor revision number change.
I usually keep my projects in their own folder with the following sub-folders:
bas is where I export to and import from any modules I want to move from book to book.
Paul, take whatever time you need to understand what is in this post. As questions about it if you need to, but really try to fully understand it.
Post Holding thread for my own use
thinking of writing a thread about project design. need a place for notes.