Move results of a vba called sql query into a variable 1

[ordendelfai]

Hello,

I am very new to VBA, and am having trouble getting this to work.

I want to take the returned value (1 result) of the below SQL statement, and assign it to a variable in VBA.

I have tried:
Private Sub Test()
Dim vProd1
vProd1 = "SELECT TOP 1 [Prod1] FROM
tbl_Client_Employee_Contrib ORDER BY [Prod1];"
End Sub


But that doesn't work at all, vProd1 just returns the sql string itself.

If you can help, would really appreciate! ;0)

~Orden

 

[jcarneir]

hi,

try this

Private Sub Test()
Dim vProd1
Dim rst as Recordset

set rst=CurrentDb.openRecordset("SELECT TOP 1 [Prod1] " _
& "FROM tbl_Client_Employee_Contrib ORDER BY [Prod1];&quot
if not rst.EOF then
vProd1 = rst!Prod1
else
'put here your error Code because the table is empty
end if
rst.Close
set rst=Nothing
End Sub

 

[TonyJollans]

Hi ordendelfai,

All you are doing is assigning a string to a variable in VBA code (your instructions on what data to get); you have to pass it to Access so it can follow those instructions. That is what jcarneir's code does by using the recordset.

The Recordset approach is the more general one and one you must understand it but in your particular example you can do ..

VProd1 = DMax("Prod1","tbl_Client_Employee_Contrib")

Enjoy,
Tony

 

[ordendelfai]

Tony, awesome, thank you, heres a star. Appreciate you trying to help jcarneir. As tony mentioned, your code did not produce the results I was looking for.

Thanks for helping =)

~Orden