Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Advanced use of RecordSet

    I am tring to use recordsets as a way to supply info to a graph.
    this is my code:
    Private Sub Form_Load()
    Dim Cur_DB As DAO.Database
    Dim Record_Set As DAO.Recordset

    Country_To_Country.RowSourceType = "Table/Query"
    Set Cur_DB = CurrentDb()
    Set Record_Set = Cur_DB.OpenRecordset("SELECT [R_Country] & ' - ' & [D_Country] AS Country_Pairs" & _
    ", Sum(Table_PCRKMS_Local_Data.FFE) AS FFE ," & _
    " (Sum(Table_PCRKMS_Local_Data.FFE)/DLookup('FFE','Table:_PCRKMS_METRICS_SUM'))*100 as [Percent]" & _
    " FROM Table_PCRKMS_Local_Data" & _
    " GROUP BY [R_Country] & ' - ' & [D_Country]" & _
    " ORDER BY Sum(Table_PCRKMS_Local_Data.FFE) DESC;", dbOpenDynaset)
    Country_To_Country.RowSource = "SELECT IIf([Percent] > 2.5 ,[Country_pairs],'Other'),FFE from Record_set;"

    End Sub

    The question that I have is at the section that I set the rowSource. Can I use the recordset like that or is this incorrect. Im getting the error that that statment doesnt exhist.
    Any Sugestions?
    Jim

  2. #2
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    Wouldn't it make more sense to use a query rather than a recordset for what you are trying to do?

    Maybe set a querydef to your "SELECT..." statement.
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by punkerboy
    Wouldn't it make more sense to use a query rather than a recordset for what you are trying to do?

    Maybe set a querydef to your "SELECT..." statement.

    how do you mean? can you give me an example?
    Jim

  4. #4
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: Advanced use of RecordSet

    First, the 'Record_set' variable is inside the RowSource SQL string so the query is looking for a table called 'Record_set' rather than the value of the Record_set variable.

    Try something like this:
    Country_To_Country.RowSource = "SELECT IIf([Percent] > 2.5 ,[Country_pairs],'Other'),FFE from " & Record_set & ";"

    Second, I will be suprised if you can refer to a recordset object that way. What will be the value of 'Record_set' at run time? What will be the value of 'Record_set' when the control loads?

    Something like this may work:
    Country_To_Country.RowSource = "SELECT IIf([Percent] > 2.5 ,[Country_pairs],'Other'),FFE from (" & Record_set.SQL & ");"

    Or you could try creating a named querydef object, refer to it by name and then delete it.

    Brian

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Advanced use of RecordSet

    Originally posted by bri
    First, the 'Record_set' variable is inside the RowSource SQL string so the query is looking for a table called 'Record_set' rather than the value of the Record_set variable.

    Try something like this:
    Country_To_Country.RowSource = "SELECT IIf([Percent] > 2.5 ,[Country_pairs],'Other'),FFE from " & Record_set & ";"

    Second, I will be suprised if you can refer to a recordset object that way. What will be the value of 'Record_set' at run time? What will be the value of 'Record_set' when the control loads?

    Something like this may work:
    Country_To_Country.RowSource = "SELECT IIf([Percent] > 2.5 ,[Country_pairs],'Other'),FFE from (" & Record_set.SQL & ");"

    Or you could try creating a named querydef object, refer to it by name and then delete it.

    Brian
    Brian the querydef seems like the right thing to do, but the help is lacking, as it always is. Can you give me a simple example of how to create a querydef?
    Jim

  6. #6
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    You could do something like:

    'aircode

    Dim qry as QueryDef
    Dim strSQL as String

    strSQL = "SELECT..."

    set qry = Currentdb.CreateQueryDef("qryTemp", strSQL)

    Country_To_Country.RowSource = "qryTemp"

    CurrentDb.QueryDefs.Delete "qryTemp"
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by punkerboy
    You could do something like:

    'aircode

    Dim qry as QueryDef
    Dim strSQL as String

    strSQL = "SELECT..."

    set qry = Currentdb.CreateQueryDef("qryTemp", strSQL)

    Country_To_Country.RowSource = "qryTemp"

    CurrentDb.QueryDefs.Delete "qryTemp"
    That worked perfect!!!
    Now for the hard part
    I need to use the first querydef in a second querydef, I need to further filter the first subset of data. How would that work?
    Jim

  8. #8
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    You should be able to refer to the first query created (qryTemp) as you would a normal query.

    "SELECT qryTemp.* FROM qryTemp WHERE (((qryTemp.field1)="something"));"
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  9. #9
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59
    I just used this last week:
    Code:
    Dim curDB as Database
    Dim qdf,  qdfDataSet As QueryDef
    
    Set curDB = CurrentDB
    
    ' create dataset for template 
        strDataSetSQL = "SELECT * FROM Namelist " & _
                            "WHERE (((Namelist.Template)='" & Me.Template & "'));"
    
    
    ' delete qrydef if it exists
        For Each qdf In curDB.QueryDefs
             If qdf.Name = "TempRaw" Then
                  curDB.QueryDefs.Delete "TempRaw"
                  Exit For
             End If
        Next qdf
    
    
    ' create qrydef
        Set qdfDataSet = curDB.CreateQueryDef("TempRaw", strDataSetSQL)

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ok here we go:

    Dim Cur_DB As DAO.Database
    Dim Record_Set As DAO.QueryDef

    Country_To_Country.RowSourceType = "Table/Query"
    Set Cur_DB = CurrentDb()
    Set Record_Set = Cur_DB.CreateQueryDef("graph_temp", "SELECT [R_Country] & ' - ' & [D_Country] AS Country_Pairs" & _
    ", Sum(Table_PCRKMS_Local_Data.FFE) AS FFE ," & _
    " (Sum(Table_PCRKMS_Local_Data.FFE)/DLookup('FFE','Table:_PCRKMS_METRICS_SUM'))*100 as [Percent]" & _
    " FROM Table_PCRKMS_Local_Data" & _
    " GROUP BY [R_Country] & ' - ' & [D_Country]" & _
    " ORDER BY Sum(Table_PCRKMS_Local_Data.FFE) DESC;")

    Country_To_Country.RowSource = "Select iif([Percent] > 2.5, [Country_Pairs],'Other'),[FFE] from graph_temp "

    Cur_DB.QueryDefs.Delete "Graph_Temp"

    I used the queryset as if it was a normal table/query and I get the error:
    the record source "Select iif([Percent] > 2.5, [Country_Pairs],'Other'),[FFE] from graph_temp " does not exist
    what am I doing wrong?
    Jim

  11. #11
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ok I got it to work by puting the delete querydeff step on the form close, but now it seems to have a lock on the base table the the query deff is dirived from. I have tried closing the querydef and the variable that i have set up for the db but that dosent work.
    Any other suggestions?
    Jim

  12. #12
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59
    Don't delete Graph_temp.

    The control is loading the RowSource after the sub has run and when it looks for the query object it has aready been deleted.

    In the code sample I posted:
    Code:
    ' delete qrydef if it exists
        For Each qdf In curDB.QueryDefs
             If qdf.Name = "TempRaw" Then
                  curDB.QueryDefs.Delete "TempRaw"
                  Exit For
             End If
        Next qdf
    I check for the existance of the query and delete it before recreating it.

    Brian

  13. #13
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Cool that works, but how do i get it to free up my base table so that I can do other things to it?
    Jim

  14. #14
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59
    What kind of error message or behavior are you getting?

    Brian

  15. #15
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    The nature of my app is a query tool baisicaly. the form that i was just working on was actualy a graph that is based on a table that is populated everytime a query is run. after I did this graph and then I go to run a diff query i get the error Cannot perform this function at this time and it debugs to a delete step of the table. The table is locked from the qurydeff, how do I release it?
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •