Remembering field location on a form (1 Viewer)

keiths

Registered User.
Local time
Yesterday, 20:27
Joined
Feb 10, 2005
Messages
28
I have a form with a sub form (ms access). On the subform are a number of textboxes for input by the user. What I need is for the sub form to "remember" the last textbox it was in when the form closes. Then, when the form opens again, the focus in the sub form "returns" to the last text box it was at when the user wants to continue entering data.... This resolves a problem with the user overwriting existing data on the subform because they "forgot" to go to the textbox where further data needs to be entered.
I set up an "extra" field in the database, and included it on the sub form so it could be used as areference point. The text box is called "NameFld". However, my further attempts at resolving this issue via vba are not working.
Any help would be appreciated.
 

llkhoutx

Registered User.
Local time
Yesterday, 21:27
Joined
Feb 26, 2001
Messages
4,018
You can save the position, but the method may depends on how the form is closed, what event has occurred, a button click or thr form OnClose event. You'll just have to test.

screen.previouscontrol.name with give you the control (datatype name) on a close button click, possibly the Onclose event too, I'm not certain. You've got to save that somewhere (on a hidden form) and then on the OnLoad event of the form, test for non-null and then use docmd.GoToControl. When the program starts up, set that value to null.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:27
Joined
Feb 19, 2002
Messages
42,981
You don't need to worry about the form's close event, you need to worry about the field the cursor was in just before the record was saved. So, you would use the form's BeforeUpdate event and the screen.previouscontrol.name would supply the value. Then use the form's Current event to set focus to the stored field name.
 

keiths

Registered User.
Local time
Yesterday, 20:27
Joined
Feb 10, 2005
Messages
28
Thanks for the Info - Have it working now

Thanks for the help...I got it working. It is not as "elegant" as I would like it, but it works fine. Here is what I did.

For each entry textbox/field I used the following code:

Private Sub x1_GotFocus()
Me![NameFld] = "x1"
End Sub

Private Sub y1_GotFocus()
Me![NameFld] = "y1"
End Sub

etc, etc, etc...

This puts the last textbox/field name in the textbox "NameFld"

Then I did the following:

Private Sub Form_Open (Cancel As Integer)
Dim LocFld as String
LocFld = Forms![Form1]![NameFld]
DoCmd.GoToControl LocFld
End Sub

On the data entry form, I also set the default value for NameFld to be the first data entry field....I also included error checking for null values, etc etc. Just did not include that here. This is a basic example of what I did.

It seems to work fine. I tried it both as a straight form, and then set it up as a sub form. Works well both ways. You just need to make sure you reference it properly in the open form sub. Using cut and paste, I was able to set up the 60 data entry textboxes the way I needed it in about 5 minutes.
 

llkhoutx

Registered User.
Local time
Yesterday, 21:27
Joined
Feb 26, 2001
Messages
4,018
On the event that you opened the form

me!NameFld = screen.previouscontrol.name
docmd.openform ...

Next time ...

Glad that you found a way to make it work. That's paramount in my book. The times for worrying about execution speed have been greatly diminished. I started programming in the days of 64K memory on a mainframe, no hard disks, saving every milli-second, double-buffering reads and writes to tape. Back then a bug was really a bug. lol

Good luck.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:27
Joined
Feb 19, 2002
Messages
42,981
Your solution uses more code than mine and it only works for the first record when you open the form. If you don't want to delete your extra code that's fine but you should move the set focus code from the open event to the current event so it works for EVERY record rather than just the first one.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)  ' <----- This is the only event that needs the code.
    Me.NameFld = screen.previouscontrol.name
End Sub
Private Sub Form_Current ()
    Dim LocFld as String
    LocFld = Forms![Form1]![NameFld]
    DoCmd.GoToControl LocFld
End Sub

Also, by placing the code in the form's BeforeUpdate event, you only update the cursor position for a record if someone actually updates a record. Just tabbing through will not dirty the record forcing it to be resaved potentially every time it is viewed as your solution will. If you actually want to update the record every time it is viewed as long as the user tabs once, then fine.
 

keiths

Registered User.
Local time
Yesterday, 20:27
Joined
Feb 10, 2005
Messages
28
Remebering field location on a form

Thanks for the tip....
I like what you are suggesting. I was thinking about it just a while ago, and wondered about the tabbing around. I will implement your code suggestion.
Thanks for the help.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:27
Joined
Feb 19, 2002
Messages
42,981
This is what I ended up with. When I had the namefield being set in the got focus event, scrolling through the recordset updated EVERY record I scrolled to as I suspected it would. You can tell this is happening because the little pencil thingy shows up in the record selector. That means that the record has been dirtied. So your code was effectively dirtying every record every time you looked at it - really poor. I'm glad you're going to change it.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.zztest = Screen.PreviousControl.Name
End Sub

Private Sub Form_Current()
    Dim LocFld As String
    If IsNull(Me.zztest) Then
    Else
        LocFld = Me.zztest 'Forms![Form1]![NameFld]
        DoCmd.GoToControl LocFld
    End If
End Sub
 

keiths

Registered User.
Local time
Yesterday, 20:27
Joined
Feb 10, 2005
Messages
28
Okay, I see what you are doing with your code. The only thing I wonder about is the "zztest". I assume it is just a variable that does not affect anything, just allows the code to run more dynamically.
 

doulostheou

Registered User.
Local time
Yesterday, 21:27
Joined
Feb 8, 2002
Messages
314
I was reading this thread, and I need to do something similar; but not quite the same.

I have implemented code, that overtakes the Up and Down arrows to actually move up and down on my form. This works fine. However, I have two fields that are running code when the user exits the field. When I go to move to the next/previous record and my cursor is in one of these fields, Access throws an error. What I am wanting to do is move the focus to another control when one of these two controls has focus and then move back to the control after we move to the record. But it seems that I need the current control instead of the previous control. Is this an item that is available?

Code:
    Dim rs As Recordset
    
    MsgBox Screen.PreviousControl.Name
    
    If IsNull(LogID) And KeyCode = 38 Then
        DoCmd.GoToRecord , , acLast
        KeyCode = 0
        Exit Sub
    ElseIf IsNull(LogID) And KeyCode = 40 Then
        KeyCode = 0
        Exit Sub
    End If
    
    If KeyCode = 38 Or KeyCode = 40 Then
        Set rs = Me.Form.Recordset.Clone
        
        rs.FindFirst "[LogID] = " & LogID
        
        If KeyCode = 38 Then
            If rs.AbsolutePosition <> 0 Then
                rs.MovePrevious
                Me.Bookmark = rs.Bookmark 'Error occurs here when focus is on a field that updates on the Exit event.  
            End If
        Else
            If rs.AbsolutePosition <> rs.RecordCount - 1 Then
                rs.MoveNext
                Me.Bookmark = rs.Bookmark
            Else: DoCmd.GoToRecord , , acNewRec
            End If
        End If
        
        Set rs = Nothing
        KeyCode = 0
    End If
 

doulostheou

Registered User.
Local time
Yesterday, 21:27
Joined
Feb 8, 2002
Messages
314
Forgive my stupidity.... Screen.ActiveControl.Name
 

Users who are viewing this thread

Top Bottom