Not sure what is going wrong.

[doulostheou]

I originally began this question under a different thread, but I ended up answering my question and then coming up with a new one that is not necessarily related. I thought it would be best to just start a new thread.

I have written code that overtakes the up and down records to move between records on a subform. The code works great, unless the focus is inside a field that runs code on the Exit event. Then it creates an error. I thought I could divert this by moving the focus off of the field (which the Exit event does by default) and then moving it back, but the code is generating an error "Cannot move the focus to the control." I'm not sure why this is happening, especially because if I hit debug and then tell the code to continue executing, everything works fine. Any insight that could be offerred would be appreciated.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim rs As Recordset
    Dim strCtl As String
    
    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
        strCtl = Screen.ActiveControl.Name
        
        If strCtl = "txtStart" Then
            Call txtStart_Exit(0)
        ElseIf strCtl = "txtEnd" Then
            Call txtEnd_Exit(0)
        End If
    
        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
                If strCtl = "txtStart" Or strCtl = "txtEnd" Then Me.Controls(strCtl).SetFocus
            End If
        Else
            If rs.AbsolutePosition <> rs.RecordCount - 1 Then
                rs.MoveNext
                Me.Bookmark = rs.Bookmark
                If strCtl = "txtStart" Or strCtl = "txtEnd" Then Me.Controls(strCtl).SetFocus
            Else: DoCmd.GoToRecord , , acNewRec
            End If
        End If
        
        Set rs = Nothing
        KeyCode = 0
    End If
End Sub

Private Sub txtStart_Exit(Cancel As Integer)
    If Not IsNull(txtStart) Then
        txtStart = IIf(CDate([txtStart] - Fix([txtStart])) = 0, #12:00:00 AM#, CDate([txtStart] - Fix([txtStart])))
        txtStart = Forms!frmTimeManager!txtDate + txtStart
        txtRefNum.SetFocus
    End If
End Sub

 

[modest]

if everything is working fine, just use this to skip over the error:

on error resume next

I don't normally work with clone's but is Set rs = Me.Form.Recordset.Clone right?
It wouldn't be Me.Recordset.Clone?
or isn't there even a Me.RecordsetClone

I'd also decare my rs as DAO.Recordset

txtRefNum.SetFocus may want to have an identifier Me.txtRefNum.SetFocus (might confuse Access if you don't)

 

[doulostheou]

Unfortunately it wasn't, but I do appreciate your taking the time to respond. I tried the resume next code, but then it just didn't do what it was supposed to do. Something about the code breaking and then resuming, caused it to work. I just put in a cheap workaround when the cursor is in one of the two problem fields. I would like to fix it, but I am ready to move on.

I believe the problem is actually related to another issue I had. Nothing would happen when I would try to leave one of those same two fields and go to a different record in the continuous form at the same time. That is why I ended up moving the focus to a different field on exit. Then a double click would take you where you want to go on the form. I wasn't thrilled with this solution, but it worked. I basically did the same thing as a workaround to the issue described in this post. If you push the down/up arrow in that field, it moves you to a different field. Then the down arrow will work as intended when you push it again.

Access doesn't seem to like updating the field on exit and switching records. I would still love to hear any help that someone could offer on this issue, but I do have it "working" even if it is a quirky fix.

 

[modest]

aggh, I just deleted a database that I had that same issue with. The code would work if I breaked, but wouldn't if it ran through. I don't remember what I did... it will come to me though.

But did you make the changes to: Me.RecordsetClone?

and rs.FindFirst "[LogID] = '" & LogID & "'"


Also try naming it to the _KeyPress() event or the _KeyUp() event


finally remember that you should close your recordset before setting the pointer to nothing. just setting it to nothing is leaving the data in memory, without a way to access it.
so:
rs.Close
Set rs = Nothing

 

[doulostheou]

I work with recordsetclones quite frequently, and the syntax is correct. There might be alternative ways to reference it, but the syntax I chose is the same syntax access generates when dealing with recordsets in the wizard for creating code to lookup a record on a form.

I don't need the extra quotations for the LogID, because it is a numeric field.

I didn't want to wait for the KeyUp action, and as far as I could tell the KeyPress event doesn't catch the arrow keys.

I have actually never heard about rs.close freeing up the memory (or if I have, I forgot). I use recordsets contstantly, which would mean I probably have some memory leak issues. I'll check into it.

Thanks again, for taking the time to respond.

 

[Mile-O]

Access doesn't seem to like updating the field on exit and switching records. I would still love to hear any help that someone could offer on this issue, but I do have it "working" even if it is a quirky fix.

That's because the Exit event, like the BeforeUpdate event, is used to check for values and not typically for assigning values. Move your code to an appropriate event.

 

[doulostheou]

I just did a quick search on the site on rs.close and memory. I found the following on this post: http://www.access-programmers.co.uk/forums/showthread.php?t=63083&highlight=memory+rs.close

rs.Close disconnects the object from the recordset it represents.

Set rs = Nothing frees up the memory used to store the recordset object.

It would appear that rs.close does not free the memory, but it is the other way around. Which actually sounds vaguely familiar. I believe I stumbled across that a few years ago and decided that I couldn't see an advantage to using rs.close. I believe I also noted that in some of the access generated code, the recordset was not closed. Not that I should be relying on the access code to always be written correctly. I will do some more research to see if I had made a rash decision.

 

[Mile-O]

Private Sub txtStart_Exit(Cancel As Integer)
    If Not IsNull(txtStart) Then
        txtStart = IIf(CDate([txtStart] - Fix([txtStart])) = 0, #12:00:00 AM#, CDate([txtStart] - Fix([txtStart])))
        txtStart = Forms!frmTimeManager!txtDate + txtStart
        txtRefNum.SetFocus
    End If
End Sub

Private Function TestStart() As Boolean
    On Error Goto Err_TestStart
    If Not IsNull(txtStart) Then
        txtStart = IIf(CDate([txtStart] - Fix([txtStart])) = 0, #12:00:00 AM#, CDate([txtStart] - Fix([txtStart])))
        txtStart = Forms!frmTimeManager!txtDate + txtStart
        txtRefNum.SetFocus
    End If
    TestStart = True
    Exit Function
Err_TestStart
    TestStart = False
    Resume Exit_TestStart
End Function

Private Sub txtStart_Exit(Cancel As Integer)
    Cancel = Not TestStart
End Sub

 

[modest]

If my explanation above (on how the commands affect memory) was incorrect, just know it's because I come from programming C - that should explain. In any case, just know:

.Close closes the Recordset object and releases its contents.

Setting the recordset object to Nothing will release the recordset object.


Furthermore, you can still feel safe as Access VBA is supposed to automatically clean up local objects when a procedure ends, but because there are errors it would be safer to write the code correctly.

 

[doulostheou]

SJ McAbney, I tried breaking out the code that runs on Exit in a separate procedure; but I'm still left with the same anomolies. Really my whole problem boils down to Access not wanting to switch records simutaneously with running code On_Exit of the active field. I could fix the problem instead of work around it, if someone could think of a good alternative for what I am trying to do.

I am working with a date/time field that is storing the current date and time. The field is set to just display the current time, but I need to store the date as well, especially because I am accounting for records that will cross over midnight.

When you click inside of the field, the display changes to show the user the date and the time. I didn't want this. What I did was create code that on Enter, truncates the date show only the time would display and then on Exit, puts the appropriate date back on.

An idea just occurred to me. I could try moving the code that verifies the record that fixes the date field in the Forms Before_Update event. It's worth a try....
_____________________________________________

Modest, I understand coming from a C background. VBA is actually my 'native' programming language so to speak. I have a good friend that used to work with me that came from a C background. While the languages are very similar in some regards, there are definitely many intracacies. I did find a very interesting post that was contributed to by a lot of the forum users that I really respect: http://www.access-programmers.co.uk/forums/showthread.php?t=65325&goto=nextoldest. I really enjoyed this line by SJ McAbney:

• rst.Close simply cuts the connection to the recordset;

• Set rs = Nothing clears the recordset object from memory

or,

• turning the TV off in the living room;

• throwing the TV out the window, restoring space in the living room

It still appears to me that there is no danger to not closing the recordset, as long as you are sure to disconnect it. As finding and correcting all the recordsets in my database would be a bit of an undertaking, I will probably just leave it alone unless there is a good reason not to.