Remembering field location on a form

[keiths]

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]

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.

 

[keiths]

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]

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.

 

[keiths]

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.

 

[keiths]

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]

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?

    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]

Forgive my stupidity.... Screen.ActiveControl.Name