CBlog(handziuk)

Bradley Handziuk's blog

How to have multiple instances of the same form open at once in Access

(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

Things to do when Access starts up

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
  1. AutoExec macro (a macro literally named AutoExec which runs as the first thing that happens when your app starts)
  2. 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

Calling out invalid data

Validating data before it get submitted on a bound form is easy enough. Each control should have its own logic and you can do things based on it passing some predefined rule. But if you have unbound controls/and unbound form needing validation it is a little trickier.

I wrote this to call out to users invalid data. It really only checks for NULLS (i.e. if a field was marked as required it needs to have something in it). When it encounters invalid data it makes that control have a red border. When the data are deemed to be valid it switches the border back to whatever it was when the form loaded.
More...


Making a survey form in Access

Recently I had a two tasks in Access where I needed to do some data collection in a sort of 'survey' format. By that I mean there is this one thing and a bunch of different attributes/questions that need to be filled in/answered before the entire set can be saved. However, these things are not all the same. For one it is an invoice system where by different clients have different charge numbers but any one client always has the same st of charge numbers. Or people answering questions about pieces of equipment. Every equipment has a different set of questions but any one piece of eqipment should always have the same questions.

The hard part of doing this in Access is making dynamic forms where the number of text boxes is hard. Or displaying a bunch of different questions to be filled in (in any order) to the user with the answers being blank by default and letting them moved between sections without getting some error message that says "this value is required" but requiring an answer be selected before everything is submitted is tricky.

More...



Adding code for the first time to GitHub through git

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.

  1. Open git bash
  2. cd to the directory where you want to put your repo. (mkdir if you need to)
  3. git clone <your repo url on GitHub>
  4. add all your files/do work 
  5. git add .
  6. git commit - commits all your changes
    1. This will open up a vim editor. Add whatever comment you want for this commit.
    2. Press Esc to get out of Insert Mode (press a to get back into edit mode)
    3. :w to save your comment
    4. :q to exit the vim editor
  7. git push- pushes that commit up to the repo

Another good to know

Revert changes git checkout -- .


The login is from an untrusted domain and cannot be used with Windows authentication.

I had set up a database in SQL Server and was having user connect to it through Access. They were not able to connect it turned out and were getting the error message like

I had checked the SQL Server logs (exec sys.xp_readerrorlog) and the Windows logs (Event viewer > Windows Logs > Secutiry) and I saw these messages

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: xx.xx.xx.xxx]
and
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xx.xx.xx.xxx]
and
The user has not been granted the requested logon type at this machine.
After a lot of reading I came across a few other's with this problem. These were most helpful

The trick was the the security policy Local Security Policy > Local Policies > User Rights Assignment > Access this Computer from Network did not include Users or Everyone thus when users tried to get into the DB, I guess SQL Server uses that user's account, not the acount under which the SQL Server service is running, to authenticate against the domain. Because that action was restricted they could not be authenticated and thus could not get access to the DB. 

The solution is to add Users to the Access this Computer from Network policy. The default is

  • Administrators
  • Backup Operators
  • Everyone
  • Users

though so you'd have to have it changed by a GPO push or made a mistake somewhere down the line to have this not be present in the first place.

How effectively pass multiple values in an OpenArgs parameter


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?

That little white bar at the bottom of Access Forms

If I have a form that is either unbound or I made my own navigation buttons I'll probbaly want to hide the Navigation Buttons on the bottom. These are great and have their place but sometimes I just want them gone. Unfortunately whenever I just set Navigation Buttons = No I get something like this


Setting Navigation Buttons = No

The unexpected result
What gives? This handy page is always hidden deep in Google search results for me but it (really Missinglinq gets the credit) explains what is happening. Apparently Access reserves space at the bottom of the form for navigation buttons and (horizontal) scroll bars. If you need the scroll bars they'll pop up, if you don't well, I guess they are hidden behind the navigation buttons. If you hide the navigation buttons you reveal the hiding scroll bars.

The trick is to disable Horizontal Scroll bars, too. You can either set the Scroll Bars to Neither or Vertical only. Both disable the horizontal component.

Set Scroll bars to Neither

White space goes away! Yay!


For reference this is what my form looked like with everything shown