Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DIM/SET statements (Access 2k)

    Code below causes "Object Required" message when from from form button. I suspect that the DIM / SET commands are incorrect.
    Object of code is to read a query and send an individual report based on each row of query. I want to loop until EOF is found in query. DO commands were pasted from converted macro to VB

    Private Sub Command367_Click()
    On Error GoTo Err_Command367_Click
    Dim email As Recordset
    Set email = db.openrecordset("select-for-email", dbopendynaset)
    DoCmd.OpenQuery "select-for-email", acNormal, acEdit
    DoCmd.GoToRecord acQuery, "select-for-email", acFirst
    Do While Not email.EOF
    DoCmd.GoToControl "lineid"
    DoCmd.RunCommand acCmdCopy
    SendKeys "^v", False
    SendKeys "{enter}", False
    DoCmd.SendObject acReport, "report-for-email", "RichTextFormat(*.rtf)", "chriss", "", "", "Traveler", "", False, ""
    DoCmd.GoToRecord acQuery, "select-for-email", acNext

    Loop
    email.Close
    Set email = Nothing

    Exit_Command367_Click:
    Exit Sub

    Err_Command367_Click:
    MsgBox Err.Description
    Resume Exit_Command367_Click


    End Sub

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DIM/SET statements (Access 2k)

    Having created a recordset from the query, you then need to deal just with recordset. You can forget about the query then.

    Do while not email.EOF
    looping code
    email.movenext
    loop
    I don't follow what you are trying to do in the loop. If you are trying to take a value from the recordset, and put it on the form, you could use something like :
    me!lineid = email!lineid
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    Thanks for the time,
    I am reading a field (lineid) from the query (select-for-email) and creating a one record report based on another query that the lineid is pasted into as a criteria.
    Read query record, past lineid into report query, generate and send report, read query next record, loop until EOF.

    I have a macro that reads the query and performs the action but does not handle the EOF. If you know how to loop a macro until EOF, I would plug that into the macro.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DIM/SET statements (Access 2k)

    If you want to read the info from the query, then you don't need the reocrdset.
    Alternatively, you create a recordset from the query, then pull info from the recordset. You need one or the other, not both.

    I have forgotten what you can do with macros.

    If you use code instead, you can create the recordset from query, then loop through the recordset using the loop I showed before.
    Regards
    John



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

    Re: DIM/SET statements (Access 2k)

    Don't try to get that fancy with a macro because they break too easily and you can't error trap them or handle anything but very simple conditions. You need code to do this properly.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    Thanks for the reply. I don't mind using code if I can get the statements right. I think the code is close per my original post to do the read and send object but the Dim/Set statements are causing the object failure. Any help there?

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

    Re: DIM/SET statements (Access 2k)

    Reread John's post <!post=here,162537>here<!/post>. You would need to add code to use the resulting keyvalue form the current field in the recordset to build a WhereCondition string and then you would send that to the report in the DoCmd.OpenReport statement.

    If you're getting an error message, tell us what the error is. There are hundreds of error messages in Access/Jet and Windows, so you'll have to be specific.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    Error with code above is: Runtime error 424, Object Required.
    Clicking "debug" yellow highlights SET statement line. I don't use VB very much so John's reply was not clear to me.
    I understood it to say that if I use the SET statement correctly, it will open the query and I will not have to use the next line, DoCmd.Open Query. With the debuger highlighting the SET, I must not be using it correctly.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DIM/SET statements (Access 2k)

    Have you initialised db with a SET command?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: DIM/SET statements (Access 2k)

    Well, unless you initialized the db object on a module level in some other routine, it's the object that is being objected to. You can only open a recordset based on certain objects, like a database object. So where is your "Dim db As DAO.Database" and "Set db = CurrentDb"?
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    Dim db as DAO.Database causes "COMPILE ERROR, User-defined type not defined" when run
    Before my first post, I tried this statement and had same compile error. Most recent code follows:

    Private Sub Command369_Click()
    Dim db As DAO.Database, rs As Recordset
    Set db = CurrentDb
    Set rs = CurrentDb.openrecordset("Select [lineid] FROM [select-for-email] GROUP BY lineid;", dbOpenSnapshot)
    Do While Not rs.EOF
    DoCmd.GoToControl "lineid"
    DoCmd.RunCommand acCmdCopy
    SendKeys "^v", False
    SendKeys "{enter}", False
    DoCmd.SendObject acSendReport, "report-for-email", acFormatRTF, "chriss", "", "", "traveler"
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    End Sub

  12. #12
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    You need to set a reference to the "Microsoft DAO 3.x Object Library"

    To do this open the VBE Select Too;s|References....

    Scroll down until you see it, and then check it.

    Have a look at the attached screenshot.
    Attached Images Attached Images
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  13. #13
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    Thanks for reply!!!
    Setting the DAO reference fixed the Dim statement. Compile Runtime error #13 now occurs, TYPE MISMATCH on the:
    Set rs=Currentdb......... statement line.
    I'm trying to open the query named "select-for-email", first record, read only. Not using it correctly, I suspect. Thoughts?

  14. #14
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    <hr>Set rs=Currentdb......... statement line.<hr>
    Change the dim statement to:

    dim rst as dao.recordset

    and the quoted line to

    Set rs=db.open........

    ADO and DAO both have Recordset datatypes and they aren't the same
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  15. #15
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIM/SET statements (Access 2k)

    Code now loops to each record of the "select-for-email query. However, it is not going to the 'lineid' field and copying content to use as criteria for the report. Comment inline

    Dim db As DAO.Database, rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select [lineid] FROM [select-for-email]", dbOpenSnapshot)
    Do While Not rs.EOF
    DoCmd.GoToControl "LineID" 'go to lineid control
    DoCmd.RunCommand acCmdCopy 'copy content of lineid control
    SendKeys "^v", False 'paste content of copy
    SendKeys "{enter}", False 'enter
    'report-for-email' is created with a query that uses the lineid as a criteria for just one lineid matched record
    DoCmd.SendObject acSendReport, "report-for-email", acFormatRTF, "chriss", "", "", "traveler ", "", False, ""
    rs.MoveNext
    Loop
    rs.Close

Page 1 of 2 12 LastLast

Posting Permissions

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