Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Understanding OpenRecordset (Acc 97 sr2 on 95b)

    I have a procedure where I currently use a macro to create a 1 row table that I then use as the source for a recordset to make docproperty values to use in a word document

    The sql for the macro query is as follows:

    SELECT Customer.Customer, Customer.IAddress1, Customer.IAddress2, Customer.ILocality, [Pc-book]![Locality] & " " & [State] & " " & [Pcode] AS AddCode, DelDockets.Date, [MonthAcc] & "-" & [Job#] AS OurRef, Original.OrderNo, DelDockets.Detail INTO tblCertDet
    FROM ((DelDockets INNER JOIN Original ON DelDockets.[Job#] = Original.[Job #]) INNER JOIN Customer ON Original.AccNo = Customer.AccNo) INNER JOIN [Pc-book] ON Customer.ILocality = [Pc-book].ID
    WHERE (((DelDockets.DelDocket)=[Forms]![Delivery Docket]![DelDocket]));

    How do I make the outcome of this a temp recordset that I can use in the following?

    Set rst = dbs.OpenRecordset("tblCertDet", dbOpenDynaset) ' tblCertDet is result of maketable macro

    With rst
    'The Nz function is used to convert any Nulls to zeros or
    'zero-length strings, to prevent problems with exporting
    'to Word
    strCustomer = Nz(![Customer])
    strInvAdd1 = Nz(![IAddress1])
    strInvAdd2 = Nz(![IAddress2])
    strInvAdd3 = Nz(![AddCode])
    dteDate = Nz(![Date])
    strOurRef = Nz(![OurRef])
    strordernum = Nz(![OrderNo])
    strDescription = Nz(![Detail])
    End With
    rst.close

    The existing code works, but I want to understand temp recordsets and how you create and reference them.
    "Heading for the deep end"

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Understanding OpenRecordset (Acc 97 sr2 on 95b)

    Hi Allan,
    The question you pose is probably beyond the scope of the Lounge, as it is a pretty complex subject when you begin to consider all of the ramifications. In a nutshell, a recordset is method of looking at a table or the results of a query on a record by record basis, and being able to move through it in various ways. The 3 steps to create a recordset are 1-define your objects, 2 - create a reference to the database you want to work in, and 3 - use the set statement to create the recordset. Then you can work with it doing Edits, Inserts and Deletes. Things can get more complicated with queries, especially if you want to use Parameter queries, as that requires using QueryDefs, etc. The book I would recommend you look at is "Beginning Access 97 VBA programming" by Smith and Sussman - published by WROX. Many of the other books cover some of the basics, and a few that focus on VBA will have more details, but I found this one to be pretty complete and logical. Hope this helps.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Understanding OpenRecordset (Acc 97 sr2 on 95b)

    What does the SQL have to do with tblCertDet ? You say it's the "sql for the macro query" (by which I assume you mean the SQL that the macro runs), but it is a select query, not a make-table, so something is missing in your explanation. That SQL will not create a table unless you are using it as the basis for a make-table query. In that case, you need to post the other query as well.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Understanding OpenRecordset (Acc 97 sr2 on 95b)

    Hans,
    Thanks for your explanation code. This is the sort of thing I was after. I can get the concept of using and creating "on the fly" recordsets from this.

    I followed the top half of your instructions (minus the INTO keyword in the middle of the SQL) and get the following error.

    Error # 3061 : Too few parameters : Expected 1

    As Wendell rightly pointed out, the SQL is a parameter query and this seems to complicate thingsand may not be easy to get around.

    Help has a method of filtering the recordset as follows

    ' Use the Filter property to retrieve only certain
    ' records with the next OpenRecordset call.
    Debug.Print "Opening recordset from existing " & _
    "Recordset object to filter records..."
    rstTemp.Filter = "LastName >= 'M'"
    Set rstTemp2 = rstTemp.OpenRecordset()

    I will attempt to use this method and take the parameter out of the SQL and see what happens.

    Thanks all for the input.
    "Heading for the deep end"

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Understanding OpenRecordset (Acc 97 sr2 on 95b)

    Don't give up on the querydefs approach completely. It is a bit more complex than a filter, but not horribly so. Basically you define a query def as follows:
    <font color=blue> dim myQdef as QueryDef, myDB as Database, myRset as Recordset
    set myDB=CurrentDB()
    set myQdef = myDB.QueryDefs("myQuery")
    myQdef.Parameters("myParam") = myParameter
    set myRset = myQdef.OpenRecordset(dbDynaset)</font color=blue>
    In this case, myQuery is the name of your parameter query, myParam is the exact string in your parameter, myParameter is a value you want to assign to myParam, and myRset is the good old recordset you want to manipulate. Hope this helps (and makes sense - it's aircode so you may have to experiment a bit).
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Understanding OpenRecordset (Acc 97 sr2 on 95b)

    <P ID="edit" class=small>(Edited by HansV on 28-Nov-02 07:42. Removed incorrect INTO from SQL string.)</P>You don't need to create a table to do this. The Source argument of the OpenRecordset method can be a SQL string instead of the name of an existing table or query. The SQL string should be that for a select query, not a create table query. For example:

    Dim strSQL As String
    strSQL = "SELECT Customer.Customer, Customer.IAddress1, Customer.IAddress2, Customer.ILocality, [Pc-book]![Locality] & ' ' & [State] & ' ' & [Pcode] AS AddCode, DelDockets.Date, [MonthAcc] & '-' & [Job#] AS OurRef, Original.OrderNo, DelDockets.Detail FROM ((DelDockets INNER JOIN Original ON DelDockets.[Job#] = Original.[Job #]) INNER JOIN Customer ON Original.AccNo = Customer.AccNo) INNER JOIN [Pc-book] ON Customer.ILocality = [Pc-book].ID WHERE (((DelDockets.DelDocket)=[Forms]![Delivery Docket]![DelDocket]))"
    Set rst = dbs.OpenRecordset(strSQL)

    Note that the "Into tblCertDet" part of the SQL statement has been removed, and that the double quotes within the statement have been replaced by single quotes.

    It is also possible to create a temporary query, by setting its name to a zero-length string "":

    Dim qdf As DAO.QueryDef
    Set qdf = dbs.CreateQueryDef("", strSQL)
    Set rst = qdf.OpenRecordset

Posting Permissions

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