Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59

    Unanswered: What Is A Module??

    This might sound a bit stupid to some of you, I am building a huge database for my company and Access has created a module on its own, just wondering what the use of a module is and if I should be using them more??

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    A module can be used for common variables and/or functions. Generally all variables and function are globally scoped unless explicitly declared private to be used only within the module itself ...

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Smile

    Think of a module as a collection of related subroutines or functions, which can also have its own private variables and its own private routines. A module should also be a self-contained unit.

    In a typical application, "thrown together" as many apps initially are, forms wind up having a whole lot of duplicated code in them. It's better to put these into a single module, such that forms simply call the common routines. Now a change is made but once.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Nov 2003
    Location
    Slovenia, EU
    Posts
    16

    Wink The good thing of module

    Well I was in the same position as you, trying to understand why should I use modules, but here is one good practical example.

    When I designed databse with user forms I had to (of course) equip every form with buttons like "close form". So If I would go by normal way I would design the buttons with the Command button wizard. And of course every button would have some code (form module) on it. OK useful but too much of identical code!!!

    So i decided (because I am a little bit lazy) to create one single standard module (modCloseForm) with function:

    Function CloseForm()
    Dim frmCurrentForm As Form

    ' As current form take active form
    Set frmCurrentForm = Screen.ActiveForm

    On Error GoTo CloseForm_Err

    ' Close current active form
    DoCmd.Close acForm, frmCurrentForm.Name


    CloseForm_Exit:
    Exit Function

    CloseForm_Err:
    MsgBox Error$
    Resume CloseForm_Exit

    End Function

    And for running I created also one macro (example mcrCloseForm), which would use 'Action' RunCode and 'Function Name' CloseForm()

    So this is it. And now you can use it for every close button on every form. Just open 'Properties' of button and put mcrCloseForm to 'Event'.

    I hope that now you understand
    Have fun
    MS Access 2000 - 2003, WinNT - WinXP

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    THis is one of my favourite modules to relink a front end database to an access backend database:

    Public Function Relink(FileName As String) As Boolean
    ' Refresh links to the supplied database. Return True if successful.

    Dim intCount As Integer
    Dim tdf As TableDef
    Dim counttables As Integer
    Dim Main As String, Command As String, GL As String
    Dim LinkFileName As String, calcPct As Integer
    Dim Check
    Dim tdfName As String
    Dim dbs As Database, qdf As QueryDef
    Set dbs = CurrentDB()

    On Error GoTo errortrap
    DoCmd.OpenForm "Message"
    Forms![Message]![Message].Caption = "Opening General Ledger File: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & FileName
    Forms![Message].Repaint
    counttables = dbs.TableDefs.Count

    Main = left(FileName, InStr(FileName, "Machines") - 1) & "Main.mdb"
    GL = left(FileName, InStr(FileName, "Machines") - 1) & "GLChart.mdb"
    For intCount = 0 To counttables - 1
    calcPct = Int(intCount / counttables * 100)
    'If calcPct Mod 10 = 0 Then
    Forms![Message]![Message].Caption = "Opening General Ledger File (" & calcPct & "%): " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & FileName
    Forms![Message].Repaint
    'End If
    Set tdf = dbs.TableDefs(intCount)
    ' If the table has a connect string, it's a linked table.
    Check = tdf.Connect
    If Len(Check) > 0 Then
    If InStr(Check, "Main") > 0 Then
    LinkFileName = Main
    ElseIf InStr(Check, "GLChart") > 0 Then
    LinkFileName = GL
    Else
    LinkFileName = FileName
    End If
    LinkFileName = left(Check, InStr(Check, "DATABASE") + 8) & LinkFileName

    If tdf.Connect <> LinkFileName Then
    tdf.Connect = LinkFileName
    tdfName = tdf.Name
    Err = 0
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    Relink = False
    Exit Function
    End If
    End If
    End If
    Next intCount

    On Error GoTo 0
    DoCmd.Close acForm, "Message"
    Command = "UPDATE UserSettings SET UserSettings.[Value] = '" & FileName & "' " & _
    "WHERE (((UserSettings.Code)='CurrentDatabaseFile'));"
    Set qdf = dbs.CreateQueryDef("", Command): qdf.Execute
    Set dbs = Nothing
    Relink = True ' Relinking complete.
    Exit Function
    errortrap:

    DoCmd.Close acForm, "Message"
    MsgBox "Unable to link to " & tdfName & " in the " & LinkFileName & " database!", vbCritical, "Critical Error"
    Relink = False
    End Function

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Originally posted by jmrSudbury
    THis is one of my favourite modules to relink a front end database to an access backend database:

    Public Function Relink(FileName As String) As Boolean
    ' Refresh links to the supplied database. Return True if successful.

    Dim intCount As Integer
    Dim tdf As TableDef
    Dim counttables As Integer
    Dim Main As String, Command As String, GL As String
    Dim LinkFileName As String, calcPct As Integer
    Dim Check
    Dim tdfName As String
    Dim dbs As Database, qdf As QueryDef
    Set dbs = CurrentDB()

    On Error GoTo errortrap
    DoCmd.OpenForm "Message"
    Forms![Message]![Message].Caption = "Opening General Ledger File: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & FileName
    Forms![Message].Repaint
    counttables = dbs.TableDefs.Count

    Main = left(FileName, InStr(FileName, "Machines") - 1) & "Main.mdb"
    GL = left(FileName, InStr(FileName, "Machines") - 1) & "GLChart.mdb"
    For intCount = 0 To counttables - 1
    calcPct = Int(intCount / counttables * 100)
    'If calcPct Mod 10 = 0 Then
    Forms![Message]![Message].Caption = "Opening General Ledger File (" & calcPct & "%): " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & FileName
    Forms![Message].Repaint
    'End If
    Set tdf = dbs.TableDefs(intCount)
    ' If the table has a connect string, it's a linked table.
    Check = tdf.Connect
    If Len(Check) > 0 Then
    If InStr(Check, "Main") > 0 Then
    LinkFileName = Main
    ElseIf InStr(Check, "GLChart") > 0 Then
    LinkFileName = GL
    Else
    LinkFileName = FileName
    End If
    LinkFileName = left(Check, InStr(Check, "DATABASE") + 8) & LinkFileName

    If tdf.Connect <> LinkFileName Then
    tdf.Connect = LinkFileName
    tdfName = tdf.Name
    Err = 0
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    Relink = False
    Exit Function
    End If
    End If
    End If
    Next intCount

    On Error GoTo 0
    DoCmd.Close acForm, "Message"
    Command = "UPDATE UserSettings SET UserSettings.[Value] = '" & FileName & "' " & _
    "WHERE (((UserSettings.Code)='CurrentDatabaseFile'));"
    Set qdf = dbs.CreateQueryDef("", Command): qdf.Execute
    Set dbs = Nothing
    Relink = True ' Relinking complete.
    Exit Function
    errortrap:

    DoCmd.Close acForm, "Message"
    MsgBox "Unable to link to " & tdfName & " in the " & LinkFileName & " database!", vbCritical, "Critical Error"
    Relink = False
    End Function


    Looks like a favourite Function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •