Results 1 to 7 of 7
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Change record source with VBA


    I'm trying to open a report and change the record source with VBA putting in SQL as it's source. It keeps sending me to the debugger. This is what I put in it:
    DoCmd.OpenReport "rptEquipmentInv", acViewPreview, "", "", acNormal
    Reports!rptEquipmentInv.Report.RecordSource = "SELECT [EquipID], [UTIDNumber], [SerialNumber], [EquipDesc], [EquipCost], [CurrentLoc] FROM qryEquipmentInv WHERE EquipTypeID <> 22 and EquipTypeID <> 23 and EquipTypeID <> 24 and CategoryID <> 2 and DeptOwned ORDER BY [UTIDNumber]; "

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use a query. that way there's no syntax error.
    but you shouldnt be editing a report while you're opening it.

    cant you just have 2 rpts. same report, but save as rpt2 (with different query source)
    no vb code needed in the report

    Code:
    if contition=true then
      docmd.openreport "rpt1"
    else
      docmd.openreport "rpt2"
    endif

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    "SELECT [EquipID], [UTIDNumber], [SerialNumber], [EquipDesc], [EquipCost], [CurrentLoc] FROM qryEquipmentInv WHERE EquipTypeID <> 22 and EquipTypeID <> 23 and EquipTypeID <> 24 and CategoryID <> 2 and DeptOwned ORDER BY [UTIDNumber]; "
    Why do you have "and DeptOwned"​. It makes no sense to me.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I actually have several ways I want to open the report. I just listed 2 ways.

    [DeptOwned] is a check box.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by UT227 View Post
    I actually have several ways I want to open the report. I just listed 2 ways.

    [DeptOwned] is a check box.
    So did you just want it to be present or is to be part of the required criteria eg: DeptOwned = True
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    It's a required criteria. I could put = True, but I found that if it's true, just putting DeptOwned works just as well.

  7. #7
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I put the IF statement into the On Open of the report. Works great.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-10-2018, 07:22 AM
  2. Change/select record source of subreports
    By TerriLynnG in forum Reports
    Replies: 2
    Last Post: 08-24-2013, 10:18 AM
  3. Change the record source on my form
    By BigMac4 in forum Forms
    Replies: 4
    Last Post: 09-19-2012, 12:36 PM
  4. Replies: 2
    Last Post: 11-29-2010, 11:16 AM
  5. change print preview record source
    By alaric01 in forum Reports
    Replies: 10
    Last Post: 10-07-2010, 09:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums