Results 1 to 13 of 13
  1. #1
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56

    What is wrong with this macro that im using in a subform.

    I have a field in a Notes subform that when clicked opens a form that will open more detailed notes in a form called NotesDetails. It works great and will open an individual note when used but not once its embedded as a subform in my Customer form. When I Click it in the subform I get the little box that asks for the NoteID even though Im looking right at it. Im guessing its trying to find the NoteID using data on the master form??


    How do i get it to use the NoteID from the Subform?
    It opens the form "NotesDetails
    using the Where condition of ...[NoteID]=[Forms]![NotesF]![NoteID]

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The syntax changes when it's a subform:

    http://www.theaccessweb.com/forms/frm0031.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Probably need to reference main form then subform container control name. I always name container control different from object it holds, like ctrNotes.

    Forms!Customer.ctrNotes!NoteID

    I don't use macros. VBA would be like:

    DoCmd.OpenForm "NotesDetails", , , "NoteID = " & Me.NoteID

    That would work in either case - as form or subform.
    Last edited by June7; 01-23-2020 at 05:11 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Thanks
    I still cant get it to work with the macro. (I cant figure out the syntax yet for container...blah blah). but the VBA script worked right out of the box. Seems much more simple. Im going to have to start digging into the VBA side for some of the simple stuff.

  5. #5
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Just out of curiosity if I wanted to only open notes that had actual notes in them and ignore the open Notesdetail request would I do something like this?
    Otherwise I would just create an "open notedetails" button and remove the field OnClick property. That way if i wanted to enter a new note where there is none, it wouldnt open further details.

    Sub IF_ELSE_FUNCTION()
    If Me.NoteID Is Null Then DoCmd.OpenForm "NotesDetails", , , "NoteID = " & Me.NoteID
    Else:
    MsgBox "There are no notes"
    End If
    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is Null is only valid in SQL. More like

    Code:
    If Len(Me.NoteID & vbNullString) = 0 Then 
      DoCmd.OpenForm "NotesDetails", , , "NoteID = " & Me.NoteID
    Else
      MsgBox "There are no notes"
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    I'm getting a missing operator syntax error with that. But I get the concept. its checking the length of the text and if the length is 0 then it runs the if. Interesting.

    Private Sub ClientNotes_Click()
    If Len(Me.NoteID & vbNullString) = 0 Then
    DoCmd.OpenForm "NotesDetails", , , "NoteID = " & Me.NoteID
    Else
    MsgBox "There are no notes"
    End If
    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That technique (using Len) checks for both Null and a zero length string. I was going to see if you noticed your logic was reversed. If the control is empty you try to use it. From your description I think it would be the reverse. If it has something in it you want to use it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Ya, true, But if it has nothing in it I also want to add to it (like making a new note from todays conversation). I dont want it jumping to another form to look at nothing.

  10. #10
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Ha!
    I see after looking at it a little better that you tricked me. the statement was reversed. Did you do that on purpose? Now thats flipped, its working fine. Thanks

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! I wouldn't trick you, I followed the logic of the code you had in post 5.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Wow, You're absolutely correct. Thinking and typing weren't jiving at that moment. I knew what i wanted to do, but for some reason wrote it backwards. More coffee please.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can't help you with coffee, but I've got a Dr Pepper here with your name on it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Macro on subform
    By xunil in forum Access
    Replies: 1
    Last Post: 11-11-2019, 05:12 PM
  2. Replies: 7
    Last Post: 08-17-2015, 11:52 PM
  3. Replies: 4
    Last Post: 07-03-2013, 10:02 AM
  4. Replies: 7
    Last Post: 05-01-2012, 11:43 AM
  5. Macro to navigate to tab or subform
    By larrydwilliams in forum Programming
    Replies: 1
    Last Post: 02-12-2012, 07:31 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