Header and Footer sections in VBA generated form

[JH]

Folks,

I have an application which needs to create a form in an .mdb other than the
one it is running in. No problem doing that with:

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase (Destination)
Set dbOut = appAccess.CurrentDb
Set frm = appAccess.CreateForm

However, the form that is created has no form header and footer, which I
need. Again I can get round that by using a template that gets exported to
the target database first.

DoCmd.TransferDatabase acExport, "Microsoft Access", Destination, acForm,
"frm_template", "frm_template"

then:

Set frm = appAccess.CreateForm(, "frm_Template")

However I want to create this utility as an .mde file, and that means I
can't export the template from the .mde file to the target .mdb file.

So, the question is: How do I create a form within VBA that has header and
footer sections? I have searched in vain for a "addsection" method, so any
help would be greatly appreciated.

John

 

[Douglas J Steele]

I think you just need to make the header and footer sections visible.

Something like:

frm.Section(acHeader).Visible = True
frm.Section(acFooter).Visible = True

 

[JH]

Doug,

Thanks for the idea, unfortunately I tried that and you get the error

Run time error 2462
The section number you entered is invalid.

Try the following snippet, you will see that it blows up at the
"frm.section(acheader).visible = true" line.

Sub MakeMyForm()
Dim db As DAO.Database
Dim frm As Form
Dim ctrl As Control

Set db = CurrentDb
Set frm = Application.CreateForm
frm.Section(acHeader).Visible = True
Set ctrl = CreateControl(FormName:=frm.Name, _
ControlType:=acLabel, _
Section:=acHeader, _
Left:=20, _
Top:=20, _
Width:=5000, _
Height:=250)
ctrl.Caption = "Hello world"

End Sub


However, if you delete the "frm....Visible = true" line, change acHeader for
acDetail in the Create control, it works find - except that I want the
contorl in the header!!!!

Any thoughts?

John

 

[Douglas J Steele]

Have you tried using an existing form as a template when you create the
form?

 

[JH]

Doug,

Yes and that works up to a point. I am trying to create a tool that I will
distribute as an mde. Originally I thought I could overcome the problem by
exporting the template from my app into the target database, create the form
and then delete the template. Works well for an mdb, but of course you can't
export from an mde! I can't rely on user having the template around as the
tool can be used by anyone.

So I am still stuck!

John

 

[JH]

Doug,

GOT IT!!!!

You need to use the

DoCmd.RunCommand acCmdFormHdrFtr

command, which toggles them on or off.

So my snippet becomes:

Sub MakeMyForm()
Dim db As DAO.Database
Dim frm As Form
Dim ctrl As Control

Set db = CurrentDb
Set frm = Application.CreateForm
DoCmd.RunCommand acCmdFormHdrFtr
Set ctrl = CreateControl(FormName:=frm.Name, _
ControlType:=acLabel, _
Section:=acHeader, _
Left:=20, _
Top:=20, _
Width:=5000, _
Height:=250)
ctrl.Caption = "Hello world"
DoCmd.Close acForm, frm.Name, acSaveYes

End Sub

Thanks for helping.

John

 

[Douglas J Steele]

Have you tested that this is going to work in an MDE?

I suspect it may not...

 

[JH]

Doug,

Yes I have tested in an MDE and it works perfectly. I have all the check
boxes in the "Startup" dialog cleared and all is OK.

Once again thanks for the sugestions,
John