storing a qdf as a variable??

[helloworld]

How would you store the results of a qdf as a variable. I need it for a where condition when I am trying to open my report.

this is my code for the qdf (I am not sure what the item in quotations on the last line does)

    Dim dbs As Database, qdf As QueryDef, strSQL As String
    Set dbs = CurrentDb
    strSQL = "SELECT [Data Element].[Standard_Data Element Name], Compound.[Standard_Cluster Compound Name] FROM [Data Element] INNER JOIN (Compound INNER JOIN Component ON Compound.[Standard_Cluster Compound Identifier] = Component.[Standard_Component Compound Identifier]) ON [Data Element].[Standard_Data Element Identifier] = Component.[Standard_Component Data Element Identifier] WHERE (((Compound.[Standard_Cluster Compound Name]) Like [Forms]![FullCompoundDataElementsReport]![Combo69]));"
    Set qdf = dbs.CreateQueryDef("whyme", strSQL)

and this is where I need to put in the results (the question marks)

 If (IsNull(Forms!FullCompoundDataElementsReport!Combo69) = False) And (IsNull(Forms!FullCompoundDataElementsReport!Combo71) = True) Then
        If Forms!FullCompoundDataElementsReport![Check84] = True Then
         stDocName = "Data Element"
         data_criteria = "[Standard_Data Element Name] like '" & ???????? & "' and "
        Else
         data_criteria = "[Standard_Cluster Compound Name] like '" & Forms!FullCompoundDataElementsReport!Combo69 & "' and "
         stDocName = "Compound Query"
        End If
    Else
    data_criteria = "[Standard_Data Element Name] like '" & Forms!FullCompoundDataElementsReport!Combo71 & "' and "
    stDocName = "Data Element"
    End If

Any help would be greatly appreciated!

 

[namliam]

You can store it in a recordset

Dim rst as dao.recordset
set rst = qdf.openrecordset

then use it

rst!Fieldname

Regards

 

[helloworld]

thanks a lot man...but this way only selects ONE record in the column...I would like it to select all the records. This is the code I have right now.

Dim ElementName As String
    Dim rst As DAO.Recordset
    
 
   ' Dim dbsCurrent As Database
   ' Set dbsCurrent = CurrentDb()
    
    Dim qrySrc As QueryDef
    Dim dbs As Database
    Dim strSQL As String
    Set dbs = CurrentDb()
    'MsgBox "not here"
    strSQL = "SELECT [Data Element].[Standard_Data Element Name] FROM [Data Element] INNER JOIN (Compound INNER JOIN Component ON Compound.[Standard_Cluster Compound Identifier] = Component.[Standard_Component Compound Identifier]) ON [Data Element].[Standard_Data Element Identifier] = Component.[Standard_Component Data Element Identifier]" & "WHERE (Compound.[Standard_Cluster Compound Name]) Like '" & [Forms]![fullcompounddataelementsreport]![Combo69] & "'"
    'MsgBox "not here"
    Set qryScr = dbs.CreateQueryDef("Temp", strSQL)
    'MsgBox "not here"
    Set rst = qryScr.OpenRecordset
    'ElementName =
    For Each qrySrc In dbs.QueryDefs
        ElementName = rst![Standard_Data Element Name]
      '  MsgBox "not here"
    Next

thanks for the reply

 

[Pat Hartman]

What was wrong with this thread?
http://www.access-programmers.co.uk/forums/showthread.php?t=67476

Please do yourself and us a favor and stick to a single thread for each problem.

 

[helloworld]

sorry...but after I posted this, I thought it would be better if I posted it in the vba and modules forum...I am new here Besides...there is still no answer to how i can retrieve multiple records using the recordset...I am only getting one record back!

 

[Pat Hartman]

Your code is only reading the first record of the recordset. The code you have is looping through the querydef collection and overlaying a single variable each time the loop iterates so you will NEVER end up with multiple values.

 

[helloworld]

hmmmm...how would I fix that? Please keep in mind that I need the variable as it is part of a string.

 

[namliam]

using something like:
rst.movefirst
elementname= ""
do while not rst.eof
ElementName = Elementname & rst![Standard_Data Element Name]
rst.movenext
loop

will run thru the records in the recordsets...

regards

 

[helloworld]

Thanks a lot namliam. I finally got it to work. Appreciate the help buddy!