Results 1 to 7 of 7
  1. #1
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43

    VBA to open one of two reports depending on the value of a field in the current report


    I am a VBA novice and trying to figure out the syntax for a button in the detail section of a report that will open one of two reports depending on the value of the item field in the current line

    I've got the Docmd working individually for each but really struggling with the If statement.

    Would really appreciate some help please

    Thanks
    Ian

    Private Sub Command95_Click()


    If [Item] contains "tile"

    DoCmd.OpenReport "lot loc", acViewReport, , " lot2='" & Me!lotlink & "'"

    Else

    DoCmd.OpenReport "itemloc", acViewReport, , " item='" & Me!Item & "'"


    Endif


    End Sub

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try this:

    Code:
    Private Sub Command95_Click()
    
    If Me.Item Like "*tile*" Then
         DoCmd.OpenReport "lot loc", acViewReport, , "lot2='" & Me.lotlink & "'"
    Else
         DoCmd.OpenReport "itemloc", acViewReport, , "item='" & Me.!Item & "'"
    Endif
    
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    If instr(txtItem,"tile")>0 then
       DoCmd.OpenReport "lot loc", acViewReport, , " lot2='" & Me!lotlink & "'"
    Else
       DoCmd.OpenReport "itemloc", acViewReport, , " item='" & Me!Item & "'"
    Endif

  4. #4
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    Quote Originally Posted by ridders52 View Post
    Try this:

    Code:
    Private Sub Command95_Click()
    
    If Me.Item Like "*tile*" Then
         DoCmd.OpenReport "lot loc", acViewReport, , "lot2='" & Me.lotlink & "'"
    Else
         DoCmd.OpenReport "itemloc", acViewReport, , "item='" & Me.!Item & "'"
    Endif
    
    End Sub
    Thanks very much for such a speedy reply and helping out, much appreciated, it works perfectly. I knew it had to be pretty straightforward but sometimes I can go around in circles for hours!

    Best

    Ian

  5. #5
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    Hi Ranman

    Thanks for helping out, Ridders reply worked out out for me but I'm sure you're solution would as well

    Mauch appreciated

    best

    Ian

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad to have helped. Could you mark the thread as solved.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    I belatedly found the Solved button

    Thanks

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

Similar Threads

  1. Replies: 5
    Last Post: 02-28-2017, 02:45 PM
  2. Replies: 6
    Last Post: 02-11-2015, 12:53 PM
  3. Replies: 2
    Last Post: 04-02-2013, 02:24 AM
  4. Open Current Report With Form Button
    By Rick5150 in forum Reports
    Replies: 8
    Last Post: 10-12-2011, 02:28 PM
  5. Open Report or Form depending on condition
    By jheintz57 in forum Forms
    Replies: 5
    Last Post: 03-12-2010, 08:16 PM

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