VBA and SQL

[Junior]

Setting the value of a variable in a sub by referencing a query [QLoc]as
below:
how could i eliminate [Qloc] and set the value using VBA in the sub?

strLoc = Nz(DMax("[LocID]", "[QLoc]"), "ZZ")


Qloc SQL:
SELECT [TblLocationDetail].[AppID], [TblLocationDetail].[LocID]
FROM TblLocationDetail
WHERE ((([TblLocationDetail].[AppID])=[Forms]![frmAppenter]![txtAppId]));

 

[Wayne Gillespie]

Setting the value of a variable in a sub by referencing a query [QLoc]as
below:
how could i eliminate [Qloc] and set the value using VBA in the sub?

strLoc = Nz(DMax("[LocID]", "[QLoc]"), "ZZ")


Qloc SQL:
SELECT [TblLocationDetail].[AppID], [TblLocationDetail].[LocID]
FROM TblLocationDetail
WHERE ((([TblLocationDetail].[AppID])=[Forms]![frmAppenter]![txtAppId]));

Create a function in your form module to return the result -

Function fGetLocation(lngAppID As Long) As Variant
Dim strSQL as String
Dim db as DAO.Database
Dim rst as DAO.Recordset

strSQL="SELECT Top 1 LocID FROM TblLocationDetail " _
& "WHERE ((AppID)=" & lngAppID & ") " _
& "ORDER BY AppID Desc;

Set db = CurrentDB()
Set rst = db.OpenRecordset(strSQL,dbOpenSnapshot)
With rst
If .RecordCount <>0 then
fGetLocation = !LocID
Else
fGetLocation = Null 'or whatever
End If
.Close
End With

If Not (rst Is Nothing) Then Set rst = Nothing
If Not (db Is Nothing) Then Set db = Nothing

End Function


Wayne Gillespie
Gosford NSW Australia