VBA Editor

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

User - 29 Jan 2008 00:25 GMT

Hello...

After many years of programing with access, I decided to automate the
creation of the vba coding behind a form.

Basically, I have a form where I select all the settings for any given
form then, by pressing a button I generate all the code I need to a
file then open it with notepad.

What I like to do is to "paste" the generated code to the form module
automatically.

I do know a few things:

1. The form needs to be open to be able to read from the controls on
it. (which I'm doing right now. If the form is not open then I open it
hidden, do my stuff and close it)

2. There's a way of doing it using the VBE.

So far, I've been able to open the form and get the focus on the
module. When I try to paste the generated code it fail! It's driving
me crazy!

I google it, but I only got examples for excel which are helpful but
not good.

Any help would be much appreciated!

Finally, going further, my goal is to create a tool, like the "M-Z
Tools" to use it directly with the editor but that has proven
difficult. No many doc. on how to.

I know how to create an mde and use it as an "Add-In" (quite fun and
easy to do BTW...) but I haven't got the part to read from the Actual
DB and NOT the Add-In ! I know is something with the references to the
db I want, but no luck yet.

Currently, I have the "Tool" Form on the DB I'm developing and
assigned a hot key thru macros to call it. It works but I would like
to have it as a "Tool" instead of copying the form every time, plus
it's easier to correct and/or enhance the "Tool" than chaging all of
them.

Many, many thanks for any help.

Reply to this Message

Salad - 29 Jan 2008 18:06 GMT

> Hello...
>
[quoted text clipped - 24 lines]
>
> Any help would be much appreciated!

At this point I would go to MSAccess Help, search for Module Object, and
look at the Methods and Property, as well as the code examples for each
method and property associated with Module.

Modules
http://www.youtube.com/watch?v=kJf9MZEdviA

Reply to this Message

Phil Stanton - 29 Jan 2008 22:25 GMT

I don't know if this is any help. Used to add code to a report

Phil

Function AddModules(Rpt As Report, MainOrSub As Byte) ' Add the
print modules 0 = Main Report, 1 = SubReport

Dim Mdl As Module
Dim lngStartLine As Long, lngBodyLine As Long
Dim lngCount As Long
Dim strProcName As String
Dim i As Integer

On Error GoTo AddModules_Err
Set Mdl = Rpt.Module

If MainOrSub = 0 Then ' Code for Main Report
only
If Rpt.OnOpen = "" Then ' No on Open module
Rpt.OnOpen = "[Event Procedure]"
lngCount = Mdl.CreateEventProc("Open", "Report")
End If

' Insert text into body of procedure.
strProcName = "Report_Open"
' Count lines in procedure.
lngCount = Mdl.ProcCountLines(strProcName, vbext_pk_Proc)
' Determine start line.
lngStartLine = Mdl.ProcStartLine(strProcName, vbext_pk_Proc)
' Determine body line.
lngBodyLine = Mdl.ProcBodyLine(strProcName, vbext_pk_Proc)

Do Until Mdl.Lines(lngStartLine + lngCount, 1) <> "" ' Problem if
there are blank lines at the end of a procedure
lngCount = lngCount - 1
Loop

Mdl.InsertLines lngStartLine + lngCount, vbTab & "Call OpenWord(" &
Chr$(34) & Chr$(34) & ")" & vbCrLf ' Open Word
Mdl.InsertLines lngStartLine + lngCount + 2, vbTab & "Documents.Add
Template:=" & Chr$(34) & "Normal" & Chr$(34) & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 4, vbTab & "Call
SetUpPage(Me)" & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 6, vbTab & "LeftOffset =
0" & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 8, vbTab & "TopOffset = 0"
& vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 10, vbTab & "StartTime =
Timer" & vbCrLf

If Rpt.OnPage = "" Then ' No on Open module
Rpt.OnPage = "[Event Procedure]"
lngCount = Mdl.CreateEventProc("Page", "Report")
End If

' Insert text into body of procedure.
strProcName = "Report_Page"

' Count lines in procedure.
lngCount = Mdl.ProcCountLines(strProcName, vbext_pk_Proc)
' Determine start line.
lngStartLine = Mdl.ProcStartLine(strProcName, vbext_pk_Proc)
' Determine body line.
lngBodyLine = Mdl.ProcBodyLine(strProcName, vbext_pk_Proc)

Do Until Mdl.Lines(lngStartLine + lngCount, 1) <> "" ' Problem if
there are blank lines at the end of a procedure
lngCount = lngCount - 1
Loop

Mdl.InsertLines lngStartLine + lngCount, vbTab & "Call
FindAndDeleteBookMark" & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 2, vbTab & "Call
NextPageAddBookMark" & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 4, vbTab & "Msgbox " &
Chr$(34) & "Time taken was " & Chr$(34) _
& " & Timer - StartTime" & " & " & Chr$(34) & " seconds" &
Chr$(34) & vbCrLf

' Count lines in procedure.
lngCount = Mdl.ProcCountLines(strProcName, vbext_pk_Proc)
' Determine start line.
lngStartLine = Mdl.ProcStartLine(strProcName, vbext_pk_Proc)
' Determine body line.
lngBodyLine = Mdl.ProcBodyLine(strProcName, vbext_pk_Proc)
End If ' End of Main report
only

For i = 0 To 20
If Rpt.Section(i).Controls.Count <> 0 Then
If Err <> 0 Then
Err = 0
GoTo NextSection
End If

'Debug.Print mdl.CountOfLines & " i= " & i & " Section: " &
Rpt.Section(i).Name

If Rpt.Section(i).OnPrint = "" Then
Rpt.Section(i).OnPrint = "[Event Procedure]"
CreatePrintSub:
'Debug.Print Rpt.Section(i).Name & " event proc"
lngCount = Mdl.CreateEventProc("Print", Rpt.Section(i).Name)
End If

' Insert text into body of procedure.
strProcName = Rpt.Section(i).Name & "_Print"
' Count lines in procedure.
lngCount = Mdl.ProcCountLines(strProcName, vbext_pk_Proc)
' Determine start line.
lngStartLine = Mdl.ProcStartLine(strProcName, vbext_pk_Proc)
' Determine body line.
lngBodyLine = Mdl.ProcBodyLine(strProcName, vbext_pk_Proc)
' Determine line number of last line in procedure.

Do Until Mdl.Lines(lngStartLine + lngCount, 1) <> "" ' Problem
if there are blank lines at the end of a procedure
lngCount = lngCount - 1
Loop

If MainOrSub = 1 Then ' Sub report
Mdl.InsertLines lngStartLine + lngCount, vbTab &
"SubRptRunning = True" & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 2, vbTab & "Call
OutputSection(Me, " & i & ", Left, Top)" & vbCrLf
Mdl.InsertLines lngStartLine + lngCount + 4, vbTab &
"SubRptRunning = False" & vbCrLf
Else
Mdl.InsertLines lngStartLine + lngCount, vbTab & "Call
OutputSection(Me, " & i & ", Left, Top)" & vbCrLf
End If
End If
NextSection:

Next i

Exit Function

AddModules_Err:
If Err = 35 Then ' Sub not found
Resume CreatePrintSub
End If
If Err = 2462 Then ' Invalid section
Resume NextSection
End If
MsgBox "Error: " & Err & " " & Err.Description

End Function

>> Hello...
>>
[quoted text clipped - 31 lines]
> Modules
> http://www.youtube.com/watch?v=kJf9MZEdviA

Reply to this Message

EI User - 29 Jan 2008 22:47 GMT

Phil... are you joking right ?

By looking at your reply, it look like the solution I need. I'll give
it a try and let you know.

Many, MANY.. THANKS !!

>I don't know if this is any help. Used to add code to a report
>
>Phil

Reply to this Message

EI User - 30 Jan 2008 00:21 GMT

Hello Phil...

It worked like Magic !!!

Again, MANY, MANY THANKS !!!!!

One last question, let's say I create a mde as an "Add-In". How do I
tell vba to read from the active db an not the mde ?

I've tried several things but no luck.

Thanks!

>I don't know if this is any help. Used to add code to a report
>
>Phil

Reply to this Message

Phil Stanton - 30 Jan 2008 19:07 GMT

Glad it put you on the right track. Still not sure what you are trying to
achieve.

Dunno about the MDE bit

If you MDE is declared in the Reference section of VBA I know there can be a
conflict with your actual DB. I have a DB which extracts stuff to build an
Outlook address book from any Database, and frequently get conflicts.
Doesn't seem to stop it working, however.

Phil

> Hello Phil...
>
[quoted text clipped - 12 lines]
>>
>>Phil

Reply to this Message

EI User - 30 Jan 2008 23:18 GMT

Hello Phil...

I've been using Access like for 6+ years at work. I don't consider my
self an expert but I think I'm pretty good. So you have an idea; I
have a DB at work that does the following:

1. Connect to the main unix system, download a specified file.
2. Convert that file using Monarch and Inport to DB
3. Manipulate the Data then send the neccessary emails, using Lotus
Notes, to some of the office managers.
4. Save reports to PDF and/or Excel
5. Custom "Select Print Settings" form where users can select any
windows printer and I have my own routines to the various print
functions.

All of the above is done by just pressing a button. Pure access forms,
reports, etc. and VBA coding !!

Mind you, I have never set foot on a class room. I learned Access and
VBA all my self. By looking at examples, trial and error, READING and
a lot of google ! LOL

So, after all these years, even though I'm very organized, sometimes
it's hard to keep track of everything.

For that reason, I've decided it was time to "automate" the creation
of a DB, from the ground up! I'm standarizing everything, from the
field names on tables to the vba modules.

I have all the basics now like default forms, reports, coding, etc.;
the part I really want is this:

I created a form, for my use only, that will create all of the coding
of a normal form in my db. That includes all of the events for each
field, everything! The same form will copy and paste the coding to the
target form. All by selecting the necessary fields and pressing a
button. With your help, I completed everything!; except;

I want to take it to the next level. I want to create a DLL or
Something like that to using as an "extension" of the VBA editor. The
best idea is the "M-Z Tools", great tool and free.

So far, I've created a mde with the neccessary settings to be used as
an "Add-In", it works great but, is doing everything on the mde and
not the actual or current db I'm working on. I've tried somethings but
no luck. If I got it to work, I'll use it as an "Add-In" but I'll love
to have it as a DLL like the m-z tools.

Any ways, many thanks for all your help.

>Glad it put you on the right track. Still not sure what you are trying to
>achieve.
[quoted text clipped - 7 lines]
>
>Phil

Reply to this Message

Tony Toews [MVP] - 31 Jan 2008 01:55 GMT

>One last question, let's say I create a mde as an "Add-In". How do I
>tell vba to read from the active db an not the mde ?

Use CurrentDB to refer to the "parent" MDB/MDE. Use CodeDB to refer to the "add-in"
mde.

Also see my Add-in Tips, Hints and Gotchas page at
http://www.granite.ab.ca/access/addins.htm which may be of some use.

Tony

Signature

Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Reply to this Message

EI User - 31 Jan 2008 22:37 GMT

Hello Tony..

Many, maaannyyy thanks !!!!

I beleive that will solve the final problem.

Again, many thanks!

>>One last question, let's say I create a mde as an "Add-In". How do I
>>tell vba to read from the active db an not the mde ?
[quoted text clipped - 6 lines]
>
>Tony

Reply to this Message