Populating forms with new or existing data and saving to a data sheet.

Tsjallie

I'm affraid the trick of using a combobox as a button (Save-and-close or Save-and-new) isn't such a good idea afterall. Will try to figure out something alike.

Late last night it started working had something to do with one of the "Save and close" or the "Save and new". I retype and it started working on my version anyway! Btw: it didn't fix the color background problem if thats part of what your talking about!

Easiest (and most appropriate) way to do that is that have the DoubleClick event respond to double-clicking the Owners column. Then Target will automatically contain the UnitOwner.

Sounds good!

I will keep plugging away making some design changes will have a version for you to look at tomorrow I hope!

I try to give you "Reputations" every time! It just refuses, sorry!

Thanks

u3rick

Allocating the rowsources should be done like this:

Private Sub cboType_Change()

      Select Case cboType.Value
                  Case "Invoice"
                        lblTypeHL = "Invoice"
                        'cboAction.RowSource = cboInvServList.Value
                        cboAction.RowSource = "cboInvServList"
                  Case "Credit memo"
                         lblTypeHL = "Credit memo"
                         'cboAction.RowSource = cboCrServList.Value
                         cboAction.RowSource = "cboCrServList"
                  Case "Check"
                        lblTypeHL = "Check"
                        'cboAction.RowSource = cboChkServList.Value
                        cboAction.RowSource = "cboChkServList"
                  Case "Payment"
                         lblTypeHL = "Payment"
                         'cboAction.RowSource = cboPyServList.Value
                         cboAction.RowSource = "cboPyServList"
            End Select
            
End Sub

About this invalid procedure call.
This is what happens when you click the "Save-and-New" button:

1. The procedure cboClose_Click will be fired
2. At the end of this procedure the Transaction form (frmTransNew) will be initialized
3. Part of the initialization is making cboAction empty
4. This will trigger the cboAction_Change event to be fired (feel 'm coming?)
5. In this procedure the Memo text attached to the service in cbo_Action will be looked up
   But cboAction is empty! So the lookup will give no result and so RecIdx will have an error code (error 2042) instead of a record number
6. Reading from the Services table with this RecIdx will cause the error: Invalid procedure call

Capice?
So actually you shouldn't want the cboAction_Change event being fired.
Do notice that you cannot disable form events. The command Application.EnableEvents only enables/disables worksheet events.
That's where this public variable FormEventsEnabled comes in.
By setting the FormEventsEnabled variable to False (right before you change cboAction) and in the cboAction_Change procedure testing on this variable before executing the code (IF...THEN) will prevent the event procedure from being executed.
Don't forget to set the FormEventsEnabled variable back to True after the cboAction is changed.

You should use this technique everywhere you don't want form events to be fired (read: executed).

Tsjallie

Thanks, it was driving me crazy. I still am having a hard time recognizing the firing order. as well as Public and Private. But I feel i am improving!

u3rick

But I feel i am improving!

You are

This should do the trick:

    If Not cboAction.Value = "" Then
        RecIdx = Application.Match(cboAction.Value, tblServicesList.ListColumns("Service").DataBodyRange, 0)
        Me.txtMemo = tblServicesList.ListColumns("Memo").DataBodyRange(RecIdx)
    End If

BTW, I posted the diagram with the Main Proces Flow to the dropbox

In the properties of the lstTransactions list add a 5th column (ColumnCount = 5) to make room for the Action in the lstTransactions list
and set the last column width to 0 so it doesn't show in the frmOwnersPayableActivity.

Then - in the sub GetOwnerTransactionList - add the following lines to read the Action into the lstTransactions list

        Me.lstTransactions.Clear
        Me.lstTransTotals.Clear
        If WorksheetFunction.CountIf(.ListColumns("UnitOwner").DataBodyRange, UnitOwner) > 0 Then
            Me.mpgOwnersPayable.Pages(0).Enabled = True
            For Each TransRec In .DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
                i = i + 1
                For Each TransCol In TransRec
                    Me.lstTransactions.AddItem
                    Me.lstTransTotals.AddItem
                    Me.lstTransactions.List(i, 0) = TransCol.Cells(1, .ListColumns("Date").Index)
                    Me.lstTransactions.List(i, 1) = TransCol.Cells(1, .ListColumns("Type").Index)
                    Me.lstTransactions.List(i, 2) = TransCol.Cells(1, .ListColumns("Number").Index)
                    Me.lstTransactions.List(i, 3) = TransCol.Cells(1, .ListColumns("Memo").Index)
                    Me.lstTransactions.List(i, 4) = TransCol.Cells(1, .ListColumns("Action").Index)'Adds the Action to the end of the list, but it won't be shown as the column width is set to 0
                    Me.lstTransTotals.List(i, 0) = TransCol.Cells(1, .ListColumns("Total").Index)

Now - when double clicking the list - the action will be available in the sub lstTransactions_DblClick

To use it there add the following line:

                .txtTypeIn = Me.lstTransactions.List(Me.lstTransactions.ListIndex, 1)
                .cboActionIn = Me.lstTransactions.List(Me.lstTransactions.ListIndex, 4)
                .txtTotalIn = Me.lstTransTotals.List(Me.lstTransactions.ListIndex, 0)

Also notice that when getting the total from the lstTransTotals list the index of the lstTransactions list is used. That's the link between the two lists.

BTW, I uploaded the working version of ES Reservation_v3.0.02.xlsm to the dropbox.

Here are some code templates as promised.

Form event

Private sub objectname_event

    If FormEventsEnabled then
        FormEventsEnabled = False
            '======================
            'Code goes here
            '======================
        FormEventsEnabled = True
    End if

End Sub

Read data from a table

Sub ProcedureName(RecId as ......., parm2 as ......, .....)
'================
'Procedure header
'================
    Dim tblTableName as listobject, RecIdx as integer

    Set tblTableName = worksheets("SheetName").listobjects("TableName")

    With tblTableName

        RecIdx = application.match(RecId, .ListColumns("IdColumn").DatabodyRange, 0)

       FormEventsEnabled = False 'In case changing a form object in one of the following lines would unwantedly trigger an form event

        FrmName.ObjectName = .ListColumns("DataColumn").DatabodyRange(RecIdx)
        'Other data to read
        '......
        '......
        '......

       FormEventsEnabled = True

    End With

    Set tblTableName = Nothing 'Clean up

End Sub

Update data in a table

Sub ProcedureName(RecId as ......., parm2 as ......, .....)
'================
'Procedure header
'================
    Dim tblTableName as listobject, RecIdx as integer

    Set tblTableName = worksheets("SheetName").listobjects("TableName")

    With tblTableName

        RecIdx = application.match(RecId, .ListColumns("IdColumn").DatabodyRange, 0)

       Application.EnableEvents = False 'In case changing data in the table in one of the following lines would unwantedly trigger an worksheet event

        .ListColumns("DataColumn").DatabodyRange(RecIdx) = FrmName.ObjectName
        'Other data to update
        '......
        '......
        '......

       Application.EnableEvents = True

    End With

    Set tblTableName = Nothing 'Clean up

End Sub

Delete data in a table

Sub ProcedureName(RecId as ......., parm2 as ......, .....)
'================
'Procedure header
'================
    Dim tblTableName as listobject, RecIdx as integer

    Set tblTableName = worksheets("SheetName").listobjects("TableName")

    With tblTableName

        RecIdx = application.match(RecId, .ListColumns("IdColumn").DatabodyRange, 0)

       Application.EnableEvents = False 'In case changing data in the table in one of the following lines would unwantedly trigger an worksheet event

        .ListRows(RecIdx).Delete

       Application.EnableEvents = True

    End With

    Set tblTableName = Nothing 'Clean up

End Sub

Insert data in a table

Sub ProcedureName(RecId as ......., parm2 as ......, .....)
'================
'Procedure header
'================
    Dim tblTableName as listobject, NwRec as Listrow, RecIdx as integer

    Set tblTableName = worksheets("SheetName").listobjects("TableName")

    With tblTableName

        Set NwRec = .ListRows.Add(AlwaysInsert:=True)
        Set NwRec = Nothing 'Needed it only once

        RecIdx = .ListRows.Count 'As the new record is always the last one

        Application.EnableEvents = False 'In case changing data in the table in one of the following lines would unwantedly trigger an worksheet event

        .ListRows(RecIdx).Delete

       Application.EnableEvents = True

    End With

    Set tblTableName = Nothing 'Clean up

End Sub

The templates above assume that the RecId is a unique identifier selecting precisely one row in the table.

If the selection criteria would select none or multiple rows in the table an other approach is needed.
Unfortunately now it's time for my beer.
So to be continued ...

Tsjallie

Sub ProcedureName(RecId as ......., parm2 as ......, .....)

Can you give me some examples of what goes in the above? Mostly where the dots are!?

Update: All I get is red letters when I try setting this up in workbook, so I am sure I am miss something or doing some wrong (Don't forget I don't know much)!

RecIdx = application.match(RecId, .ListColumns("IdColumn").DatabodyRange, 0)

FrmName.ObjectName = .ListColumns("DataColumn").DatabodyRange(RecIdx)

Are both the names in red shown above related to the Table(Worksheet)?

Also, I put the latest version in dropbox. When you have it opened Right click unit BBH 5125 then click Owner Detail tab then edit. This is as far as I got, I tried some of the template code but couldn't get the information to fill in from the Owners Table. Take a look and tell me how I should have done it please!

Thanks

u3rick

Here's an example of the template for reading data from a table.
Words in red are substituted with the actual names to be used.

Remember that there are multiple techniques to get this done. Some are better than others, but often it's also about personal preference.
More important is to choose one and use that consistently.
Something I myself am not very good in as you may have noticed

Sub FillOwnerDetailsPage(OwnerId As Double, Frm As UserForm)
'================
'Procedure header
'OwnerId = owner to get the details from
'Frm = userform to put the data in.
'Adding the Frm parameter makes the procedure useable for multiple forms having Owner details.
'In that case the procedure should be in a Module (like mdlESReservation)
'But you might as well leave it in the userform's codemodule. Then you should leave out the Frm parameter and replace the Frm-prefix at objects with "me".
'================
    Dim tblOwners As ListObject, RecIdx As Integer

    Set tblOwners = Worksheets("Owners").ListObjects("Owners")

    With tblOwners

        RecIdx = Application.Match(OwnerId, .ListColumns("Owner").DataBodyRange, 0)

        FormEventsEnabled = False 'In case changing a form object in one of the following lines would unwantedly trigger an form event

        Frm.OLastName = .ListColumns("OLastName").DataBodyRange(RecIdx)
        Frm.OFirstName = .ListColumns("OFirstName").DataBodyRange(RecIdx)
        'Other data to read
        '......
        '......
        '......

       FormEventsEnabled = True

    End With

    Set tblOwners = Nothing 'Clean up

End Sub

frmOwnerInfo("ChkNewOwner") should be frmOwnerInfo.ChkNewOwner

Ok, didn't get that yet.
Here's what happening:
in the cmdSave_Click() procedure of frmOwnerInfo you're storing the OwnerId as text where the Max-function calculates the highest numeric value which is the ownerid in the previous record (1000035).
So

.ListColumns("OwnerId").DataBodyRange(RecIdx) = Me.txtOwnerId

should be

.ListColumns("OwnerId").DataBodyRange(RecIdx) = CDbl(Me.txtOwnerId)

That's probably, because the last ownerid is still stored as tekst.
Just open the cell containing the last ownerid and press [Enter] to close it again.
That should make Excel store it as number. At my end that is.

You need to give me some more background on this. Guess you're talking about the file exchange to/from Lodgix?
Where would you be copying the table rows to?
There are several techniques to make sure you have stored what you received. But also depends on how the data is delivered and in which volumes.
You could save the whole workbook after each record or you could save the workbook at the end after checking everything is there.
A procedure to recover if something went wrong is also a way to go.
In short the best technique to use largely depends on the criticallity of the data, the delivery technique, time dependency etc
If this is about exchanging data with Lodgix, is there some kind of manual on these matters which you could upload?

Sorry! I didn't explain it well. I was talking about when data is copy to a table(Sheet) within the current workbook. Like Invoices Credits etc., and I want it to be saved as unloaded. Otherwise if you crash or close while not thinking about it the data will be missing and lost. I have not started on Lodgix yet will do manually at first.

u3rick

.... data is copy to a table(Sheet) within the current workbook...

Maybe I'm missing something, but wouldn't that normally be done by formulas in the target sheet (or table) referencing cells in the source sheet (or table)?

Anyhow, saving the workbook after each row is copied will be killing for performance.
Is there an example of this issue in the workbook? Can you point me to that so I can have a closer look.

And one other thing for you to consider about deleting records.
In a system I developed years ago for my company and where auditing is top priority, I didn't fysically delete or overwrite any (important) records but just deactivated them.
This gave a very nice audit trail showing a complete history of all changes made to these records.
It was implemented by just adding 3 columns to each important table: Active_YN, Date, User and - of course - have the procedure handle updates and deletes accordingly.
Such a table could like something like this:

OwnerId	FirstName	LastName	Active_YN	Date	User
1000035	John	Doe	N	11/09/2015	Tsjallie
1000035	Jane	Doe	N	11/10/2015	Tsjallie
1000035	Joe	Doe	Y	11/11/2015	Rick

Consequence of this approach however, is that you would always need multiple criteria for a unique identifier, but that shouldn't be very exciting.
Major pro's of it would be that it would offer you the possibility to rollback changes and you won't need additional change logging.
It's just a thought I didn't want to keep from you.

Tsjallie

Maybe I'm missing something, but wouldn't that normally be done by formulas in the target sheet (or table) referencing cells in the source sheet (or table)?

What happens if you enter info into the Userform then insert that data into a table and then you close without saving? is the data in the receiving table still there?

I do want to implement post# 231, do I add the needed columns to all tables? I think most tables have date and some have Active?

If you close the workbook without saving it all changes made since the last time it was saved will be lost.
You can prevent this by forcing Excel to automatically save the workbook when it's closed.
Enter this event code into ThisWorkbook codemodule:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False 'Suppresses Save Y/N message
    ThisWorkbook.Save 'Save the workbook
    Application.DisplayAlerts = True
    Application.Quit 'Quit Excel
End Sub

But be aware that the workbook will also be saved when unwanted.

I do want to implement post# 231 do I add the needed rows to all tables I think most have date and some Active?

The date could be used, but the Active column you're using has an other meaning.
Better call the column Deleted_YN and have "Y" in it when deleted and "N" when not.
To do the lookups with still one criteria you could add yet another column concatenating the Id and the Deleted_YN column making it a unique identifier.
So (e.g) with the Owners table in this column you would get a value "1000035N" for the active record and "1000035Y" for any deleted record.
Looking up the Owner could then be done with the value (e.g.) txtOwnerId & "N".
Notice that in this case you would be surging a column with text value, so you would not have to convert the OwnerId to the numeric value with cdbl(txtOwnerId).

I wouldn't implement this in all tables, just the ones you think are important to audit.

Tsjallie

But be aware that the workbook will also be saved when unwanted.

That could be a pain for us, but don't you think that is preferable for End Users?

Could you take a look at version 5.2.10 and tell me why the form box txtFName is not turning red when tabbed when blank? Also run from the new Owner button and check code this might not be the best way to do this if there is a better way let me know?

This is because the background style of the textbox is set transparant.
Add this line:

txtFName.BackStyle = fmBackStyleOpaque

Or just set the BackStyle in the properties of the textbox

That could be a pain for us

I certainly wouldn't turn this on while developing the workbook

Where do I pickup the User

Application.Username will return the user.

.... but its a lot to answer so may take a couple of days to upload my version!

And this is only a start. My time has come to make your life miserable with all kind of nasty questions and chalenges
But, somehow I feel your gonna surprise me!

Take your time. A proper design will save you an awful lot of time later!

This unique identifier is the Primary Key of a table plus it's Deleted_YN column.
Because in the PropertyOwner table (as it was originaly) OwnerId and PropertyId together make up the Primary Key the unique identifier would be OwnerId & PropertyId & Deleted_YN.
Lookup would be done with (e.g.) txtOwner & txtPropertyId & "N".

Yep, that's what I mean.
One small change though:

=([OwnerId]&""&[PropertyId]&""& [PODeleted_YN])

should be (no need for the quotes)

=[@OwnerId]&[@PropertyId]&[@[PODeleted_YN]]

For readability you could also add dashes between the 3 fields

=[@OwnerId]&"-"&[@PropertyId]&"-"&[@[PODeleted_YN]]

Could you help me figure out the Right click and double click on the OwnersPayableList sheet. I tried to redo with out using UnitOwner as Identifier. Not much luck! (v5.2.14)

Worksheet_BeforeRightClick

• frmOwnersPayableActivity.txtOwnerId = Target -> This will fire the Userform_Initialize() procedure of frmOwnersPayableActivity
• When the Userform_Initialize() procedure is excuted the OwnerId will not yet have arrived at the txtOwnerId. So txtOwnerId will still be empty.
• Therefore you need to move the call to FillOwnerInfoOPA to a txtOwnerId_Change() procedure.

Also the FillOwnerInfoOPA expects the OwnerId as a numeric value (double).
So the call should be

FillOwnerInfoOPA CDbl(Me.txtOwnerId)

FillOwnerInfoOPA

• Me.txtUnitOwner = .ListColumns("UnitOwner").DataBodyRange(Recidx) -> This will fire the txtUnitOwner_Change() procedure
• The txtUnitOwner_Change() procedure will call the FillOwnersPayablePage() procedure with the txtUnitOwner
• In the FillOwnersPayablePage() procedure the PropertyOwners table is filtered on column 3 (= PODUniqId) with the UnitOwner.
• So
  

  .AutoFilter Field:=3, Criteria1:=UnitOwner

  should be
  

  .AutoFilter Field:=12, Criteria1:=UnitOwner

• Me.txtOwnerAddress = .ListColumns("OwnerAddress").DataBodyRange(Recidx) give an error "subscript out-of-range"
• This is because the column name should "AddressOwner"
• frmOwnerPayableActivity.Show gives an error "object required"
• This is because the name of the form is misspelled.
• You can also remove the line frmOwnersPayableActivity.Show, because is aready shown in the Worksheet_BeforeRight() procedure

GetOwnerTransList

• .AutoFilter Field:=4, Criteria1:=UnitOwner -> will leave no rows
• This is because UnitOwner contains a value "x BBH 5125/Frabasilio, Ron", where the UnitOwner column in the table has a value "x BBH 5125/Frabasilio, Ron".
• The value in the table has an extra space between x and BBH

Noticed an error in the lookups for the OwnerId, UnitOwner and Phone in the OwnerPayableList sheet.
This is because

• the INDEX-formula is referencing the sheet rows in the PropertyOwners table instead of the data rows
• the part calculating the k-parameter in the SMALL-function should reference the header row of the OwnerPayableList table

The formula for the OwnerId should be like this

=IFERROR(
           INDEX((tblPropertyOwners[OwnerId]),
                    SMALL(--(tblPropertyOwners[POActive]="Y")*ROW(tblPropertyOwners)-tblPropertyOwners[#Headers],
                              COUNTIF(tblPropertyOwners[POActive],"N")+ROW()-ROW(tblOwnersPayableList[#Headers]))),
   "")

The formula for the UnitOwner should be like this

=IFERROR(
           INDEX((tblPropertyOwners[UnitOwner]),
                    SMALL(--(tblPropertyOwners[POActive]="Y")*ROW(tblPropertyOwners)-tblPropertyOwners[#Headers],
                              COUNTIF(tblPropertyOwners[POActive],"N")+ROW()-ROW(tblOwnersPayableList[#Headers]))),
   "")

The formula for the Phone should be like this

=IFERROR(
           INDEX((tblPropertyOwners[Cellular]),
                    SMALL(--(tblPropertyOwners[POActive]="Y")*ROW(tblPropertyOwners)-tblPropertyOwners[#Headers],
                              COUNTIF(tblPropertyOwners[POActive],"N")+ROW()-ROW(tblOwnersPayableList[#Headers]))),
   "")

It are still array formulas, so close 'm with [Ctrl]+[Enter]

Sorry, my mistake.

tblPropertyOwners[#Headers]

should be

ROW(tblPropertyOwners[#Headers])

Tsjallie

I have put latest version in Dropbox and attached but looks like you have fixed.

Update: Now it works fine!

BTW: Could you look at the AdvancedDepositLedger in v5.2.16 and see if there is a formula that will keep a running balance for the whole acct. I assume I will need another column to get a running balances for each invoice #?? This is the ledger that will record the data from Lodgix AD report. I think if possible heroes where I need a button that could tell Lodgix to run the AD Report. Then it would copy the selected data to the AdvancedDepositLedger. This button would be run when Bank Deposits are posted to QB's by VBB staff. I think the correct posting for both accounting systems will be the (QB's 1010 Advanced Deposits gets debit and offset with credit 6900 Suspenses) (ManagementAccounting 6900 Suspenses get debit and 2100 Advanced Deposit Credit). The invoice will be track in the Management Systems accounting until Checkout date at which time the final posting to both clears the Suspense Acct's for that Invoice to 0.This leaves the 1010 Bank Account in QB's with Vacationville's portion of the revenue offset to proper Income acct. The Invoice History stays in the Management System where I think it belongs. Leaving QB's to Handle Vacationville's Income, Expenses, Payroll and Taxes and the management system to track all rental relate data.

Thanks

u3rick

a formula that will keep a running balance for the whole acct. I assume I will need another column to get a running balances for each invoice

You can calculate a running total per invoice with this formula

Formula:

=SOMPRODUCT(--([InvNo]=[@InvNo]),--([Deleted_YN]="N"),[Paid])

Calculating a running total for the account could be done with

Formula:

=SOMPRODUCT(--([OffsetAcct]=[@OffsetAcct]),--([Deleted_YN]="N"),[Paid])

Both formulas will exclude any deleted records.

This is the ledger that will record the data from Lodgix AD report

Can you post a sample of such a Lodgix AD report in the dropbox?
If necessary anonomize the data.

I think if possible heroes where I need a button that could tell Lodgix to run the AD Report

Is there a command string which can be sent to Lodgix?

Then it would copy the selected data to the AdvancedDepositLedger

You could automate importing the data form the Lodgix AD report, but you can't do that in an Excel table (thk u Microsoft!).
So that needs an additional trick.
If you can post such report I can make up a example for that.

Well I have a good Idea of where thin will be done, that is pretty much directed by the Programs (Lodgix, QB's).
I mainly questioning the makeup of the accounting part of the management system, and the number of tables needed, what information to hold in those tables, etc.

Can you check the functions listed in the functional decomposition of the CRUD-matrix in the VacationVille Adminstration Model.xlsx.
Is that list complete? Which functions should be covered by in the Management System?

It seems like QB's uses one huge table? Is that best for our purpose?

Depends if it's still manageable in that layout. May be it's better to split it up in the Management System.
If you need to generate an export file this can be collected from different table.

Found some interesting info here: http://docs.lodgix.com/m/5502/l/51950-financial-reports
I would specially be interested in the Data Dump Report

SOMPRODUCT should be SUMPRODUCT

I'll get my coat

Hate to spoil your fun , but the formulas in the Balance columns don't work correct.

Formula:

=SUMPRODUCT(--([InvNo]=[@InvNo]),--(([Canceled_YN]="N")=([CompletedInv_YN]="N")),[Paid])

Problem is this part:

Formula:

...--(([Canceled_YN]="N")=([CompletedInv_YN]="N"))...

This only returns 1 when Cancelled_YN and Completed_YN are equal. So either both TRUE or both FALSE, while it should only return 1 if Canceled_YN and Completed_YN are both "N".
So the formula to work correctly should be

Formula:

=SUMPRODUCT(--([InvNo]=[@InvNo]),--([Canceled_YN]="N"),--([CompletedInv_YN]="N"),[Paid])

BTW, what happened to Deleted_YN?

I'm looking at automatically updating the AdvancedDepositLedger sheet from the Advanced Deposits Report.
Some questions about this AdvancedDepositLedger sheet:

• Where do you get the Account from?
• What are columns Dr and Cr (both copy the value of the column Paid)?
• Where do you get the OffsetAccount from?

Ok, got it!
It helps that I used to be an accountee like you, but that's 3 lives ago (may be more)
So I have to dig deep sometimes...

Overlooked one column:
Where is the TotalBookingChgs coming from? It's not in the Advance Deposit Report coming from Lodgix.
Is that the total charge for the whole rental period?

Still chewing on this AdvanceDepositsLedger. Shouldn't that have the journals you described?
If so, I would think that importing the Advance Deposit Report record would trigger the generation of these journals and post them to the AdvanceDepositsLedger.
Hmmmm....
Where does this Advanced Stays Ledger come in?

Overlooked one column:

It is in the Advanced Deposit Report the column is hidden, Sorry!

I would think that importing the Advance Deposit Report record would trigger the generation of these journals and post them to the Advance Deposits Ledger.

Yes you are right, but I notice in QB's (as close as I could tell) it looks like a report that they pull off of the Ledger table. But I am not 100% on that so, we can do the way it is easiest to program.

Where does this Advanced Stays Ledger come in?

Its one i am thinking on but should be for advance payments on ES rentals. Sometimes renters pay ahead. But I think it can be handled in The Advanced deposits ledger instead. Thats why I add the Type column using SV or ES to show kind of activity.

Update: What I should have said here, is that I changed the name of Advanced Deposit Ledger to Advanced Stays Ledger. The reason is I added the possibility of posting prepaid extended stay rent to this ledger. So I changed the name!!

u3rick

check my formulas inAdvancedStayLedger sheet to see if my corrections are right.

Are the formulas in columns Dr and Cr meant to distribute the amounts in column Paid over the columns Dr and Cr, so amounts equal or larger than 0 would go into column Dr and amount smaller than 0 would go into column Cr?
If so then

• the formula in column Dr should be like
  Formula:

  IF([@Paid] < 0, 0, [@Paid])

• and the formula in column Cr should be like
  Formula:

  IF([@Paid] < 0, ABS([@Paid], 0)

Notice the [@Paid]. This looks in the Paid column on the same row. Without the @ it references the entire column.

The formulas in columns InvBalance and AcctBalance, TotalBookingChgs, TotBkingChgsByUnit and TotAcctBalBkingChgs are OK.
What's the function of CompletedInv_YN ?

Do we still need tables to move InactiveOwners, InactiveProperties, TransHistory

Active is a property of Owners and Properties. So that should be a column in the Owners table or the Properties table resp.
Therefor no need for the InactiveOwners and InactiveProperties tables (and alike).

History is built and maintained with the Deleted_YN (or Cancelled_YN?) column. So no need for TransHistory or any other specific history tables.

Rick,
here is a demo of a procedure to automatically import the Advance Deposit Report into the AdvancedStaysLedger.
The procedure has some prerequisites to execute:

• The folder where the demo resides needs to have the subfolders "Inbound" and "Archive"
• The folder "Inbound" needs to contain one or more files named like "Advance_Deposits_Report*.xls"

The demo performs the following actions:

1. Checks for the existence of the folder "Inbound"
2. Checks if the folder "Inbound" contains one or more files named like "Advance_Deposits_Report*.xls".
3. If any such files are found ask whether they should be imported now
4. Check if the record structure of the import file has a valid record structure
5. Strip any unnecessary rows from the import file to keep only the data (2 header rows and a totals row)
6. Convert the remaining data rows in the import file to a table
7. Copy these data to the AdvancedStaysLedger table extending it for each imported row
8. Close the import file without saving it
9. Move the import file from the "Inbound" folder to the "Archive" folder

Important is that the structure of the Advance Deposit Report is always the same.

Demo is also in our dropbox.

check formulas in OwnerTransLedger and ServiceList

Why aren't you using row 2 in the OwnerTransLedger table?

Will look at the formula tomorrow.

Some Reservation Systems cancel the invoice # if any changes are made, but Lodgix does not.

So an invoice number can occur multiple times in the AdvancedStaysLedger, but only the last one will have "N" in Deleted_YN and - apart from the first record - they're all changes to the previous record. Right?

In that case indeed the import procedure should check for any previous records with the same invoice number and set Deleted_YN in that recod to "Y".
Remember that in this system a record is never physically deleted or updated, but versions of it are kept and all previous versions ae marked Deleted.

I will extend the demo to check for changes and mark previous versions of a record as Deleted.

This report only lest us pick two things end date and weather to use Check-in or check-out date as its fulfillment date. (We always use check-out date).

Don't understand what you mean by this. Can you explain?

If I have a unit cleaning chg that goes on an owners acct.

What is a unit cleaning chg?

Then I need to post it to The Accts Payable Ledger, the Journal and the General Ledger, Can I do this in the same code or do I have 3 separate Sub

If these always go together then you better put 'm together in one sub. If you also need to execute these individually then split 'm in separate subs.

Checked the AdvancedStaysLedger table.

Formula:

=([Number]&"-"&[OwnerId]&"-"&[Deleted_YN])

should be

Formula:

=([@Number]&"-"&[@OwnerId]&"-"&[@[Deleted_YN]])

Notice that the syntax for Deleted_YN is different. That's because of the underscore in the column name.

Rest of the formulas seem OK. You're getting the hang of it

But there's something else going in this table.
Depending on what checks you have set on formulas (see File -> Options -> formulas -> Rules) you may notice the red triangles at the topleft of the cells in some columns.
These indicate there's an inconsistency in the formula of the column.
And - for instance - you will see that, when adding a new row, the column UnitOwner contains a formula (with errors) which you probably thought to have removed. Same goes for some other columns.
This is (what I consider) a bug in Excel. It happens that "under the hood" things like formulas remain present in the table definition and so are copied to new rows.
Solution is easy: just copy the entire table to the clipboard and immediately paste it back. Done.

BTW, what's the source of these data?

I was going to say happy Thanksgiving then realized you don't have that holiday!

I'll have a beer anyway! Don't need a holiday for that

Tsjallie

I have loaded a new version of Management system in Dropbox (v6.0.4) and attached here. I have dummied up the Journal and and General Ledger sheets to look as they should in showing the data. I have looked on the internet and found no direction on how to set up the sheets to make them work as setup. So maybe you can look at these sheets and shed some light on this for me?

I think Ledger entries should be triggered with each entry to the Journal. As I see it, the Journal is trigger by events or entries to the special Ledgers. I am trying to assimilate a list of these events that trigger entries to the Journal. An event in my mind would be a Stay's ending date or an ES Reservation triggering a credit to a owners account for next months rent, as well as to the Guest for Rent due. Is this thinking in line with yours?

Also made some changes in special ledgers you can look at!

Every time I think I am making headway I really only find 20 more Questions!! Lool!

Thanks

u3rick

I think Ledger entries should be triggered with each entry to the Journal. As I see it the Journal is trigger by events or entries to the special Ledgers. I am trying to assimilate a list of these events that trigger entries to the Journal. An event in my mind would be a Stay's ending date or ES Reservation triggering a credit to a owners account for next months rent, as well as to the Guest for Rent due. Is this think in line with yours?

Yep, that's exactly the way I think the core of such apps should work. Together with some ad hoc procedures like reporting, archiving and alike.

Also made some changes in special ledgers you can look at!

What sheet is that?

No problem. But the functionality wouldn't be in the DatePicker.
It would (probably) be a userform with a selectionbox and 2 dateboxes (controled by the Datepicker).
When selecting a period from the selectionbox a procedure would be executed with a select case construct.
Each case would handle a period and calculate a startdate and an enddate for it.
The calculated dates would then be fed into the dateboxes and there you are.

I've been looking at the Ledger.
Getting that right is not just a matter of some neat formulas, but is firstly a design issue.
I will try to get back to you tomorrow with a proposal to get this fixed. Need to (mentally) check the setup I'm thinking of yet.

Feeding the Ledger.
Here's what I'm thinking of how to feed data into the Ledger.
Either from external sources, the ES Reservation Calendar or manually.
The GeneralLedger sheet has a diagram showing the process flow, a process description and a sample of newly introduced IN/OUT-table in it.
The latter possibly could be replaced by the Journal. Not sure yet.
The whole process is event based.

Let me know what you think.

Something that's been worrying me for quite some time now: will the workbook(s) be used (and updated) by multiple users simultaneously?

No problem. But the functionality wouldn't be in the DatePicker.
It would (probably) be a userform with a selectionbox and 2 dateboxes (controled by the Datepicker).
When selecting a period from the selectionbox a procedure would be executed with a select case construct.
Each case would handle a period and calculate a startdate and an enddate for it.
The calculated dates would then be fed into the dateboxes and there you are.

What the best way to make the addition to the date picker, if I want to use it in many reports. Can you put forms in forms?

What the best way to make the addition to the date picker, if I want to use it in many reports. Can you put forms in forms?

You don't need to change the datapicker.
Just make a workbook (or use an existing one) and create a new userform.
Add a selectionbox and 2 textboxes to that userform.
Assign a list of reporting periods to the selectionbox.
Finally write a procedure which fires when a selection is made (change event of the selectionbox).
This procedure should calculate a start date and an end date based on the selected reporting period.
Best to do this in a select case construction.

You can use the datapicker like in the other workbooks.

I think 99% of the time i would say no, but I can't say about that other 1%. I assume thats a big problem?

It's not a big problem and if it's not necessary for 99% of the time then it's also not urgent.

Making the workbook multi-user could (and in my opinion should) be done by moving the data to a database like Access. Technically not very exciting, but if it's not urgent I would park that for later.

There are however some other reasons for separating the data from the functionality:

• Your workbook is largely a database application. Most of it comes down to selecting, filtering, updating of data records.
  Only exception is the Calendar. The purists among us will say you should develop a database application on a database platform.
• An other argument would be the ability to easily change the application. If the application is being used while you're adapting it, you will need to make sure the new version has the latest data when making this new version available to the users. Dissociating the data from the functionality (which is changed) would solve that. But this can also be done within Excel without introducing Access.

BTW, as we are more and more discussing design, I think it's appropriate to start a new thread focussed on that.
A nice title for that would be: Designing an Excel application for simultaneous use and for optimal maintainability.

Two things did not work correctly; 1) If you pick a date from dtbxFrom or dtbxTo the cboTimePeriods should change to "Custom". 2) If you use the cboTimePeriods it should redo the choice you make. That seemed to work except All Dates would not clear dtbxFrom or dtbxTo.

There are a few things you need to change to get it working OK:

• Move the code setting the selected item in cboTimePeriods back to "Custom" from dtbxFrom_Click() to dtbxFrom_Change(). Same goes for dtbxTo.
• In dtbxFrom_Change() as well as dtbxTo_Change() check for the FormEventsEnabled variable. Only execute the code if true.
• When executing the cboTimePriods_Change() procedure start with setting the FormEventsEnabled variable to False. Set it back to True at the end of the procedure.
• Declare the public FormEventsEnabled variable as boolean in the mdlTimePeriod
• In the cboTimePeriods_Change() procedure change Case "All dates" into Case "All Dates". It's case sensitive.

I also see you using a very peculiar way to call functions. Never seen it being used like that and working

How do you want it to be written for this project?

Well, it's not about what I want, but normally a function would be called like

myVar = myFunction(parm1, parm2, etc)

But if your way works fine, why bother?

Few other things though (just informational):

• You're adding the name of the module containing the functions (qualifying) while the functions are declared public.
  In that case they are available throughout the workbook and so qualifying is not necessary.
• 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.