(I'm pretty sure I didn't come up with this but it's been in some of my stuff for a few years now. If I find where I got it from I'll post that link)
Typically when you open a form in Access
DoCmd.OpenForm "Name of Form"
It will open it then if when you call that code again (while still having the first form open) it will just pop focus back to the form as it was first opened. Th Open and Load events don't fire either so you can't "reinitialize" without closing it first.
To get around this you can create a New Form
and pass around the Hwnd
of that form. I use this to keep a form for creating dynamic datasheets (i.e. you pass in a recordset or recordsource and it figures out how to display it by setting all the appropriate control sources)
Public multiInstanceDic As Dictionary
'returns the window handle (long)
Public Function OpenNewDynamicDataSheetInstance(queryForRecordSource As String, Optional inputCaption As String) As Long
If multiInstanceDic Is Nothing Then
Set multiInstanceDic = New Dictionary
End If
Dim frm As Form
Set frm = New Form_DynamicDatasheet
frm.Caption = inputCaption
frm.SetRecordSource queryForRecordSource
multiInstanceDic.Add frm.Hwnd, frm 'required to keep form alive after function exits
frm.SetFocus
OpenNewDynamicDataSheetInstance = frm.Hwnd
End Function
Public Function GetDynamicDataSheetInstance(frmHandle As Long) As Form_DynamicDatasheet
Set GetDynamicDataSheetInstance = multiInstanceDic(frmHandle)
End Function
Now whenever I need to open this form I can call this:
Dim createdWindowHandle As Long
createdWindowHandle = Windows.OpenNewDynamicDataSheetInstance("ViewForRecordsource", "My Caption Text")
And if I need to access things about that specific object like setting some property uniquely for this instance I can do it like this:
Dim dDataSht As Form_DynamicDatasheet
Set dDataSht = Windows.GetDynamicDataSheetInstance(createdWindowHandle)
dDataSht.SomeProperty = "set to this"
Then I clean up the reference to the form from this statis public dictionary in the Unload
event of the form. I have this set up to only work with one type of form object. I you want multiple instances of multiple types of forms there are probably work arounds for that. I'm sure I'll need to do that some day in which case I'll update this.
Private Sub Form_Unload(Cancel As Integer)
If Not MultiFormInstance.multiInstanceDic Is Nothing Then
If MultiFormInstance.multiInstanceDic.Exists(Me.Hwnd) Then
MultiFormInstance.multiInstanceDic.Remove Me.Hwnd
End If
End If
End Sub
And in my dashboard close event I can get rid of any stragglers (which there shouldn't be any but...who knows)
If Not Windows.multiInstanceDic Is Nothing Then
'kill all the dynamic datasheets
Dim key As Variant
For Each key In Windows.multiInstanceDic.Keys
Set Windows.multiInstanceDic(key) = Nothing
Next key
End If
Related Links:
http://www.vbforums.com/showthread.php?372811-Classic-VB-Why-can-t-I-use-WithEvents-on-arrays-of-objects
Even if you compiled your Access app it probably doesn't start up exactly how you want it to look. i.e. users might have minimized the ribbon but you want it the other way around or usually (for me) you want the navigation pane hidden. There are two ways of automatically executing code when your app starts
AutoExec
macro (a macro literally named AutoExec
which runs as the first thing that happens when your app starts)
- Default form
Things get funky and they step on each other when you have both of these set.
You can use the AutoExec
macro by creating anew macro and naming it AutoExec
. Then paste this code in there. It is just the RunCode
macro which calls a function (it has to be a function not a sub. A sub will not work) from some static module (not a class module).
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
<Statements>
<Action Name="RunCode">
<Argument Name="FunctionName">
DoThisOnStartup()
</Argument>
</Action>
</Statements>
</UserInterfaceMacro>
</UserInterfaceMacros>
This is the code I typically run on startup. You can substitute ACCDE for ADE or MDE (whatever you are using). Note that you cannot just use the Applicaiton.IsCompiled
property because that just indicates whether or not the code is in a temporarily compiled state, not the saved file type.
Public Function DoThisOnStartup()
Dim fso As New FileSystemObject
Dim ErrorValue As String
'only do this stuff if the file is compiled
On Error GoTo DoThisOnStartup_Error
If fso.GetExtensionName(CurrentProject.FullName) = "ACCDE" Then
'MsgBox CurrentProject.FullName
'hide the navigation pane
ErrorValue = "DoCmd.SelectObject acForm, , True"
DoCmd.SelectObject acForm, , True
On Error GoTo DoThisOnStartup_Error
DoCmd.NavigateTo "acNavigationCategoryObjectType": DoCmd.RunCommand acCmdWindowHide
DoCmd.NavigateTo "acNavigationCategoryObjectType" ', "Forms"
'incase they unhide it
ErrorValue = "DoCmd.LockNavigationPane True"
DoCmd.LockNavigationPane True
' CommandBars.ExecuteMso "MinimizeRibbon" ' another, less robust way to hide the ribbon
If Application.CommandBars("Ribbon").Height >= 150 Then
SendKeys "^{F1}"
End If
End If
VeryifyBackendIsInPlace
Exit_DoThisOnStartup:
DoCmd.OpenForm "Dashboard"
On Error GoTo 0
Exit Function
DoThisOnStartup_Error:
' logging if you can
Resume Next ' continue trying to start up
End Function
The git documentation seems really good so far but it is a little verbose. Here's a fast bullet list to get you going immediate. It assumes that you have a GitHub account and you made a repo through the website.
- Open git bash
cd
to the directory where you want to put your repo. (mkdir
if you need to)git clone <your repo url on GitHub>
- add all your files/do work
git add .
git commit
- commits all your changes- This will open up a vim editor. Add whatever comment you want for this commit.
- Press Esc to get out of Insert Mode (press
a
to get back into edit mode)
:w
to save your comment
:q
to exit the vim editor
git push
- pushes that commit up to the repo
Another good to know
Revert changes git checkout -- .
I always need to write up a little procedure to handle passing in values in the OpenArgs paramater of DoCmd.OpenReport or DoCmd.OpenForm
. Well no more! I am liking this style. I's kind of JSONy. Given a string formatted like
<name of="" control="">|<value for="" that="" control="">||<another control="" name="">|<value for="" that="" control="">...</value></another>
it will put the values in the right place.
Function PopulateWithOpenArgs(ctrls As Controls, OArgs As String) As Dictionary
Dim controlValues As New Dictionary
Dim controlsValuesArr() As String, i As Integer
Dim ctrlValPair() As String
controlsValuesArr = Split(OArgs, "||")
On Error Resume Next
For i = LBound(controlsValuesArr) To UBound(controlsValuesArr)
ctrlValPair = Split(controlsValuesArr(i), "|")
If UBound(ctrlValPair) >= 1 Then
controlValues.Add ctrlValPair(0), ctrlValPair(1)
End If
Next i
Dim ctrlName
For Each ctrlName In controlValues.Keys
ctrls(ctrlName) = controlValues(ctrlName)
Next ctrlName
Set PopulateWithOpenArgs = controlValues
End Function
So, for example, if I have a form, Frm_ReportOpenOptions, which is responsible for gathering some data from a user before a report is generated and on that form I have two textboxestxtSubmitterNameInput and txtDateSubmittedInput, after they enter their values, I can create a pipe and double-pipe delimited string of those value and the destination control names on the report or form.
Dim rptOpenArgs As String
Dim whereClause As String
rptOpenArgs = "txtDateSubmitted|" & txtDateSubmittedInput & "||txtSubmitterName|" & txtSubmitterNameInput
whereClause = set to whatever value...
DoCmd.OpenReport "Rpt_MonthlyPayments", acViewPreview, , whereClause, OpenArgs:=rptOpenArgs
Then in the Load event I just need to call
Private Sub Report_Load()
PopulateWithOpenArgs Me.Controls, Me.OpenArgs
End Sub
And the controls are automagically populated. This works well for reports that have a field that needs to be set once, like in the header section. Otherwise, more complicated controls in detail sections might need to get set in a Format event. Reports also cannot have their values changed once they are formatted. So, while a form can be opened via DoCmd.OpenForm then have properties set via Form("Name of form").Property a report canot do that.
Got any better ways of passing in an arbitrary number of OpenArgs parameters?