Creating modules in code (A97 SR2)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

To what purpose?

Reply | Quote

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 ?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote