Designing an Excel application for simultaneous use and for optimal maintainability

It all worked well except it leaves the Calendar like this.

This is the effect of introducing the audit trail in the PropertyOwners table on the Calendar's formula.
With the audit trail Properties can appear multiple times in the PropertyOwners table. Property xy JL 105 (3000037) does.
In the second part of the Calendar's formula (when no GuestStay is found), the formula looks if the Property is unavailable for the month involved.
This part of the formula selects the record concerning the Property

Formula:

Please Login or Register  to view this content.

but it expects only 1 record. Now there are more we need to adjust the formula for that.
We can do that by adding the POArchived_YN column to the formula like this:

Formula:

Please Login or Register  to view this content.

Tried that and it works fine.
However the same problem would rise when a Property would have more than one owner. Then again the Property-record would appear in the PropertyOwners table multiple times and (likely) with PO_Archived_YN = "N".
I will change the formula in v1.0.6 in the Dropbox.

Just realized that what I really want is not recurring transactions like QB's. I am thinking this should charges guests as due according to their booking terms. This should be an internal part of the reservation system.

Once a Stay is book there will be transactions posted as part of the FrmGuestActivity and payments accepted by staff. Then the internal system should take over and charge each guest 5 day before the last day of the month for the duration of their stay. Payments will be recorded as paid by staff. The system should included the same activities for the owners side of the transactions.The owners are credit on the last day of each month for next months rent and invoiced for management fees. This will be offset by a check that is paid to the owners with their statement each month.

Rule 1. Guests are billed by the system 5 days before the last day of the month.
Rule 2. ES rent is prepaid, in other words rent paid in February's statement is rent for March.
Rule 3. Statements are generated after the 10th and mailed by the 15th of each month.
Rule 4. Prorated rent paid before the 10th of February will be paid with February's statement with a check back dated to February 29 (28).
Rule 5. Prorated rent paid after 9th of the month will be held for March's statement.
Rule 6. Owners credits, Management fee and Stays commissions will be generated on the last day of each month for activity Stays.
Rule 7. Accounting wise all systems ES, Both, and SV will not term rent as earned until the last day of the stay, or the last day of the prepaid month. So the checks sent to ES owners will be held in a prepaid account and released to the earned rent account on March 31st.
Rule 8. Stays will not be Archived_YN until the deposit is refunded, retained, or some mix of the two that clears the deposit account.

Its late so I hope this make sense, its just a start!

Thanks

u3rick

When are you leaving on your trip?

I'm leaving wednesday morning very early. I don't expect to be online after the weekend.

Everything is working, but I notice that Private Sub FillConnectionDetailsBox(OwnerId As Double) is only filling the first record. I tried the ways you have used to get the "N" included, but listbox is causing problems for me. I could use your help if you have time.

Ran into an error in frmConnectionActivity

Made some changes to make it work again (saved v1.0.6 to the dropbox):

lstConnOwnerBox_Click()

- disabled deselecting lstUnConPropBox (makes no sense)
- made call to FillConnectionDetailsBox conditional, because you need both an OwnerId and a PropId
- added PropId to the call to FillConnectionDetailsBox

lstUnConOwnerBox_Click()

- disabled blanking txtPropertyIdCD (makes no sense)
- disabled deselecting lstUnConPropBox (makes no sense)
- made call to FillConnectionDetailsBox conditional, because you need both an OwnerId and a PropId
- added PropId to the call to FillConnectionDetailsBox

lstUnConPropBox_Click()

- disabled deselecting lstUnConOwnerBox (makes no sense)
- disabled deselecting lstConnOwnerBox (makes no sense)
- made call to FillConnectionDetailsBox conditional, because you need both an OwnerId and a PropId
- added PropId to the call to FillConnectionDetailsBox

FillConnectionDetailsBox

- added PropId as parameter
- adjusted the lookup of RecIdx to handle not found error

This gave a type mismatch because RecIdx is integer (positive numbers) where a not-found error is a negative number

- disable setting txtPropertyIdCD and txtOwnerIdCD (already there)
- made filling the connection box conditional based of value of RecIdx

If 0 initialize connection box

Funny thing: I can't test the Connect button, because it's just outside the boundaries of my laptop screen
Edit: managed to get to the button tabbing. Connecting seems to work OK.

Where can I see the Properties an Owner is connected to?
Why can I not connect an Owner to an already connected Property? Thus facilitating dual ownership.

I love that set of rules. Look like requirements
Will study them.

Owed you an explaination of the audit trail.

That shows up on the frmManageProperties.

That shows the Owner(s) of a Property, but not the Properties of an Owner ??

BTW, where did v1.0.6 go? Don't see it in the Dropbox

We never do that with owners (Two Owners in the system)! We make them pick one owner and they are the owner of record and we pass info to the other through him.

Ok, fair enough.
But how will you be handling payments by or to the owners. Or will you just assign one of the owners as legally responsible?

Btw; the lstConnOwnersBox when clicked on doesn't fill the Connection Details with the needed information to edit!

That's because you need both the OwnerId and the PropertyId to find the Connection Details.
But you can't select a connected property.

Now my problems, the sort is not kicking out the POArchived_YN ="Y"

As far as I can see the archived records (POArchived_YN="Y") are correctly filtered out.

and (this one I knew would happen) it only feeds the first record

Can clarify what you mean?

As for the logic of the form I must admit that I find it confusing (and probably users too).
Normally these kind of forms are in a master-detail layout kinda like in the old forms though I would support to have separate forms for Owners and Properties.

if you click on any record in the connected owner box it only shows the first record info in the Detail box. That not surprising though as there is no code present to search for it.

The reason for that is I didn't know how to write with a listbox!

why is the lstConnectedOwnersBox not formatting correctly?

You need to format the data when moving it to the listbox like this

Please Login or Register  to view this content.

That not surprising though as there is no code present to search for it.

Yes, there is. It's in v1.0.6. See my post #207.
But there seems to be something rotten in that version. It crashes when opening. Don't know why.

bet your keeping a backup now because I can't be trusted!!

I'm saving directly to Dropbox. But maybe I shouldn't

OK, let's go throught the code step by step

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The listboxes are 2 dimensional meaning they have rows and colums. You can refer to rows and columns as parameters of List([rowindex],[columnindex]).
Both the rowindex and the columnindex are optional. If you leave one VBA takes the default value (=0).
In this case you've specified the rowindex and left out the columnindex always giving the PropertyId of row 0 and row 1.
To use the currently selected row and use the PropertyId or the OwnerId you can use the Column-property which takes the column number as a parameter. And so the right syntax would be

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You're using i for the rowindex, but i never gets a value assigned and so stays empty. That's interpreted is 0. So this will always give the PropertyId and OwnerId of the first row (=0).
Instead of using a variable here also the Column-property can be used for pointing to the selected row and column
Correct syntax would be

Please Login or Register  to view this content.

Other observations

• in lstUnConOwnerBox_Click FillConnectionDetailsBox is called with the curent values of lstUnConOwnerBox and lstUnConPropBox,
  but lstUnConPropBox is first unselected a few before.
• same goes for lstUnConPropBox_Click with lstConnOwnerBox and lstUnConOwnerBox
• adjusting the PropId and OwnerId accoring to selections in the listboxes is not consistent.

Rick,
are there any things you need me to address before I'm off?

How long you get to go for?

Will be home on 3/28 and then lay in coma for a few days
So that'll be somewhere in the week following Easter.

Can you post are mail a sample of a Guest-invoice.
Thinking of using that as a template.

I hope you have a great trip!

Lots and lots of snorkeling

Will this do?

Actually I was thinking of a sample of the invoice like you send to a guest.
Idea is to have such template as a sheet and filling that from the tables.
Then saving if as an pdf to a folder.
And finally from that folder mailing the pfd-invoices to the guests.

I just edited a property in the Connection Center and it put a "Y" on the old record. Made a new record but never re-sorted the calendar correctly and the record on the Connection Center is messed up. its prop 3000004 and owner 1000004.

The Connect-button saves the connection and details to PropertyOwners table. But reading the connection details is done from POConnected table.
Adjusted FillConnectionDetailsBox the read details from PropertyOwners table.
As far as I can see this now works OK.

Looks like we're mixing up versions again. I'm working in ES_Reserv_MgmtSys_Step7Test_v1.0.0.
Made the adjustments for the connection details in there (with addition to filename).
Now looking into issue from yr post #240 also in ES_Reserv_MgmtSys_Step7Test_v1.0.0.

As to post #240:
In sub AddEditDeleteOwnerPropRelationship

• Added Application.EnableEvenst=True before the last change to table PropertyOwners in order to have the Worksheet_Change event of sheet PropertyOwners sheet be fired which adjusts the number of rows in the Calendar and sort the PropertyOwners table
• Disabled changing POLastModBy because that column no longer exists.
• In the Edit section added a line to set the old record's POArchived_YN column to "Y"

but still have sorting problem?Hmmmm!

Propbably solved with last post

So the worksheet code events only happen when Application.EnableEvents = True. Hmmmm this is slowly sinking in!

Yes, Application.EnableEvents only affects worksheet events.

I am assuming that one project ahead is to delete all columns in all tables that read LastModBy and LastModDate. Then make sure that they have xxArchived_YN. as a result all filters will need to look for Archived_YN to = "N" first. unless looking for Archived files then it would be ="Y"

Check

I think the Disconnect Button needs to be deemphasized if the PropertyOwner has blocked dates or an active stay? That way no chance to disconnect a property with files stuck in those tables?

Check, but if with "deamphasized" you mean "disabled".

I think we are going to need a full sheet table for tblPropertyAvailability as it needs to be deleted or Archived_YN and thats hard to do on that Tables sheet. Your thoughts?

You may just add Archived_YN, CreatedBy and CreateDate to the existing table.
Not necessary to move it to a separate sheet, but if you think that's more convenient for you then that's fine.
But, if you move that table to another sheet, remember to adjest the references to that table in the code. You need to change the sheetname in the set statement defining the table object.

Well, anything that could help in making the internal calendar charge the guests during their stay. Also any quick insights into making and printing reports or where I might find type info.

Mixed up the Guest charging from the Calendar and printing reports. Really need this vacation

My thoughts so far (no time left to make up demo):
Charging Guests from the Calendar

• From the sub BookGuestStay
  • Generate recurring transactions for each month of the booked stay
  • Summarize total amount invoiced in a new sheet Guest Receivables
• Upon date change (may be on opening the workbook or just by clicking a button)
  • Summarize total amounts due for each Guest in the new sheet Guest Receivables
• Upon booking a GuestTrans
  • Summarize payments/refunds for each Guest in the new sheet Guest Receivables

Making and printing reports

• Best is to have sheets making up a report in the layout you want to print it.
  You can have the data cells filled by a formula in it or you can have some code filling the cells
• When printing a report you can
  • Directly print the report sheet or
  • Copy the report sheet to a new (temporary workbook) and save that workbook as a pdf-file
    By doing so you can build a history of reports. The temporary workbook can be closed without saving after saving the pdf.
  I'd recommend the second approach.

Tsjallie

Thanks for all the questions answered today!! I know that you had lots to do!

Have a great trip!!

Until next time!

u3rick

Tsjallie signing off

One last-minute question:
What precisely is the difference between a GuestStay coming in from Lodgix and one made thru the Calendar and
what precisely is the difference between a transaction coming in from Quickbooks and one made thru GuestTrans in the VV system?

Yes, everything is set and ready. Now I'm bored.

Tsjallie

What precisely is the difference between a GuestStay coming in from Lodgix and one made thru the Calendar

Well the GuestStay coming from Lodgix are only going to use OwnerTrans side of VV system, because all guest trans are handle in Lodgix (other than the Journal posting).

what precisely is the difference between a transaction coming in from Quickbooks and one made thru GuestTrans in the VV system

Not sure how much will come from QB's most will be sent to QB's. I think what we send to QB's will be the same from Lodgix or VV system.

If your needing stuff to ponder check my post on forum for help on DCount (Don't laugh that I didn't make it 24hrs without needing help) and I could really use a templet on how we filter everything for Archived_YN ="N" for Current and "Y" for Archived. It has to be used everywhere I am discovering.

Thanks!!

That one with the DCount takes time to figure out, but will ponder on it while floating on warm waters

Tsjallie

That one with the DCount takes time to figure out, but will ponder on it while floating on warm waters

Guess thats why no one has helped!

Don't forget to drink many beers!!

Tsjallie

When you get cranking after your resting period, here is a project. Goto the calendar and make booking pick Book a Present or Past Guest's Stay and when the calculator comes up just click continue then book in the frmStaysActivity. Then the fun begins Getting errors that I don't understand?

Hope you had a great trip!

The file is in the dropbox its to big for attaching here, sorry!

UpDate: I have solved the problem, not sure why its so slow, so still take a look!

Latest update: Its working better i am posting a lot of data so, run it and see what you think?

Tsjallie

Forgot I have protected the workbook Password is SEA0916!

Here is a slightly striped version for people who are following the project

Back home again after a great journey, but still not fully arrived in the land of the living
Will try to dig into this again. Something with Excel I believe

Wow! You've been working your heart out I see.
I don't think it's that slow, but I'm sure that can be improved still.

One remark though (for now). When you bring up the property form from the Calendar and select Availability there's an error.
That's because you filter on field 10 where the Archived_YN column is field 9.

Tsjallie

Welcome back!

Is that using the above attached version or the Dropbox version? I think I have it working on Dropbox version, let me know?

Tsjallie

When I add the filter line in red I get this error. The table filters correctly, but won't load into the listbox?

Screen Shot 2016-03-30 at 7.48.06 AM.png

Please Login or Register  to view this content.

Thanks

Is that using the above attached version or the Dropbox version?

I used the Dropbox version.

When I add the filter line in red I get this error

Rng may produce a non-consecutive range when filtered on "* paid". So you need to sort the table first on the filter-criteria.
Rowsource must be a consecutive range of cells.

Changed the filter from 10 to 9 ...thanks

Tsjallie

Can't find anyway I know of to make it work? When you filter the table with both filters it looks correct, just won't load in listbox!?

UpDate: Change the sort a bit and found as long as the guest had only one booking it worked fine but the one I was testing had two. So that could happen (guest do rent more than one unit). Guess I need to hold history until a Stay is clicked.?

I will see what issues that causes!

Tsjallie

Rt click on an existing Stay and checkout how it works, then check code and let me know what I need to change or add!

I am changing all code as follows (Is this what you intended?)

From this;

Please Login or Register  to view this content.

To this;

Please Login or Register  to view this content.

When doing this it makes you get rid go the;

Please Login or Register  to view this content.

??
Thanks

Looks OK to me.

Tsjallie

I am having a problem in with;

Please Login or Register  to view this content.

Changed above code and now get this error

Screen Shot 2016-03-31 at 9.11.26 AM.png

As the error says the variable (object) tblPropertyOwners does not exist.
You can either assign the object Worksheets("PropertyOwners").ListObjects("tblPropertyOwners") to the variable(object) tblPropertyOwners (with Set).
Or replace tblPropertyOwners in the CountIfs by Worksheets("PropertyOwners").ListObjects("tblPropertyOwners").

Ah

I am going with

replace tblPropertyOwners in the CountIfs by Worksheets("PropertyOwners").ListObjects("tblPropertyOwners")

Forget that advice. Like I said, I'm not quite returned to the land of the living.
Just remove tblPropertyOwners form the CountIfs-statement. The dot in fornt of ListColumns tells vba to refer to the object in the With-statement.

Please Login or Register  to view this content.

ok sound good!

I am that way all the time

Tsjallie

I did something to cause the filtering to return the Y(DeletedGroup) to the Unconnected Owners and the Unconnected Properties, but I don't see it. Here is the unconnected owners code!

Please Login or Register  to view this content.

It worked a while back not sure what changed!

Thanks

I am out of dropbox if you want to copy latest version

I fixed it and not sure why, but it was very particular about where the

Please Login or Register  to view this content.

and the

Please Login or Register  to view this content.

went! Also, when I copied it I did it with both Subs using OwnerId. Where the unconnected property sub needed PropertyId!:

Tsjallie

Completed code remodel and I think everything still works. Still have lots to add but nice to have everything somewhat the same and working at this point. Check it out and see what you think?

Its to big to attach here so latest version is in the Dropbox.

Thanks

Hi, just curious, did you do anything about restoring Excel after the application closes?

Keebeliah

I turned off the part that takes the program to full screen. I have a big screen and it makes it to big, but I have not added code to return Excel to it original state.

Can you resend that code and I will put it in as I am trying to get as much into the prototype as possible.

Thanks

I'va packed the vba module in attached zip file.
the GP stand for General Purpose

Thank you I will take a look!

I'va packed the vba module in attached zip file

I would expect such a routine to store the settings which are current at the time the workbook is opened and restore those settings when it's closed again.
Can you make something like that?

Tsjallie

I am looking for help on this task I am working on. I have a form that has 3 list boxes that feed a detail section at the bottom of the form(below). Depending on which list box you click different buttons appear. One of the buttons is a disconnect button. It appears when the Owners and Properties are all ready connected and click on. Here is the task I want to accomplish. I want the disconnect button to be disabled if the Property has any bookings, stays or dates block (the disconnect button is only available when clicking in the Connected Owners Box). This will give the user a chance to still edit the file, but not disconnect it until there are no stays, bookings, or blocked dates on the calendar. I have tried a DCount in my sub that I have no idea how to code or work, and I am not sure it even works with Excel? Maybe there is a better way to see if the propertyId exists in the tblPropertyAvailability and or tblGuestStays?

The need for something like this comes up in a few different places. When you delete something and it should not be completed if other things have not been finished or deleted? Any ideas??

Thanks

The need for something like this comes up in a few different places. When you delete something and it should not be completed if other things have not been finished or deleted? Any ideas??

I think this is about the referential integrity thing of which I uploaded a demo lately? This could also affect the enablement of buttons.

Tsjallie

I re-visited the referential integrity demo. (btw: it runs on my mac version and thats strange because mac doesn't run forms!?) anyway it works great at deleting everything or undeleting everything as I believe you designed it too.

I was referring to the fact that I can currently delete a unit, owner, connection or guest and all could have an active stay in progress. Now I would not do that, but it could happen. So I was thinking that the delete button should check and see if there are active stay before it comes up. In researching a bite I saw that the DCount could just check for a number and could tell if it was there! Then if it was there that info would shut the button off.

Now you may be able to do that in the referential integrity demo and I just missed it !Lool!

Thanks

Tsjallie

While you were gone, I realized that some stays have no end date. How can those stays extend with the calendar each month? Vacationville has 3 or 4 that fall into that category they are ongoing and should never show as vacant on the Calendar?

Thoughts?

Thanks

Tsjallie

I have reset the open full window thing!

I am working on my laptop and it works well with that size screen! I note my forms are a little big can they be zoomed down slightly?

Also, I have fixed the Notifybox and its working on my laptop now too!

Thanks

Tsjallie

Does your Excel program show times where it say ES program is not responding? It does that on both my computers, but they both run windows 10 and the latest versions of Excel 2016?

Thanks

By the way its really nice to have you back for all these questions that I have going in and out of my head all the time!!

Thanks for your help!!

Does your Excel program show times where it say ES program is not responding? It does that on both my computers, but they both run windows 10 and the latest versions of Excel 2016?

Haven't noticed that yet. Still on Win7 and Office2010.

I note my forms are a little big can they be zoomed down slightly?

I notice that sometimes the forms are bigger than my screen and not showing all buttons.
I think indeed it would be better to size the forms for a average screen size, say 17"

Tsjallie

Check this out

http://www.dreamincode.net/forums/to...reen-size-vb6/

Tsjallie

Question how does the user close and save the workbook, just by clicking the screen box in the ribbon, or do we need buttons?

(When in full screen)

How can those stays extend with the calendar each month? Vacationville has 3 or 4 that fall into that category they are ongoing and should never show as vacant on the Calendar?

If you enter this formula in the DateOut column of the table GuestStays then the stay will extend with the Calandar.

Formula:

Please Login or Register  to view this content.

You then also need to have a CheckBox on the booking form to indicate it's a perpetual stay.
When checking this box the date from the formula would go into the MoveOut textbox and when saving the stay you would need to check if the Checkbox is set on and if so move the formula to the DateOutColumn in the GuestStays table. Remember to use the .formula prperty for the column to get the formula in there iso the value.

Tsjallie

I will get back to work today and see if I can make this work!

Thanks

Check this out

http://www.dreamincode.net/forums/to...reen-size-vb6/

That's VB, not VBA. VBA in itself doesn't allow you to resize a userform. You would need to use the Windows API for that.
Just pick a usefull size. Bare in mind that a userform which doesn't fit a 17" or even a 15" screen is probably over crowded with data.

Ok, just checking!

Question how does the user close and save the workbook, just by clicking the screen box in the ribbon, or do we need buttons?

You can make the workbook automatically be saved when the user closes the workbook or you can leave that up to the user.
No need for extra buttons.

Tsjallie

You can make the workbook automatically be saved when the user closes the workbook

Is that done with the

Please Login or Register  to view this content.

in ThisWorkbook?

I think you don't like buttons!:roll eyes:Lool!

Tsjallie

I was going to replace some code in the Sheets. Just want to have you to check and make sure it will be ok!

I changed this:

Please Login or Register  to view this content.

To This:

Please Login or Register  to view this content.

I changed the way the Dim's are listed just because it a little easer for me to see what going on. I like the way you do it takes less room.

Thanks

Tsjallie

Is there a way to open the program to a zoom size that fits a 15" laptop screen, but keeps the program in those borders no full screen? (You can do it after it opens by going to Windows and clicking Zoom)

Is there a way to have a home page as the only one that shows in tabs? It would have buttons for Calendar and Guest Payables list sheets and others that may be added. It would have dashes board features that would show a a list of over due guests and a list of vacant units? Could also have a button for bring up the ReferentialIntegrity for data maintenance?

Thanks

Tsjallie

Since I change code to our templet "With" Picking up following error when checking Availability on Calendar.

Screen Shot 2016-04-04 at 10.38.47 AM.png

Screen Shot 2016-04-04 at 10.39.03 AM.png

Please Login or Register  to view this content.

Thanks

To save the workbook without asking add this code in the ThisWorkbook module.

Please Login or Register  to view this content.

You're no longer setting the tblPropertyOwners and tblCalendar objects to the resp. tables.
So using these objects will give an error (variable not declared).

To post #314:
When suggesting to limit the screensize to fit a 15" screen I was referring to userform. You can just set the size of the forms to - say - 900 x 520 (wxh) and that'll be ok.
As far as sheets are concerned I would show the Calandar fullscreen, but other sheets depend on there data. Nice would be to see all columns, but that could result in very small (unreadable) text or very big text. You can set an accurate zoom factor for a sheet with ActiveWindow.Zoom which you can put in the event worksheet_activate()

Tsjallie

Ok, Thanks!

You can set an accurate zoom factor for a sheet with ActiveWindow.Zoom which you can put in the event worksheet_activate()

Do you need to put a size or will it just make it a window that fits the size screen being used?

Thanks

You're no longer setting the tblPropertyOwners and tblCalendar objects to the resp. tables.
So using these objects will give an error (variable not declared).

But error is

Screen Shot 2016-04-04 at 10.39.03 AM.png

Do I NEED TO CHANGE THE HOLE LINE?

??