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
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
Tsjallie
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.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.
Last edited by u3rick; 12-01-2015 at 11:22 AM.
More explanation neededDeclare the public FormEventsEnabled variable as boolean in the mdlTimePeriod
Last edited by u3rick; 12-01-2015 at 11:21 AM.
This is not working so my guess here is wrong, whats missing?Please Login or Register to view this content.
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.
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
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.Declare the public FormEventsEnabled variable as boolean in the mdlTimePeriod
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.
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.Please Login or Register to view this content.
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 ...
You would still need something to hold your data, execute your functionality and host it all along with that web based program.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?)
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.
In what version did you make this change? So I can have a look.This is not working so my guess here is wrong, whats missing?
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 gotentered in mdlTimePeriod.Please Login or Register to view this content.
Thanks!!
Last edited by u3rick; 12-01-2015 at 10:30 PM.
No problems today? I'm getting bored
BTW, the workbook you're uploaded in post #9 doesn't contain the mdlTimeperiod
Sorry about that!BTW, the workbook you're uploaded in post #9 doesn't contain the mdlTimeperiod
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
Guess I've been losing track of things. Must be old ageThought you were working on Journal and Ledger design setup for tables?
Also finishing V3.0.5!
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:Not sure yet whether to combine all in 1 workbook or split it up into multiple.
- Operations (ES Reservations)
I think you have this pretty much figured out- Accounting (Management Sys)
- Communications (AdvDepRpt AutoImport)
- Reporting
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.
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
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
You need to help me out here. What was not working where?Seems to me that something in operations 3.0.2 was not working and you were going to fix it.
Been looking back in the other thread a long way, but can't find anything ringing a bell.
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
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.
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
Rather sounds like hard labor, I would sayThat sounds like a plan ...
I was planning to continue prototyping the communctions section:Will you direct me on how to build the Journal and Ledger sheets (Tables).I expect the Journalize Engine will force me to address the Journal and the Ledgers too.
- AutoImport
- Journalize Engine
- AutoExport
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.
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
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
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
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
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
It's in the Dropbox
Thank you!
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.
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
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.
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
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
Do keep it simple.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?
Tsjallie
I get that! My plan is to give every table the following columns (I think this is what you suggest some time ago).Do keep it simple.
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.
Yes, I think this will give you a proper audit trail.Once these are added then the code will need to be changed to look at only not deleted data? (I will need help here!)
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.
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
When I make a booking (following your instructions) I get an error in the sub cmdCancelSA_Click() at the following line: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
Problem is in the highlighted part as column 0 does not exist. This should be column 1.Please Login or Register to view this content.
Is that the problem you're encountering?
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
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.
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
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
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
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?
Looking at the tables I'm not sure you're going the right way. It's getting very fuzzy now.I am working on my tables and adding the columns that I showed you in post #34
Not sure what you're trying to achieve.
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
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.
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
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
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.
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.Please Login or Register to view this content.
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 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.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.
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.
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
Tsjallie
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!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.
u3rick
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 caseNow that might not be the right path feel free to re direct me!!
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.
Tijallie
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!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).
Thanks
u3rick
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
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.
Tsjallie
Disregard this post I found them!
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!!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.
u3rick
Last edited by u3rick; 01-29-2016 at 12:55 PM.
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.
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
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
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
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.I understand what you are saying in post #56. Not sure what to change? The frmGuestActivity works?
In that case the range to set the rowsource to spans only 1 row and so is consecutive by definition.
should bePlease Login or Register to view this content.
andPlease Login or Register to view this content.
should bePlease Login or Register to view this content.
Please Login or Register to view this content.
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
Discovered an error in the sub HandleRightClick in mdlESReservation which cuases monthly unavailabilities not being saved.
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.Please Login or Register to view this content.
If you set FormEventsEnabled True right before the line [frmManagePropertiesAndOwners.Show] this will be solved.
I'm working with the file you posted here. The dropbox file is all yours.
Got it ! changes made to Dropbox version!
thanks
You can replace the entire code of frmGuestActivity with this code.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.
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.
Tsjallie
Very cool!!
Thanks so much!!
u3rick
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
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
The Calendar disappearing is caused by the formulas in the Property columns in the newly added GuestStay record being in error.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.
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()
ChangeintoPlease Login or Register to view this content.
Private Sub chkNewGuest_Click()Please Login or Register to view this content.
ChangeintoPlease Login or Register to view this content.
Private Sub chkNewGuestBox(GuestId As Double) <- Remove the parameterPlease Login or Register to view this content.
At start of sub insertChangePlease Login or Register to view this content.
intoPlease Login or Register to view this content.
ChangePlease Login or Register to view this content.
intoPlease Login or Register to view this content.
ChangePlease Login or Register to view this content.
intoPlease Login or Register to view this content.
At end of sub insertPlease Login or Register to view this content.
Private Sub SaveEditGuestStaysInfo(GuestId As Double)Please Login or Register to view this content.
Change (3 times)intoPlease Login or Register to view this content.
ChangePlease Login or Register to view this content.
intoPlease 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.Please Login or Register to view this content.
Last edited by Tsjallie; 01-30-2016 at 04:21 PM.
Thanks!!
still amazed at how little i know!!
u3rick
This should work.I would like to filter the cboGuestlistGI which gets its lists from the Table ListBox with the GDeleted_NY
Please Login or Register to view this content.
Tsjallie
Update: Ah finally figured out what you are talking about!!
I must have forgot or never knew what is the difference?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
u3rick
Last edited by u3rick; 01-30-2016 at 05:07 PM.
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.
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
This is the correct one
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.
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.
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
Last edited by u3rick; 01-31-2016 at 02:08 AM.
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
Goodmorning
Standard = GeneralDoes Standard = Text or General I am thinking text after re-reading your post!
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.
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
Yes they can.I am assuming that my calculating columns and ones showing amounts can be set on numbers or $ format?
But remember Excel is very picky on data types. It's important to use them consistently.
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.
Tsjallie
Ok I will pick one number, dollar and date format then stick to them!
u3rick
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
Addition to post #85:
don't forget to test for FormEventsEnabled in cboGuestListGI_Change, like I just did
Just leave them blank. Saves coding.Is it better to leave cells that have no data blank or should you posting something to them? (like N/A or 0.00)
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
As a rule you may catch me online from 20:30 to 22:30.Tsjallie is sleeping!
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
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.
Tsjallie
I was trying to drum up some help so you could have a life other than fixing my many problems!! lool!
u3rick
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
There's a typo in the variable name: FormsEventsEnabled
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.
Tsjallie
After changing the typo the change went through but only changed the first name the rest had no change?
u3rick
Tsjallie
Yes, post #97 fixed the problem!
Thanks for all your help!!
u3rick
Checkbox IncludeDeletedGuest is not working yet.
To make it work include this code in the frmGuestActivity:
and change this line in GuestActivity_Initialize:Please Login or Register to view this content.
And you also need to change this linePlease Login or Register to view this content.
Please Login or Register to view this content.
Where did you set the FormEventsEnabled variable?After changing the typo the change went through but only changed the first name the rest had no change?
Do you check on this variable in the change event of the combobox?
no where does it go in cboGuestListGI_Change()?
no where does it go in cboGuestListGI_Change()?Please Login or Register to view this content.
Thanks!!
Nothing is ever easy!!:roll eyes:
Good Night!!
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.
Setting FormEventsEnabled True in GuestActivity_Initialize should do the trick.
Please Login or Register to view this content.
Thanks
As always you fixed it!!
That stuff really mixes me up! Its like what comes first the chicken or the egg!!
Tsjallie
I better stop everything is working!iooi!
u3rick
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
Last edited by u3rick; 02-01-2016 at 02:53 AM.
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.
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?"
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
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!
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
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!!!!
Last edited by Tsjallie; 02-01-2016 at 05:09 PM. Reason: Finally got the message complete
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!
It's something in the forum site. It seems posting code sounds the security alarms.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!
But the post is finished.
To post #113:
Rng probably contains a non-consecutive range.
The sorts I introduced solve this.
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
Last edited by u3rick; 02-02-2016 at 04:17 PM.
In the Userform_Initialize() procedure of frmStayActivity remove the lineIt does have a mismatch error that happens every other time
This prevents that in the crashing procedure a not-yet-existing GuestId will be looked up giving the error.Please Login or Register to view this content.
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?
Tsjallie
Wow!!, did you call me a VBA programmer?Still a happy 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
You can drop Module 2.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?
Module 1 and 3 can be combined. You could also move these to mdlESReservation if you like.
In order to include/excude Deleted Stays History you need to make the following changes: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!
Add a sub CheckBox1_Click to frmGuestActivity calling cboGuestListGI_Change
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 HistoryPlease Login or Register to view this content.
Last edited by Tsjallie; 02-03-2016 at 06:14 AM.
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
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.
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
Yes, you need that in order to maintain database integrity. I'm sure there are already orphaned records.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?
Stick to 1 system: either virtual deletion (with deleted_yn) or fysical (permanent) deletion.Also what about transactions that just needed deleted and not brought back for ant reason ??
Matter of weighing pros and cons. I think virtual deletion has a better pro/con balance than the fysical deletion.
Tsjallie
I agree just was thinking out loud!
u3rick
No problem, but it's a Visio file. I believe you couldn't handle these files and so I posted it as pdf.So, I was wondering if you would mind supplying me the file?
Will upload it to the dropbox anyhow.
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
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
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!
ThanksThis 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)?
u3rick
The forum is safe.I keep getting this message should we be concerned????
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
Oops! Overlooked that one.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)?
I think indeed the Active columns are obsolete since the introduction of deleted_yn which (afaik) has the same function/meaning.
Yes, I think it's important to have that up-and-running before you go for real operational data.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.
Will figure something out to get you going.
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
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.
Tsjallie
Not so good I guess, but only effecting my emails at this point!
Thanks
u3rick
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.
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
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?
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
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.Please Login or Register to view this content.
---
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
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
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
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.The email that came with your last post went to junk.
There are lots of things like this we need to address some time.I am sure Tsjallie will respond as he did that part of the plan!
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
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
Tsjallie
This should be named FillConnectedDetailsPage and the data is only available in the tblPropertyOwners, I think!FillPropertyOwnerDetailsPage is getting the PropertyOwner’s data from tblPropertyOwners where it should actually get these from tblOwners
Thanks
u3rick
You're right again!This should be named FillConnectedDetailsPage and the data is only available in the tblPropertyOwners, I think!
Tsjallie
How many things can I mess up if I change the name?
u3rick
As long as you use find-and-replace with the whole project as scope it'll be ok.
Ok, I am on it!
u3rick
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
That's great! CongratulationsI have a Granddaughter having her 1st birthday and party Today
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
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
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
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
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
Tsjallie
Where and how do you do this?Changed the moment when application.enableevents is switched on/off to get the table sorted with all data present.
u3rick
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.
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.
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!
Hi thanks u3rick,
I'll look at it too just for kicks and see if I can offer ideas.
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.
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
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
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.
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
This one workes fine.
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.
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!!
Rick,
in the sheet PropertyOwners the column POActive_YN has this formula:
Formula: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"?
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
Thought it would something that. Adjusted the formula.Well if a property is SV (Short Vacation) it’s not to show on the calendar
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.
Deleted_YN is equal to Archive_YN. Active_TN is the opposite.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”?
So Deleted_YN = "N" corresponds to POArchived_YN = "N" corrsponds to Active_YN ="Y"
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"Guess I have a little work to do, I will go to it later!!
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
Last edited by u3rick; 02-29-2016 at 01:54 AM.
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.
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_BeforeRightClickmdlESReservation:- This will call the HandleRightClick procedure- HandleRightClickfrmManagePropertiesAndOwners (FormEventsEnabled = True!):- .cboPropIdEU = PropertyId 'This will trigger cboPropIdEU_Change()- 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
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.
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.
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
Tisjalle
Here is what I dreamed up in my sleep!
Screen Shot 2016-02-29 at 11.25.41 PM.png
u3rick
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.
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.
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
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.
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
@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.
Go it to work again.
In cboPropIdEU_Change:
- Disabled calling FillConnectionDetailsBox.Thisprocedure is called when selecting an item in lstOwnedPropertiesNOIn GetOwnedProperties:
- Disabled selecting 1st item of lstOwnedPropertiesNOIn 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 unownedpropertiesCalling the the frmManagePropertiesAndOwners is terribly slow.
I think that's because multiple procedures are called multiple times.
Need to look into that sequence yet.
Tsjallie
Great, I will take a look this evening!
Thanks couldn't do this with you!!
u3rick
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
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!
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.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?
Over time the flow is being poluted by adding things time-over-time.
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
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.
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".
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
Tsjallie
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)Is this the layout (look) you're gonna use throughout the workbook or will you continue using these complete different looks?
Thanks
u3rick
wrong file attached in one of the last posts
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks