Posts by lawsonrw

Private Sub Button_Run_Click()
    
    Dim Passcode As String
    
    Passcode = TextBox.Input_Passcode.Value
    
    Me.Filter = Passcode
    Me.FilterOn = True
    
End Sub

I have a MS Access Form object named Input_Passcode. This is a TextBox where a user may input their passcode and retrieve filtered content in their form.

On the same form is a Run button. When the user clicks the Run button, I want the button to take the value in Input_Passcode, and filter the form.

I don't know if TextBox.Value is the correct property to accomplish this but I'm not finding any real examples of the correct syntax.

Please help. Thanks.

Hello, World!

I always think it helps to start with the narrative and design goals, if you don't need this, please jump to the part you're interested in. My manager has tasked me with sending out emails to 165 managers, who collectively have 816 employees. These 816 employees have not completed their annual training(s). I'm asking the managers to respond by email with an update of their team members' progress, which can be (1) Incomplete the default, (2) Complete, (3) Exempt, (4) On Leave, (5) Separated.

I think it would be much easier for everyone to self-manage this process than me dealing with more than 165 emails and trying to keep track of everything, and am making my first real attempt at an MS Access database. I think its cleanest if, upon opening their forms, the managers only see it come up blank. For sake of managing access controls/signatures, I only want them to see their team members, not all 816 records.

I've created two tables inside my database. Those are WorkingTbl and Dispositions. The former contains all the relevant data for each incomplete training. The latter contains the 5 types of dispositions. The relationship is defined from Disposition to the Condition field of WorkingTbl. A Query, WorkingTbl Query, and Form, Annual Compliance Training, sit on top of these foundational tables.

I'm trying to have some VBA to (1) get the form to open as blank and (2) get the "run" button the filter WorkingTbl by its "Passcode" column. So if a manager only has 6 employees and they all have her passcode stored in their "Passcode" field, then those 6 records should be available to that manager. They can then go in, change the disposition for each employee, and save and exit.

And I guess the next part after this is being able to send the managers a version of the form only, such that they can make their updates and "send" the updates to my "Source" file which will then update the "Condition" column of WorkingTbl with the new disposition.

Hello, World!

By no means am I an Access expert but I've used it in previous roles and I know it's quite powerful. I have to send notifications to 165 managers that they have specific team members who are out of date on certain trainings. All 165 managers email me back with notes like "this person left the company, that person is on leave, the third just completed the training." In total, I have almost 1,000 records, so you can imagine how stressful it is to keep all of this up to date. I think its going to be much easier for everyone to self-manage their team's compliance responses.

Can I use Access to:

(1) hold a "primary table" that has all the pertinent data for each record

(2) assign each manager a 4-digit "passcode." The pass code will be useful for the next step

(3) create a single form where the manager inputs their passcode and presses a "Run" button which retrieves all the records (team members) under them, show what training their out of date on, and a combo box to disposition that training, options being "Incomplete (the default), Complete, Exempt, On Leave, and Separated."

(4) once the manager dispositions each record, they hit update, this updates the records in the primary table's "disposition" column, if they close out and come back in, and hit run, the new dispositions should show/be saved

(5) I can then run a few reports, one for each disposition, so I can then tell the appropriate parties to update their records for employees who have left the company, are on leave, etc.

Enter the KM's you rode in Cell F1 of Sheet "Report" and click the button.

Finished it myself, please find the necessary code below:

This code helps in creating PO numbers that are specific to the user's initials:

Function GetFirstLetters(rng As Range) As String
Dim arr
Dim I As Long
arr = VBA.Split(rng, " ")
If IsArray(arr) Then
For I = LBound(arr) To UBound(arr)
GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
Next I
Else
GetFirstLetters = Left(arr, 1)
End If
End Function

I saved the following as a single module since its all the "worker bees":

Sub ResetDropDowns()


Dim rngLists As Range
Dim ListCell As Range


On Error Resume Next
Set rngLists = Sheets("Purchase Order Form").UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0


If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
Next ListCell
End If


End Sub


Sub PrintAndSave()
Call Send_PDF("SendEmail")
End Sub
Function Send_PDF(Optional action As String = "SaveOnly") As Boolean
Dim Thissheet As String, ThisFile As String, PathName As String
Dim SvAs As String
Dim PONum As Variant


Application.ScreenUpdating = False


Thissheet = ActiveSheet.Name
ThisFile = ActiveWorkbook.Name
PathName = ActiveWorkbook.Path
PONum = Range("J11").Value
SvAs = PathName & "\" & PONum & ".pdf"
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("TempTbl")


On Error Resume Next
ActiveSheet.PageSetup.PrintQuality = 600
Err.Clear
On Error GoTo 0


On Error GoTo RefLibError
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
On Error GoTo 0


If action = "SendEmail" Then
On Error GoTo SaveOnly
Application.Dialogs(xlDialogSendMail).Show
On Error GoTo 0
GoTo EndMacro
End If


SaveOnly:
MsgBox "A copy of this sheet has been successfully saved as a  .pdf  file: " & Chr(13) & Chr(13) & SvAs & _
"Review the .pdf document. If the document does NOT look good, adjust your printing parameters, and try again."


Send_PDF = True
GoTo EndMacro


RefLibError:
MsgBox "Unable to save as PDF. Reference library not found."
Send_PDF = False
EndMacro:
End Function


Sub SaveAndClose()
ActiveWorkbook.Close SaveChanges:=True
End Sub

And calling it all with a single button:

Sub CompletedPO() 
Call PrintAndSave 
Call ResetDropDowns 
Call SaveAndClose 
End Sub 

And you can see it all run together in the attached file. I hope someone else finds this helpful. Thank you to the community of coders all over the world who left breadcumbs for me to follow ♡♡♡♡♡♡♡♡♡♡

Any help, please?

LOL, I mentioned I would continue to massage the file, I realized I didn't discuss how I'd like the output to appear. The updated attached file has TempTbl filled in, this is more or less how I'd like to receive the PDF.

Some additional thoughts:

The printArea will need to automatically expand. On a day to day basis I'm restocking no more than 5 or 6 items, but should it ever be longer than that the printArea shouldn't leave anything out.

Everything above Row 28 is basically copied over from the Purchase Order Form Worksheet.

I'd like the saved PDF file name to be the purchase order number, extracted from cell J11 of either the TempTbl worksheet or the Purchase Order Form worksheet.

As mentioned in the original specs, once the PDF has been emailed off it resets TempTbl to be blank, and also resets Purchase Order Form dropdown to blank and wipes the textbox as well.

Hello!

I need help with finishing my excel file. The idea is (1) one of my employees goes into the file and (2) selects which items need to be restocked then (3) presses a button which (3a) copies the selected items to the TempTbl worksheet and (3b) automatically index(match) each item to the Fulfillment columns from InventoryTbl then (3c) alphabetically sorts this new table first by fulfillment then by item so that it may finally (4) print to PDF, email me the PDF, then wipe TempTbl clear. Once the email is sent, the form resets all dropdowns on the form to blank, saves the file, and notifies the user the email was sent with a thank you message, once they click "Ok" it closes Excel.

*it will send the file from a microsoft outlook email account that's already logged into the Windows user.

*i will ideally password protect and/or hide the Admin worksheet so that it is only accessible to an admin, the end user in this case should only interact with the form.

*the file is hosted in a OneDrive folder and shared with local users on the PC. I imagine it might be logical to store the saved PDFs to a subfolder in the same shared folder as the file, lets call that Subfolder "PO Archive" for working purposes

*I really only want the end user to be able to (a) manipulate the dropdowns, (b) add text to the text box under notes and and (c) submit the form. Ideally they won't see any of the formulas or behind-the-scenes machinations of how the file works (less room for them to mess it up) ... i know there is a way to hide formulas and lock cells, I can do this myself but just stating that I will probably continue to massage the file

*I'm clearly not a professional at this, so if you see any opportunities to streamline this, please go for it!

The file is attached.

Thanks in advance for all your help!

Carim

HAPPY NEW YEAR!

Now that I'm ready to add the actual funds and data the information isn't flowing up to the Master Table. Attached is the file with my first edit. When I run the macro on the Admin worksheet it will update Table1 just fine. But this new activity is not flowing over to Table2 the Master Table worksheet.

Quote from KjBox

Just been having a play with your file.

If you want something that is straight forward and simple for everybody to use then I think using user forms and multiple buttons is the way to go.

In the attached file there is a hidden sheet that is a template for creating new T-Account sheets, there is also a hidden sheet named "Report" which holds the Funds Table and is where you should add your pivot table and slicers. The hidden "Admin" sheet contains 3 tables that are used for the user forms comboboxes lists.

The first button, when clicked, will open a user form where there are options to either update an existing T-Account or create a new one. The other buttons are self-explanatory.

Note all the T-Account sheets can remain hidden so the workbook is not cluttered.

If you ever need to print the Master sheet then the buttons will not get printed.

Display More

KjBox, wow!!!

If I totally had my say, this would be the implementation. But my managers are .... change averse. They've been using this old t-account setup for more than a decade, they've all been here more than a decade, and they kinda go crazy when you try to change something. I really appreciate your efforts and I'd use it if I thought it had a chance of winning support from my bosses but from my 2 years of being here, I can say that is beyond unlikely.

Some background for anyone who might even care:

We are a large healthcare nonprofit that is more than 100 years old, as is common, we invest excess cash because keeping it in "cash" is a losing position when you consider inflation. Our old method was to open an individual account at our custodian bank for each investment. So think of it this way: Your 401(k) might have five mutual funds (for example). Thats 1 account with 5 investments. We used to open a separate account for each investment, so using our method, you would have 5 separate 401(k) accounts, each holding just a one investment! This got to be burdensome and expensive.

So we're transitioning to a Master Trust structure wherein all the investments are held in a single account, this would move us back towards the 1 account with 5 funds in the above example. It used to be very easy to reconcile investment activity because each account only showed activity for that specific investment. But now we're going to have 66 investments in one account. For audit reasons, we need a "master table" that reasonably matches our custodian bank. But for us to track the individual actions of each investment, we still need to see activity from that perspective.

I'm going to be creating "closing entries" on all 66 of the old t-accounts and kinda starting fresh with this new structure, but management is extremely touchy about being able to do things the way they've always done them ::insert eye roll::

Again KjBox, thank you!!!

Quote from Carim

In order to be coherent ... the Admin tab has been moved to the first position in workbook...

Indeed, since your Master Table sheet is entirely dependent on the complete list of Funds,

the ' Index & Admin ' macro needs to be launched BEFORE the ' Master ' macro ...

Attached is your Version 4

Hope this will help

Display More

Again, thank you so much Carim! Everything works, its very clean, and exactly what I was hoping to get to!!

I forgot, what did you say your CashApp handle was?

Last thing: I added an Investment C using the procedure I outlined earlier, after running the Index & Admin button, Investment C did populate on the Admin tab but it appeared "below" the table instead of "on the table" idk if that makes sense, but file is attached with those changes.

Thanks Carim!

First, lets not call it 'compensation,' and just a "tip." For real, drop you CashApp handle.

It works amazingly!

Last thought:

There's an "Investments List" on the Master Table worksheet that hyperlinks to each respective fund-specific t-account. But these are the same as the Funds Table on the Admin worksheet. Would it be possible to just have the Funds Table on the Admin worksheet have the Names hyperlinked, cutting down on redundancy?

[h=1][/h] Thanks Carim!

Two questions:
-I'd like to thank you by sending a CashApp payment so if you want to direct message it to me or share it here, look for a cash thank you from me!
-Would it be smart to put the "Index Funds" button on the Admin tab and let it populate the names directly onto the table? The Indirect formula would then automatically pickup the 'Committed' and 'Callable' amounts?

Ah yes, you're right! I saw that you did that in the file you sent back, just didn't make that change in the version where I added your VBA. Obvious version control issues on my end, lol, thanks!

Hi Carim,

That's a nifty bit of code! In the full version I created a table of contents that hyperlinks to the desired tab (there are 66 existing t-accounts, with more to come), which just made it easier to get to the correct tab to input accounting entries.

But to answer your question, yes, it is simply a recap all the entries from the "child" t-accounts. With the headers filtering turned on, yes I will most likely have them sorted by date, but I might also want to sort by "transaction description" and gather up all the Capital Calls for a given period, for example. On the newly attached file (which includes your VBA), you can see I added an additional attribute column on the Master Table.

Hi Carim!

That indirect formula helps A TON! Thank you!

Hi royUK!

Thank you for the help! I do need to maintain the individual T-Accounts because that's basically what management wants, lol. But in all honesty it does help us more directly track unfunded commitment, paid-in capital, and the call as a % of commitment. This could be accomplished with a slick new pivot table and VBA and kinda have it setup as a form/report but I've tried implementations like that and it honestly just scares off management because they're so unfamiliar with those kinds of tools. So I've resolved to try to keep it "low tech" and more familiar for them, which is an entirely different set of challenges.

Hello World!

I'm not sure if this would fall under formulas or VBA, but here's my best attempt at explaining what is going on:

My employer tracks investment cashflows using basic t-accounts. We are still going to input cashflows using independent t-accounts for each investment, but we now want all that data to automatically flow into a "master chart"

If you image, Investment A pays a dividend of $100 on November 1... I'll input that on the T Account (which is a unique worksheet) for Investment A. Investment B pays a $25 dividend on November 4, the same procedure is triggered: I input that activity on a t-account for Investment B. If I print either worksheet (for investments a or b) it will only show data for THAT particular investment. But I also need a "Master Account" which automatically collects new lines from its "child tables" and copies them to the next available line on the Master Chart. The idea is to not be required to enter the same data twice, and thereby avoid human-error in doing so.

Additionally, there will be a constant influx of new investments with their own t-charts so whatever the tool is, be it formula or VBA, needs to be relatively easily "reprogrammed" to look at new worksheets.

Lastly, I *think* I want to accomplish this with a pivot table, which would allow me to slice and dice the data in the future on an ad hoc basis... but I don't know how to get the data from the individual t-accounts to flow into a table.

Attached is a sample mock-up of what I'm trying to get to. Any help is much appreciated!

Quote from pike

Hi
what cells are the constants ?

Hi Pike!

Constants:
StartingValue = K4 for Eddard Stark | L4 for Pycelle, etc.
%Change references relative dates and names:

...lets say we're working with MSCI ACWI. The variable named myArray(1) already has a value set to 15%. The VBA then has to go to 'Table2' located on tab 'Where the Action Is', find the most recent date for MSCI ACWI which is 8/16/2019 and store the price from that date for the % change formula as "EACWIcv." It then needs to find the earliest date in MSCI ACWI for that year (2019)... which is 1/2/2019 and store the price from that date as "EACWIpv." It then needs to calculate the %Change which is: (1+(EACWIcv-EACWIpv)/EACWIpv)... in this case, EACWIcv = 71.18, EACWIpv = 64.139999 so the formula with the numerical values inserted would read (1+(71.18-64.139999)/64.139999) = 1.109759918 = %Change

Inserting the product of %Change in the formula we would get CurrentValue = StartingValue * myArray(1) * 1.109759918 + StartingValue * myArray(2) * 1.109759918 + ..... StartingValue * myArray(n) * 1.109759918

Which is why %Change can be stored as a variable instead of calculated for each iteration of myArray(n)

Inserting numerical values for all the variables would read CurrentValue = 408,910,275 * .15 * 1.109759918 + 408,910,275 * .30 * 1.109759918 + 408,910,275 * .25 * 1.109759918 + 408,910,275 * .08 * 1.109759918 + 408,910,275 * .22 * 1.109759918, CurrentValue = 455,352,242

StartingValue
myArray(n)
%Change

If you take a look at the tab titled ''Outputs' you will see where this is being calculated in E27. For this particular method, Prior Period Close = StartingValue, so we see that represented as a simple cell reference. Below that we see the actual % change is is similar to the %Change formula above, but this is basically the YTD performance of the mixed portfolio, instead of individual indices. and we see a nominal change, so this is the dollar amount change, saying that with the existing asset allocation mixture, Eddard Stark has earned $46MM YTD...

Right now we're only working with the one portfolio, EddardStark
And for the one method: YTD

But this needs to be repeated for the other 3 portfolios, and for the 2 other methods: Monthly and Weekly.. These two methods have to calculate the "PP Close" the same way they calculate the CP Close, but for different dates. I can provide more detail if you're interested.

I know this is A LOT that I'm asking. I'm willing to pay because this will literally save me hours of frustration every week, as I'm expected to produce these numbers weekly (our statements, like most people, are monthly, and the higher ups want to get a sense of where we are on an intra-month basis). I'm just trying to put as much of it in VBA as possible because it will remove the human factor. Plus I want to be able to produce outputs on the fly, so with monthly (for example), I could pull a drop down and select July, or February, and everything calculates, instead of me scrolling, clicking, scrolling, etc... messing up somewhere, not catching it, getting yelled at. I thought I had a grasp on VBA until I attempted this.

Please send me a private message and we can settle on price and payment method (cashapp?)

Quote from pike

Hyello,
I can attempt to answer a specific question about excel/vba but not solve an equation ,, you could try recording a macro to fill in the blanks or make a worksheet formula of the problem.
Can you expnad and ask a specific question?

Hi Pike!

Now that the nonblanks have been populated into array variables, how would I insert that variable into the following equation?:

CurrentValue = StartingValue * myArray(1) * PercentChange + StartingValue * myArray(2) * PercentChange + ..... StartingValue * myArray(n) * PercentChange

For n iterations of the array?