Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: Access GoTo macro question

    HI-

    I am currently working on a new database I created for my boss and there is an issue that I am not sure has a solution.

    I have a form to enter a student's information, including the high school they attended. If the high school is not part of the table already, there is a button to "Add a new high school" that pops up the High School form. Then, once entered, one clicks the close button and a macro also closes and re-opens the student form so that the high school will now be added to the list. This brings the form back to the first record of the table.

    My question is this:
    If you can have the GoTo in a macro form go to first, last, specific records, etc., is there a way to have the form return to the profile you were working on? I fully realize this might not be possible- and if it is a VBA thing, I may not be experienced enough to do it, but I would love some input or ideas.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You'll need a little VBA but nothing very complex:

    1. In an independent module (existing or new but do not use the module of a form), paste this code:
    Code:
    ' this line in the Declarations section (top of the module).
    Public RecordID As Variant
    
    Function ReturnToPreviousRecord()
    
        Dim varRecordid As Variant
        Dim rst As DAO.Recordset
        Dim frm As Form
        
        If RecordID <> 0 Then
            varRecordid = RecordID
            RecordID = 0
            Set frm = Forms!<Form1>
            Set rst = frm.RecordsetClone
            rst.FindFirst "<UniqueID>=" & varRecordid
            If rst.NoMatch = False Then frm.Bookmark = rst.Bookmark
            Set rst = Nothing
            Set frm = Nothing
        End If
        
    End Function
    Replace <Form1> with the name of the form the macro reopens and replace <UniqueID> with the name of a unique identifier for that form data set (usually the primary key).

    2. Create an event handler for the Current event of the form and paste this line of code into it:
    Code:
        RecordID = Me!<UniqueID>
    Again, replace <UniqueID> with the name of a unique identifier for the form data set (usually the primary key).
    The Current sub should look like this (here the name of the unique identifier is SysCounter):
    Code:
    Private Sub Form_Current()
    
        RecordID = Me!SysCounter
        
    End Sub
    3. In the macro that reopens the form, add this command: RunCode and for the function name type: ReturnToPreviousRecord()

    Note: If the unique ID field is not numeric you must change a line in the function to:
    Code:
           rst.FindFirst "<UniqueID>='" & varRecordid & "'"
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    why not just requery that feild when you close the other form
    Form_formname.objectname.Requery

    or

    put a event on the combo box on the Enter Event to requery its else when ented
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    May 2011
    Posts
    3

    Follow-up

    Sinndho-
    I am afraid you may still be going a little over my head- I am super new to VBA. I got step one done and then I am unfamiliar with how to create an event handler in Access (I have 2007). If you could let me know what I have to do to create an event handler, I think the rest looks straight forward. I really appreciate your help and am excited that this will expand my little bit of VBA knowledge if I can get it to work

    Myle-
    When I tried your solution, I got an error message that said the field did not exist. I put my code as Form_frmStudent_MASTERLIST.High_School.Requery under a requery category in my macro. Can you tell from that description if I have not followed your directions correctly? Thanks for your help too!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To create the event handler open the form in Design view then open the Properties window and select the Event tab. Open the combo of the line related to the event you want to handle (here On Current) then select [Event Procedure] (see attached picture). Finally click on the button with 3 dots (on the right of the line) to open the VBA Editor that should display:
    Code:
    Private Sub Form_Current()
    
    End Sub
    Add the code between the 2 lines.
    Attached Thumbnails Attached Thumbnails Create_EventHandler.jpg  
    Have a nice day!

  6. #6
    Join Date
    May 2011
    Posts
    3

    Sinndho

    Thank you!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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