CODEWITHSUNDEEP
formssql-server-2005ms-accessvbams-access-2007
user1175126
user1175126

Reputation: 179

running select query in ms access 2007 vba and assign the resulting value to a textbox

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.

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.

Upvotes: 1

Views: 19408

Answers (2)

Fionnuala
Fionnuala

Reputation: 91306

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

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

Upvotes: 0

markblandford
markblandford

Reputation: 3193

Try this:

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
Me.txtInstalledQuantity = rs.Fields("CountInstalledQuantity").Value

Upvotes: 1

Related Questions