set variable equal to sql query result in VB

[Guest]

I am trying to set a variable equal to the result of a sql query all within
VB (Access Form Code). Here is an excerpt from my code:

Dim lsSQL As String
lsSQL = "SELECT [5_Deficiency_Item].[Magnitude] from [5_Deficiency_Item] " & _
"WHERE [5_Deficiency_Item].[Item_ID] = " & Me![Item_ID] & ""

Dim loQD As DAO.QueryDef
Set loQD = loDB.CreateQueryDef("", lsSQL)

Dim msMagnitude As Long
msMagnitude = "Call loQD.Execute"

It doesn't like the line msMagnitude = "Call loQD.Execute"

What can I do?

Thanks!

 

[Guest]

Try:
dim isSQL as String
dim loQD as DAO.Recordset
Dim msMagnitude As Long
lsSQL = "SELECT [5_Deficiency_Item].[Magnitude] from [5_Deficiency_Item] " & _
"WHERE [5_Deficiency_Item].[Item_ID] = " & Me![Item_ID] & ""
Set loQD = Currentdb.OpenRecordset(IsSQL)
msMagnitude = loQD.Fields("Magnitude").value
loQD.close
set loQD = nothing

That should set the variabel "msMagnitude" to the value of the field
"Magnitude" in the table "5_Deficiency_Item"

HTH

Mr. B

 

[Guest]

First, put all your Dims at the beginning of your procedures. It makes them
much easier to read. Then, Here is what you want:

Dim lsSQL As String
Dim loQD As DAO.Recordset
Dim loDB as Database
Dim msMagnitude As Long

lsSQL = "SELECT [5_Deficiency_Item].[Magnitude] from [5_Deficiency_Item] " & _
"WHERE [5_Deficiency_Item].[Item_ID] = " & Me![Item_ID] & ""

Set loDB = CurrentDB
Set loQD = loDB.OpenRecordset(lsSQL)
If loQD.RecordCount Then
'Do whatever happens if there is not a match
Else
msMagnitude = loQD![Magnitude]
End If

Now, if there is going to be only one match and all you want is one match,
this is a better way:
Dim msMagnitude As Long

msMagnitude = DLookUp("[Magnitude]", "5_Deficiency_Item", "[Item_ID] = " _
& Me.Item_ID)
If IsNull(msMagnitude) Then
'Do your no match stuff here
End If

 

[Marshall Barton]

I am trying to set a variable equal to the result of a sql query all within
VB (Access Form Code). Here is an excerpt from my code:

Dim lsSQL As String
lsSQL = "SELECT [5_Deficiency_Item].[Magnitude] from [5_Deficiency_Item] " & _
"WHERE [5_Deficiency_Item].[Item_ID] = " & Me![Item_ID] & ""

[snip ill fated attempt]


You need to open a recordset to retrieve the value:

Set rs = CurrentDb.OpenRecordset(IsSQL)
msMagnitude = rs!Magnitude
rs.Close : Set rs = Nothing

 

[Ron Weiner]

I am trying to set a variable equal to the result of a sql query all within
VB (Access Form Code). Here is an excerpt from my code:

Dim lsSQL As String
lsSQL = "SELECT [5_Deficiency_Item].[Magnitude] from [5_Deficiency_Item] " & _
"WHERE [5_Deficiency_Item].[Item_ID] = " & Me![Item_ID] & ""

[snip ill fated attempt]


You need to open a recordset to retrieve the value:

Set rs = CurrentDb.OpenRecordset(IsSQL)
msMagnitude = rs!Magnitude
rs.Close : Set rs = Nothing

OR just use dlookup to get one value from the table.

msMagnitude = dlookup("Magnitude","5_Deficiency_Item","Item_ID=" &
Me![Item_ID] )

Ron W

 

[Guest]

..How would you do this using an adp from Acess 2003? I am trying to do the
same thing but the commands are not quite the same. thanks.

I am trying to set a variable equal to the result of a sql query all within
VB (Access Form Code). Here is an excerpt from my code:

Dim lsSQL As String
lsSQL = "SELECT [5_Deficiency_Item].[Magnitude] from [5_Deficiency_Item] " & _
"WHERE [5_Deficiency_Item].[Item_ID] = " & Me![Item_ID] & ""

[snip ill fated attempt]


You need to open a recordset to retrieve the value:

Set rs = CurrentDb.OpenRecordset(IsSQL)
msMagnitude = rs!Magnitude
rs.Close : Set rs = Nothing