Designing an Excel application for simultaneous use and for optimal maintainability

[Tsjallie]

Hold on with this new version it works so you already fixed it That is fast!!

I know nothing

[u3rick]

So, I just have a list of all the Txt boxes and if they change it enables the Save. Kinda like formatting?

[u3rick]

Btw I erased both recurring tables and related code!

[u3rick]

Tsjallie

You can have the Save-button disabled by default and if a change event is fired than you can enable the button in that event procedure.

When I do this, the first thing that I do is click on in the cbobox to bring up a guests info. That fires all the change procedures in all the txt boxes and the save button comes up, But I don't want it up yet!

I want it to come up when I change the spelling of the first name in the txtFName box and tab out of it, thats when the Save button should appear!(But of course it is already there) Guess I need more help!!

Thanks

[u3rick]

Tsjallie

I have grandpa duties today, so won't be back and working until past your bed time. Could you spend sometime on the basic code for a module that will make the monthly auto posting to the GuestTrans Table and OwnerTransLedger. Need that part next I think and after some attempts I admit its past my skill level!

For guests and members that are following the progress of this project I have attached the latest version.

[Tsjallie]

So, I just have a list of all the Txt boxes and if they change it enables the Save. Kinda like formatting?

Example for frmDateChange() DateOut textbox

Please Login or Register  to view this content.

You would need to have such a sub for each textbox which can change.

Some goes for the Guest Activity Center and all other forms where you can change records.

[Tsjallie]

I want it to come up when I change the spelling of the first name in the txtFName box and tab out of it, thats when the Save button should appear!

For that you need to use the txtFNameGI_AfterUpdate() procedure. This will fire when leaving the textbox and when it's changed.
txtFNameGI_Change() will fire everytime the textbox is changed. Quite annyoning.

That fires all the change procedures in all the txt boxes and the save button comes up

No it doesn't.
BTW, I don't see why the save-button and the delete-button should overlap. I think they should be both available.

[Tsjallie]

Could you spend sometime on the basic code for a module that will make the monthly auto posting to the GuestTrans Table and OwnerTransLedger.

Can you give me more details on what you want to achieve with that module. Obviously you are further in your thinking about that part than I am.

May be something like this:
Trans.JPG

[u3rick]

Tsjallie

Well all stays are billed in the system on the same day (5 days before the end of the month)
Owner credits and Management Commissions are invoiced or credited on the last day of each month.
The Owner Mgmt. fee is charge to all active owners if it is activated with an amount on the last day of each month. General Vacationville gives the owners the first 6months with no Owner Mgmt. fee.

The first months rent and any prorates are done at the time of the booking so it does not need to be auto.

Since the rent is due and accounted for on the last day of the month for next months rent, there is no chg on that last month. (payment due march 31st is for Aprils rent)

That pretty much covers the auto stuff for ES.

I updated this info stating what needs to be posted and when. Now how is where I have a problem!

Please Login or Register  to view this content.

Not sure what that was! Can't see anything?

Thanks

[Tsjallie]

Not sure what that was! Can't see anything?

That was I suggestion for things to record in transaction tables in case of different events (not complete).
Used the post you quoted for that.
It's an jpg-image. Strange that you can't see it.
Will post the sheet in the dropbox.

[u3rick]

Tsjallie

Noticed that the

Please Login or Register  to view this content.

in the tblGuestStays causes "#VALUE!" do we need to do something to fix that?

After looking at your TransactionProcessing, I feel the need to change the OwnerTransLedger to be more like the GuestTrans. Now that we are making most posting auto there is less needed for many of the invoice types. I made some changes to your chart and added "Column". The Column names for OwnerTransLedger don't represent changes that I may make in that table.

Thanks!

[u3rick]

Tsjallie

If I understand a past post correctly you want the "Edit" used only when it brings up data to be edited, and the button used to record that data needs to be "Save". Also the showing of the "Save" should only happen when an actual change takes place using AfterUpdate. I am in the process of changing this through the code.

Thanks!

[u3rick]

Tsjallie

Screen Shot 2016-04-18 at 10.38.49 AM.png

Found these "#NA" in the GuestTrans table they have something to do with the second reservation and the first name. Not sure why it doesn't show up on Last name. Could you make a suggestion here?

Thanks

[Tsjallie]

Noticed that the

Please Login or Register  to view this content.

in the tblGuestStays causes "#VALUE!" do we need to do something to fix that?

You are inserting a formula into a cell, but actually you insert a string. In that case you need this syntax:

Please Login or Register  to view this content.

The column "StayInMonths" uses the value in column DateOut for a calculation. But you can't do calculations with strings.
That give the "VALUE" error.

[Tsjallie]

If I understand a past post correctly you want the "Edit" used only when it brings up data to be edited, and the button used to record that data needs to be "Save". Also the showing of the "Save" should only happen when an actual change takes place using AfterUpdate. I am in the process of changing this through the code.

If you can start editing immediately when a data form is presented then you don't need an "Edit"-button at all.
You just need the "Save"-button to be enabled when something is changed.

[Tsjallie]

Please Login or Register  to view this content.

This formula looks up the GuestId in the table GuestStays, but the GuestIds 8500013 and 8500014 are not in that table. That gives the "N/A"-error.
BTW, why isn't the GuestId and GFirstname looked up in the table Guests?

[u3rick]

BTW, why isn't the GuestId and GFirstname looked up in the table Guests?

Not sure I think I used the table I used because the Archived_YN was already looked at?? don't really remember!

[Tsjallie]

I saved the changes from posts #414 and #416.

[u3rick]

Also why does the last name work?

[u3rick]

Tsjallie

I will be flying to Evansville IN to work on the race boat tomorrow night. I am taking my computer and laptop and hope to still get a couple hours a day to work on the program! I will be there 5 to 6 weeks to complete the repairs. I will be buying a new monitor when I get there and setup may take a couple days, but I will have my laptop during that time!

You may get a couple more beers a week during this slow down!!

[u3rick]

BTW, I don't see why the save-button and the delete-button should overlap. I think they should be both available.

So, I should do away with the checkbox and just have the delete button available? Also still need to make sure there are no activity stays or moneys owed before allowing deleting of certain things? (So many things to think about!!)

[u3rick]

May have a couple beers early today!!

[Tsjallie]

Also why does the last name work?

I changed the formula to lookup GuestId and GFname in tblGuests

[Tsjallie]

So, I should do away with the checkbox and just have the delete button available?

Yes, don't use checkboxes as command buttons

[Tsjallie]

Also still need to make sure there are no activity stays or moneys owed before allowing deleting of certain things?

Yes and that's gonna be a problem, because things are pretty much mixed up by now. So it will be hard and labourious to make the richt changes without causing other things to fault.

[Tsjallie]

I'm working on a to-be-overview of the system. If you like it or not
Less forms, less tables, less code.
What data comes from Quickbooks?

[Tsjallie]

I will be buying a new monitor when I get there and setup may take a couple days ...

May the force be with you
I'll start having the extra beers right now.

[u3rick]

I really have not planned on any info coming from QB's. That doesn't mean we can't just always looked at I going to QB's!

[Tsjallie]

I really have not planned on any info coming from QB's.

Ok, so incoming and outgoing bank transfers are booked by yourself into the ES Program?

[u3rick]

If you like it or not Less forms, less tables, less code.

Sounds good to me!!

Like Invoices for both Guest and Owner only need one form for all, with a few overlapped boxes! But I didn't know how to do that when I made them!

[u3rick]

Yes, deposits and checks will be manually accept or written and then auto posted to QB's and printed. The only exception will be the end of month statements. They need to have any balance owed the owner auto generated and auto posted to QB's and Checks printed (Part of completing the end of period statement process for Owners).

[u3rick]

Tsjallie

Found the FName problem it had the wrong GuestId in GuestTrans GuestId column, not sure how it got there? That must of came from when the booking was made! May have to check that out!

[u3rick]

Tsjallie

I am working on the frmGuestActivity and I think I have got the buttons working correctly. But as you pointed out the combobox that is used to pick guest doesn't have the guests with two bookings included. I am thinking the only way to get them included is to match them with the Unique ID after filtering the "Y" out? Is this the best way? Or as you would put it filtering for "N"!

Thanks

[u3rick]

Tsjallie

What is the easiest way to get the tabs to show? I thought you made did something to show them and they did the first time or two I opened the 1.1 version but not anymore! Hmmm!

Thanks

[u3rick]

Tsjallie

This is the code that goes with post#433

Please Login or Register  to view this content.

Thanks

[u3rick]

Tsjallie

Told you the flood gates were opening!

Do we need to keep modules mdlChangeLog?

Thanks

[Tsjallie]

What is the easiest way to get the tabs to show?

I thought this was set on an Excel-level, but apparently is stored in the workbook.
I corrected that.

[Tsjallie]

No, that one can go.

[Tsjallie]

In the first place you need a listbox to show multiple stays.

[Tsjallie]

Please Login or Register  to view this content.

only gets the first record for a GuestId from the GuestTransTable.

I would suggest the following procedure for filling the Guest Activity Center when a GuestId is selected:
1. Collect the Guest data (no need to change this)
2. Collect all the Stays for the selected Guest (this will need a listbox)
3. When a stay is selected in the Stays listbox, collect all the transactions for the Stay (this will need a listbox)
4. Generate recurring (prospected) transactions and add this to the Transactions listbox

[u3rick]

Tsjallie

Not sure why I can't give you reputations for the amount you deserve, but I guess I have to give some away to people not helping to be able to give you more!Lool!

hmmm!

[u3rick]

So the form would look a little like the Connection Center form, with its larger list boxes?

Also that combobox with the guestId needs to have Archived_YN with an "N" add to it, what is best method?

Please Login or Register  to view this content.

Thanks

[u3rick]

Here is the latest version we are working with if interested!

[u3rick]

Tsjallie

Finally figured out why my mdlNewOwners button code would not change the buttons. Forgot that you moved to Home Page Buttons. Its much easer now to get them to work!:roll eyes: hmmm! (the things I forget!)

I need Beer!!

u3rick

[u3rick]

Tsjallie

I notice that I don't have any horizontal scroll bar on any of my sheets? Maybe you can set that in the opening setup, so its visible for now?

Thanks

[Tsjallie]

Just open Excel (no workbook).
Goto to File -> Options -> Advanced
Scroll down to display and check the "Show horizontal scrollbar" option. And click OK.
Close Excel and it should be fixed.

[Tsjallie]

Uploaded a demo for the Guest Activity Center (sneak preview) together with a fact sheet listing some highlights.

I started out with this form to just make the buttons be enabled/disabled when needed, but decided to make a form as it (I think) should be when going for the real thing.
Takes more time of course than just making a prototype, but I really think we are very close to finish prototyping and start out for the real thing.
So - though not finished yet - consider this form as a blueprint for all other to-be forms.
It's not about the colouring or the data it's showing, but more how it's constructed to be well operable for users not familiar with how things works behind the scenes and
guard them from making errors inadvertedly.

Important: the form uses the non-proportional font LinePrinter to align currency columns correctly. If that font is not on your computer just copy it to the directory
holding the fonts (in windows that is c:\windows\fonts; don't know where that is on a Mac). The font file is also in the Dropbox.

Just take a look at the fact sheet and play around with the demo.

[u3rick]

Tsjallie

Ok, I will when I get up tomorrow! Fun!

Thanks

[u3rick]

Tsjallie

That is a big change!! Its like a new language for me. Works very nice and so fast. I guess it will come to me as we use it, no different than the rest of my learning on this project!

If I go in and hit the New Guest button before anything else it errors:

Screen Shot 2016-04-28 at 8.12.46 AM.png

Screen Shot 2016-04-28 at 8.13.00 AM.png

Everything else was working good, will continue to play with it and read over code, and step through the different stages.

Nice stuff!!

BTW needed Font is in both systems on my computer will add to laptop next time I use it!

Thanks!

[Tsjallie]

Uploaded a new version with the "undocumented feature" (=error) fixed (not ready to post here).

Also replaced most of the literal strings in the code by constants.
These constants are declared in the module mdlDeclarations if they are needed throughout the application
or in the userform's module if they are only needed within the context of the form.
Idea behind this if to improve the maintainability of the application as a lot of things that might need to be adjusted
can just be changed in these declaration sections. So less chance you need to go through the whole code (and overlook lines needing change).
It also promotes the consistent use of names.

[u3rick]

Uploaded a new version with the "undocumented feature" (=error)

You should have known I would go to "undocumented feature" first! Lool!...Sorry!

Taking a look at the revised form. So 3 tables or so within a sheet will be the norm? With this change I notice you are setting the sheet and table and assume that is necessary due to multiple tables?

[Tsjallie]

So 3 tables or so within a sheet will be the norm? With this change I notice you are setting the sheet and table and assume that is necessary due to multiple tables?

The number of tables per sheet is totally depending on the data it's using and where this data comes from. So can be any number of tables.
You "always" need to supply the sheet a table is in.

[Tsjallie]

As I am going through all the details now I ran into a serious flaw in the use of the Archived_YN columns.
As you know, when changing a record, the original is archived and a new record is inserted holding the new data.
If you make more than 1 change to a record then you will have more than 1 archived record.
This causes the Unique id no longer to be unique.

To solve this we need to change the way we use Archived_YN and at the same time introduce a version number.
This means that when a record is changed the original record isn't touched at all anymore and a new record with a 1 higher version number is inserted. This means that archiving (e.g.) a Guest is just a change in the data of the latest record (version). So a new version of the record is inserted have "Y" in the Archived_YN column.

I'm working on implementing this and will (try to) upload the result this weekend.
So far I like what I see. I think it makes more sense.

[u3rick]

Ok.... sounds like a plan!

The number of tables per sheet is totally depending on the data it's using and where this data comes from.

So will Stays and TransActions be in the Owners sheet also?

[Tsjallie]

So will Stays and TransActions be in the Owners sheet also?

I would think that the Owners form will look quite much like the Guest Activity Center with Owner data and Transactions, but have Properties iso Stays.

I want to fully finish the Guest Activity Center first and then look if it can be made hybrid to host The Guest Activity Center as well as a Owners Activity Center.
Important is that it will not get too complex.

[u3rick]

So... the goal is to make this as small and as compact as possible with the code and the number of sheets. I would assume that makes the program faster with less sheets and code to cause errors.

Important is that it will not get too complex.

The above statement is your limiting factor on down sizing the programs sheets, code and tables.

Am I understanding this correctly?

[Tsjallie]

So... the goal is to make this as small and as compact as possible with the code and the number of sheets. I would assume that makes the program faster with less sheets and code to cause errors.

The above statement is your limiting factor on down sizing the programs sheets, code and tables.

Am I understanding this correctly?

Yes, that's correct. But I don't consider it limiting, rather a challenge.
Ratio behind it is that one sad day you will need to maintain the application by yourself.
Now I can make the code sophisticated and flexible as possible, but if that goes beyond your skills the result would be useless and I wouldn't be very helpfull.
Nevertheless I think we can build an application together which will do it's job properly and which will be maintainable for you.

But for that it's important that you guide me: if I'm doing things which are beyond you, have me explain or change them. And be assertive!.

I'll be uploading a new version, working with record versions.
Also changed the constants to use column names iso their indexes, which were beyond me.

Hmm, noticed that saving a record need some adjusting to the new method with versions.

[u3rick]

Tsjallie

But for that it's important that you guide me: if I'm doing things which are beyond you, have me explain or change them. And be assertive!.

There is a problem here, most of what we have been doing is beyond me!! Lool! (without your help)!! My basic skills are not very strong and I rely on learning just what you use as we build the program with your templates and prototypes. When I look at the new stuff in Guest Activity Center its very different and I can sort of see what its doing, but will need some time changing other parts of the program over to the template to learn it. This is the best way for me to learn.

It is hard to be assertive when I like the highest-tech stuff to be used, so if I have half a chance to maintain it.... I want it!!

Question: Why didn't you just include the time stamp with the "N" to make unique?

Program: I entered a new guest and clicked Save Guest worked fine. Then I click the button delete the new guest and I didn't see a new line. It also didn't delete the guest as far as I could see?

Program Next: I edit the new guest and got a block error (Sorry didn't take a screen shot) but the data was added. Also didn't see a old line with "Y" and new line with "N". This could be because you are not done but wanted to let you know!

When you told me a few weeks ago, we would need to start over when the prototype was done, I didn't understand why! After trying to follow all the many things going on in the prototype its now very apparent why rebuilding the final product one complete self sustaining part at a time is very important!!

BTW: My 12 hours a day of boat work is cutting into my time on this project, but not my excitement at what you are doing! I try to study what you send me at least 2 hrs a day!

As always thanks for your help and making me think hard every day!

[u3rick]

Tsjallie

Here is a basic question: I notice you have Functions in the new code. I know I should know this, but what is the difference from a Sub?

changed the constants to use column names iso their indexes, which were beyond me.

Expand further on what you are referring too?

[Tsjallie]

Question: Why didn't you just include the time stamp with the "N" to make unique?

That would have been possible, but when (as I look at it) determining the latest version using a number is must simpler.

I notice you have Functions in the new code. I know I should know this, but what is the difference from a Sub?

A function returns a value and a sub doesn't.
When using a function it becomes possible to return an indication whether the function was executed succesfully or not and respond to that in the code.

changed the constants to use column names iso their indexes, which were beyond me.
Expand further on what you are referring too?

In the first version I used column numbers to specify wha columns should be included in the combox and listboxes.
While working on the demo I inserted some columns and moved some others. Each time I needed to figure out the new column numbers.
Until I got totally fed up with that and started to used the column names instead. Now I don't have to worry about where the columns are.

Posted a new version GuestActivityCenter v2.
I think the Guest section is pretty complete now.
Though I'm sure you'll find something

[u3rick]

Tsjallie

Why did you go back to separate sheets in this version?

I am wondering how this will effect the Forms brought in the calendar. Most the new guests and bookings (Stays) will be generated from there by VBB staff.
I am sure you have already got that planned!

I will see if I can find anything that doesn't work today and tonight.

It looks good!

I will also see how it works with my graphics as I think we have different tastes in that department! Lool! BTW: yours looks very nice!

[u3rick]

Screen Shot 2016-05-03 at 8.44.47 AM.png

I think we should only be able to check one box at a time in this area and Transaction too? If you click two it clears Trans and they don't come back unless you recheck!:roll eyes:

Screen Shot 2016-05-03 at 8.52.50 AM.png

[u3rick]

Screen Shot 2016-05-03 at 8.55.01 AM.png

Get the above error if I go straight to "New Guest"

[u3rick]

Big news I just loaded the new version of Excel for Mac, and the program works on it!! Still can't build forms on it, but thats not a problem. Now they can actually use the program at Vacationville!! (When Finished)

[Tsjallie]

Why did you go back to separate sheets in this version?

When tables are on the same sheet and you want to insert or move columns in a table which has one or more other tables to the right of it Excel is complaining about that.
Also having one table on one sheet is more convenient. You always know where you are and have the tabs to instantly take you to the table you want to go to.

I am wondering how this will effect the Forms brought in the calendar. Most the new guests and bookings (Stays) will be generated from there by VBB staff.
I am sure you have already got that planned!

I know and we'll keep it that way. The button Add Stay will just be another route to the same booking form you would be starting from the Calendar.
And the button New Guest will just be the equivalent of the button New Guest on the Booking form.

I will see if I can find anything that doesn't work today and tonight.

Well, that wasn't too difficult I noticed! Me so sloppy

I will also see how it works with my graphics as I think we have different tastes in that department!

I noticed that as well. Didn't put too much effort in that, though. It's you app. You'll make the decisions on these things.
Exception on that however is the LinePrinter font in the listboxes. That's needed to nicely align currency columns.

[Tsjallie]

I think we should only be able to check one box at a time in this area and Transaction too? If you click two it clears Trans and they don't come back unless you recheck!

I know. Still thinking on how to go on that.
Here we have two options:

1. Select one stay and one transaction at a time
   This is easier to handle, but won't allow you to archive multiple stays (and their connected transactions) in one run.
2. Select multiple stays and multiple transactions at a time
   This is more complex to handle, but will allow you to archive multiple stays (and their connected transactions) in one run.
   It would also allow you to view all transactions for a Guest (when selecting all stays)

At this moment the working of the Stays list and the Transactions list is yet a bit in between both options.

Which option would you prefer?
Personally I would go for option 1 and have the Archive procedure make it possible to archive a whole hierachy. E.g. a Guest with all his connected Stays and all their connected Transactions.
Actually was already kinda preparing for that with introducing column for the Balance and Current Stays.

[u3rick]

Which option would you prefer?

I just noticed that it didn't do anything when two were checked thats all I was talking about! I will go with your choice because you have a better insight to the end program!

[Tsjallie]

Uploaded GuestActivityCenter v3 with some repairs.

[u3rick]

When I add things to existing Guest I end up with both versions having an "N" is that correct?

Screen Shot 2016-05-05 at 7.42.04 AM.png

Seems like version 1 should have a "Y"?

Now only showing version 2 when show versions checkbox not check. I am not sure why it showed both for a time?

[u3rick]

When I archive Guest Version 3 best a "Y" and version 1 & 2 still have "N". It may be that I don't understand this system?

I have put an * by the text boxes that need to be filled before the save button should be enabled, this is true in all cases I think. Now I can make a booking with just a first name only!

[u3rick]

I think the Projected Amount of a booking on the Form will be confusing to the average user, at least here in US. Would be more standard practice here to show Past Due Amount, Current Amount Due, Paid, Balance Due. If the program works better having a projected amount just don't show on from. ?? Is that a problem from a code stand point. I know that requires it to look to the Calendar at a certain point each month, to see who should be charged each month!

Got to go!

Thanks as always!!

[Tsjallie]

When I add things to existing Guest I end up with both versions having an "N" is that correct?

Yes, that's correct.
The version number and Archived_YN are now (semantically) detached. Meaning Archived_YN is now just an attribute of Guest, like the name, the address and alike.

The version number refers to the record where the record with the highest version number is the current. By default that record is shown in the userform.

Archived_YN refers to the Guest (the entity). So if you would archive a Guest the record with the highest version number should have "Y" in the Archived_YN column.
If later you would unarchive a Guest than a new record is inserted getting the highest version number and "N" in the Archived_YN column.

In the screenshot it shows that you made a change to the Guest data, but you didn't archive that Guest. So the table shows the history of all changes made to a Guest which is your audit trail.

Also nitice that, when you select "Show all versions", "Include Archived Guests" is als selected. If not you would not get to see the record versions with "Y" in Archived_YN.
If you select "Include Archived Guest" you will still see the current versions of record, but current versions with "Y" in Archived _YN will be included.

[Tsjallie]

I have put an * by the text boxes that need to be filled before the save button should be enabled, this is true in all cases I think.

Was planning to ask you about what the minimal required fields for a guest are. Are we a team or not?

Why not just leave Company Name empty if it's not applicable?
Why not make Country required (can insert USA as default)?

Same will go for the Owner.

[Tsjallie]

Past Due Amount, Current Amount Due, Paid, Balance Due

Now you're really helping me.
The Projected Revenue is just something I made up. It's not a problem at all to change these things as they are calculated by formulas in the table. So no need to code.
Just need to change the specifications of the listbox in the declarations section of the Userform (StaysCols).

What the difference between Past Due Amount and Current Amount Due? Think I know, but just verifying.

[Tsjallie]

I know that requires it to look to the Calendar at a certain point each month, to see who should be charged each month!

No, the Calendar looks at the Stays table and so will the formulas calculating amounts due.

Coming to speak about the Calendar. Would it be an idea to change the colouring to show months due, over due etc?
Now the colouring is pretty meaningless.

[u3rick]

Tsjallie

Why not just leave Company Name empty if it's not applicable?

It should be empty if FName & LName are occuiped but if those are empty then it is required if that make sense?

Why not make Country required (can insert USA as default)?

Thats a good idea!

What the difference between Past Due Amount and Current Amount Due? Think I know, but just verifying.

Current Amount due is The current month only, Past Due is everything else except current month.

We do make a good team, but it is heavily dependent on your skill level!!

Thanks as always

[Tsjallie]

Current Amount due is The current month only, Past Due is everything else except current month.

As far as I recall a month's rent becomes due 5 days prior to the start of that month and it should be paid before the months starts.
So due period will start 5 days prior to the first day of the month and end on the last day of that month (and become past due)?

Almost what I guessed

[u3rick]

Almost what I guessed

You are correct by the book, but lets not make it past due until the 10th so they have a 5 days before and 10 days after where they are not past due. Thats the way Vacationville does it!

Thanks

[u3rick]

Would it be an idea to change the colouring to show months due, over due etc?

Sorry I missed this, and I think its a great idea! In fact that is something that Lodgix's calendar does that I forgot about.

[Tsjallie]

Uploaded a new version with data as required for stays and transactions (all calculated; no code) and with check for mandatory fields in the Guest sections.

Also introduced FirstMonthRent (prorated). Are you also prorating the last month?

[u3rick]

Tsjallie

Are you also prorating the last month?

No Vacationville policy is only for early move ins.

I have been working with the Guest Activity Center and its working very well!

I also did a little blending of the form into the graphic style of the full program. I think it will do the job. I will see if I can get it into the new version and you can check it out and see if it still has all the parts it started with.

The boat work is about 1/3 done, its going slower than I had hoped. Still trying to get two hours a day in reviewing the new code. I can fellow it, but can't wait for more time so I can work with it and need you to fix it!!lool!

Thanks

[u3rick]

Tsjallie

The prorate form in v.1.1 of the program did the prorate calculations and just posted as rent is that easer?

[u3rick]

Tsjallie

The Paid (as total amount of rent paid) will also confuses staff. This column is best used on the owners statements showing the owner the amount of rent collected. It would be a running yearly total. The Paid could be changed to Last Paid and show the date of last payment. I think that could be more helpful at Vacationville.

[u3rick]

Uploaded version with changes to form graphics


Thanks for all your efforts thing are working very well!

[Tsjallie]

The Paid could be changed to Last Paid and show the date of last payment.

Do you want that in the Stays list or in the Overall Balance box?

Forget this post. You already answered that in Userform2

[u3rick]

Tsjallie

Is there going to be a rebuild in every form and sheet in the ES_VMS_v1.1?

- Cascaded archiving of Guests, Stays and Transactions

Give an example of what this means?

Thanks

[u3rick]

Can't make a company by itself as a guest?

I think if you leave First and Last blank and fill in company it should still let you "save". Or, maybe a check box that activates Company as mandatory and changes the First and Last to Contact but not mandatory. It would have a sort key by Company name.

[Tsjallie]

Is there going to be a rebuild in every form and sheet in the ES_VMS_v1.1?

Not sure if that's what you mean, but - like mentioned before - I strongly encourage you the rebuild the final system from scratch.
But not after drawing up the system to be. Actually, this is what I'm preparing for.
That sounds like a lot of redo work (and it is), but from my experience I can tell you that changing the current prototype into a final system
will be much more work and will for sure not result in a stable and reliable system.

Cascaded archiving of Guests, Stays and Transactions

This means that when you archive a Guest its related stays will also be archived. And so will all the transactions related to these stays.
Of course a Guest cannot be archived when there are any current stays or when there is an unsettled balance.
In that case you can still archive any past stays and their related transactions.
Again, of course, only if there is not an unsettled balance on such a stay.

[Tsjallie]

Or, maybe a check box that activates Company as mandatory and changes the First and Last to Contact but not mandatory.

I was thinking of that option too. So you can explicitely identify a guest as a company instead of natural personal.
Will add such a checkbox.

[u3rick]

Tsjallie

I agree with rebuilding the whole thing with as much code as possible like the Guest Activity Center! , so I am in with that, just can't do much till the boat is done!!

[Tsjallie]

... with as much code as possible ...

You want more? Can be done. Just say the word! (Wuhahaha!)

[u3rick]

Well I don't know about more, but all the program code needs to be updated to your latest code style in 3.1

[u3rick]

Tsjallie

What should I be working on in my small amount of spare time? Also, I will be flying home on June 1st and will be having a lot more time to work on program!

[Tsjallie]

What should I be working on in my small amount of spare time?

What I was planning to do is go through these huge threads and collect all statements saying something about what the system should, how to do it and what (business) rules apply.
You could make a start with that (please mention the post #).
Also I'm gonna propose an other way of working together which should add more value for you and the forum.
Will send a PM with details.

In the meantime I'm still working on the Guest Activity Center. Takes quite some time because I'm going through every tiny detail and simultaneously working on a design document.
Good news is that when the Guest Activity Center is done, the Owner Activity Center should be light work as that will be a copy for, say 90%.

[u3rick]

Tsjallie

Sounds good, I will take a look at the at our history!

Thanks

[u3rick]

Tsjallie

I took a look at our first big thread "Copy random columns from one sheet to another in the same workbook...Problem?"

What I was planning to do is go through these huge threads and collect all statements saying something about what the system should do, how to do it and what (business) rules apply.
You could make a start with that (please mention the post #).

Here is a list of first 3 pages to see if we are on the same page as to what you are looking for?

Post# 4, 5, 6, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 30, 33, 34, 36, 37, 38, 39, 42, 43, and 44.

Wow! we have type lost of stuff!!

Have a cold beer!!

[Tsjallie]

What I'm looking for are functional rules like you stated in post #203. These things are all over the place.
Probably you will just know these rules as you are in the middle of your business. So maybe no need for you to go through all these post. Not a very nice job

[u3rick]

Tsjallie

Ya I new I was not going the right direction, but I will look it over again as I have time. Mainly to remember what I have told you and what I have not!:roll eyes:

Thanks

[Tsjallie]

This thread is growing to long.
We are sure it contains useful information, but just try to find it.

So we're closing this thread.
Instead of that we will continue our project via email and dropbox.

Solutions coming from the project and which may be useful to other members will by posted on this forum in separate threads.
In this way we hope to add more value to the forum.