Microsoft Access

Stock report opening code for Access

Steve Gray — Tue, 29 Jul 2008 18:40:00 GMT

I put the following code in all my Access reports

 Option Explicit
Dim strCompanyID As String
Dim strFormName As String
Dim mlngBgColor As Long
Dim mintCount As Integer


Private Sub Report_Close()
    If isOpen(strFormName) Then
        DoCmd.Close acForm, strFormName
        DoCmd.Restore
    End If
End Sub


Private Sub Report_Open(Cancel As Integer)
    
    Dim strDatabase As String
    Dim strStartProject As String
    Dim strEndProject As String
    Dim strStartDate As String
    Dim strEndDate As String
    
    strFormName = "frmCostCatSummary"
    
    DoCmd.OpenForm strFormName, acNormal, , , , acDialog
    
    If isOpen(strFormName) Then
        DoCmd.Maximize
        
        strStartProject = Trim(Forms!frmCostCatSummary!cboStartProject)
        strEndProject = Trim(Nz(Forms!frmCostCatSummary!cboEndProject))
        strStartDate = Trim(Nz(Forms!frmCostCatSummary!txtDate1))
        strEndDate = Trim(Nz(Forms!frmCostCatSummary!txtDate2))
        
        strCompanyID = setDatabase("qryCostCatSummary", "sp_EP_CostCatSummary '" & strStartProject & "','" & strEndProject & "','" & strStartDate & "','" & strEndDate & "'")
        lblCompanyID.Caption = strCompanyID
    Else
        Cancel = True
    End If
End Sub

IsOpen function for Access

Steve Gray — Tue, 29 Jul 2008 18:39:00 GMT

Public Function isOpen(strName As String, Optional intObjectType As Integer = acForm) As Boolean
On Error GoTo err_isOpen
        'Returns True if strName is open, False otherwise.
        'Assume the caller wants to kmow about a form.
        isOpen = (SysCmd(acSysCmdGetObjectState, intObjectType, strName) <> 0)
exit_isOpen:
    Exit Function
err_isOpen:
    MsgBox Err.Description
    Resume exit_isOpen
End Function

Insert statement in Access

Steve Gray — Sat, 25 Aug 2007 17:59:00 GMT

Set DB = DBEngine.OpenDatabase("C:\MyDir\MyDB.MDB")
DB.Execute "INSERT INTO MyTable (MyField1, MyField2) VALUES ('abc',
123)"
DB.Close
Set DB=Nothing
End Sub