Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: Send List of data from table

    Hi Can anyone help?

    I have a table as follows:
    | Date | Postcode |

    and a query that extracts all of the postcodes for the current day

    A module runs this query and then sends an email with no attachment. I would like to include a list of the postcodes that have been extracted.
    eg.
    MessageText:="This email has been created automatically. New postcodes have been modified. Postcodes are:

    "
    Then i would like to list the postcodes.

    Any ideas?

    Thanks,
    Dan
    Access 97

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You'll need a recordset based on either your query or the base table.

    Declare a string variable, for ex. strPC
    Then iterate through the recordset (I think setting this up is something like set rst = currentdb.OpenRecordset(strsql) or something)

    and keep adding to strPC, for ex
    Do while not rst.eof
    strpc = strpc & rst("postcode") & " "
    loop

    then append strPC to your message text.

    HTH.

    Mike.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Aug 2003
    Posts
    106

    Send List of data from table

    Hi Mike

    Thanks for this. I have tried it and it's almost there!
    It brings back a list of the first postcode over and over again..

    Any ideas?

    Thanks,
    Dan

  4. #4
    Join Date
    Aug 2003
    Posts
    106

    Send List of data from table

    Sorry - should have included code:

    'Count the number of records
    Set dbs = CurrentDb
    strsql = "SELECT Count(WalkpackLogTbl.District) as CountofDistrict FROM WalkpackLogTbl"
    Set rst = dbs.OpenRecordset(strsql)
    rcdcount = rst!countofDistrict

    'Create string of postcodes
    Set dbs = CurrentDb
    strsql = "SELECT WalkpackLogTbl.Date, WalkpackLogTbl.User, WalkpackLogTbl.District, WalkpackLogTbl.Brand, WalkpackLogTbl.[Number of Prospects] FROM WalkpackLogTbl WHERE (((WalkpackLogTbl.Date)=Date()) AND ((WalkpackLogTbl.User)=fOSUserName()))"
    Set rst = dbs.OpenRecordset(strsql)
    myCount = 0
    Do
    myCount = myCount + 1
    strPC = strPC & rst("District") & " "
    Loop Until myCount = rcdcount

    Dan

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    rst.movenext
    ...just before the Loop

    izy

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and HomerBoo's original with .eof was more elegant, safer, faster, and uses a few bytes less memory

    do while not rst.eof
    'something
    'something
    rst.movenext
    loop

    izy

  7. #7
    Join Date
    Aug 2003
    Posts
    106

    Send List of data from table

    Now i get the error message :

    No Current Record

    Sorry for being stupid! What am i doing wrong?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thats why .eof is safer

    izy

  9. #9
    Join Date
    Aug 2003
    Posts
    106

    Send List of data from table

    Thanks Izy! (and Mike)

    I have it now! I changed the .eof originally because i was getting an error.

    It's working now i have the eof and movenext.

    Thanks again,

    Dan

  10. #10
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Unhappy

    Sorry about not telling you about the MoveNext method of the recordset object. I sometimes forget to include that myself!
    All code ADO/ADOX unless otherwise specified.
    Mike.

  11. #11
    Join Date
    Aug 2003
    Posts
    106

    Send List of data from table

    No problem!

    Thanks for helping me out,

    Dan

Posting Permissions

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