vba query results set into a variable

[SnakeEyes909]

hello again. i am having a issue getting the results of a vba query into a veriable or to be displayed.

i have a form, that i need to insert a item from a access db. i can get it to query, but how do i display the results.

 strSQL2 = "select SIG_IMAGE FROM tblPHYSICIAN WHERE PHYSICIAN_NAME = " & PHYSICIAN_NAME
  
 strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
     & "Data Source = " & strPath
    Debug.Print strConnection
    Set cnn = New ADODB.Connection
    cnn.Open strConnection
    cnn.Execute strSQL2, lngSuccess
    cnn.Close
    
'SigFile =
doc.InlineShapes.AddPicture FileName:=SigFile, LinkToFile:=False, SaveWithDocument:=True, Range:=doc.Bookmarks(bkmName).Range

  Set docs = Nothing
  Set cnn = Nothing

SigFile is the variable that i need to put the results of the query into. basicly SIG_IMAGE is a path saved in a db field. any answer and explination so i can learn from my mistake...

thanks

 

[randy700]

How about...

yourField = DLookup("SIG_IMAGE", "yourQuery")

Randy

 

[SnakeEyes909]

i'll try it, but my query is stated in the vb statement. i'm using word and trying to pull this item into word from access db.

 

[MajP]

open a recordset that returns 1 record and 1 field. Then return the value of the return field.

Public Sub getVariable()
    Dim strConnection As String
    Dim strPath As String
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim x As Variant
    Dim strSql2 As String
    
    strPath = "C:\Northwind.mdb"
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
     & "Data Source = " & strPath
    
    strSql2 = "SELECT TOP 1 Employees.EmployeeID FROM Employees ORDER BY Employees.EmployeeID DESC"
    Set cnn = New ADODB.Connection
    
    cnn.Open strConnection
    Set rs = cnn.Execute(strSql2)
    x = rs.Fields(0)
    Debug.Print x
    
    cnn.Close
End Sub

 

[SnakeEyes909]

Thank you all for your help.

MajP. i used a variation of your code, and it is working now. thank you again.