+ Reply to Thread
Page 1 of 3 1 2 3
Results 1 to 200 of 499

Designing an Excel application for simultaneous use and for optimal maintainability

  1. #1
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I have started the new thread you suggested, as we get deeper in to our design and assembly of the parts into a usable program!

    More info coming soon as I am a couple days behind currently, it seems!:roll eyes:

    u3rick

  2. #2
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    A collateral benefit of declaring the functions public, is that they become available in the worksheets. Meaning you can use them in formulas.
    Not sure if that's what you intended when putting them in a separate module and declaring them public.
    I was following how you did the DatePicker. I as you suggested I didn't know the end result of doing it that way but do feel it will be used in other reports.
    Last edited by u3rick; 12-01-2015 at 11:22 AM.

  3. #3
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Declare the public FormEventsEnabled variable as boolean in the mdlTimePeriod
    More explanation needed
    Last edited by u3rick; 12-01-2015 at 11:21 AM.

  4. #4
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Please Login or Register  to view this content.
    This is not working so my guess here is wrong, whats missing?

    Update: What I am trying for here is if the date is picked from the DatePicker manually the cbo should read "Custom", If the date is picked using the cbo then the method used should show. It should be able to go back and forth!
    Last edited by u3rick; 12-01-2015 at 12:07 PM.

  5. #5
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    If we move to Access will it be easer to convert to a web based program, so it can be used without Excel or Access (Like Lodgix?)

    u3rick

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Declare the public FormEventsEnabled variable as boolean in the mdlTimePeriod
    This is the same variable as we used in the ES Reservation workbook. You need it to prevent formevents being executed inadvertedly and trigger an uncontrolled chain of events.
    As you need to have the value of variable available throughout the workbook it needs to be declared as public.
    Put this line at te top of a module.
    Please Login or Register  to view this content.
    Then - when in the code you're changing the value of a form object (textbox, combobox or alike) - set FormEventsEnabled to False. And set it back to True after the form object's value is changed. Preferably set the variable to False at the beginning of a procedure changing form objects and set it back to True at the end of it, so you can easily find it.

    And then don't forget to have the event procedures of formobjects (xxxx_Change(), xxx_Click() and alike) check for the value of FormEventsEnabled and only execute the code of it when FormEventsEnabled is True.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  7. #7
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    If we move to Access will it be easer to convert to a web based program, so it can be used without Excel or Access (Like Lodgix?)
    You would still need something to hold your data, execute your functionality and host it all along with that web based program.
    Unless you can find such a program on the web which already has all the functionality you're now building yourself.

    I would say: don't bother. Just stick to what you're doing in Excel. I don't think it will be necessary to move the Access. Maybe on the long run, but we'll look at that when it comes up.

  8. #8
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    This is not working so my guess here is wrong, whats missing?
    In what version did you make this change? So I can have a look.

  9. #9
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Here is the latest version It may not be the exact same code but still is not working!

    Ya it started working when i got
    Please Login or Register  to view this content.
    entered in mdlTimePeriod.

    Thanks!!
    Attached Files Attached Files
    Last edited by u3rick; 12-01-2015 at 10:30 PM.

  10. #10
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    No problems today? I'm getting bored
    BTW, the workbook you're uploaded in post #9 doesn't contain the mdlTimeperiod

  11. #11
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    BTW, the workbook you're uploaded in post #9 doesn't contain the mdlTimeperiod
    Sorry about that!
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Thought you were working on Journal and Ledger design setup for tables?

    Also finishing V3.0.5!

    Maybe your done and I didn't see ??

    Thanks

    u3rick

  13. #13
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Thought you were working on Journal and Ledger design setup for tables?
    Also finishing V3.0.5!
    Guess I've been losing track of things. Must be old age
    To reinstate oversight I decided to pull the whole system together in one chart, so we can use that as kinda roadmap and tune what's being addressed.
    Posted it to dropbox.

    If you look at the chart you can see it's split up in three sections:
    • Operations (ES Reservations)
      I think you have this pretty much figured out
    • Accounting (Management Sys)
    • Communications (AdvDepRpt AutoImport)
    • Reporting
    Not sure yet whether to combine all in 1 workbook or split it up into multiple.
    I think you can go for 1 for now, but let's see what that does with performance.
    If necessary it can be split up in workbooks each hosting on of the sections above.

    Hart of the system (and new) is a Journalizing Engine which is (most of the times) triggered by change events of sheets events and generates all necessary bookings either in the Accounting section or in the Operations section.
    For instance, if an invoice (GuestStay) is imported from Lodgix the GuestStays are update in the Operations section and financial records are fed into Aucounting section. Also records are written to an data file for upload to QuickBooks.

    Ideally the processing in the Journalizing Engine would be guided thru a table holding the rules.
    Not sure that's possible, but if the journalizing can automated then it will also be possible to have the rules in a table.
    I believe you we're assembling an list of events to be handled? May be we can bring these into a table.

    Another thing to notice from the chart is - if you still remember you're first post - you've gotten a long way.
    And something nice is developing.

  14. #14
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I totally agree it will be nice and you have directed me past anything I thought possible. I will look at this tonight as I am in the middle of another project!

    Thanks,

    u3rick

  15. #15
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Seems to me that something in operations 3.0.2 was not working and you were going to fix it. I would like to move the necessary parts from 3.0.2 to the Management System when you say it working. Not now what the problem was been a few weeks!

    I should move AdvDepRpt AutoImport into sheet AdvancedStaysLedger?

    u3rick

  16. #16
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Seems to me that something in operations 3.0.2 was not working and you were going to fix it.
    You need to help me out here. What was not working where?
    Been looking back in the other thread a long way, but can't find anything ringing a bell.

  17. #17
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I also looked and think it may have been in a private message, but I have deleted them all, but I ran the latest version 3.0.2 in the Dropbox late last night and I didn't find anything in that session that didn't work! So lets move on! sorry for wasting time.

    Next, I tried to combine the two programs and I failed there is a copy in your drop box you can look at. I didn't know the best way to combine so I went forward with a guess and I assume I was wrong. Combined program name is (Reservation Management System Lodgix QBs) RMS_Lodgix_QBs_v1.1.0. Take look and direct me on a better way to combine. The main problem is have changed some column headings and add vacation units that don't go on the calendar. Also when i copied it held the path from the original version some still may be present.

    Thanks

    u3rick

  18. #18
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Merging sheets from different workbooks is tricky business. As with the Properties and Owners.
    Simply copying is not an option as you may have noticed.
    So merging ES Reservations and ManagementSys will be pretty labourious and you'll really need to know what you're doing.

    When I look at it I think it's best to take the ES Reservations workbook as the basis and copy the sheets from ManagementSys which are not in ES Reservations.
    Sheets who are already in ES Reservations are best changed manually to comply with those in ManagementSys.

    When you delete columns or change column names check the code in the modules and userforms for lines referencing these columns. And change or remove these lines accordingly.

    Make the changes step-by-step. Check if the workbook still works after each step and if so, save a new version.

  19. #19
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    That sounds like a plan I will start over and work on that plan over the next few days.

    Will you direct me on how to build the Journal and Ledger sheets (Tables). I assume they would be tables but they have multiple lines so no idea on that!?

    Thanks

    u3rick

  20. #20
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    That sounds like a plan ...
    Rather sounds like hard labor, I would say

    Will you direct me on how to build the Journal and Ledger sheets (Tables).
    I was planning to continue prototyping the communctions section:
    • AutoImport
    • Journalize Engine
    • AutoExport
    I expect the Journalize Engine will force me to address the Journal and the Ledgers too.

    BTW, you don't need to import the AutoImport into the new integrated workbook.
    It's just a demo and I'm still working on that.

  21. #21
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I have been very busy with a client that has some accounting and inventory problems. It has required all my time the last week. Don't give up on me I will be back soon!!

    Have a few beers!!

    u3rick

  22. #22
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Just wanted to wish you the best for the happy Holidays! I hope to get back working on this project soon. How are things going with you and the engine!


    u3rick

  23. #23
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Oops! Completely missed your previous post.
    I'm making some progress with the engine (incoming part). Think it's getting quite nice.
    I've also been busy planning our next travel to Indonesia. Took me quite some time (we're spoiled and picky), so progress on the engine has been slow.
    I'm now waiting for my agent's proposal and was just about to pick up the engine again. It's always good to take some distance from a project after being focussed on it for a long time.

    Happy holidays to you too and all the best for the year to come

  24. #24
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I can't believe I have not got to work on this project for so long. I should be ready to start in the next week or so. Hope I can still remember what I was trying to do!

    Just checking in!

    u3rick

  25. #25
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Do yo have ES Reservation_v3.0.02? My excel crashed and wreaked mine? You can put it in Dropbox if its easier.

    Thanks

    u3rick

  26. #26
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    It's in the Dropbox

  27. #27
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Thank you!

  28. #28
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I need help? Take a look at the dropbox ES_Reserv_MgmtSys_Step1_v1.0.2 please. I am making head way on combining the worksheets, but check the blank rows on the Calendar can't seem to get it to only show the ones that are there without extra rows. Now that I have added all the units whether they go on the calendar or not, I think thats the problem!

    Thanks

    Rick

    ES_Reserv_MgmtSys_Step1_v1.0.2.xlsm
    Last edited by u3rick; 01-24-2016 at 05:17 PM.

  29. #29
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Still having the problem on last post and having the same sorting type problem with OwnersPayableList. Good part is most all forms and sheets are working combined!

    Thanks

    u3rick

  30. #30
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Wow, that's a long time ago. Pfff

    The Worksheet_Change event procudure of the Properties worksheet adjusts the number of rows in the Calendar to the number of active properties when a cell in column PActive is changed.
    This procedure still works properly.
    However the formula in the column Property of the Calendar has changed and is no longer looking the PActive column of the Properties sheet, but instead at the Status column of the PropertyOwners sheet.
    This column itself has a formula looking at 3 other column which also contain formulas.

    The number of "enabled" properties in the PropertyOwners sheet is 22. That's the number of properties shown in the Calendar.
    The number of "active" properties in the Properties sheet is 31. That's the number of rows in the Calendar. Of which the last 9 are empty.

    To solve this I highly recommend to get rid of the chain of formulas. That's always a PITA and leading to this kind confusion.

  31. #31
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Ok I will try! There are a number of columns in the table PropertyOwners That are ODeleted_YN, PDeleted_YN and PODeleted_YN. Do I need all? I will add these YN columns to other tables that don't have them, so we can delete without getting rid of the data, correct?

    Thanks

    u3rick

  32. #32
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Can you try to make a new GuestStay and block out a unit that has an xy in front of its # for July and Aug. Neither of those thing are working for me. I must of messed something up!? It was all working so well that I was starting to setup a working copy to run with actual Vacationville data. When I started entering new GuestStays it would let me enter a New Guest, I then worked the rest of the day trying to figure out why it won't work. Its now 11:00 pm and I am giving up for tonight, maybe you can give me a little direction. Seems like I forgot more than I knew! Lool!

    Thanks

    u3rick
    Attached Files Attached Files

  33. #33
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Ok I will try! There are a number of columns in the table PropertyOwners That are ODeleted_YN, PDeleted_YN and PODeleted_YN. Do I need all? I will add these YN columns to other tables that don't have them, so we can delete without getting rid of the data, correct?
    Do keep it simple.

  34. #34
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Do keep it simple.
    I get that! My plan is to give every table the following columns (I think this is what you suggest some time ago).

    Deleted_YN ScreenShot.png

    The G in this instance stand for Guests. I am assuming the heading needs that letter in front so when it is combined into the GuestStays table it will be unique. I am also adding this column below to each table.

    GuestUniqId.png

    If you think any of these are overboard let me know and tell me which ones I should use and which ones I don't need.

    Once these are added then the code will need to be changed to look at only not deleted data? (I will need help here!)

    Thanks as always could not do this without your guidance!

    u3rick
    Last edited by u3rick; 01-26-2016 at 05:00 PM.

  35. #35
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Once these are added then the code will need to be changed to look at only not deleted data? (I will need help here!)
    Yes, I think this will give you a proper audit trail.
    But be aware of tone thing:
    this Worksheet_Change procedure adjusting the size of the Calendar (rows) will only be triggered when the content of one or meore cells has changed.
    A formula giving an other result will not fire the change event because the content (is the formula itself) did not change.
    So - if you want to only show active properties - you'll need to change the deleted_YN and active_YN columns thru code instead of formulas.

  36. #36
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I have removed the formulas from those columns as you pointed out earlier. If you have a chance when you are helping me with #32 could you check and see if they are ok now? Have not added a unit with latest version so don't know if calendar will grow?

    u3rick

  37. #37
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Can you try to make a new GuestStay and block out a unit that has an xy in front of its # for July and Aug
    When I make a booking (following your instructions) I get an error in the sub cmdCancelSA_Click() at the following line:
    Please Login or Register  to view this content.
    Problem is in the highlighted part as column 0 does not exist. This should be column 1.

    Is that the problem you're encountering?

  38. #38
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I think so, as I remember how it worked, it was supposed to work when you checked New Guest. It would put a new GuestID in the cbo and clear any other stuff. When I tried that it would crash, then if there was a existing guest in the cbo and you check New Guest it would erase that guest in the Guests table and then crash.

    Sorry forgot to tell what was wrong?

    u3rick

  39. #39
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Disregard this post... I fixed the problem, there was a long ago mistake made by me in the check box name. I found in stepping through the debugging process! I know that rare for me!!

    I can't find a version where this works so here is the problem the way it appears on my computer:

    1) I pick some days on calendar and right click, and the select what to do comes up

    Screen Shot one.png

    2) I clicked book a guest stay and click continue next screen.

    Screen Shot two.png

    3) then I click on New Guest Box and get an instance error message!

    Screen Shot three.png

    4) clicked on Debug

    Screen Shot four.png

    5) This crashes the program won't work until restart Excel!

    Thanks

    u3rick
    Last edited by u3rick; 01-27-2016 at 03:24 AM.

  40. #40
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Here is second problem

    I was trying to block out Jul and Aug for Owners that rent by the night in the summer. So I highlight the two months.

    Screen Shot one.png

    That brought up the what to do screen

    Screen Shot two.png

    Then i click on edit the property availability button.

    Screen Shot three.png

    Then I clicked on Add and it worked.

    Screen Shot four.png

    I ended up with the following which is what i had hoped for!!

    Screen Shot six.png

    Next I went on to do the next unit on my list which I will show on my next post.

    Thanks

    u3rick

  41. #41
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The rest of the story!!

    Screen Shot five.png

    then I clicked Edit the Property's availability and the next screens comes and looks good.

    Screen Shot eight.png

    Then I click add and the problem showed up it erased my block on JL 604 and put one on JL 510??

    Screen Shot seven.png

    It also now shows two Unavailable periods on the screen below period blocks?? I check the table in tables and I shows the same??

    I just could not find why this was happening it will crash if you delete theta some point.

    Thanks for your help

    u3rick

  42. #42
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Bet you missed all these questions!!?:roll eyes:

    I am working on my tables and adding the columns that I showed you in post #34. In the tblGuestTrans it is made up from a combination of a Transaction info & GuestStays (GuestStays is a combination of Guests & Stays). My question is in the table GuestTrans should all the columns in GuestStays table and new columns that deal with the Transaction be included, or just the ID #'s from GuestStays and the columns that deal with the Transaction?

    Hope your having a Great day!

    u3rick

  43. #43
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Yak! I think it's got something to do with counting the number of records for a property in the unavailability table.
    Need to take a further look at that.
    I guess you're referring to ES_Reserv_MgmtSys_Step1_v1.0.2?

  44. #44
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I am working on my tables and adding the columns that I showed you in post #34
    Looking at the tables I'm not sure you're going the right way. It's getting very fuzzy now.
    Not sure what you're trying to achieve.

  45. #45
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Yes or v1.0.3 i think both are in Dropbox. it works if you add then directly to the table so its something in the in-between!!

    u3rick

  46. #46
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Its something that we talked about a while back about what is needed when you are combining tables. When its better to have all the info in the combined table or not. That is the part I remember.

    u3rick
    Last edited by u3rick; 01-27-2016 at 05:20 PM.

  47. #47
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    My goal for the short term is getting the Calendar working and excepting payments from renters so I can use that part of the program while finishing the rest. I am close on everything except using the payment transactions and having a report that I can manually post to QB's in the interim! There is lots of cleanup and updating code and changing forms etc left in the new combined version. But I would like to get it running and then update as needed as I am testing the working parts.?? Now that might not be the right path feel free to re direct me!!

    u3rick

  48. #48
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I have re-worked some of the code in the frmStayActivity and it mostly works. But of course it has a couple big problems.

    The New Guest checkbox is causing some problems. I tried to use the templet code you sent me a while back, but there was nothing to follow on some of this code so it needs your assistants.

    To reproduce just pick dates and Book a Gueststay and then check new Guest.

    Having trouble with the table GuestStays filling in the formulas in the Properties section which makes the calendar not work.

    To reproduce just pick dates and Book a Gueststay with and existing guest.

    Also in the Dropbox

    Thanks!

    u3rick
    Attached Files Attached Files

  49. #49
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Got the problem from yr post #41 figured out.

    This piece of code in StoreAdhocAvailability moves the data to all cells in the specified column because no record number is specified.
    Please Login or Register  to view this content.
    Change the code like this and it'll work OK.
    Please Login or Register  to view this content.
    Same problem occurs in StoreMonthlyAvailability. Solution is the same.

    BTW, I've been looking at ES_Reserv_MgmtSys_Step1_v1.0.2, because ES_Reserv_MgmtSys_Step1_v1.0.3 gives other others (you've been changing column names in the tables, but not in the code).

  50. #50
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Its something that we talked about a while back about what is needed when you are combining tables. When its better to have all the info in the combined table or not.
    I still think it's a good idea to have copies of columns of other tables to minimize the number of lookups. But I also think these copies should serve a purpose.
    A good purpose would be the use of them in forms. It would be a proper way to have the base table with actual data (property, owner, propertyowner, guest, etc) and for each form have a table (view) with copies of the columns from the base tables which are used in the form.
    That would it more manageable.

  51. #51
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I am not sure, I have combined ES Reservation and Management Systems and tried to change code and headings as you suggested one table at a time., I have not changed any except when needed as far as I know. I am trying to get everything working and then if a heading needs change for some reason, I would do it one at a time! The version v1.0.3 exists because of a crash of my Excel and it is the recovered version and seemed to crash less than v1.0.2 so its my main version now!!

    u3rick

  52. #52
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    It would be a proper way to have the base table with actual data (property, owner, propertyowner, guest, etc) and for each form have a table (view) with copies of the columns from the base tables which are used in the form.
    That would it more manageable.
    We are not doing that now are we? guess I am not totally understanding how that works or maybe I am doing it and don't see it the same way!Loo!

    u3rick

  53. #53
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Now that might not be the right path feel free to re direct me!!
    There's not one right path. Yes, there are methods and techniques. That's important when you want to make your job of programming. I don't believe that's the case
    In your case perseverance, focus and prototyping is a good way to go. And that's what I believe you're doing. It is however not a smooth road as you're experiencing
    It all will work one day.

  54. #54
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tijallie

    BTW, I've been looking at ES_Reserv_MgmtSys_Step1_v1.0.2, because ES_Reserv_MgmtSys_Step1_v1.0.3 gives other others (you've been changing column names in the tables, but not in the code).
    I have got the changes working in v1.0.3!! I did delete a couple date columns in two tables as they were doubled with the same info!

    Thanks

    u3rick

  55. #55
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Ok, 1.0.3 works fine now.
    Will look into the problem with frmStayActivity.

    Edit:
    Apparently the name of the checkbox has changed, but in the chkNewGuestSA_Change the old name is still used.
    May be it's time to switch on "option explicit". These kind of errors would then be automatically detected.
    Last edited by Tsjallie; 01-29-2016 at 04:31 AM. Reason: additional info

  56. #56
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    There's more about this frmGuestActivity.

    In the sub cboGuestListGI_Change of the form the tables ListStayInfo and ListPymtHis are used.
    Both (view) tables derive the columns from tblGuestStays and tblGuesTrans resp.

    In order to avoid non-contingeous ranges being fed to the rowsources of the lists used a sort is being executed.
    But the sort for the ListPymtHis is done on the view table. As the sort order is defined by the base table, sorting the view table has no effect.

    The sort on the table tblGuestStays doesn't sort on GuestId, so the filter still renders a non-contingeous range.

  57. #57
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Disregard this post I found them!

    In the sub cboGuestListGI_Change of the form the tables ListStayInfo and ListPymtHis are used.
    Both (view) tables derive the columns from tblGuestStays and tblGuesTrans resp.
    I can't find these. Could you do a screen shot of location? It will help me find where these problems are. I know I should be able to find them!!

    u3rick
    Last edited by u3rick; 01-29-2016 at 12:55 PM.

  58. #58
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Added it as a Variant (Dim i AS Variant) and it worked so I hope that is correct!

    I added Option Explicit to the code for frmStayActivity! I had made changes in the code and it worked (Found your code templets) but with Option Explicit on get this error message.

    Screen Shot 2016-01-29 at 8.23.07 AM.png

    I await you help! I think we need to keep Option Explicit on!

    u3rick
    Last edited by u3rick; 01-29-2016 at 01:17 PM.

  59. #59
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I understand what you are saying in post #56. Not sure what to change? The frmGuestActivity works?

    Screen Shot 2016-01-29 at 9.22.31 AM.png

    u3rick

  60. #60
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    ES_Reserv_MgmtSys_Step2_v1.0.4.xlsm is the current version(In Dropbox) and has Option Explicit in all forms except frmDatePicker you can handle that one!

    I am going to start to update code to the templets you made a while back. Most of the code in MgmtSys used the templets but most ES Res were not wish me luck. This project will be second to getting the calendar operating as Vacationville ES reservation calendar and payment system!

    Thanks #gettingthere! lool! This is a Twitter thing my kids do!

    u3rick
    Attached Files Attached Files

  61. #61
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjillie

    As far as redoing code would you look at frmGuestActivity. It has lots of lines that are not in the templet. If you make the necessary changes to it I can use it as the templet for future form's.

    Thanks

    u3rick

  62. #62
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I understand what you are saying in post #56. Not sure what to change? The frmGuestActivity works?
    In ES_Reserv_MgmtSys_Step2_v1.0.4 frmGuestActivity coincidently works because all the guests in the calendar appear only once in the ListStayInfo table.
    In that case the range to set the rowsource to spans only 1 row and so is consecutive by definition.

    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  63. #63
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    My Notifybox is not working I tried uninstalling and reinstalling but there is some problem with Windows 10. Not sure what to do but waned you to know!

    u3rick

  64. #64
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Discovered an error in the sub HandleRightClick in mdlESReservation which cuases monthly unavailabilities not being saved.

    Please Login or Register  to view this content.
    As you can see the form is shown after FormEventsEnabled has been set to false. This makes that the change event procedures of the checkboxes are not executed.
    If you set FormEventsEnabled True right before the line [frmManagePropertiesAndOwners.Show] this will be solved.

  65. #65
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I'm working with the file you posted here. The dropbox file is all yours.

  66. #66
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Got it ! changes made to Dropbox version!

    thanks

  67. #67
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    As far as redoing code would you look at frmGuestActivity. It has lots of lines that are not in the templet. If you make the necessary changes to it I can use it as the templet for future form's.
    You can replace the entire code of frmGuestActivity with this code.
    All changes I made (not many) are marked with a comment starting with "Tsjallie:", so you can compare the code to the old version.
    It also contains the changes I advised earlier.
    Please Login or Register  to view this content.

  68. #68
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Very cool!!

    Thanks so much!!

    u3rick

  69. #69
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I still have this bad problem every time I make a New Stay the Calendar goes away. Seems to happen when the new line is inserted into tblGuestStays. I also have a problem when the Option Explicit errors.

    Just pick some open months on the calendar and right click them then book and existing guest. Next do the same but instead of picking an existing guest check New Guest it will error!

    Thanks

    u3rick
    Attached Files Attached Files

  70. #70
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I would like to filter the cboGuestlistGI which gets its lists from the Table ListBox with the GDeleted_NY. I want the listbox to only show the guests that ="N". Then the frmGuestActivity has a chkbox that if checked would have the (ListBox)show all guests no matter if deleted or not.

    I have some code that I played with (Not so well) that I will post with the workbook here. The code is in Module 1 and it works with the Guests Info button located by the logo in the Tsjallie Calendar.

    Thanks

    u3rick
    Attached Files Attached Files

  71. #71
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I still have this bad problem every time I make a New Stay the Calendar goes away. Seems to happen when the new line is inserted into tblGuestStays. I also have a problem when the Option Explicit errors.
    The Calendar disappearing is caused by the formulas in the Property columns in the newly added GuestStay record being in error.
    This error is caused by the PropertyId which is moved from the userform to the table as text. Remember all objects in a userform containing data are of datatype text.

    The error with the type mismatch is also caused by a data type conflict. The column GuestId in the Guests table is formatted as number where it should be standard.
    Best is to check all Id columns in all tables and make sure they are formatted as Standard

    Finally the sub cboGuestIdSA_Change was called in a situation that it shouldn't be called. Can be solved by setting FormEventsEnabled properly.

    Here are the changes you should make to get things work OK (hope I got'm all):

    Worksheet Guests
    Change the format of the column GuestId to Standard

    Private Sub cboGuestIdSA_Change()
    Change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Private Sub chkNewGuest_Click()
    Change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Private Sub chkNewGuestBox(GuestId As Double) <- Remove the parameter
    At start of sub insert
    Please Login or Register  to view this content.
    Change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    At end of sub insert
    Please Login or Register  to view this content.
    Private Sub SaveEditGuestStaysInfo(GuestId As Double)
    Change (3 times)
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Don't be suprised if solving this problem will lead to the next. I think you reached that stadium by now.
    Last edited by Tsjallie; 01-30-2016 at 04:21 PM.

  72. #72
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Thanks!!

    still amazed at how little i know!!

    u3rick

  73. #73
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I would like to filter the cboGuestlistGI which gets its lists from the Table ListBox with the GDeleted_NY
    This should work.
    Please Login or Register  to view this content.

  74. #74
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Update: Ah finally figured out what you are talking about!!

    The error with the type mismatch is also caused by a data type conflict. The column GuestId in the Guests table is formatted as number where it should be standard.
    Best is to check all Id columns in all tables and make sure they are formatted as Standard
    I must have forgot or never knew what is the difference?



    u3rick
    Last edited by u3rick; 01-30-2016 at 05:07 PM.

  75. #75
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Does Standard = Text or General I am thinking text after re-reading your post!
    Last edited by u3rick; 01-30-2016 at 05:12 PM.

  76. #76
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The Calendar still disappears here is what tblGeastStays looks like

    Screen Shot 2016-01-30 at 1.26.45 PM.png

    Sorry this is wrong file attached!

    Thanks

    u3rick
    Attached Files Attached Files

  77. #77
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    This is the correct one
    Attached Files Attached Files

  78. #78
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Good thing I have very little hair or I would be pulling it OUT!!

    Here is a new mismatch only has a problem if i use the Button in Calendar! I have check the Id's they are all set to Text, I am thinking that is correct, so I am lost again!!

    Screen Shot 2016-01-30 at 2.00.39 PM.png

    Screen Shot 2016-01-30 at 1.57.55 PM.png

    Thought this worked earlier when I added the cmdSave for guest info??

    Thanks

    u3rick
    Last edited by u3rick; 01-30-2016 at 06:22 PM.

  79. #79
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Rebuilt the GuestStays table but it didn't change anything. Although it help the one column that would not copy to added rows. It is now copying to the new rows!

    u3rick
    Last edited by u3rick; 01-31-2016 at 02:11 AM.

  80. #80
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Hi all

    Tsjallie tried to get you some help in answering my many question!..No takers looks like its on you!! Lool!

    Tsjallie is sleeping! Does anyone have an idea why this code is not copying over the existing data in the Guests table. The codes works as far as no errors but nothing changes?


    Please Login or Register  to view this content.

    Thanks

    u3rick
    Attached Files Attached Files
    Last edited by u3rick; 01-31-2016 at 02:08 AM.

  81. #81
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Recurring Transaction are a need for the GuestStays Section. Each guest should be billed by the system 10 days before the end of each month within their Stays period (After first payment). I am think a separate table that has the info with code the charges the guest on a certain date each month and is posted to the GuestTrans table!? Am I thinking correctly and do you have any ideas how the code should look??

    Is there any chance that the problem with the GuestStays table showing all those #N/A is a problem with Excel, seems very strange!! (if you re-type the numbers the info shows up...things that make you go hmmm!)

    Also, when I step through the code for posting a New Stay. Why does stepping through the First and Last Names lines take you on a wild ride through the code for FillGuestDetailPage?? Then after those two it doesn't go there anymore.... (More things that make you go hmmm!):roll eyes:

    Thanks, hope you are getting your share of Beers!

    u3rick

  82. #82
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Goodmorning
    Does Standard = Text or General I am thinking text after re-reading your post!
    Standard = General

    In the last version you posted in the SaveEditGuestStaysInfo procedure the GuestStays record is still saved with id's as text rendering the formulas in error and so make the calendar disappear.
    Code should be like this
    Please Login or Register  to view this content.
    Last edited by Tsjallie; 01-31-2016 at 03:47 PM.

  83. #83
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Thanks, I have checked all the Ids in all tables and they are set on General.

    I am assuming that my calculating columns and ones showing amounts can be set on numbers or $ format?

    Thanks

    u3rick

  84. #84
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I am assuming that my calculating columns and ones showing amounts can be set on numbers or $ format?
    Yes they can.
    But remember Excel is very picky on data types. It's important to use them consistently.

  85. #85
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    As to you post #80.
    Just checked and I see things changing. Accessed the formGuestActivity by right clicking the calendar.
    But something is not going right:
    in the sub SaveEditedGuestInfo the values of the form are copied to the Guest table.
    Changing the Guest table however will change ListGuests which is the rowsource of cboGuestListGI.
    So a change in the rowsource means a change in cboGuestListGI which will fire cboGuestListGI_Change.
    And that is reading the table and feeding the data into the userform.
    To solve this I think you best set FormEventsEnabled to false before moving the data from the form to the table.
    And set it beack to true right after that.

  86. #86
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Ok I will pick one number, dollar and date format then stick to them!

    u3rick

  87. #87
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Is it better to leave cells that have no data blank or should you posting something to them? (like N/A or 0.00)

    Calendar is working now I am so Happy!!

    u3rick

  88. #88
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Addition to post #85:
    don't forget to test for FormEventsEnabled in cboGuestListGI_Change, like I just did

  89. #89
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Is it better to leave cells that have no data blank or should you posting something to them? (like N/A or 0.00)
    Just leave them blank. Saves coding.

  90. #90
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The only thing not working is when I use the button (on calendar) to call the Guest Activity form it comes up and the cbobox works but when you choose a guest it errors with the mismatch in post #78. But when you right click on the calendar you can use the cbobox to pick different guests and it works fine!!

    u3rick

  91. #91
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie is sleeping!
    As a rule you may catch me online from 20:30 to 22:30.
    With a time difference of 9 hrs that would be 11:30 to 13:30 for you.
    The rest of the time I'm sleeping

  92. #92
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    By using the GuestInfo button the Guest combox box has 8500001-N as the GuestId.
    That's a string and so not compatible with the GuestId in the Guests table which is a number.

  93. #93
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I was trying to drum up some help so you could have a life other than fixing my many problems!! lool!

    u3rick

  94. #94
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Found that form not forms

    just got this trying to change guest info.

    Screen Shot 2016-01-31 at 12.43.03 PM.png

    I may have missed something got lost going on with all these changes!

    u3rick

  95. #95
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    There's a typo in the variable name: FormsEventsEnabled

  96. #96
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Fixing the Guest combobox when using GuestInfo button can be done be removing the offset from this line in GuestActivity_Initialize:
    Please Login or Register  to view this content.

  97. #97
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    After changing the typo the change went through but only changed the first name the rest had no change?

    u3rick

  98. #98
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Yes, post #97 fixed the problem!

    Thanks for all your help!!

    u3rick

  99. #99
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Checkbox IncludeDeletedGuest is not working yet.
    To make it work include this code in the frmGuestActivity:
    Please Login or Register  to view this content.
    and change this line in GuestActivity_Initialize:
    Please Login or Register  to view this content.
    And you also need to change this line
    Please Login or Register  to view this content.

  100. #100
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    After changing the typo the change went through but only changed the first name the rest had no change?
    Where did you set the FormEventsEnabled variable?
    Do you check on this variable in the change event of the combobox?

  101. #101
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    no where does it go in cboGuestListGI_Change()?

  102. #102
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    no where does it go in cboGuestListGI_Change()?
    Please Login or Register  to view this content.

  103. #103
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Thanks!!

    Nothing is ever easy!!:roll eyes:

    Good Night!!

  104. #104
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Now the information doesn't come up?

    Screen Shot 2016-01-31 at 1.48.26 PM.png

    no sleep yet!
    Last edited by u3rick; 01-31-2016 at 05:49 PM.

  105. #105
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Setting FormEventsEnabled True in GuestActivity_Initialize should do the trick.
    Please Login or Register  to view this content.

  106. #106
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Thanks

    As always you fixed it!!

    That stuff really mixes me up! Its like what comes first the chicken or the egg!!

  107. #107
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I better stop everything is working!iooi!

    u3rick

  108. #108
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I spoke to soon things are not all working as I thought. There is a problem between the tblGuests and the ListGuest table. In the tables tab the ListGuest table does not change with the sort of the guest table. The ListGuest table is what is feeding the cboGuestListGI.

    The pulling up of guest info is inconsistent, whether using the button or the right click. Some times it works and other times it doesn't. I may have mess up the code (hard to believe)! I do know when its right it will work every time!

    So if you could give it a little check under the hood it will make my day!!

    We are getting closer to my first goal...I am seeing a slight light at the end of the tunnel!

    I attached a shinny new version to move forward with. The old version # is the backup, there is nothing different but the number and the guesttrans forms!

    Thanks so much for your support!!

    u3rick
    Attached Files Attached Files
    Last edited by u3rick; 02-01-2016 at 02:53 AM.

  109. #109
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    As I get closer to actually using this program. I was wondering if we should make it automatic when a booking ends that it goes to Deleted_YN = "Y" 1 month after its DateOut. This gives management time to clean and take care of the SecDep and any other things left with the guest. Then it would be moved to deleted with no manual task being necessary. How do you think we should do? With a formula in the column or code?

    Update: Was also thinking of having a tables for both owners and guests with recurring transactions but maybe I actually only need one that can do all?

    Thanks

    u3rick
    Last edited by u3rick; 02-01-2016 at 01:56 PM.

  110. #110
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    As to post #108:
    I can't reproduce the ListGuests not changing when Guests table is sorted, but may you meant filter?.
    For that I experimented with using table ListGuest istead of table Guests for GuestActivity_Initialize().
    For that I added a column GDeleted_YN to the ListGuests table.
    However that raised other problems with the cboGuestListGI_Change() being executed over-and-over. Probably a matter of switching FormEventsEnabled on and off at the right time.
    Need to dig in to that further.

    As to post #109:
    I would semi-automate that. Meaning a procedure you start manually and that reports any records which are candidate for deletion in a list.
    From that list you could then select the record to be actually deleted.
    This would automate the proces while still leaving you in control.

    Not sure what you mean with:
    "Was also thinking of having a tables for both owners and guests with recurring transactions but maybe I actually only need one that can do all?"

  111. #111
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Yes filtering the Deleted_YN sorry

    The main thing is the inconsistency with the guest info button and the right click to get the same form.

    I will need to charge the guest each month for rent and I will need to credit the property owner for the same transaction. To hold recurring trans QB's uses a single table. I was thinking two tables. then at a prescribed time it runs through the table and charges all the listed guests and owners for that month. hope this helps explain.

    u3rick

  112. #112
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The listGuests table does not add new guests. I tried it and nothing was added and that makes it hard to use the Delete -YN and the chkbox!

  113. #113
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    When I right click an existing reservation i get this:

    Screen Shot 2016-02-01 at 12.30.38 PM.png

    Then when I use the button and pick a number I get this:

    Screen Shot 2016-02-01 at 12.32.20 PM.png

    When I hit cancel i get this:

    Screen Shot 2016-02-01 at 12.32.38 PM.png

    Screen Shot 2016-02-01 at 12.32.48 PM.png

    u3rick

  114. #114
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Don't know what's going on but I can't post my findings to post #108 and post #112 with the edited workbook.

    Let's see where we can get

    These are the changes I made:

    Added column GDeleted_YN to ListGuests.

    Uncommented the Worksheet_Change() procedure of sheet Guests to synchronize Guests table with ListGuests.
    This solves the problem of post #112.

    Changes to GuestActivity_Initialize()
    Deleted the first line show te userform (it's already at the bottom of the sub)
    Moved FormEventsEnabled
    Use table ListGuest iso table Guests
    Added a sort on the Guests table to prevent an unconsecutive range being fed to the rowsource
    If Deleted are excluded sort is on Deleted_YN + GuestId
    If Deleted are included sort is on GuestId
    Show frmGuestActivity only when it's not shown already AND when GuestActivity_Initialize() is called from an object (in this case the GuestInfo button)

    Changes to sub HandleRightClick in mdlESReservation
    In the section AllOccupied of the select case (see code below)
    Add call to GuestActivity_Initialize
    Show frmGuestActivity only when it's not shown already AND when GuestActivity_Initialize() is called from an object (in this case the GuestInfo button)
    Knipsel.JPG
    Seems I can't post code Post it as picture.

    Added call to GuestActivity_Initialize in sub chkIncludedDeletedGI_Click

    Removed chkIncludedDeletedGI_Click from Module1 (should be in userform codemodule)



    Yeahhhh!!!!
    Attached Files Attached Files
    Last edited by Tsjallie; 02-01-2016 at 05:09 PM. Reason: Finally got the message complete

  115. #115
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    There in the folder backup version or old version in the dropbox, if thats what you mean. I have nothing open if you need something in Dropbox!

  116. #116
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    There in the folder backup version or old version in the dropbox, if thats what you mean. I have nothing open if you need something in Dropbox!
    It's something in the forum site. It seems posting code sounds the security alarms.
    But the post is finished.

  117. #117
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    To post #113:
    Rng probably contains a non-consecutive range.
    The sorts I introduced solve this.

  118. #118
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Could never make my version work but updated yours to mine went pretty well!

    I have made a new sort for the to fill the StayActivity.cboGuestIdSA. I adjust what you had used in the other cbo in guest activity. it seems to work after many hours and problems that I won't go into due to the amount of pages I would need to type!lool! It does have a mismatch error that happens every other time so I know I have a number format problem but sure can't find it! This sort looks for Deleted_YN that have Y because they don't have a current booking and don't want two booking to same person in Extend stays! So I have attached please make a booking and checkout!

    This brings me to a question. I have been thinking about getting rid of the Active columns in all tables and rely totally on the Deleted_YN (This is how Qb's works)?

    With that in mind I will need to make ways that the Deleted_YN can be turned back to "N" for all instances that should allow for total flexibility.

    In the form GuestAcivity I want to filter the two list boxes by the Deleted_YN and I have added chkBoxes to the from to allow including deleted history. I need a little help with the filtering code!:roll eyes:

    Thanks

    u3rick
    Attached Files Attached Files
    Last edited by u3rick; 02-02-2016 at 04:17 PM.

  119. #119
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    It does have a mismatch error that happens every other time
    In the Userform_Initialize() procedure of frmStayActivity remove the line
    Please Login or Register  to view this content.
    This prevents that in the crashing procedure a not-yet-existing GuestId will be looked up giving the error.

    The fact that it crashes every other time is because - after the crashing and ending the program - the FormEventsEnabled is not set back to True.
    So next time you run it FormEventsEnabled is still False and just does not get to the line crashing the program.
    Still a happy vba-programmer?

  120. #120
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Still a happy vba-programmer?
    Wow!!, did you call me a VBA programmer?

    When I used your version yesterday it came with a couple Macro's in Module 2 are they something I need to keep, also in that area, should I combine Module 1 and 3 into one module?

    Thanks!!

    u3rick

  121. #121
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    When I used your version yesterday it came with a couple Macro's in Module 2 are they something I need to keep, also in that area, should I combine Module 1 and 3 into one module?
    You can drop Module 2.
    Module 1 and 3 can be combined. You could also move these to mdlESReservation if you like.

  122. #122
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    In the form GuestAcivity I want to filter the two list boxes by the Deleted_YN and I have added chkBoxes to the from to allow including deleted history. I need a little help with the filtering code!
    In order to include/excude Deleted Stays History you need to make the following changes:

    Add a sub CheckBox1_Click to frmGuestActivity calling cboGuestListGI_Change
    Please Login or Register  to view this content.
    Add or change some lines (marked red) to the sub cboGuestListGI:
    Please Login or Register  to view this content.
    Same goes for including/excluding Deleted Payment History
    Last edited by Tsjallie; 02-03-2016 at 06:14 AM.

  123. #123
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Got it!

    Wish I could write code like that and as fast as you do!lool! Seems that the firing order and the format of the Id's are not clear enough to me. As well as the filtering process.

    Other than that i got!!!

    Thanks

    u3rick

  124. #124
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    So the deleted _YN program is moving forward and it brings up a question that I have. Won't I need some code added so when I delete and Owner, Property, PropertyOwner relationship or Guest or GuestStay that it looks up all related transactions and changes all to "Y" and back to "N" if requested?

    It gets to hard for me if you consider Guest can change units and units can change owners!

    Update: Also what about transactions that just needed deleted and not brought back for any reason ?? thing that make me go hmmmmm!

    u3rick
    Last edited by u3rick; 02-03-2016 at 04:29 PM.

  125. #125
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I was looking at the VV System Integrated.pdf you made. It is so cool!! I want to make some adjustments and phasing as i see it (Not that you didn't nail it)! So, I was wondering if you would mind supplying me the file? So I don't need to start from the beginning!

    Thanks

    u3rick

  126. #126
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    So the deleted _YN program is moving forward and it brings up a question that I have. Won't I need some code added so when I delete and Owner, Property, PropertyOwner relationship or Guest or GuestStay that it looks up all related transactions and changes all to "Y" and back to "N" if requested?
    Yes, you need that in order to maintain database integrity. I'm sure there are already orphaned records.

    Also what about transactions that just needed deleted and not brought back for ant reason ??
    Stick to 1 system: either virtual deletion (with deleted_yn) or fysical (permanent) deletion.
    Matter of weighing pros and cons. I think virtual deletion has a better pro/con balance than the fysical deletion.

  127. #127
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I agree just was thinking out loud!

    u3rick

  128. #128
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    So, I was wondering if you would mind supplying me the file?
    No problem, but it's a Visio file. I believe you couldn't handle these files and so I posted it as pdf.
    Will upload it to the dropbox anyhow.

  129. #129
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Well all the records in there are orphans at this point!LooL! Soon, I will build data for the tables from Vacationville and guess nows s the time to add that code? Hope you will provide a temple of it. As I see it being used in many places in each form? BTW; Just purchase Visio helping the local economy !!

    Thanks

    u3rick

  130. #130
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Well all the records in there are orphans at this point!LooL! Soon, I will build data for the tables from Vacationville and guess nows s the time to add that code? Hope you will provide a temple of it. As I see it being used in many places in each form? BTW; Just purchase Visio helping the local economy !!

    I keep getting this message should we be concerned????

    Screen Shot 2016-02-03 at 2.46.07 PM.png

    Thanks

    u3rick

  131. #131
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I ask the below question in an earlier post, but a lot was going on with code changes and I didn't see an answer. I wanted to get your thoughts before I deleted them!

    This brings me to a question. I have been thinking about getting rid of the Active columns in all tables and relying totally on the Deleted_YN (This is how Qb's works)?
    Thanks

    u3rick

  132. #132
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I keep getting this message should we be concerned????
    The forum is safe.
    The message you're getting is about an other site.
    You can check if a site is safe on http://www.google.com/transparencyre...ing/diagnostic

  133. #133
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    This brings me to a question. I have been thinking about getting rid of the Active columns in all tables and relying totally on the Deleted_YN (This is how Qb's works)?
    Oops! Overlooked that one.
    I think indeed the Active columns are obsolete since the introduction of deleted_yn which (afaik) has the same function/meaning.

  134. #134
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Soon, I will build data for the tables from Vacationville and guess nows s the time to add that code? Hope you will provide a temple of it.
    Yes, I think it's important to have that up-and-running before you go for real operational data.
    Will figure something out to get you going.

  135. #135
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    in regards to post #132; Since I have been getting that message all my emails from the forum goto junk mail! Have you had any problems?

    Thanks

    u3rick

  136. #136
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I'm not getting any message when I visit the forum.
    I have notifications switched off, so I'm not getting any mail.
    Will switch it back on and see if there's any problem. Don't expect so.
    Do you use Chrome with Safesearch?
    The message you posted was not about the forum. Does it only appear when you go the forum or also when you visit other sites?
    What virus software are you using?

    If the message keeps coming I suggest you leave a message on the forum's facebook.

    Update: there seems to be something going on. See this thread.
    Last edited by Tsjallie; 02-04-2016 at 04:15 PM.

  137. #137
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Not so good I guess, but only effecting my emails at this point!

    Thanks

    u3rick

  138. #138
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I think there's more going on. See this article.
    I suggest that for the time being (say next 2 weeks) we avoid the forum and continue thru mail and dropbox.
    May be I'm overreacting, but better safe than sorry.

  139. #139
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Sounds good to me! I have deleted all the Active columns and that was not simple but its all working and I think I did pretty good I have a little more stuff to do and I will put in dropbox and you can give me a grade LOOL!!

    Talk to you in emails for awhile!

    u3rick

  140. #140
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Rick,
    can you check if you're still getting security messages when visiting the forum?
    And are the email notifications still going to junk mail?

  141. #141
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I hope you guys accept my 'butting-in'. I don't have answers for your questions but I do have a tip and something I consider required when you change Excel's window state.
    That is to restore averything before the file is closed.
    You invoke the Application.FullScreen = tru and if you don't reset it on close it remains like that when you open a new session of Excel

    If you add the the following code to ThisWorkBook it takes care of a relatively painless Restore

    Please Login or Register  to view this content.
    If it asks to save the file (again) then you can add a check before if the workbook was already saved to avoid the question of saving it again.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  142. #142
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The email that came with your last post went to junk. I am not sure that Google message I posted is truly coming from the forum as it says nothing about the forum when I track it? But I am not good at all that Admin stuff!!

    If you think its good I will go with your feeling on this.

    u3rick

  143. #143
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Keebellah

    Thanks for your comments, and I am sure Tsjallie will respond as he did that part of the plan!

    Feel free to help at any time!

    u3rick

  144. #144
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    The email that came with your last post went to junk.
    As long as the notifications end up in junk mail I think the battle is not over yet and we better keep using mail/dropbox.
    I am sure Tsjallie will respond as he did that part of the plan!
    There are lots of things like this we need to address some time.
    And indeed it's a matter of decent programming to leave Excel in the state you entered it.
    That's one difference between Excel and a bottle of beer

  145. #145
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The xy portion in the property column is not in proper order on your update?

    Screen Shot 2016-02-26 at 7.32.15 AM.png

    Thanks

    u3rick

  146. #146
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    FillPropertyOwnerDetailsPage is getting the PropertyOwner’s data from tblPropertyOwners where it should actually get these from tblOwners
    This should be named FillConnectedDetailsPage and the data is only available in the tblPropertyOwners, I think!

    Thanks

    u3rick

  147. #147
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    This should be named FillConnectedDetailsPage and the data is only available in the tblPropertyOwners, I think!
    You're right again!

  148. #148
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    How many things can I mess up if I change the name?

    u3rick

  149. #149
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    As long as you use find-and-replace with the whole project as scope it'll be ok.

  150. #150
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Ok, I am on it!

    u3rick

  151. #151
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Changed the name as planned and got some organizing started still lots to finish but its improving!

    The new v.1.0.4 is in the dropbox

    I have a Granddaughter having her 1st birthday and party Today, so won't be home until you are in bed!

    Thanks

    u3rick

  152. #152
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I have a Granddaughter having her 1st birthday and party Today
    That's great! Congratulations

  153. #153
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I changed the Deleted_YN to Active_YN late last night. I thought it went well, but I did pick up an error and may not get it fixed before I leave this morning. You may have to locate it for me. I have not changed all the tables yet. The ones I did change used ODeleted_YN, PDeleted_YN, and PODeleted_YN. Still need to change Deleted_YN, GDeleted_YN GTDeleted_YN and maybe a couple in tables that I didn't check.

    I still have a couple hours to check it out but so far no luck.

    Also, the OwnersPayable Table is messed up. I think it needs the same changes that were made to the sheet PropertyOwners as thats where its code was copied from.

    Hope to get these fixed before I leave! In case i don't this is your warning!

    Thanks

    u3rick

  154. #154
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Everything is fixed. Its yours till I get home!

    I will finish the Deleted_YN switch to Active_YN in the remaining tables tonight then we will be done with that. Do you need to change anything in your demo's?

    Have a great evening!

    Thanks!!

    u3rick

  155. #155
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I was fixing the Company name problem in the OwnerPayablesList (Fixed) that when the Calendar or the OwnersPayable sort the screen flashes. I am not sure where to turn the Application.ScreenUpdating False?


    Thanks

    u3rick

  156. #156
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Uploaded an updated version v.1.0.3b to the dropbox.
    Focussed on the connections in frmManagePropertiesAndOwners.
    Merged procedures for adding, editing and deleting connections into one procedure which creates an audit trail. See PropertyOwners sheet for property 3000037.

    Need to adjust GetUnownedProperties yet. Selection is not correct.
    Also need to adjust adding and deleting PropertyOwners for the audit trail.
    I think I need two more sessions tomorrow night and Monday night for these adjustments.

    After that I will need to merge v1.0.3b with v.1.0.4 for the changes you made (Deleted_YN = Active_YN).

    All in all I think we can have the frmManagePropertiesAndOwners up and running before I'm off for holiday

  157. #157
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I will add your changes to my v1.0.4 and finish the (Deleted_YN = Active_YN) changes in the other forms.

    Thats right your big trip is coming up!

    Might need a little guidance in making up reports and printing them, then I can use this thing!

    Thanks

    u3rick

  158. #158
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Changed the moment when application.enableevents is switched on/off to get the table sorted with all data present.
    Where and how do you do this?

    u3rick

  159. #159
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Didn't know if you notice but zz JL 806 should not be on the Calendar and xy JL 1001 has no formulas in its calendar section!?

    Screen Shot 2016-02-27 at 4.31.54 PM.png

    When I copy the new code to v1.0.4 It also lost the bottom line (xy JL 1001) calendar formulas although I didn't gain ZZ JL 806.

    u3rick
    Last edited by u3rick; 02-27-2016 at 08:39 PM.

  160. #160
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Thumbs up Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I've been following this but it's a pity I no longer see the files since you're sharing them in dropbox.
    Looks good though.

  161. #161
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Keebellah

    Sorry, we were having some problems with the site so we lost a few weeks being here! Thing are better now so we are back!

    Here is the latest version I had to kill some live data that made its way on in some testing!
    Attached Files Attached Files

  162. #162
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Hi thanks u3rick,

    I'll look at it too just for kicks and see if I can offer ideas.

  163. #163
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Rick,
    if you've just been changing Deleted_YN columns to Active_YN columns you'd better take v1.0.3b as a basis, because that has a lot more changes in it.
    Also I would suggest not to use Active_YN but Archived_YN instead.
    That's because there are already columns with Active in it and that would be confusing.
    Also Archived_YN would be more to the point as for it's usage.

    Need to take you through this audit trail method. It's very important to have a thorough understanding of how it works and so how to read it.

  164. #164
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I can do that without to much trouble the hard part was making sure the Y and N were opposite everywhere, which I think is correct in v1.0.4. I have been changing everything every nite that you have listed as changes in your versions to v1.0.4. If you did changes that didn't get listed they would not be in it? (I actually copied your code from v1.0.3a, then copied found changes in v.1.0.3b.)

    I have done a fair amount of organizing in the code page on frmManagePropertiesAndOwners v.1.0.4. It would be nice to keep that work, so maybe you could take a quick look ay v1.0.4. It works the same and as far as I can tell all the forms work and fill.

    The only problem now is the button from OwnersPayable sheet errors when it a unconnected owner. I think the code you used in finding unowned units will fix this error and that is this mornings job.:roll eyes:

    Thanks

    u3rick

  165. #165
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Version v.1.0.5 is the same as v1.0.4 except it uses Archived_YN. Its in the Dropbox.

    Have great evening!

    u3rick
    Attached Files Attached Files

  166. #166
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Guess something went wrong with uploading v1.0.5 to Dropbox. Getting all kinds of errors.
    v1.0.5 you uploaded here works fine.
    I will continue with that version.

  167. #167
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Use the one in Dropbox I am out of it. The first one Attached on the forum didn't get all the Archived_YN changed correctly! have had a chance to update !

    u3rick

  168. #168
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    This one workes fine.

  169. #169
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I have every thing working with the Button in OwnerPayablesList, but cannot figure out how to get the unconnected owners to show, so I can connect them. It was getting an error because they(unconnected owners) don't have a property so no PropId!!??
    Last edited by u3rick; 02-28-2016 at 03:40 PM.

  170. #170
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I have an other project i need to complete, so while you are using the file, I will do what I should be doing! Instead of driving myself crazy on this project!!

  171. #171
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Rick,
    in the sheet PropertyOwners the column POActive_YN has this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This formula sets POActive_YN to "N" when POArchived_YN is "N". I would expect that to be the other way round.
    And what's the rule for PropertyProgram? Should POActive_YN be "N" when PropertyProgram is "SV"?

  172. #172
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Well if a property is SV (Short Vacation) it’s not to show on the calendar

    "=IF(OR(([@PropertyProgram]="SV"),[@[POArchived_YN]]="N"),"N","Y”)”

    When it was Deleted _YN it was “Y” then Active_YN was it was “N" so if Its Archived_YN we are back to “N”?

    And if thats all true, I will need to reverse all the Letters in the Program. I thought they would be the same with little thought!

    Sorry

    u3rick

  173. #173
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Well if a property is SV (Short Vacation) it’s not to show on the calendar
    Thought it would something that. Adjusted the formula.

    Also checked and adjusted GetOwnedProperties and GetUnownedProperties to make the right selections.
    Also changed some PODeleted_YN's into POArchived_YN's, but only two I believe .

    Yet to do:
    frmManagePropertiesAndOwners:
    - Add / Delete Owners
    General:
    - adjusting all procedures adding, editing or deleting record to create a proper audit trail

    Will draw a document for explaining the audit trail.

    Edited v.1.0.5 is uploaded.

  174. #174
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    When it was Deleted _YN it was “Y” then Active_YN was it was “N" so if Its Archived_YN we are back to “N”?
    Deleted_YN is equal to Archive_YN. Active_TN is the opposite.
    So Deleted_YN = "N" corresponds to POArchived_YN = "N" corrsponds to Active_YN ="Y"

  175. #175
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Guess I have a little work to do, I will go to it later!!

  176. #176
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Guess I have a little work to do, I will go to it later!!
    I believe so, but be aware of if statements containing NOT: "N" = NOT "Y"

  177. #177
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Thought I had it everything working with the Archived_YN, all the "Y" and "N" in the right context. Then I made a couple other changes and was testing them, when this error came up and it blocks all use of frmManagePropertiesAndOwners?

    Screen Shot 2016-02-28 at 9.12.56 PM.png

    Screen Shot 2016-02-28 at 9.13.28 PM.png

    Went over everything as I had changed the Table OwnerList by adding a sorted to the PropertyOwners table, but I set it back to what it was. No help though!

    I used the code from GetUnownedProperties for a templet and got it to fill a comboBox with unconnected owners, but that is in a different form (frmOwnerChoice), so I can't see how that could have cause it.

    I sure must of missed something up!! Hope you can find it quickly, I just don't understand the filtering that well!

    I was happy for awhile because I made the combobox work and it only took a couple hours.

    As usual a few steps forward and then a few backwards!!

    Thanks

    u3rick
    Attached Files Attached Files
    Last edited by u3rick; 02-29-2016 at 01:54 AM.

  178. #178
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    GetOwnedProperties is called recursively. That's too much for it.
    I'm making up a list of procedures called showing the sequence and where it goes wrong (and bezirk).
    Will try to post that tonight.

  179. #179
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Ok, here's what going on and leading to crashed the program.
    Called procedures are indented. You can reconstruct this by stepping thru the code with F8.

    Worksheet Tsjallie_Calendar
    - Right Property column
    - Worksheet_BeforeRightClick
    - This will call the HandleRightClick procedure
    mdlESReservation:
    - HandleRightClick
    - .cboPropIdEU = PropertyId 'This will trigger cboPropIdEU_Change()
    frmManagePropertiesAndOwners (FormEventsEnabled = True!):
    - cboPropIdEU_Change
    - FillPropertyDetailsPage (FormEventsEnabled = False)
    - txtUnitCodeEU_Change
    - txtStrAdd1EU_Change
    - txtRentEU_Change (called twice because it changes its own object)
    - txtSecDepEU_Change (called twice because it changes its own object)
    - GetPropertyOwners (FormEventsEnabled = False)
    - FillOwnerDetailsPage (FormEventsEnabled = False)
    - FillPropertyAvailabilityPage (FormEventsEnabled = False)
    - GetOwnedProperties (FormEventsEnabled = False)
    - lstOwnedPropertiesNO_Click (called by Me.lstOwnedPropertiesNO.Selected(i) = True)
    - FillConnectionDetailsBox (FormEventsEnabled = False)
    - cboStatusNO_Change (doesn't check for FormEvensEnabled)
    - txtEVMgmtFee_Change
    - txtSVMgmtFee_Change
    - txtMFAmtNO_Change
    - cboOwnerIdNO_Change (called by .DataBodyRange.AutoFilter)
    - FillOwnerDetailsPage
    - FillPropertyDetailsPage
    - Same calls as from FillPropertyDetailsPage
    - GetOwnedProperties
    - This is the recursive call where setting autofilter crashes the program!!


    So this the problem.
    Not sure how to solve it properly yet. Need to sleep on that I think

  180. #180
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    What about dividing up the form to four forms? Or is that just what it takes to setup that part of the form?
    Last edited by u3rick; 03-01-2016 at 09:54 AM.

  181. #181
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Can you check if this works (in a new version!). Will explain later. It's about logic.
    Notice the lines commented out.
    Please Login or Register  to view this content.

  182. #182
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    The button that calls from the cboOwnerIdNO_Change() side worked but was missing the unit detail and Property Availability as well as the connection detail.

    The Private Sub cboPropIdEU_Change() error at the following:

    Screen Shot 2016-02-29 at 2.57.28 PM.png

    Screen Shot 2016-02-29 at 2.58.37 PM.png

    Thanks!

    u3rick

  183. #183
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tisjalle

    Here is what I dreamed up in my sleep!

    Screen Shot 2016-02-29 at 11.25.41 PM.png

    u3rick

  184. #184
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Hi guys, I''ve been following this and it really looks awesome.
    I decided to take a look but get the error that the librray is missing:

    Microsoft Windows Common Controls-2 6.0 (SP6)

    I've checked the Microsoft site and seen it but I wonder if installing it (it asks to update), is it specific VB6 or will it conflict.

    I did look it up for Windows 10 but am hesitant to install it.

  185. #185
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I would like to add the following comment.
    I write Excel custom applications too and one thing I always do is make sure that when the session is ended the user's lay-out is restored before closing or at least set Excel back to its initial state.
    You application makes use of a fullscreen (xlMazimized), no header rows, formula bar, status bar, etc. etc.

    The next time you open plain Excel, not you application it is still in that state.
    Amongst many other macros in my Personal.Xlb I already had a with shortcut key combination to reset Excel to my default lay-out.
    Suggest this minor addition would be a welcome surprise for the Excel user.

  186. #186
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Keebellah

    I am not sure on that, I am not a Windows guy all my stuff runs on a Mac using Parallels. I just cross my fingers every day hoping no Windows issues come up!. I am running Windows 10 and have not seen that error to date.

    Not much help sorry!

    Good luck

    u3rick

  187. #187
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I would like to add the following comment.
    I write Excel custom applications too and one thing I always do is make sure that when the session is ended the user's lay-out is restored before closing or at least set Excel back to its initial state.
    You application makes use of a fullscreen (xlMazimized), no header rows, formula bar, status bar, etc. etc.

    The next time you open plain Excel, not you application it is still in that state.
    Amongst many other macros in my Personal.Xlb I already had a with shortcut key combination to reset Excel to my default lay-out.
    Suggest this minor addition would be a welcome surprise for the Excel user.

    BTW I solved the MISSING ocx control, just deleted the reference and all works, except for three non declared/ non existing textboxes in three forms.

  188. #188
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Keebellah

    Thanks for your interest in our project and for explaining why my excel always opens to that full screen. I know you commented on this before and I think it's on Tsjallie's list of many loose ends that need attention.

    Thanks

    u3rick

  189. #189
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    @Keebellah
    please understand that the workbook is still a prototype, meaning that our main focus is on getting clear what it should do and how it should do it.
    Also means that "under the hood" it pretty much looks like a battlefield.
    So when finished we actually have a working design. From there (in my opinion, but not yet sure Rick is with me on that) it should be constructed from the bottom up.
    So, apart from restoring the original state of Excel, there are quite some things (e.g. proper error trapping being one of them) to be addressed yet.

    As for your intentions:
    do you intend to just issue some suggestions from time to time or participate in this project? In both cases you're most welcome as far as I'm concerned.
    However in the latter case I think we should make arrangements on who's working on what and how to deal with versions.
    Even with just the two of us we sometimes are in each others way and need to stay alert to keep versions synchronized.

  190. #190
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Go it to work again.
    In cboPropIdEU_Change:
    - Disabled calling FillConnectionDetailsBox.
    Thisprocedure is called when selecting an item in lstOwnedPropertiesNO
    In GetOwnedProperties:
    - Disabled selecting 1st item of lstOwnedPropertiesNO
    In HandleRightClick:
    - Added selecting 1st item of lstOwnedPropertiesNO just before the form frmManagePropertiesAndOwners is shown.
    This makes sure that FillConectionDetailsBox is called when all previous procedures are finished.
    In FillConnectionDetailsBox:
    - Changed the lookup key to look for unarchived records: CombKey = OwnerId & "-" & .Value & "-N"
    - Same for ownedproperties and unownedproperties
    Calling the the frmManagePropertiesAndOwners is terribly slow.
    I think that's because multiple procedures are called multiple times.
    Need to look into that sequence yet.

  191. #191
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Great, I will take a look this evening!

    Thanks couldn't do this with you!!

    u3rick

  192. #192
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    I noticed when I am stepping through the Subs that they go to places that in my mind they don't need to go, is that normal?

    I wish I could remember a exact incident but I can't!

    But that must make it slower?

    u3rick

  193. #193
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    It's late here and I have finished a slightly different approach to the program. Its still not complete as far as copying records to the tables with the Connection Center, but I deleted a lot of code in the frmManageProperties&Owners. As well as changing its name to frmManageProperties. Check it out. It speeds things up a lot and does everything I need! I am sure you will see many things that can be changed to improve it more. So, I hope you will not need to spend any more hours trying to figure out why it crashes.

    See what you think its in the Dropbox and I will attach a copy here in case I am using it!
    Attached Files Attached Files

  194. #194
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I noticed when I am stepping through the Subs that they go to places that in my mind they don't need to go, is that normal?
    That's what I mean when I call it a battlefield. And I think it's the mean reason why it has become so slow.
    Over time the flow is being poluted by adding things time-over-time.

  195. #195
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Lets say I have a owner and unit leave the program. Then in a year he likes what he had and comes back.

    So, I would need to re-enter him. To do that I would need to add a button that says view Archived Records in frmManageProperties, OwnerInfo, frmConnectionActivity (v1.0.6 Names). Then copy from them a new records with Archived_YN = "N" for each. Is this the correct way to handle this? I guess you could always just enter it again!

    Thanks

    u3rick

  196. #196
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    I must say this v1.0.6 pleases me. Specially when I see how much of vba you already picked up.
    Is this the layout (look) you're gonna use throughout the workbook or will you continue using these complete different looks?

    Of course there's always something to improve In this case I would pay specific attention to the logic (intuitivity) in operating the screens.

  197. #197
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Yes, the proper way to re-enter a new owner (or property or guest) is to dig up the latest record.
    And then copy the data to a new record with Archived_YN = "N".

  198. #198
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Thanks, it is getting easier with your templets and all the code you have provided.

    I added your AddEditDeleteOwnerPropRelationship Sub and connect my first owner and property. It all worked well except it leaves the Calendar like this.

    Screen Shot 2016-03-02 at 8.30.04 AM.png

    I have check but don't see the problem?

    I will checkout of the Dropbox version so you can look at the same program.

    Thanks

    u3rick
    Attached Files Attached Files

  199. #199
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    Tsjallie

    Is this the layout (look) you're gonna use throughout the workbook or will you continue using these complete different looks?
    I think so, as I rebuild forms they will go that direction, but will leave things that are working alone for now! The look is similar to how QB's looks although there SQL front end is bowser based. But they will never know. Since the end goal is to make this work with QB's I thought a similar look would be good. Couldn't copy Lodgix as he will see it, because his customers will be using it and he needs to help us automate.(If I ever get that far)

    Thanks

    u3rick

  200. #200
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Designing an Excel application for simultaneous use and for optimal maintainability

    wrong file attached in one of the last posts
    Attached Files Attached Files

+ Reply to Thread
Page 1 of 3 1 2 3

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Solver - Optimal Strategy
    By adodson in forum Excel General
    Replies: 7
    Last Post: 02-22-2015, 06:15 PM
  2. [SOLVED] simultaneous non linear excel
    By ruecel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2015, 01:28 PM
  3. Designing a Survey in Excel & VB
    By willowonyx in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-29-2011, 01:57 PM
  4. Designing a KPI Reporting app within Excel
    By broro183 in forum Excel General
    Replies: 3
    Last Post: 06-20-2011, 05:24 AM
  5. Excel files maintainability
    By cycleinmars in forum Excel General
    Replies: 2
    Last Post: 04-21-2010, 08:02 PM
  6. [SOLVED] Simultaneous Excel backgrounds....
    By Colin Hayes in forum Excel General
    Replies: 2
    Last Post: 06-01-2005, 05:05 PM
  7. [SOLVED] Excel Workbook - Simultaneous Use
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1