在 ms access 2007 vba 中运行选择查询并将结果值分配给文本框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9394229/
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
running select query in ms access 2007 vba and assign the resulting value to a textbox
提问by user1175126
I need to run a sql query and the resulting value is assigned to a textbox in the form. I am using docmd.runsql sql to run this query.
我需要运行一个 sql 查询，并将结果值分配给表单中的一个文本框。我正在使用 docmd.runsql sql 来运行这个查询。
Private Sub Form_Current()
Dim SQL As String
Dim db As Database
Dim rs As DAO.Recordset
If IsNull(Me.txtInstalledQuantity) Then
SQL = "select count(tblequipmentbase.id)AS CountInstalledQuantity FROM (tblequipmentbase INNER JOIN tblequipmentparts ON tblequipmentbase.id=tblequipmentparts.idconnect) INNER JOIN tblparts ON tblequipmentparts.idpart=tblparts.id where tblparts.id= " & txtQueryID.Value & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
DoCmd.RunSQL SQL
Me.txtInstalledQuantity = rs!CountInstalledQuantity
Set rs = Nothing
Set db = Nothing
End If
End Sub
The error is in the runsql command.
错误在 runsql 命令中。
回答by markblandford
Try this:
尝试这个：
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
Me.txtInstalledQuantity = rs.Fields("CountInstalledQuantity").Value
回答by Fionnuala
I suggest you create a query, let us call it Equipment:
我建议你创建一个查询，我们称之为设备：
SELECT eb.id
FROM (tblequipmentbase eb
INNER JOIN tblequipmentparts ep
ON eb.id=ep.idconnect)
INNER JOIN tblparts p
ON ep.idpart=p.id
You can then use this query in several ways, for example, you can set a textbox control source to:
然后，您可以通过多种方式使用此查询，例如，您可以将文本框控件源设置为：
= DCount("eb.id","Equipment","p.ID=" & txtQueryID)
More info: http://msdn.microsoft.com/en-us/library/aa168244(v=office.10).aspx
更多信息：http: //msdn.microsoft.com/en-us/library/aa168244(v=office.10).aspx
Me.txtInstalledQuantity = DCount("eb.id","Equipment","p.ID=" & txtQueryID)
Or
或者
Dim rs As DAO.Recordset
s = "SELECT Count(eb.id) As EqC " _
& "FROM (tblequipmentbase eb " _
& "INNER JOIN tblequipmentparts ep " _
& "ON eb.id=ep.idconnect) " _
& "INNER JOIN tblparts p " _
& "ON ep.idpart=p.id " _
& "WHERE p.id = " & Me.txtQueryID
Set rs = CurrentDB.OpenRecordset(s)
Me.txtInstalledQuantity = rs!EqC
