提问人：CaptScott 提问时间：10/19/2023 最后编辑：GustavCaptScott 更新时间：10/20/2023 访问量：65
高级 VBA 和 SQL 代码不匹配类别
Advance VBA and SQL Code not matching catagorys
问：
我正在伸出援手，因为在我的代码中，我想将一些类别与当前开放访问表单中引用此表“tbl_PgmAndReqMonthly_Input”的内容进行匹配。我想将以下类别与名为“单元程序”的表进行匹配。（基本上，过滤以下字段）
[上级机构]
[职能领域] [基金] [资助类别]
一旦它们被匹配和过滤，我想得到该领域的总数 [单位资金] 然后，我想在标记为txtPgmTotal的文本框中的表单中显示该数字。
然后我想在 txtPgmUsed 中显示 TotalPgm 编号。
然后，我希望从中减去标记为txtPgmRemainal的形式中的文本框
[单位资金] – [总计划]
但是，我的代码只是向我显示了这个错误（下图）。
我哪里做错了？在此处输入图像描述
代码如下
Private Sub programInput()
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT SUM([Unit Program]) AS Limit FROM [Unit Funding] WHERE [Parent Organization] = """ & Me.ParentOrg & _
""" And [Functional Area] = """ & Me.FunctionalArea & _
""" And [Fund] = """ & Me.Fund & _
""" And [Funding Category] = """ & Me.FundingCategory """
Set rst = CurrentDb.OpenRecordset(strSQL)
If IsNull(rst!Limit) Then
programSQL = 0
Me.txtPgmTotal.Value = 0
Else
programSQL = rst!Limit
Me.txtPgmTotal.Value = programSQL
End If
totalAdj = 0
'PgmOnly + From
'strSQL = "SELECT SUM(TotalPgm) AS Adjusted FROM tbl_ProgramMonthly_Input WHERE ParentOrg = """ & Me.ParentOrg & """ And FunctionalArea = """ & Me.FunctionalArea & """" _
'& " And RID <> """ & Me.Text348.Value & """ And Fund = """ & Me.Fund & """ And [FundingCategory] = """ & Me.FundingCategory & """"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not IsNull(rst!Adjusted) Then totalAdj = totalAdj + rst!Adjusted
'PgmReq + From
strSQL = "SELECT SUM(TotalPgm) AS Adjusted FROM tbl_ProgramMonthly_Input WHERE ParentOrg = """ & Me.ParentOrg & """ And FunctionalArea = """ & Me.FunctionalArea & """" _
& " And RID <> """ & Me.Text348.Value & """ And Fund = """ & Me.Fund & """ And [FundingCategory] = """ & Me.FundingCategory & """"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not IsNull(rst!Adjusted) Then totalAdj = totalAdj + rst!Adjusted
Me.txtPgmUsed.Value = totalAdj
Me.txtPgmRemaining.Value = programSQL - totalAdj
End Sub
Function programCheck() As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
If Me.TotalPgm.Value <= 0 Then
programCheck = 1
Exit Function
End If
strSQL = "SELECT SUM([Unit Program]) AS Limit FROM [Unit Funding] WHERE [Parent Organization] = """ & Me.ParentOrg & _
""" And [Functional Area] = """ & Me.FunctionalArea & _
""" And [Fund] = """ & Me.Fund & _
""" And [Funding Category] = """ & Me.FundingCategory & """
Set rst = CurrentDb.OpenRecordset(strSQL)
If IsNull(rst!Limit) Then
programCheck = -1
Else
programSQL = rst!Limit
totalAdj = 0
'PgmReq + From
strSQL = "SELECT SUM(TotalPgm) AS Adjusted FROM tbl_ProgramMonthly_Input WHERE ParentOrg = """ & Me.ParentOrg & """ And FunctionalArea = """ & Me.FunctionalArea & """" _
& " And RID <> """ & Me.Text348.Value & """ And Fund = """ & Me.Fund & """ And [FundingCategory] = """ & Me.FundingCategory & """"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not IsNull(rst!Adjusted) Then totalAdj = totalAdj + rst!Adjusted
'PgmandReq + From
strSQL = "SELECT SUM(TotalPgm) AS Adjusted FROM tbl_PgmAndReqMonthly_Input WHERE ParentOrg = """ & Me.ParentOrg & """ And FunctionalArea = """ & Me.FunctionalArea & """" _
& " And RID <> """ & Me.Text348.Value & """ And Fund = """ & Me.Fund & """ And [FundingCategory] = """ & Me.FundingCategory & """"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not IsNull(rst!Adjusted) Then totalAdj = totalAdj + rst!Adjusted
totalAdj = totalAdj + Me.TotalPgm.Value
If programSQL >= totalAdj Then
programCheck = 1
Else
programCheck = 0
End If
End If
rst.Close
Set rst = Nothing
End Function
答：
0赞
CHill60
10/19/2023
#1
只需浏览并删除对 ContractName 的所有引用，确保您不会删除不相关的文本，例如，第一部分可能成为
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT SUM([Unit Program]) AS Limit FROM [Unit Funding] WHERE [Parent Organization] = """ & Me.ParentOrg & _
""" And [Functional Area] = """ & Me.FunctionalArea & _
""" And [Fund] = """ & Me.Fund & _
""" And [Funding Category] = """ & Me.FundingCategory "";"
Set rst = CurrentDb.OpenRecordset(strSQL)
请注意，在该示例中，ContractName 前面的 is 仍然是必需的。
尽管如果您使用单引号，您的代码会更清晰 - 或者更好的是参数化查询"""
由于字符串连接，它变得更混乱，但仍然只是删除对该列的引用的情况，例如
strSQL = "SELECT SUM(TotalPgm) AS Adjusted FROM tbl_ProgramMonthly_Input WHERE ParentOrg = """ & Me.ParentOrg & """ And FunctionalArea = """ & Me.FunctionalArea & """" _
& " And RID <> """ & Me.Text348.Value & """ And Fund = """ & Me.Fund & """ And [FundingCategory] = """ & Me.FundingCategory & """
评论
0赞
CaptScott
10/20/2023
不幸的是，这只会给我一个没有记录可以导航。
评论