vba 以编程方式创建在 Access 中打开表单的按钮
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8568074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Programmatically Create a Button That Opens a Form In Access
提问by p0lar_bear
When my database is opened, it shows a form with a "loading bar" that reports the progress of linking external tables and such, before showing a "Main Menu" form. The Main Menu has code that generates a form programmatically behind the scenes with buttons on it, and when that's done it saves and renames the form, and assigns it as the SourceObjectto a subform.
当我的数据库打开时，它会显示一个带有“加载栏”的表单，在显示“主菜单”表单之前，该表单报告链接外部表等的进度。主菜单有代码在幕后以编程方式生成一个带有按钮的表单，完成后它保存并重命名表单，并将其分配SourceObject给子表单。
This all works fine and dandy, that is, until I decide to make the buttons actually do something useful. In the loop that generates the buttons, it adds VBA code to the subform-to-be's module. For some reason, doing that makes VBA finish execution, then stop. This makes the (modal) loading form not disappear as there's an Ifstatement that executes a DoCmd.Closeto close the loading form when it's done loading. It also breaks functionality that depends on a global variable being set, since the global is cleared when execution halts.
这一切都很好，很花哨，也就是说，直到我决定让按钮真正做一些有用的事情。在生成按钮的循环中，它将 VBA 代码添加到子表单的模块中。出于某种原因，这样做会使 VBA 完成执行，然后停止。这使得（模态）加载表单不会消失，因为有一个If语句DoCmd.Close在加载完成后执行 a来关闭加载表单。它还破坏了依赖于正在设置的全局变量的功能，因为在执行停止时会清除全局变量。
Is there a better way to go about creating buttons that do stuff programmatically, short of ditching Access outright and writing real code? As much as I would love to, I'm forced to do it in Access in case I leave the company so the less tech-savvy employees can still work with it in my absence.
有没有更好的方法来创建以编程方式执行操作的按钮，而不是完全放弃 Access 并编写真正的代码？尽管我很乐意，但如果我离开公司，我不得不在 Access 中这样做，这样不太懂技术的员工在我不在的情况下仍然可以使用它。
Below are bits and pieces of relevant code, if needed.
如果需要，下面是相关代码的点点滴滴。
Form_USysSplash:
表单_USysSplash：
'Code that runs when the form is opened, before any processing.
Private Sub Form_Open(Cancel As Integer)
'Don't mess with things you shouldn't be.
If g_database_loaded Then
MsgBox "Please don't try to run the Splash form directly.", vbOKOnly, "No Touching"
Cancel = True
Exit Sub
End If
'Check if the user has the MySQL 5.1 ODBC driver installed.
Call CheckMysqlODBC 'Uses elfin majykks to find if Connector/ODBC is installed, puts the result into g_mysql_installed
If Not g_mysql_installed Then
Cancel = True
DoCmd.OpenForm "Main"
Exit Sub
End If
End Sub
'Code that runs when the form is ready to render.
Private Sub Form_Current()
'Prepare the form
boxProgressBar.width = 0
lblLoading.caption = ""
'Render the form
DoCmd.SelectObject acForm, Me.name
Me.Repaint
DoEvents
'Start the work
LinkOMTables
UpdateStatus "Done!"
DoCmd.OpenForm "Home"
f_done = True
End Sub
Private Sub Form_Timer() 'Timer property set to 100
If f_done Then DoCmd.Close acForm, Me.name
End Sub
Form_Home:
表格_首页：
'Code run before the form is displayed.
Private Sub Form_Load()
'Check if the user has the MySQL 5.1 ODBC driver installed.
'Header contains an error message and a download link
If Not g_mysql_installed Then
FormHeader.Visible = True
Detail.Visible = False
Else
FormHeader.Visible = False
Detail.Visible = True
CreateButtonList Me, Me.subTasks
End If
End Sub
'Sub to create buttons on the form's Detail section, starting at a given height from the top.
Sub CreateButtonList(ByRef frm As Form, ByRef buttonPane As SubForm)
Dim rsButtons As Recordset
Dim newForm As Form
Dim newButton As CommandButton
Dim colCount As Integer, rowCount As Integer, curCol As Integer, curRow As Integer
Dim newFormWidth As Integer
Dim taskFormName As String, newFormName As String
Set rsButtons = CurrentDb.OpenRecordset("SELECT * FROM USysButtons WHERE form LIKE '" & frm.name & "'")
If Not rsButtons.EOF And Not rsButtons.BOF Then
taskFormName = "USys" & frm.name & "Tasks"
On Error Resume Next
If TypeOf CurrentProject.AllForms(taskFormName) Is AccessObject Then
buttonPane.SourceObject = ""
DoCmd.DeleteObject acForm, taskFormName
End If
Err.Clear
On Error GoTo 0
Set newForm = CreateForm
newFormName = newForm.name
With newForm
.Visible = False
.NavigationButtons = False
.RecordSelectors = False
.CloseButton = False
.ControlBox = False
.width = buttonPane.width
.HasModule = True
End With
rsButtons.MoveLast
rsButtons.MoveFirst
colCount = Int((buttonPane.width) / 1584) 'Twips: 1440 in an inch. 1584 twips = 1.1"
rowCount = Round(rsButtons.RecordCount / colCount, 0)
newForm.Detail.height = rowCount * 1584
curCol = 0
curRow = 0
Do While Not rsButtons.EOF
Set newButton = CreateControl(newForm.name, acCommandButton)
With newButton
.name = "gbtn_" & rsButtons!btn_name
.Visible = True
.Enabled = True
.caption = rsButtons!caption
.PictureType = 2
.Picture = rsButtons!img_name
.PictureCaptionArrangement = acBottom
.ControlTipText = rsButtons!tooltip
.OnClick = "[Event Procedure]"
'This If block is the source of my headache.
If Not IsNull(rsButtons!open_query) And rsButtons!open_query <> "" Then
newForm.Module.InsertLines newForm.Module.CountOfLines, _
"Private Sub gbtn_" & rsButtons!btn_name & "_Click()"
newForm.Module.InsertLines newForm.Module.CountOfLines, _
"DoCmd.OpenQuery """ & rsButtons!open_query & """"
newForm.Module.InsertLines newForm.Module.CountOfLines, _
"End Sub" & vbCrLf & vbCrLf
ElseIf Not IsNull(rsButtons!open_form) And rsButtons!open_form <> "" Then
newForm.Module.InsertLines newForm.Module.CountOfLines, _
"Private Sub gbtn_" & rsButtons!btn_name & "_Click()"
newForm.Module.InsertLines newForm.Module.CountOfLines, _
"DoCmd.OpenForm """ & rsButtons!open_form & """"
newForm.Module.InsertLines newForm.Module.CountOfLines, _
"End Sub" & vbCrLf & vbCrLf
End If
.height = 1584
.width = 1584
.Top = 12 + (curRow * 1584)
.Left = 12 + (curCol * 1584)
.BackThemeColorIndex = 1
.HoverThemeColorIndex = 4 'Accent 1
.HoverShade = 0
.HoverTint = 40 '60% Lighter
.PressedThemeColorIndex = 4 'Accent 1
.PressedShade = 0
.PressedTint = 20 '80% Lighter
End With
curCol = curCol + 1
If curCol = colCount Then
curCol = 0
curRow = curRow + 1
End If
rsButtons.MoveNext
Loop
DoCmd.Close acForm, newForm.name, acSaveYes
DoCmd.Rename taskFormName, acForm, newFormName
buttonPane.SourceObject = taskFormName
End If
End Sub
回答by Stephen Turner
There is no need to write code while code is running, especially as you are writing essentially the same code over and over again. All you need do is call a function instead of an event procedure.
无需在代码运行时编写代码，尤其是当您一遍又一遍地编写本质上相同的代码时。您需要做的就是调用函数而不是事件过程。
In your code above write the OnClick event like this:
在上面的代码中，像这样编写 OnClick 事件：
If Not IsNull(rsButtons!open_query) And rsButtons!open_query <> "" Then
.OnClick = "=MyOpenForm(""" & rsButtons!open_form & """)"
ElseIf Not IsNull(rsButtons!open_form) And rsButtons!open_form <> "" Then
.OnClick = "=MyOpenQuery(""" & rsButtons!open_form & """)"
End If
Then create these two permanent (non-generated) functions somewhere the form can see them:
然后在表单可以看到它们的地方创建这两个永久（非生成）函数：
Public Function MyOpenForm(FormName as String)
DoCmd.OpenForm FormName
End Function
Public Function MyOpenQuery(QueryName as String)
DoCmd.OpenQuery QueryName
End Function
And ditch the code writing to the module.
并放弃写入模块的代码。
