Results 1 to 8 of 8
  1. #1
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37

    Creating Macro in form to open the record in another form

    I have two forms, one form (called "Egg Form") where users will enter data. I have another form (called "Merge Form") where all the data is amalgamated for me to see, and it is based off a query I built that obviously amalgamates a lot of data together based on the users entry. Using a macro, when I click on the ID field in the 'Merge Form', it opens the "Egg Form" however, it does not open to the corresponding ID I clicked. What do I need to change in the macro so if I for example click on ID # 35 on the Merge form, it will go the ID 35 on the Egg Form?




    Click image for larger version. 

Name:	Merge Form Macro.PNG 
Views:	12 
Size:	11.2 KB 
ID:	28776
    Attached Thumbnails Attached Thumbnails Merge Form Macro.PNG  

  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
    If you want to use VBA:

    http://www.baldyweb.com/wherecondition.htm

    if you stick with a macro, you'd use the where condition argument.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37
    Quote Originally Posted by pbaldy View Post
    I
    you'd use the where condition argument.
    Yes, I tried to use the where condition but did not appear to work. I tried to equal the current ID on the form and the ID on the Egg Form, but no dice I'll try the link you sent me but I do not know anything about VBA so I am not sure how easy it will be for me to implement.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37
    Quote Originally Posted by pbaldy View Post
    Okay, thank you. I will give it a shot this week and let you know if I run into any issues

  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
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37
    So I edited your code to represent the name of my forms, that is I replaced:

    Me.txtEmpID --> Me.ID
    "frmEmployeesDetail" --> "EggForm"

    And it worked great!! Thought I would post the solution in case anyone else wanted to see it.

    Thanks again pbaldy

    Code:
    Private Sub ID_Click()
    
    
    Dim rs As Object
    Dim lngBookmark As Long
    
    
    'set a variable to the current record
    lngBookmark = Me.ID
    'open the new form
    DoCmd.OpenForm "EggForm"
    
    
    'take it to the selected record
    Set rs = Forms!EggForm.RecordsetClone
    rs.FindFirst "ID = " & lngBookmark
    
    
    'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
    If rs.NoMatch Then   'optional - if no match, go to a new record
      DoCmd.GoToRecord acForm, "frmEmployeesDetail", acNewRec   'optional
      Forms!frmEmployeesDetail.txtEmpID = Me.ID  'optional - copy the employee ID from this form
    Else   'optional
      Forms!EggForm.Bookmark = rs.Bookmark
    End If   'optional
    Set rs = Nothing
    
    
    
    
    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
    Happy to help!
    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. Replies: 2
    Last Post: 02-20-2015, 06:32 AM
  2. Replies: 3
    Last Post: 04-18-2014, 12:20 PM
  3. Replies: 1
    Last Post: 05-03-2012, 02:25 PM
  4. Replies: 7
    Last Post: 05-01-2012, 11:43 AM
  5. Replies: 3
    Last Post: 01-14-2010, 08:32 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