Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating modules in code (A97 SR2)

    Does anyone know how to create a code module in VBA. I have searched all the Help files without any luck.

    Thanks for any help

    Bodders

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating modules in code (A97 SR2)

    Hi Bodders

    Let me take a shot at this. As a newbie myself, a few months ago I use to keep redundant code behind each control on each form. Then I discovered by example on this forum how to put common code into modules and call it when needed from the control on a form.

    This is just one example of having the following code behind each combo box on any form that calls Function Append2Table_OtherLookup_Method1 to do a particular function.

    The idea is to pass all necessary data/variables to one common function/sub that does all the work.

    This allows you to eliminate redundant code for each combox box, do it once, call it many. Reduces code and you maintain it in one place

    Once you develop a library of Function/Sub your next application becomes easier because you can reuse code that has proven itself in previous apps.

    HTH

    John

    =====================
    Private Sub lngKey1ID_NotInList(NewData As String, Response As Integer)

    Response = Append2Table_OtherLookup_Method1(Me![lngKey1ID], _
    NewData, _
    "tbl_GM_OtherLookup", _
    "CM", _
    " ", _
    "KY1", _
    "Contact Manager KEY1")
    End Sub
    ===================

    Function Append2Table_OtherLookup_Method1(cbo As ComboBox, _
    NewData As Variant, _
    strTableName As String, _
    strApplCode As String, _
    strCode As String, _
    strTypeCode As String, _
    strTypeDesc As String) As Integer

    On Error GoTo err_Append2Table_OtherLookup_Method1

    Dim ctl As Control
    Dim lbl As Label
    Dim msg As String
    Dim varField As Variant
    Dim varCaption As Variant
    Dim intResponse As Integer
    Dim strSQL As String

    Set ctl = cbo ' save Control Value, Set ctl = lngKey1ID
    varField = cbo.ControlSource
    Set lbl = cbo.Controls(0)
    varCaption = lbl.Caption

    If Not (IsNull(varField) Or IsNull(NewData)) Then
    ' Prompt user to verify they wish to add new value.
    msg = "Add A New (" & varCaption & " " & NewData & ") To The Lookup List?" _
    & vbLf & vbLf & vbCr _
    & "Select Yes if this is what you want to do, or" _
    & vbLf & vbLf & vbCr _
    & "Select No to ignore the Add and select form list."
    intResponse = msgbox(msg, vbYesNo + vbQuestion + vbDefaultButton2, _
    "Add A NEW (" & varCaption & " " & NewData & ") To The Lookup List?")
    If intResponse = vbYes Then
    Append2Table_OtherLookup_Method1 = acDataErrAdded
    NewData = NewData ' Add string in NewData argument to table.
    strSQL = "INSERT INTO "
    strSQL = strSQL & strTableName & " "
    strSQL = strSQL & "( strProfileCode, "
    strSQL = strSQL & " strApplCode, "
    strSQL = strSQL & " strCode, "
    strSQL = strSQL & " strDescription, "
    strSQL = strSQL & " strTypeCode, "
    strSQL = strSQL & " strTypedesc ) "
    strSQL = strSQL & "SELECT '" _
    & GetPref("Profile Code") & "', '" _
    & strApplCode & "', '" _
    & strCode & "', '" _
    & NewData & "', '" _
    & strTypeCode & "', '" _
    & strTypeDesc & "'"

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    ctl.Value = NewData
    DoCmd.SetWarnings True
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
    Append2Table_OtherLookup_Method1 = acDataErrContinue
    ctl.Undo
    End If
    ctl = Nothing
    End If

    exit_Append2Table_OtherLookup_Method1: ' mod
    Exit Function

    err_Append2Table_OtherLookup_Method1: ' mod
    If Err = 2113 Then
    Err = 0
    Resume Next
    Else
    msgbox "Error: " & Err.Number & ": " & Err.Description, vbInformation, _
    "Append2Table_OtherLookup_Method1"
    Resume exit_Append2Table_OtherLookup_Method1 'mod
    End If

    End Function

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating modules in code (A97 SR2)

    What exactly is your question--how to insert a new code module into the database container using VBA, or how to add code into the module from VBA, or how to write VBA code in a module?
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating modules in code (A97 SR2)

    The first one, Charlotte. I know how to add code to existing modules, but what I would like to do is actually create a new module using VBA

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating modules in code (A97 SR2)

    To what purpose?
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating modules in code (A97 SR2)

    AFAIK, there is no way to create a new standard (or class) module in Access 97. I ran into this some time ago. The only workaround I could think of was to create an empty module by hand (containing only the standard lines Option Compare Database and Option Explicit) and keep this as a kind of template. Any time you need a new module, copy it and then add code to the copy.

    To copy the module using code, use DoCmd.CopyObject if the "template" module is in the current database. If you want to import it from another database, use DoCmd.TransferDatabase.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating modules in code (A97 SR2)

    I have created an Access add-in which automatically builds forms and code for existing tables and queries. It makes extensive use of record types and expects two modules - modRecordType and modRecordBuild - to already exist in the target database. I know it's easy enough to flag a warning message if they are not found, but what I would like to do is use the add-in to create them for me. However, there does not seem to be the equivalent of CreateForm, CreateReport for modules, & I was wondering how to get round that. Perhaps a warning message is the easy answer ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating modules in code (A97 SR2)

    If your add-in is not protected, you could put the modules in the add-in and import them in the target database if needed:

    DoCmd.TransferDatabase acImport, "Microsoft Access", CodeDb.Name, acModule, "modRecordType", "modRecordType"

    CodeDb refers to the database executing the code - in this case your add-in.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Creating modules in code (A97 SR2)

    Aren't Add-Ins normally referenced from your target database rather than the Add-In calling modules in the target database? As Hans suggests, you could import them, or you could export the objects from the Add-In. I'm curious about the purpose of the Add-In. Are you using it as a wizard to build forms and code for databases someone else developed, or are they intended as development tools for speeding your own development work?
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating modules in code (A97 SR2)

    It's intended to speed up my own development work, but it could work for other databases, so long as they are starting from scratch.

    I think the import trick should sort out the problem. thanks for the help

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Creating modules in code (A97 SR2)

    You might look at how the Access form and report Wizards that come with Access are constructed. I don't recall the source, but I'm sure I saw a pretty detailed description of how they are built in one of the early version of Access. In any event they do much the same kind of thing you are describing - althought the results for forms are generally not appealing cosmetically.
    Wendell

Posting Permissions

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