Creating SQL statement in code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have set up an Access query (which works) which I want to include in my
form code.
The query SQl is
SELECT Sum(EquipmentDetail.UnitPrice) AS PackPrice
FROM Packages INNER JOIN EquipmentDetail ON Packages.ItemIdRef =
EquipmentDetail.ItemID
WHERE (((Packages.PackageId)="pack02"));

On my form, I want to select the packageId from a combo box then run this
sql from the AfterUpdate property of the combo to give me the PackPrice to
display on the form with the Package Id.
Anyone help on how to code this?
 
Hi,
There are a couple of options available to you. You can save the the query (without the WHERE clause)
and use it in a DLookup statement, or use the statement itself and open a recordset to get the value.

I'll show you the recodset method because it's a good thing to know.

Dim strSql As String
Dim rs As DAO.Recordset

strSql = "SELECT Sum(EquipmentDetail.UnitPrice) AS PackPrice " & _
"FROM Packages INNER JOIN EquipmentDetail ON Packages.ItemIdRef = " & _
"EquipmentDetail.ItemID " & _
"WHERE (((Packages.PackageId)='" & yourCombo & "'))"

'make sure your sql makes sense
Debug.Print strSql

'open the recordset
Set rs = CurrentDb.OpenRecordset strSql
yourTextBox = rs!PackPrice

'get rid of recordset
Set rs = Nothing
 
Brilliant, used the recordset method - just one thing the line
Set rs = CurrentDb.OpenRecordset strSql needs ( ) round the strSql.
Many thanks
 
Back
Top