By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,929 Members | 634 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,929 IT Pros & Developers. It's quick & easy.

Programmatic Built-in Commands

P: n/a
Are there arguments for the built-in commands called via RunCommand
such as acCmdNewObjectReport or do they always require interactive
input? Is the code behind the built-in commands exposed anywhere? At
one time there was some code available for wizards, is this all hidden
now?

Thanks!
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Dave Boyd wrote:
Are there arguments for the built-in commands called via RunCommand
such as acCmdNewObjectReport
No.

or do they always require interactive input? Is the code behind the built-in commands exposed anywhere?
No.

At one time there was some code available for wizards, is this all hidden
now?
I think there may have been an add-in.
Thanks!


Nov 13 '05 #2

P: n/a
"Dave Boyd" <da**********@boeing.com> wrote...
Are there arguments for the built-in commands called via RunCommand
such as acCmdNewObjectReport
No.
or do they always require interactive input?
Yes.
Is the code behind the built-in commands exposed anywhere?
Some yes, some no. But there are a buttload of commands -- can you be
specific about which one you want?
At one time there was some code available for wizards,
Yes, up until Access 2000.
is this all hidden now?


Basically, yes. But this is only a small percentage of the code behind all
the runcommands in Access, though.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 13 '05 #3

P: n/a
"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in message news:<40******@news.microsoft.com>...
"Dave Boyd" <da**********@boeing.com> wrote...
Are there arguments for the built-in commands called via RunCommand
such as acCmdNewObjectReport


No.
or do they always require interactive input?


Yes.
Is the code behind the built-in commands exposed anywhere?


Some yes, some no. But there are a buttload of commands -- can you be
specific about which one you want?

How about acCmdNewObjectReport? I have a query that may have a
different number of fields which can be built programmatically, but
the report and the columns representing the fields is currently
static. I want to build the report programmatically so I don't have
to rewrite it every time the query finds that the number of fields has
changed. Here's some options I have considered:

1) RunCommand acCmdNewObjectReport and feed the dialog boxes with
something like Expect. (Easiest would be if there was a documented
argument list or a macro recorder.)

2) Build a static template of the report and try to change the columns
programmatically.

3) Use Crystal Reports from VS .Net.

4) Write the report via automation in Word

Any advice? Thanks.
Nov 13 '05 #4

P: n/a
"Dave Boyd" <da**********@boeing.com> wrote...
How about acCmdNewObjectReport? I have a query that may have a
different number of fields which can be built programmatically, but
the report and the columns representing the fields is currently
static. I want to build the report programmatically so I don't have
to rewrite it every time the query finds that the number of fields has
changed. Here's some options I have considered:

1) RunCommand acCmdNewObjectReport and feed the dialog boxes with
something like Expect. (Easiest would be if there was a documented
argument list or a macro recorder.)

2) Build a static template of the report and try to change the columns
programmatically.

3) Use Crystal Reports from VS .Net.

4) Write the report via automation in Word

Any advice? Thanks.


You can use CreateReport and CreateReportControl, the programmatic commands
that create a report (see online help for details).
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
Nov 13 '05 #5

P: n/a
Hi Dave

"Michael (michka) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
message news:40********@news.microsoft.com...
You can use CreateReport and CreateReportControl, the programmatic commands that create a report (see online help for details).


I have recently helped (I hope) someone using the CreateReport method that
Micheal has mentioned above.

Here is the code (along with a little background info) that I / we ended up
using, if it might be of some help to you. :

We were wanting to create a report that displayed 2 "static" fields
("FirstName" and "LastName"), as well as a variable number of checkbox
fields that contained "True" values (IOW - display checkboxes that had been
"checked")

The basic premise is to build an SQL string ("MySQL") which is filtered by
the items selected in a multi-select listbox.
This SQL is used for the Recordsource of a subform, which is intended as a
"preview" of what you will see on the report that is about to be created.
This same SQL string is then used as the Recordsource of the report ... so
what you see in the subform is what you get in the report!
Just say "NO" when prompted to save the report after viewing or printing it.
(It's not nessecary to save as we want to re-create it each time.)

************************************************** **
Option Compare Database
Option Explicit
Dim MySQL As String 'This is declared in the form's declaration section so
that it can be used in more than one procedure on this form.

'-------------------------------------------------------------------------
Private Sub cmdCreateReport_Click()

'FYI notes that apply to the code below
'------------------------------------------
'Section(0) - Detail Section
'Section(1) - Report Header
'Section(2) - Report Footer
'Section(3) - Page Header
'Section(4) - Page Footer
'Section(5) - Group Level 1 Header
'Section(6) - Group Level 1 Footer
'Section(7) - Group Level 2 Header
'(all sizes in twips)... 1 twip = 1/1440 of an inch
'CreateReportControl(reportname, controltype[, section[, parent[,
columnname[, left[, top[, width[, height]]]]]]])
------------------------------------------

sRequerySubform ' Makes sure that the data is up-to-date as of the time that
this button is pressed.

Dim rpt As Report

Dim MyCtl As Control 'Probably could have used this for various control
types.
Dim ctlTextBox As Control
Dim ctlListBox As Control
Dim ctlLabel As Control
Dim ctlCheckBox As Control
Dim varItm As Variant
Dim strField As String
Dim intDataX As Integer, intDataY As Integer
Dim intLabelX As Integer, intLabelY As Integer

Set ctlListBox = Me.lstFieldList
' Create new report with tblCustomers as its record source.
Set rpt = CreateReport
rpt.RecordSource = MySQL 'Yup same good old SQL string

rpt.Section(0).Height = 500
rpt.Section(4).Height = 300
rpt.Width = 9360 '6.5 x 1440 twips/inch

'Set up the page header section (which is pretty "Plain Vanilla")
'------------------------------------------------------------------
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acPageHeader, , , 100,
100)
ctlLabel.Caption = "Whatever you want to call this report."
ctlLabel.Height = 720
ctlLabel.Width = 7000
ctlLabel.FontName = "Times New Roman"
ctlLabel.FontSize = 18
Set MyCtl = CreateReportControl(rpt.Name, acLine, acPageHeader, , , 0, 840,
9360)

'----------------------- Create the "fixed" label / textbox
fields --------------------------

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"FirstName", 1000, 100)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "FirstName", 100, 100)

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"LastName", 1000, 400)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "LastName", 100, 400)

'----------------------- Create the "variable" label / checkbox
fields --------------------------
' Initialize positioning values for the first pair of variable controls.
intLabelX = 2880
intLabelY = 100
intDataX = 3880
intDataY = 100
If ctlListBox.ItemsSelected.Count > 0 Then

For Each varItm In ctlListBox.ItemsSelected
strField = ctlListBox.ItemData(varItm)
Set ctlCheckBox = CreateReportControl(rpt.Name, acCheckBox, , ,
strField, intDataX, intDataY)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, ,
ctlCheckBox.Name, strField, intLabelX, intLabelY)

intLabelX = intLabelX + 1440 'each label is 1" apart
'intLabelY = 100
intDataX = intDataX + 1440 'each checkbox is also 1" apart
'intDataY = 100
Next varItm

End If

DoCmd.Maximize ' Maximize the report
DoCmd.RunCommand acCmdPrintPreview 'Switch to Print Preview mode

End Sub

'-------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
sRequerySubform
End Sub

'-------------------------------------------------------------------------
Private Sub lstFieldList_AfterUpdate()
sRequerySubform
End Sub

'-------------------------------------------------------------------------
Public Sub sRequerySubform()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.
'in order to alow the user to view:
' 1.) ALL records
' 2.) filtered by one field only
' 3.) filtered by multiple fields

Dim ctl As Control
Set ctl = Me.lstFieldList

Dim Msg As String

Dim CR As String
CR = vbCrLf

Dim varItm As Variant
Dim whr As String

'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT tblCustomers.*"
MySQL = MySQL & " FROM tblCustomers "

'Build the WHERE portion
whr = "" 'initialize variable
If ctl.ItemsSelected.Count > 0 Then

For Each varItm In ctl.ItemsSelected

If Len(whr) > 0 Then
whr = whr & " OR " & ctl.ItemData(varItm) & "= True"
Else
whr = whr & ctl.ItemData(varItm) & "= True"
End If

Next varItm

End If

If Len(whr) > 0 Then
MySQL = MySQL & "WHERE (" & whr & ")"
End If

MySQL = MySQL & "; "
'Debug.Print MySQL

Me.sbfCustomers.Form.RecordSource = MySQL
Set ctl = Nothing

End Sub
************************************************** **

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

Nov 13 '05 #6

P: n/a
Hmmm...
Here's a wee improvement on the code in in my previous post.
It deals with the problem of the number of controls exceeding the maximum
width of the report, and forces them to "wrap" them to a new line...

********************
.....

For Each varItm In ctlListBox.ItemsSelected
strField = ctlListBox.ItemData(varItm)
Set ctlCheckBox = CreateReportControl(rpt.Name, acCheckBox, , ,
strField, intDataX, intDataY)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, ,
ctlCheckBox.Name, strField, intLabelX, intLabelY)

intLabelX = intLabelX + 1440 'each label is 1" apart
If intLabelX > 7200 Then '7200 = 5 inches (maximum allowed)

intLabelY = intLabelY + 200 'Wrap label to new line
intLabelX = 2880 ' Reset label to intial position, so that it is
aligned with first row
End If

intDataX = intDataX + 1440 'each checkbox is also 1" apart
If intDataX > 8640 Then '8640 = 6 inches (maximum allowed)
intDataY = intDataY + 200 'Wrap checkbox to new line
intDataX = 3880 ' Reset checkbox to intial position, so that it
is aligned with first row
End If

Next varItm

.....
********************

"Don Leverton" <le****************@telusplanet.net> wrote in message
news:TRouc.4216$CD4.3323@edtnps84...
************************************************** **
Option Compare Database
Option Explicit
Dim MySQL As String 'This is declared in the form's declaration section so
that it can be used in more than one procedure on this form.

'-------------------------------------------------------------------------
Private Sub cmdCreateReport_Click()

'FYI notes that apply to the code below
'------------------------------------------
'Section(0) - Detail Section
'Section(1) - Report Header
'Section(2) - Report Footer
'Section(3) - Page Header
'Section(4) - Page Footer
'Section(5) - Group Level 1 Header
'Section(6) - Group Level 1 Footer
'Section(7) - Group Level 2 Header
'(all sizes in twips)... 1 twip = 1/1440 of an inch
'CreateReportControl(reportname, controltype[, section[, parent[,
columnname[, left[, top[, width[, height]]]]]]])
------------------------------------------

sRequerySubform ' Makes sure that the data is up-to-date as of the time that this button is pressed.

Dim rpt As Report

Dim MyCtl As Control 'Probably could have used this for various control
types.
Dim ctlTextBox As Control
Dim ctlListBox As Control
Dim ctlLabel As Control
Dim ctlCheckBox As Control
Dim varItm As Variant
Dim strField As String
Dim intDataX As Integer, intDataY As Integer
Dim intLabelX As Integer, intLabelY As Integer

Set ctlListBox = Me.lstFieldList
' Create new report with tblCustomers as its record source.
Set rpt = CreateReport
rpt.RecordSource = MySQL 'Yup same good old SQL string

rpt.Section(0).Height = 500
rpt.Section(4).Height = 300
rpt.Width = 9360 '6.5 x 1440 twips/inch

'Set up the page header section (which is pretty "Plain Vanilla")
'------------------------------------------------------------------
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acPageHeader, , , 100, 100)
ctlLabel.Caption = "Whatever you want to call this report."
ctlLabel.Height = 720
ctlLabel.Width = 7000
ctlLabel.FontName = "Times New Roman"
ctlLabel.FontSize = 18
Set MyCtl = CreateReportControl(rpt.Name, acLine, acPageHeader, , , 0, 840, 9360)

'----------------------- Create the "fixed" label / textbox
fields --------------------------

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"FirstName", 1000, 100)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "FirstName", 100, 100)

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"LastName", 1000, 400)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "LastName", 100, 400)

'----------------------- Create the "variable" label / checkbox
fields --------------------------
' Initialize positioning values for the first pair of variable controls.
intLabelX = 2880
intLabelY = 100
intDataX = 3880
intDataY = 100
If ctlListBox.ItemsSelected.Count > 0 Then

For Each varItm In ctlListBox.ItemsSelected
strField = ctlListBox.ItemData(varItm)
Set ctlCheckBox = CreateReportControl(rpt.Name, acCheckBox, , ,
strField, intDataX, intDataY)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, ,
ctlCheckBox.Name, strField, intLabelX, intLabelY)

intLabelX = intLabelX + 1440 'each label is 1" apart
'intLabelY = 100
intDataX = intDataX + 1440 'each checkbox is also 1" apart
'intDataY = 100
Next varItm

End If

DoCmd.Maximize ' Maximize the report
DoCmd.RunCommand acCmdPrintPreview 'Switch to Print Preview mode

End Sub

'-------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
sRequerySubform
End Sub

'-------------------------------------------------------------------------
Private Sub lstFieldList_AfterUpdate()
sRequerySubform
End Sub

'-------------------------------------------------------------------------
Public Sub sRequerySubform()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.
'in order to alow the user to view:
' 1.) ALL records
' 2.) filtered by one field only
' 3.) filtered by multiple fields

Dim ctl As Control
Set ctl = Me.lstFieldList

Dim Msg As String

Dim CR As String
CR = vbCrLf

Dim varItm As Variant
Dim whr As String

'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT tblCustomers.*"
MySQL = MySQL & " FROM tblCustomers "

'Build the WHERE portion
whr = "" 'initialize variable
If ctl.ItemsSelected.Count > 0 Then

For Each varItm In ctl.ItemsSelected

If Len(whr) > 0 Then
whr = whr & " OR " & ctl.ItemData(varItm) & "= True"
Else
whr = whr & ctl.ItemData(varItm) & "= True"
End If

Next varItm

End If

If Len(whr) > 0 Then
MySQL = MySQL & "WHERE (" & whr & ")"
End If

MySQL = MySQL & "; "
'Debug.Print MySQL

Me.sbfCustomers.Form.RecordSource = MySQL
Set ctl = Nothing

End Sub
************************************************** **

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.