vba - MS Access 文本框默认值最大大小限制

Question

我有以下设置:

我有一个带有未绑定(bind)文本框控件的表单。我有一个在每个 AfterUpdate 事件后触发的过程。

该过程将输入值发送到表中，并将表中输入的值作为文本框的 DefaultValue 返回。该表通过表单和控件名称、2 个主键定义唯一行。

输入数据后，有一个按钮可以将数据以结构化方式从表单发送到另一个表，并从表中读取报告。数据通过 SQL 查询以结构化方式输入到表中。

问题是，当用户在其中两个字段中输入超过 2048 个字符的文本时，代码会因限制而中断。由于表单的性质，每页表单上的两个字段可能会包含超过 2k 个字符。

我的问题是，我可以规避、增加或绕过字符限制吗？

我的代码发布在下面，以供需要时引用(名为 OptimizeS 的过程加载到表单上的每个文本框中):

将输入值写入表的过程:

Private Sub OptimizeS()
Dim cmd As ADODB.Command
Dim strForm As String
Dim strControl As String
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "FormName = """ & Me.Name & """ " & _
"And ControlName = """ & Me.ActiveControl.Name & """"
' is there an existing default for this control?
If Not IsNull(DLookup("FormName", "Defaults", strCriteria)) Then
' if so then update row in table
strSQL = "UPDATE Defaults " & _
"SET DefaultVal = """ & Me.ActiveControl & """ " & _
"WHERE " & strCriteria
Else
' insert new row
strSQL = "INSERT INTO Defaults(" & _
"FormName,ControlName,DefaultVal) " & _
"VALUES(""" & Me.Name & """,""" & _
Me.ActiveControl.Name & """,""" & _
Me.ActiveControl & """)"
End If

cmd.CommandText = strSQL
cmd.Execute
End Sub

表格本身的支持程序:

1)

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim cmd As ADODB.Command
Dim strOpened As String
Dim strForm As String
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strOpened = "Opened = """ & Me.Name & """ "

If Not IsNull(DLookup("Opened", "AllOpened", strOpened)) Then
strSQL = "UPDATE AllOpened " & _
"SET Opened = """ & Me.Name & """ " & _
"WHERE " & strOpened
Else
strSQL = "INSERT INTO AllOpened(Opened) " & _
"VALUES(""" & Me.Name & """)"
End If

cmd.CommandText = strSQL
cmd.Execute
End Sub

2)

Private Sub Form_Open(Cancel As Integer)
Dim cmd As ADODB.Command
Dim strCriteria As String
Dim strOpened As String
Dim varDefault As Variant
Dim varOpened As Variant
Dim strForm As String
Dim strSQL As String
Dim ctrl As Control

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strOpened = "Opened = """ & Me.Name & """ "

varOpened = DLookup("Opened", "AllOpened", strOpened)
If IsNull(varOpened) Then
strSQL = "INSERT INTO AllOpened(Opened) " & _
"VALUES(""" & Me.Name & """)"
cmd.CommandText = strSQL
cmd.Execute
Else
GoTo ErrMsg
End If

For Each ctrl In Me.Controls
strCriteria = "FormName = """ & Me.Name & """ " & _
"And ControlName = """ & ctrl.Name & """"

varDefault = DLookup("DefaultVal", "Defaults", strCriteria)
If Not IsNull(varDefault) Then
ctrl.DefaultValue = """" & varDefault & """"
End If
Next ctrl
Exit Sub
ErrMsg:
MsgBox ("The form is already open by another user. Please double-check before editing."), , "Important!!!"
End Sub

3)

Private Sub Form_Unload(Cancel As Integer)
Dim strSQL As String
Dim strForm As String
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "DELETE FROM AllOpened " & _
        "WHERE Opened = """ & Me.Name & """"

cmd.CommandText = strSQL
cmd.Execute

End Sub

Answer 1

这是您上一条评论中的暗示。然后使用字符串变量尝试此详细代码，该变量肯定没有 2K 限制:

Dim DefaultString As String

DefaultString = Nz(varDefault)
ctrl.DefaultValue = DefaultString