在不打开表单的情况下获取Form Recordsource [英] Get Form Recordsource without opening the form
问题描述
MS Access是否允许在不打开表单本身的情况下获取表单的记录源值?到目前为止，我正在尝试优化我的代码，我所做的是我只是隐藏表单，然后获取Recordsource表单查询，但是由于某些表单在onload上触发了代码，因此加载它需要花费时间.
我在这里很晚-有时我会在发布原始问题数月或数年后发布答案，因为当我快速搜索时会发布自己的解决方案的堆栈"发现了与我今天自己的问题相关的问题，但没有我可以实际使用的答案.
[更新，2016年6月6日]
从Access 2010开始，文档对象中'NameMap'属性不可用.但是，"Stacker Thunderframe 指出，现在在"MsysNameMap"表中可用.
我已经修改了代码，并且可以在Access 2010和2013中使用.
[/UPDATE]
大多数表单属性仅在打开表单时可用，但某些属性在DAO Documents集合的表单条目中可用.
DAO文档"是一个可怕的对象:它不会持久存在于内存中，您每次使用它时都必须明确地引用它:
FormName ="MyForm"
对于 To To.nbsp; Application.CodeDb.Containers("Forms").Documents(FormName).Properties.Count - 1
Debug.Print i vbTab Application.CodeDb.Containers("Forms").Documents(FormName).Properties(i).Name ; vbTab& vbTab&& Application.CodeDb.Containers("Forms").Documents(FormName).Properties(i).Value
下一个
为您的表单运行该代码段，您将看到一个'NameMap'属性，其中包含表单控件的列表以及 some 表单属性.
...以一种真正的可怕格式，需要二进制解析器.您可能想立即停止阅读并服用阿司匹林，然后再继续.
健康警告:
NameMap属性未记录.因此，它不受支持，并且不能保证此解决方案将在将来的Microsoft Access版本中运行.如果NameMap的记录源的两字节二进制标签发生更改，或者它是特定于语言环境的，则下面代码中的解决方案将停止工作.
这是一个可怕的骇客:对于您的理智有任何影响，我不承担任何责任.
好的，这是代码:
一个VBA函数，用于从封闭的MS-Access表单返回记录源:
私有函数FormRecordSource_FromNameMap(FormName As String)As String
'从Document对象的NameMap属性读取记录源的形式.
'警告:此处可能存在错误:如果表单的RecordSource属性为空 '，并且具有一个或多个带有.RecordSource属性的列表控件 '列表，此函数将返回第一个列表控件的Record Source.
'如果您在表单名称中使用非ASCII字符(字符> 255)，则此方法将无效.
昏暗的整数 Dim j作为整数 Dim k As Integer
昏暗arrByte()为字节
Dim strOut作为字符串 如果是Application.Version< 12然后
arrByte = Application.CodeDb.Containers("Forms").Documents(FormName).Properties("NameMap").Value
对于i = 1到UBound(arrByte)-2步骤2
'NameMap中querydef的2字节标记:
如果(arrByte(i)= 228和arrByte(i + 1)= 64)然后
j =我+ 2 当arrByte(j)= 0且arrByte(j + 1)= 0且j<时做UBound(arrByte) '循环遍历标记和字符串开头之间的空字符 j = j + 2 循环
strOut =" 直到(arrByte(j)= 0并且arrByte(j + 1)= 0)或j> = UBound(arrByte)-2 如果arrByte(j)= 0则j = j + 1 '循环直到我们到达终止该字符串的null char '追加表或查询的Bchar(不是Unicode Wchars！) strOut = strOut& Chr(arrByte(j)) j = j + 2 循环
对于'退出，我们只需要第一个数据源 如果结束
下一步i
其他
arrByte = Nz(DLookup("[NameMap]"，"[MSYSNameMap]"，"[Name] ='"& FormName&'")，vbNullChar)
如果UBound(arrByte)< 4然后退出功能
strOut =" 对于j = 60到UBound(arrByte)-2步骤2
如果arrByte(j)= 0且arrByte(j + 1)= 0，则退出For
strOut = strOut& Chr(arrByte(j))
下一页j
如果
则结束 frmRecordSource_FromNameMap = strOut
擦除arrByte
最终功能
如果您在(例如)OpenRecordset中使用RecordSource或DCOUNT函数，我建议您将其封装在方括号中:您可能会获得从RecordSource中的"SELECT"语句保存的隐藏查询对象的名称，并且该名称将包含需要特殊处理的'〜'波浪号字符.
现在，您没有要求提供一些额外的功能，但是其他人会在这里搜索"MS Access RecordSource for a close form"的方式吗?
获取MS-Access窗体的RecordSource，无论它是否打开
大多数情况下，您的表格将打开.问题是，您不知道...而且，如果它是子窗体，则在Forms()集合中可能不可见.更糟糕的是，作为子窗体托管的窗体可能在多个打开的窗体中作为多个实例存在.
祝您好运，如果您希望提取动态属性……就像过滤器一样，或者如果VBA将其设置为即时"，则可以选择记录源".
分享并享受:对于代码示例中任何不必要的换行符，请接受我的歉意.公共函数GetForm(表单名称为字符串，可选，父名称为字符串(=")) 如果表单名称为 " FormName "FormName"可以包含通配符. 如果未打开匹配表格，则不会返回任何内容.
枚举打开的表单中的子表单，并返回该子表单. "具有匹配的名称.请注意，表单"可能会以多个实例的形式打开. 比一个子表单托管的子项多的功能"会返回第一个匹配项" 实例".请指定名为父级"表格的表单(或子表单"控件的名称)，如果 您需要避免来自该表格的多个实例的错误.
Dim&objb&AssAccess.Form
如果父母姓名" ="然后 对于每个objForm In Informs If objForm.Name Set GetForm = objForm "退出功能 End 如果 下一步 如果结束
如果没有GetForm，然后 对于每个objForm In Informs Set GetForm = SearchSubForms(objForm， FormName， ParentName) 如果不是GetForm就是什么，然后 &退出 End 如果 下一步 如果结束
结束功能
私有函数SearchSubForms(objForm Access.Form，SubFormName As String， 可选 ParentName As = ) " " " " " " " " " " " 带有子名称的返回的表单对象"，例如子表单名称"，如果子对象名称"为子表单名称 可以以递归方式将[nbsp]子表单的"nbsp"或"nbsp"的"nbsp; nbsp; nbsp;"或"nbsp; nbsp; nbsp;"
'n此功能返回第一个匹配表:请注意，表n可以被实例化 实例"是否比子表单"控件使用的实例更多.
昏暗的objCtrl As 控件 对于每个objCtrl&in objForm
TypeName(objCtrl) = "SubForm" If objCtrl.Form.Name就像SubFormName一样 ParentName = ; Like ParentName Then Set SearchSubForms = Ctrl. 结束 Set SearchSubForms = SearchSubForms(objCtrl.Form， 结束 End 如果 结束
下一个objCtrl
结束功能
公共函数FormRecordSource(表单名称为字符串，可选，父项名称为字符串，==") ' sp; & s
' a n a a a a a a a a a a a 为该表单托管的子表单的父表单名称:您的命名表单可能会打开 子表单实例"比一个父母表单"更多.
'警告:有可能存在错误的地方:如果该表格不是打开的，并且是 ' sp ssp s b b b ' 属性 填充一个n a a sp b sp b &b
Dim&obj> AsForm
如果FormName = "然后 退出功能 如果结束
设置objForm = GetForm(FormName， ParentName)
如果objForm没问题，然后 FormRecordSource = FormRecordSource_FromNameMap(FormName) 别的 FormRecordSource = objForm.RecordSource Set objForm = 如果结束
结束功能
Does MS Access allow to get the recordsource value of the form without opening the form itself? I'm trying to optimize my code as of now, what I did is I just hide the form then get the Recordsource form query but it takes time to load since some of the forms trigger a code upon onload.
I'm late to the game here - I sometimes post answers months or years after the original question was posted, as I post my own solutions when a quick search of the 'Stack finds questions relevant to my own problem of the day, but no answers that I can actually use.
[UPDATE, 06 June 2016]
The 'NameMap' property is not available in document objects from Access 2010 onwards. However, 'Stacker Thunderframe has pointed out that this is now available in the 'MsysNameMap' table.
I have amended the code, and this works in Access 2010 and 2013.
[/UPDATE]
Most of a form's properties are only available when the form is open, but some are available in the form's entry in the DAO Documents collection.
The DAO 'document' is a horrible object: it won't persist in memory and you have to refer to it explicitly every time you use it:
FormName = "MyForm"
For i = 0 To Application.CodeDb.Containers("Forms").Documents(FormName).Properties.Count - 1
Debug.Print i & vbTab & Application.CodeDb.Containers("Forms").Documents(FormName).Properties(i).Name & vbTab & vbTab & Application.CodeDb.Containers("Forms").Documents(FormName).Properties(i).Value
Next
Run that snippet for your form, and you'll see a 'NameMap' property that contains a list of the form's controls, and some of the form's properties.
...In a truly horrible format which needs a binary parser. You might want to stop reading and take an aspirin, right now, before continuing.
Health Warnings:
The NameMap Property is undocumented. It is therefore unsupported and there is no guarantee that this solution will work in future versions of Microsoft Access.The solution in my code below will stop working if the NameMap's two-byte binary label for a Record Source ever changes, or if it's locale-specific.
This is a horrible hack: I accept no liability for any effects on your sanity.
OK, here's the code:
A VBA function to return the Record Source from a closed MS-Access form:
Private Function FormRecordSource_FromNameMap(FormName As String) As String
' Reads the Record Source from the NameMap Property of the Document object for the form.
' WARNING: there is a potential error here: if the form's RecordSource property is blank ' and it has one or more list controls with a .RecordSource property populating ' the list, this function will return the first list control's Record Source.
' This won't work if you're using non-ASCII characters (Char > 255) in your form name.
Dim i As Integer Dim j As Integer Dim k As Integer
Dim arrByte() As Byte
Dim strOut As String If Application.Version < 12 Then
arrByte = Application.CodeDb.Containers("Forms").Documents(FormName).Properties("NameMap").Value
For i = 1 To UBound(arrByte) - 2 Step 2
' 2-byte marker for a querydef in the NameMap:
If (arrByte(i) = 228 And arrByte(i + 1) = 64) Then
j = i + 2 Do While arrByte(j) = 0 And arrByte(j + 1) = 0 And j < UBound(arrByte) ' loop through the null chars between the marker and the start of the string j = j + 2 Loop
strOut = "" Do Until (arrByte(j) = 0 And arrByte(j + 1) = 0) Or j >= UBound(arrByte) - 2 If arrByte(j) = 0 Then j = j + 1 ' loop until we reach the null char which terminates this string ' appending the Bchars (not unicode Wchars!) of the table or query strOut = strOut & Chr(arrByte(j)) j = j + 2 Loop
Exit For ' we only want the first datasource End If
Next i
Else
arrByte = Nz(DLookup("[NameMap]", "[MSYSNameMap]", "[Name] = '" & FormName & "'"), vbNullChar)
If UBound(arrByte) < 4 Then Exit Function
strOut = "" For j = 60 To UBound(arrByte) - 2 Step 2
If arrByte(j) = 0 And arrByte(j + 1) = 0 Then Exit For
strOut = strOut & Chr(arrByte(j))
Next j
End If
frmRecordSource_FromNameMap = strOut
Erase arrByte
End Function
If you use the RecordSource in (say) OpenRecordset or a DCOUNT function, I would advise you to encapsulate it in square brackets: you might get the name of a hidden query object saved from a 'SELECT' statement in the RecordSource, and that name will contain '~' tilde characters which need special handling.
And now, something extra that you didn't ask for, but other people will be looking for if they Googled their way here for 'MS Access RecordSource for a closed form':
Getting an MS-Access form's RecordSource, whether it's open or not
Most times, your form will be open. Problem is, you don't know that... And if it's a subform, it might not be visible in the Forms() collection. Worse, a form that's hosted as a subform might exist as multiple instances in several open forms.
Good luck with that, if you're looking to extract dynamic properties... Like filters, or the Record Source if it's set 'on the fly' by VBA.
Share and enjoy: and please accept my apologies for any unwanted line breaks in the code sample.Public Function GetForm(FormName As String, Optional ParentName As String = "") As Form ' Returns a form object, if a form with a name like FormName is open ' FormName can include wildcards. ' Returns Nothing if no matching form is open.
' Enumerates subforms in open forms, and returns the subform .form object if ' it has a matching name. Note that a form may be open as multiple instances ' if more than one subform hosts it; the function returns the first matching ' instance. Specify the named parent form (or the subform control's name) if ' you need to avoid an error arising from multiple instances of the form.
Dim objForm As Access.Form
If ParentName = "" Then For Each objForm In Forms If objForm.Name Like FormName Then Set GetForm = objForm Exit Function End If Next End If
If GetForm Is Nothing Then For Each objForm In Forms Set GetForm = SearchSubForms(objForm, FormName, ParentName) If Not GetForm Is Nothing Then Exit For End If Next End If
End Function
Private Function SearchSubForms(objForm As Access.Form, SubFormName As String, Optional ParentName As String = "") As Form ' Returns a Form object with a name like SubFormName, if the named object SubFormName is subform ' of an open form , or can be recursively enumerated as the subform of an open subform.
' This function returns the first matching Form: note that a form can be instantiated in multiple ' instances if it is used by more than one subform control.
Dim objCtrl As Control For Each objCtrl In objForm
If TypeName(objCtrl) = "SubForm" Then If objCtrl.Form.Name Like SubFormName Then If ParentName = "" Or objForm.Name Like ParentName Or objCtrl.Name Like ParentName Then Set SearchSubForms = objCtrl.Form Exit For End If Else Set SearchSubForms = SearchSubForms(objCtrl.Form, SubFormName, ParentName) If Not SearchSubForms Is Nothing Then Exit For End If End If End If
Next objCtrl
End Function
Public Function FormRecordSource(FormName As String, Optional ParentName As String = "") As String ' Returns the Recordsource for a form, even if it isn't open in the Forms() collection
' This will look for open forms first. If you're looking for a subform, you may need a ' parent name for the form which hosts the subform: your named form might be open as a ' subform instance in more than one parent form.
' WARNING: there is a potential error here: if the form isn't open, and it has a blank ' RecordSource property, and it has one or more controls with a .RecordSource ' property populating a list, a list control's RecordSource could be returned
Dim objForm As Form
If FormName = "" Then Exit Function End If
Set objForm = GetForm(FormName, ParentName)
If objForm Is Nothing Then FormRecordSource = FormRecordSource_FromNameMap(FormName) Else FormRecordSource = objForm.RecordSource Set objForm = Nothing End If
End Function
这篇关于在不打开表单的情况下获取Form Recordsource的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
