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.
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:
' this line in the Declarations section (top of the module).
Public RecordID As Variant
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
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:
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):
Private Sub Form_Current()
RecordID = Me!SysCounter
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:
why not just requery that feild when you close the other form
put a event on the combo box on the Enter Event to requery its else when ented
hope this help
See clear as mud
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.
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
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!
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: