Assign OnClick with VBA

[chris seiter]

I'm trying to create a form that will auto populate with buttons that will
open reports in the database. As more reports get added, more buttons will
be created automatically.

I've got a form called "frm_Reports" that has an OnOpen Sub that looks at
all the reports in the database and creates a new form called
"frm_CreateReports" and makes a button for each report. I'm stuck on the
part where I assign the OnClick function to actually open the report. Here's
the OnOpen code I'm using to create the buttons on the new form:

'Blow away the current form
DoCmd.DeleteObject acForm, "frm_CreateReports"
'create new form from template
DoCmd.CopyObject CurrentProject.FullName, "frm_CreateReports", acForm,
"frm_CreateReportTemplate"
'open form in design mode to add buttons
DoCmd.OpenForm "frm_CreateReports", acDesign, , , , acHidden
Set db = CurrentDb
'qry_Reports givea a list of the current reports in the db, borrowed from
website
Set rs = db.OpenRecordset("qry_Reports")
'cycle through each line of query, setting button properties
rs.MoveFirst
DoCmd.SetWarnings (off)
Do While Not rs.EOF
strButtonCaption = ""
strButtonName = ""
intFromTop = intFromTop + 1
intFromLeft = 1
Set ctl = CreateControl("frm_CreateReports", acCommandButton, , , ,
intFromLeft * 50, intFromTop * 510)
strButtonCaption = Replace(rs![EXPR1], "_", " ")
ctl.Caption = strButtonCaption
ctl.Height = 500
ctl.Width = Len(strButtonCaption) * 110
ctl.FontSize = 11
ctl.TabStop = False
strButtonName = "cmd_" & rs![EXPR1]
ctl.Name = strButtonName
ctl.OnClick = strButtonCaption <---Stuck here
rs.MoveNext
Loop
DoCmd.Save acForm, "frm_CreateReports"
DoCmd.OpenForm "frm_CreateReports", acNormal
DoCmd.Close acForm, "frm_Reports"

I've tried to type in the "docmd" code to just open the report, but it tells
me it doesn't know what docmd is.

Any thoughts?

 

[Jim Burke in Novi]

If I were you I would just populate a listbox with the report names, using
the same query that you're using to create your button controls. You can
modify the query to create the field that strips off the prefix of the
report. Then the user would just click on the report name in the list box. Is
there any particular reason why you're using buttons?

 

[chris seiter]

When I was originally trying, I made a switchboard a edited the table
directly. Once I thought about the number of reports may go over 7, I just
continued on with the "switchboard button" mentality and had blinders on.
I'll was able to make a list box with the list of reports, but it looks kinda
"horkey" with only two reports. It will look better when it fills up. I'll
figure out the controls and properties for the list box later on.

Thanks for taking off my blinders.

If I were you I would just populate a listbox with the report names, using
the same query that you're using to create your button controls. You can
modify the query to create the field that strips off the prefix of the
report. Then the user would just click on the report name in the list box. Is
there any particular reason why you're using buttons?

I'm trying to create a form that will auto populate with buttons that will
open reports in the database. As more reports get added, more buttons will
be created automatically.

I've got a form called "frm_Reports" that has an OnOpen Sub that looks at
all the reports in the database and creates a new form called
"frm_CreateReports" and makes a button for each report. I'm stuck on the
part where I assign the OnClick function to actually open the report. Here's
the OnOpen code I'm using to create the buttons on the new form:

'Blow away the current form
DoCmd.DeleteObject acForm, "frm_CreateReports"
'create new form from template
DoCmd.CopyObject CurrentProject.FullName, "frm_CreateReports", acForm,
"frm_CreateReportTemplate"
'open form in design mode to add buttons
DoCmd.OpenForm "frm_CreateReports", acDesign, , , , acHidden
Set db = CurrentDb
'qry_Reports givea a list of the current reports in the db, borrowed from
website
Set rs = db.OpenRecordset("qry_Reports")
'cycle through each line of query, setting button properties
rs.MoveFirst
DoCmd.SetWarnings (off)
Do While Not rs.EOF
strButtonCaption = ""
strButtonName = ""
intFromTop = intFromTop + 1
intFromLeft = 1
Set ctl = CreateControl("frm_CreateReports", acCommandButton, , , ,
intFromLeft * 50, intFromTop * 510)
strButtonCaption = Replace(rs![EXPR1], "_", " ")
ctl.Caption = strButtonCaption
ctl.Height = 500
ctl.Width = Len(strButtonCaption) * 110
ctl.FontSize = 11
ctl.TabStop = False
strButtonName = "cmd_" & rs![EXPR1]
ctl.Name = strButtonName
ctl.OnClick = strButtonCaption <---Stuck here
rs.MoveNext
Loop
DoCmd.Save acForm, "frm_CreateReports"
DoCmd.OpenForm "frm_CreateReports", acNormal
DoCmd.Close acForm, "frm_Reports"

I've tried to type in the "docmd" code to just open the report, but it tells
me it doesn't know what docmd is.

Any thoughts?

 

[BeWyched]

Hi Chris

Jim is correct - you are going about this in a rather cumbersome way.

However, if you are determined you could use the CreateEventProc method
which gives you what you want. The method is described fully in the VBA Help
files.

Cheers.

BW

When I was originally trying, I made a switchboard a edited the table
directly. Once I thought about the number of reports may go over 7, I just
continued on with the "switchboard button" mentality and had blinders on.
I'll was able to make a list box with the list of reports, but it looks kinda
"horkey" with only two reports. It will look better when it fills up. I'll
figure out the controls and properties for the list box later on.

Thanks for taking off my blinders.

If I were you I would just populate a listbox with the report names, using
the same query that you're using to create your button controls. You can
modify the query to create the field that strips off the prefix of the
report. Then the user would just click on the report name in the list box. Is
there any particular reason why you're using buttons?

I'm trying to create a form that will auto populate with buttons that will
open reports in the database. As more reports get added, more buttons will
be created automatically.

I've got a form called "frm_Reports" that has an OnOpen Sub that looks at
all the reports in the database and creates a new form called
"frm_CreateReports" and makes a button for each report. I'm stuck on the
part where I assign the OnClick function to actually open the report. Here's
the OnOpen code I'm using to create the buttons on the new form:

'Blow away the current form
DoCmd.DeleteObject acForm, "frm_CreateReports"
'create new form from template
DoCmd.CopyObject CurrentProject.FullName, "frm_CreateReports", acForm,
"frm_CreateReportTemplate"
'open form in design mode to add buttons
DoCmd.OpenForm "frm_CreateReports", acDesign, , , , acHidden
Set db = CurrentDb
'qry_Reports givea a list of the current reports in the db, borrowed from
website
Set rs = db.OpenRecordset("qry_Reports")
'cycle through each line of query, setting button properties
rs.MoveFirst
DoCmd.SetWarnings (off)
Do While Not rs.EOF
strButtonCaption = ""
strButtonName = ""
intFromTop = intFromTop + 1
intFromLeft = 1
Set ctl = CreateControl("frm_CreateReports", acCommandButton, , , ,
intFromLeft * 50, intFromTop * 510)
strButtonCaption = Replace(rs![EXPR1], "_", " ")
ctl.Caption = strButtonCaption
ctl.Height = 500
ctl.Width = Len(strButtonCaption) * 110
ctl.FontSize = 11
ctl.TabStop = False
strButtonName = "cmd_" & rs![EXPR1]
ctl.Name = strButtonName
ctl.OnClick = strButtonCaption <---Stuck here
rs.MoveNext
Loop
DoCmd.Save acForm, "frm_CreateReports"
DoCmd.OpenForm "frm_CreateReports", acNormal
DoCmd.Close acForm, "frm_Reports"

I've tried to type in the "docmd" code to just open the report, but it tells
me it doesn't know what docmd is.

Any thoughts?

 

[chris seiter]

All this just so the wife can keep track of her cross stich thread and
projects. Thanks guys.

Hi Chris

Jim is correct - you are going about this in a rather cumbersome way.

However, if you are determined you could use the CreateEventProc method
which gives you what you want. The method is described fully in the VBA Help
files.

Cheers.

BW

When I was originally trying, I made a switchboard a edited the table
directly. Once I thought about the number of reports may go over 7, I just
continued on with the "switchboard button" mentality and had blinders on.
I'll was able to make a list box with the list of reports, but it looks kinda
"horkey" with only two reports. It will look better when it fills up. I'll
figure out the controls and properties for the list box later on.

Thanks for taking off my blinders.

If I were you I would just populate a listbox with the report names, using
the same query that you're using to create your button controls. You can
modify the query to create the field that strips off the prefix of the
report. Then the user would just click on the report name in the list box. Is
there any particular reason why you're using buttons?

:

I'm trying to create a form that will auto populate with buttons that will
open reports in the database. As more reports get added, more buttons will
be created automatically.

I've got a form called "frm_Reports" that has an OnOpen Sub that looks at
all the reports in the database and creates a new form called
"frm_CreateReports" and makes a button for each report. I'm stuck on the
part where I assign the OnClick function to actually open the report. Here's
the OnOpen code I'm using to create the buttons on the new form:

'Blow away the current form
DoCmd.DeleteObject acForm, "frm_CreateReports"
'create new form from template
DoCmd.CopyObject CurrentProject.FullName, "frm_CreateReports", acForm,
"frm_CreateReportTemplate"
'open form in design mode to add buttons
DoCmd.OpenForm "frm_CreateReports", acDesign, , , , acHidden
Set db = CurrentDb
'qry_Reports givea a list of the current reports in the db, borrowed from
website
Set rs = db.OpenRecordset("qry_Reports")
'cycle through each line of query, setting button properties
rs.MoveFirst
DoCmd.SetWarnings (off)
Do While Not rs.EOF
strButtonCaption = ""
strButtonName = ""
intFromTop = intFromTop + 1
intFromLeft = 1
Set ctl = CreateControl("frm_CreateReports", acCommandButton, , , ,
intFromLeft * 50, intFromTop * 510)
strButtonCaption = Replace(rs![EXPR1], "_", " ")
ctl.Caption = strButtonCaption
ctl.Height = 500
ctl.Width = Len(strButtonCaption) * 110
ctl.FontSize = 11
ctl.TabStop = False
strButtonName = "cmd_" & rs![EXPR1]
ctl.Name = strButtonName
ctl.OnClick = strButtonCaption <---Stuck here
rs.MoveNext
Loop
DoCmd.Save acForm, "frm_CreateReports"
DoCmd.OpenForm "frm_CreateReports", acNormal
DoCmd.Close acForm, "frm_Reports"

I've tried to type in the "docmd" code to just open the report, but it tells
me it doesn't know what docmd is.

Any thoughts?