Query Results as Variable

[helloworld]

Urgent help needed!

Hi there,

I am new to VB and Access and I have the following problem.


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!

 

[SunWuKung]

You execute the QueryDef and put its result to a RecordSet. I have copied here the Sub from Access help. Once that is done you have to navigate to the record you need, but if you are certain that the query will only return one and only one record you can skip this.

hth
SWK

----------------------------------------------

Dim rst AS RecordSet

ExecuteQueryDef qdf, rst
Debug.Print rst![Standard_Data Element Name]


Sub ExecuteQueryDef(qdfTemp As QueryDef, _
rstTemp As Recordset)

Dim errLoop As Error

' Run the specified QueryDef object. Trap for errors,
' checking the Errors collection if necessary.
On Error GoTo Err_Execute
qdfTemp.Execute dbFailOnError
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTemp.Requery

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub

 

[helloworld]

hey there...thanks for the reply but I keep getting the following error:

Compile Error:

ByRef argument type MisMatch

Can you help me?

 

[Pat Hartman]

You have a fixed SQL string. It should be stored as a querydef. You should not be creating the string on the fly. This method causes severe database bloat and that forces frequent compact and repair operations.

Your column and table names contain embedded spaces. This is poor practice and can cause strange problems with VBA.

You are using Like when you should be using =. Like is ONLY used when you have a partial string value and is ALWAYS accompanied by wild cards before/after the partial string. Whenever you have the complete value, use = since it is far more efficient.

 

[helloworld]

Thanks for the feedback Pat. Right now I am just trying to get this thing to work. Then I will go back and edit it. These statements are still not working!

 

[helloworld]

hey there...

I have this code now and I am getting a type mismatch error. could someone please tell me what is wrong?

   Dim ElementName As String
    Dim rst As Recordset
 
    Dim dbsCurrent As Database
    Set dbsCurrent = CurrentDb()
    Set rst = dbsCurrent.OpenRecordset("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 & "' ));")
   
    If Not rst.EOF Then
    ElementName = rst("Standard_Data Element Name")
    End If
    rst.Close

Thanks a lot

 

[helloworld]

ok...after many hours of torture...I have managed to narrow it down to this...

I am getting a data type mismatch error at this statement

    Set rst = dbsCurrent.OpenRecordset("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 & "' ));")

Does anyone know why?

 

[Mile-O]

What data type is the first column of the combobox you are using as criteria? You have it in the query as text although, as I can't see, it may be a number.

 

[helloworld]

Hi Milo...I checked the first column and it is indeed a text data type... This is driving me crazy


I will paste my code again...

 Dim ElementName As String
    Dim rst As Recordset
 
    Dim dbsCurrent As Database
    Set dbsCurrent = CurrentDb()
    MsgBox "Not here" '<---just testing to see where the error is
    Set rst = dbsCurrent.OpenRecordset("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" '<---just testing to see where the error is
    If Not rst.EOF Then
    ElementName = rst("Standard_Data Element Name")
    End If
    rst.Close

the code runs to the first msgBox but not the second. So I guess that is where the error is.

PLEASE HELP!