Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Reading values from table into VBA (2000)

    I have a table of Locations and Recipients that I am trying to incorporate into a looping SendObject command. Right now I have multiple reports set up, varying only by filter value, and separate functions written to send the reports, but to maintain the existing setup would require diving into the VBA and to add additional recipients would be even more effort. If I can get the macro to read the records and pass the field values on as variables, maintenance would be reduced to adding or changing a record.

    I tried adapting some sample code I found to loop through the table as a Recordset:<pre>Dim AllRecip as Recordset
    Set AllRecip = CurrentDb.OpenRecordset("tblRecipients")</pre>

    but the code would abort here with a 'Type Mismatch' error. The table only has three fields: an autonumber ID, a numeric LocNum, and a text Recipient. What's not right?

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

    Re: Reading values from table into VBA (2000)

    CurrentDb is a DAO object, while Recordset can be both an ADO and a DAO object. This confuses VBA.

    1. Select Tools | References... in the Visual Basic Editor.
    Make sure that Microsoft DAO 3.6 Object Library is ticked, then click OK.

    2. Change the declaration to remove the ambiguity:

    Dim AllRecip As DAO.Recordset

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reading values from table into VBA (2000)

    <P ID="edit" class=small>(Edited by d_rasley on 05-May-05 15:01. Attached final version of code.)</P>That did the trick, thanks! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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