Name and Save New Module (Access 97)

Topic

New Reply

Hi Everyone,

I can create a new module with

DoCmd.RunCommand acCmdNewObjectModule

but what can I use to automate naming and saving the new module.

I have tried SendKeys but I couldn’t get it to work.

Thanks

Reply | Quote

Replies

What you are attempting is quite complex and full of pitfalls. If you can give us a few details about the problem you are trying to solve, perhaps we can suggest an alternative. Or is this more of an academic exercise?

Reply | Quote

Hi Wendell,

Thanks for responding. I guess the answer is both.

The problem I am trying to solve:

I now have several functions that I use in most of my projects and my thought was to place them in a table. When beginning a new project I would like to select the functions I will use, auto-create one function, and pass the functions I will use in that project to the created function.

The academic exercise:

If a module can be created with DoCmd.RunCommand acCmdNewObjectModule, when and why would you use this, and how would you programmatically name and save the module?

Reply | Quote

When you execute RunCommand acCmdNewObjectModule, a new module will be opened, so it will be a member of the Modules collection. If you can be sure that it is the only open module, you can refer to it as Modules(0). But if you have other modules open, it’s hard to say which is the new one.
If you consistently give your modules names that do not start with “Module”, the new, unsaved module will be named Module1. So you could refer to it by name. Of course, this won’t work if you accidentally save a module as Module1.

Here is some code to manipulate a module in code. Note: you *must* put something into a new module before you can actually save it.

Dim mdl As Module
RunCommand acCmdNewObjectModule
Set mdl = Modules(“Module1”)
mdl.AddFromString “‘ Test”
DoCmd.Close acModule, “Module1”, acSaveYes
DoCmd.Rename “basTestModule”, acModule, “Module1”
Set mdl = Nothing

Reply | Quote

Most people do this kind of thing by simply importing a module with all of their general purpose functions into each database they develop – or they use a library database or store code in VSS. As to the issue of doing selective generation, it isn’t often that you know in advance that you won’t need a particular function, and the small amount of additional storage used by unused functions is well down in the noise level.

As to the academic side – code generating new code has always made me wary. Most of the time we have enough trouble debugging code as it is. Actually, there is fairly complete support for this concept in Access 2000/2002, but I’ve never had the situation arise where I even considered it. Furthermore, the documentation in this area is sketchy at best. That certainly doesn’t answer your original question, but just exhausts my knowledge in the area.

Reply | Quote

You can do a lot of things using the DoCmd object, but they aren’t always the best way. Once you created the object, you would have to use DoCmd.SelectObject to select it. I actually populated form modules in an Access 97 database some years ago, and it was a monstrous undertaking. Modules are basically text, but Access parses out lines like Function MyFunction() As String in order to tell that it’s come to the beginning of a procedures. You would have to find a way to always find the bottom on the existing text in order to know where to insert the new lines.

Here’s some code I used to find a line in the module.

Private Function FindModTextLine(ByRef mdl As Module, ByVal strText As String) As Long
  On Error GoTo FindModTextLine_err
  ' Based on on-line help samples
  ' created by Charlotte Foust 12/10/98
  Dim lngSLine As Long
  Dim lngSCol As Long
  Dim lngELine As Long
  Dim lngECol As Long
  Dim strLine As String
  
' Search for string.
  If mdl.Find(strText, lngSLine, lngSCol, lngELine, lngECol) Then
    FindModTextLine = lngSLine
  Else
    FindModTextLine = 0
  End If
FindModTextLine_exit:
  Exit Function
FindModTextLine_err:
  FindModTextLine = 0
  Resume FindModTextLine_exit
End Function

It’s called and used like this :

   lngLine = FindModTextLine(mdl, strText)
 
   If lngLine = 0 Then
     ' text not found so determine 
     ' the last line number
     lngReturn = mdl.CountOfLines
     mdl.InsertLines lngReturn + 1, strText
   End If

As Wendell pointed out, this is not a trivial exercise, so be prepared to spend a LOT of time writing this tools to reuse functions in other projects. It would be easier to just put the functions in a library database and call the ones you needed.

[indent]

---

auto-create one function, and pass the functions I will use in that project to the created function

---

[/indent] You totally lost me here. Do you mean pass the functions to the created *module*?

Reply | Quote

Thanks Wendell, Hans, and Charlotte.

Hans, your code is just what I was looking for.

Wendell, when you use a library database how are you calling the functions and what type of overhead is there over a Novell and/or MS network?

Charlotte,
___________________________________________________________________
You totally lost me here. Do you mean pass the functions to the created *module*?
___________________________________________________________________

Yes. My plan was to pass the entire function into the newly created module. Sorry for the confusion.

Reply | Quote

<>

Essentially what you are doing is creating another database that only contains code, and you reference it just as you would other DLLs and OCXs. Once you do that the functions become available to your project. As to overhead, if you leave the library database on the server, and it’s a slow network, it could be significant, though standard modules do get loaded into the workstation memory when you open the database. The net result isn’t a great deal different as long as both databases are on the server. We normally deploy it to the workstation in a split database arrangement. Hope this clarifies things a bit. If you want to create a full add-in as is used to supply Access wizards, the process is a bit more complex – see the Access Developer’s Handbook for more details, and Helen Feddema’s Access 2002 Inside Out has a step-by-step description for later versions.

Reply | Quote

Thanks for the info.

Reply | Quote