Useful vba routines for data entry forms

data_entry.vba

'------------------------------------------------------------------------------
' Purpose:          sample comment
' Args:
' Created:          
' Modified:
' Author:           
'-------------------------------------------------------------------------------
'===============================================================================
Sub MoveFirst()
'------------------------------------------------------------------------------
' Purpose:          Moves to the first position in a masked text box
' Args:
' Created:          7/1/2014
' Modified:
' Author:           Mitch Gritts
'-------------------------------------------------------------------------------
Dim acform As String
Dim accontrol As String

acform = Screen.ActiveForm.Name
accontrol = Screen.ActiveControl.Name

'================================================================================
Sub DeleteDefs(defs)
'------------------------------------------------------------------------------
' Purpose:      Deletes temp table and query defs iteratively
' Args:
'   defs - Array of table or querry definitions to delete
' Created:      3/1/2015
' Modified:
' Author:       Mitch Gritts
'------------------------------------------------------------------------------
For Each def In defs
    For Each d In CurrentDb.QueryDefs
        If d.Name = def Then
            CurrentDb.QueryDefs.Delete def
            Exit For
        End If
    Next
    For Each d In CurrentDb.TableDefs
        If d.Name = def Then
            CurrentDb.TableDefs.Delete def
            Exit For
        End If
    Next
Next
                
End Sub

Forms(acform).Controls(accontrol).SetFocus
Forms(acform).Controls(accontrol).SelStart = 0

End Sub

'================================================================================
Sub DoubleClickClear()
'------------------------------------------------------------------------------
' Purpose:          Clears value from active control
' Args:
' Created:          7/1/2014
' Modified:
' Author:           Mitch Gritts
'-------------------------------------------------------------------------------
Dim actformscr As String
Dim actctl As String

actformscr = Screen.ActiveForm.Name
actctl = Screen.ActiveControl.Name

Forms(actformscr).Controls(actctl).Value = Null

End Sub
'================================================================================
Sub clearform(actfrm As String)
'------------------------------------------------------------------------------
' Purpose:          Clears data from the form for the next record in navigation
' Args:
'   actfrm - the name of the current from
' Created:          4/1/2015
' Modified:
' Author:           Mitch Gritts
'-------------------------------------------------------------------------------
For Each ctl In Forms(actfrm).Controls
    Select Case ctl.ControlType
        Case acTextBox, acListBox, acComboBox, acCheckBox
            ctl.Value = Null
    End Select
Next ctl

End Sub
'===============================================================================
Sub TransferDefs(defs, filename As String)
'------------------------------------------------
' Purpose:      transfers query or table defs to spreadsheet, each item in array to different sheet
' Requires:     defs as an array (list) of table or query names
'------------------------------------------------
    For Each def In defs
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, def, filename, True, def
    Next
    
End Sub