473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Programmatic Built-in Commands

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
6 2423
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
"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
"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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: JimmyT | last post by:
I just configured and installed 2.3.4 and noticed there is no datetime module. I noticed there is a datetimemodule.c file that did not get built (ie no object file). Is there something I need to...
1
by: Alex Elbert | last post by:
Hi I have built dynamic HTMLTable. Now I want to attach it directly to the Email Body - it is already built, so why not to use a ready table. However, I cannot find the way of getting plain HTML...
0
by: Andrew Crook | last post by:
does MYSQL have a quota built into it! I need it limit the size of each database AndiC
1
by: Mark | last post by:
Is there a way to execute a statement that is built dynamically by a .NET application. For example I have a loop that is reading values from a database and I want to do something like the...
4
by: Yasutaka Ito | last post by:
Hi, Is there a way to determine which version of .NET Framework any given assembly is built with? thanks! -Yasutaka
1
by: William | last post by:
Looking for a pre built dotnet corporate or small business website template.
0
by: Daniel | last post by:
programmatic to ftp virtual directories when i connect to an ftp server and i only have access to a few of the ftp virtual directories, do i just change directory to them after connecting and...
6
by: Blaine Manyluk | last post by:
I have a very unusual request. I need to be able to generate reports and save them as TIF files, with full programmatic control. The application will provide the filenames. Each page of the...
48
by: meyer | last post by:
Hi everyone, which compiler will Python 2.5 on Windows (Intel) be built with? I notice that Python 2.4 apparently has been built with the VS2003 toolkit compiler, and I read a post from Scott...
3
by: drewj840 | last post by:
I built a Windows service that sweeps a set of folders every 60 seconds and puts the files into a SQL Server database. I am creating a second service that will delete this set of folders and recreate...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.