Lost Focus

L

lmcc007

Registered User.
Local time
Today, 14:42
Joined
Nov 10, 2007
Messages
635
Lost Focus when requering

frmViewEventHistory is a continuous form with Pop Up = Yes and Modal = Yes. While viewing the form and you double click a row it will open fdlgEventDetails form (that is also Pop Up = yes and Modal = Yes). That’s okay.

After closing fdlgEventDetail, you’re back on your continous form, but at the first record—not the record last on when double-clicked. It’s becoming annoying when you have a lot of records and have to figure out where you were.

How do I get it to go back to the record I was on?

Below is my unload code for fdlgEventDetail:

Code: 
Private Sub Form_Unload(Cancel As Integer)

On Error GoTo ErrorHandler
    
    If CurrentProject.AllForms("frmViewEventHistory").IsLoaded Then
        Forms!frmViewEventHistory.Requery
    End If


CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
        "Description:  " & Err.Description & vbCrLf & _
        "Error Number:  " & Err.Number, vbCritical, gstrAppTitle)
    Resume CleanUpAndExit
  
End Sub
 
Last edited:
You need to save the bookmark of the form's recordset into an object before requerying then apply the bookmark back to the form afterwards.

Search the help file for Recordset.Bookmark
 
vbaInet said:
You need to save the bookmark of the form's recordset into an object before requerying then apply the bookmark back to the form afterwards.

Search the help file for Recordset.Bookmark
Click to expand...

Yeah, I just did. That's a lot of coding--beyond me.
 
All you need is three lines of code. Reread what I said about how to do it and try to follow it. :)
 
vbaInet said:
All you need is three lines of code. Reread what I said about how to do it and try to follow it. :)
Click to expand...

Not getting it. And the instructions for Form.Bookmark Property goes into ADO and DAO all that stuff. I'll look some more and maybe by tomorrow it'll come to me or know how to do it.
 
No DAO or ADO required here, just Me.Recordset.Bookmark because you're referring to the form's recordset. The bookmark returns and object so you need to save that object somewhere, requery, then set that saved bookmark back to Me.Recordset.Bookmark
 
vbaInet said:
No DAO or ADO required here, just Me.Recordset.Bookmark because you're referring to the form's recordset. The bookmark returns and object so you need to save that object somewhere, requery, then set that saved bookmark back to Me.Recordset.Bookmark
Click to expand...

I'm not getting it. I have to understand what Me.Recordset.Bookmark does/means. When I think of bookmark, I think of marking a place to come back to. (And, it's Friday night--my brain is wondering--I didn't plan on getting into something like this weekend.)
 
You can always check the help files for its meaning and like you rightly said, it's a marker.

(And, it's Friday night--my brain is wondering--I didn't plan on getting into something like this weekend.)
Click to expand...
It's certainly the weekend :)
 
I reread it again and still do not understand what you are talking. I searched the web and do not understand the stuff I found. Tried some of the examples but nothing happened--still when you requery it goes back to Record # 1.

Also, this must be a huge problem because one forum I happened upon has over 4,500 entries asking the same question. I guess MS forgets most people are not tech gurus trying to understand. I guess that's why my Dummies books don't cover adding a bookmark.

Anyway, I did the below to get what I needed:

Code: 
Private Sub Form_Unload(Cancel As Integer)

On Error GoTo ErrorHandler
    
    If CurrentProject.AllForms("frmViewEventHistory").IsLoaded Then
        Forms!frmViewEventHistory.Requery
    End If


    If DCount("[EventAttachment]", "Event", "[CompanyID] = " & Me!txtCompanyID) = 0 Then
        Forms!fmainCompany!cmdAttachments.Caption = "No Attachment"
        Forms!fmainCompany!cmdAttachments.ForeColor = vbRed
    Else
        Forms!fmainCompany!cmdAttachments.Caption = "Attachment"
        Forms!fmainCompany!cmdAttachments.ForeColor = -2147483615
    End If

    If IsNull(DLookup("EventID", "Event", "CompanyID = " & Me!txtCompanyID)) Then
        Forms!fmainCompany!cmdEventHistory.Caption = "No History"
        Forms!fmainCompany!cmdEventHistory.ForeColor = vbRed
    Else
        Forms!fmainCompany!cmdEventHistory.Caption = "History"
        Forms!fmainCompany!cmdEventHistory.ForeColor = -2147483615
    End If   
        

CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
        "Description:  " & Err.Description & vbCrLf & _
        "Error Number:  " & Err.Number, vbCritical, gstrAppTitle)
    Resume CleanUpAndExit
  
End Sub
 
Last edited:
Oh, I was thinking you would go for a FindFirst option, but never mind. Here are the three lines I mentioned:
Code: 
On Error GoTo ErrorHandler
    
    dim bkMark as String

    If CurrentProject.AllForms("frmViewEventHistory").IsLoaded Then
        bkMark = Forms!frmViewEventHistory.bookmark
        Forms!frmViewEventHistory.Requery
        Forms!frmViewEventHistory.bookmark = bkMark
    End If
 
vbaInet said:
Oh, I was thinking you would go for a FindFirst option...
Click to expand...

Actually vbaInet, you are correct. I found the code below and tried to make it work for me but was unsuccessful:

Code: 
Private Sub cmdEventHistory_Click()

 On Error GoTo ErrorHandler
 
     Dim CompanyID As Long               ' variable placeholder
    CompanyID = Me!txtCompanyID      ' ID of record

    '... do whatever
    DoCmd.OpenForm ("frmViewEventHistory"), acNormal, , "CompanyID=" & Me!txtCompanyID

    Me.Requery

    Me.RecordsetClone.FindFirst "CompanyID = " & CompanyID

    If Not Me.RecordsetClone.NoMatch Then
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    
    
CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
        "Description:  " & Err.Description & vbCrLf & _
        "Error Number:  " & Err.Number, vbCritical, gstrAppTitle)
    Resume CleanUpAndExit
    
End Sub

but it did the same thing--returned to first record.

I read that when it requeries it goes back to first record. So, I was wrong in thinking that it filtered record 1 of 1. I kind of understand that part that if you run the query again it starts over at the first record. What I don't understand is why bookmark is so complicated to comprehend. Because in my mind I think:

1) I am form 1--fmainCompany
2) mark the record I am on
3) click to open form 2 -- frmViewEventHistory
3) make some changes, requery, close form 2
4) return to record I was on
5) requery form 1--fmainCompany

Anyway, let go over what you wrote. See if I get it, try it, and let you know. Thanks!
 
Is CompanyID not the name of your CompanyID field too? Name your variables properly. A good syntax could be intCompanyID.

In your code, all you needed was this line:
Code: 
Me.Recordset.FindFirst "CompanyID = " & CompanyID
Not recordsetclone but simply recordset. No other code line is required.

The bookmark option should be faster anyway.
 
I tried the bookmark code, but it's not updating fmainCompany. Below is what I did:

1) Put the below code in the fdlgEventDetailDeleteEdit form in On Unload Event.

Code: 
Private Sub Form_Unload(Cancel As Integer)

On Error GoTo ErrorHandler
    
    Dim bkMark As String

    If CurrentProject.AllForms("frmViewEventHistory").IsLoaded Then
        bkMark = Forms!frmViewEventHistory.Bookmark
        Forms!frmViewEventHistory.Requery
        Forms!frmViewEventHistory.Bookmark = bkMark
    End If
    

CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
        "Description:  " & Err.Description & vbCrLf & _
        "Error Number:  " & Err.Number, vbCritical, gstrAppTitle)
    Resume CleanUpAndExit
  
End Sub

2) close form

3) open fmainCompany (form 1)

4) hit cmdHistory button to open frmViewEventHistory form.

5) hit cmdDelete/Edit button to edit the record.

6) fdlgEventDetailDeleteEdit opens. Make changes. Close form.

7) Error: Description: Not a valid bookmark. Error Number: 3159

8) hit OK to close error message box. (forms closes—error message and fdlgEventDetailDeleteEdit)

9) Now back at frmViewEventHistory. It has been updated.

10) hit OK

11) back at fmainCompany (form 1). Still at the same record, but fmainCompany not updated--the command buttons didn’t change to reflect the changes made.
 
Is fdlgEventDetailDeleteEdit pulling from the same table or query as the other form?
 
I'm almost positive I adviced you to use an unbound form and pass the details to the main form instead? This is bad design practice.

You would need to revert to the FindFirst code in that case.
 
vbaInet said:
I'm almost positive I adviced you to use an unbound form and pass the details to the main form instead? This is bad design practice.

You would need to revert to the FindFirst code in that case.
Click to expand...

I am not following you here. The forms are:

fmainCompany -- Record Source = Company (1--main form--will always be opened--open other forms by clicking buttons)

fdlgEventDetail -- Record Source = Event (2--used to add new events)

frmViewEventHistory -- Record Source = qryF_EventHistoryList (3--a continuous form--you can click a button on the main form to view the records)

fdlgEventDetailDeleteEdit -- Record Source = Event (4--used to revise or delete events)
 
vbaInet said:
Just use the FindFirst option. You can also use Docmd.GoToRecord as well, look into that.

Here's a link from pbaldy:
http://baldyweb.com/Requery.htm
Click to expand...

Isn't this like the one I told you I tried but had trouble with? That is:

Code: 
   Dim CompanyID As Long               ' variable placeholder
    CompanyID = Me!txtCompanyID      ' ID of record

    '... do whatever
    DoCmd.OpenForm ("frmViewEventHistory"), acNormal, , "CompanyID=" & Me!txtCompanyID

    Me.Requery

    Me.RecordsetClone.FindFirst "CompanyID = " & CompanyID

    If Not Me.RecordsetClone.NoMatch Then
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If

I thought you said bookmark is faster and to use the bkmark code posted.

The unbound forms--I thought you use them to search or filter info.
 
Compare your code again with pbaldy's code. There are differences.

Bookmark won't work for you due to your design.
 
You must log in or register to reply here.

Similar threads

R
Synching subforms after query
Replies
18
Views
1,374
renrenren
R
J
Error "Can't set focus"
2
Replies
21
Views
1,939
Isaac
Isaac
A
Solved VBA AfterUpdate event
2
Replies
20
Views
2,972
Isaac
Isaac
C
Sub or function not defined
Replies
9
Views
2,052
CJ_London
C
B
Solved Error 6 Overflow
2
Replies
26
Views
2,257
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom