lost focus...?

[johnlute]

Dale Fye kindly gave me a hand with this:

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "txtProfileID='" & Me.lstProfilesAssocs.Column(0) &
"'"

If rs.NoMatch Then
MsgBox "Unable to find record!"
Else
Me.Bookmark = rs.Bookmark
End If

rs.Close
Set rs = Nothing
End Sub

It works great except I'd forgotten about a combo box [cbqrytypes]
which may be used to filter the form. If so, this results in a
filtered RecordSetClone which will be missing the matching record.
Yeesh.

I thought that maybe I could work it out. I've tried the following
however I know the problem is with having to requery the form. Of
course, this results in lost focus of what record was selected in the
listbox. Now the double-click has nowhere to take the user except to
the first record of the requeried recordset.

Am I on the right track? Is there something I can throw into the
OnLostFocus event that will permit the form to be requeried and still
navigate to the selected record in the listbox?

Thanks for your help!

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "txtProfileID='" & Me.lstProfilesAssocs.Column(0) &
"'"

If rs.NoMatch Then
If MsgBox("Sorry! Unable to navigate to this ID because the
form is currently filtered." & vbCrLf & _
"Click OK to remove the filter and navigate to this ID.",
vbOKOnly + _
vbQuestion) = vbOK Then
[cbqrytypes] = Null
Me.cbqrytypes.Requery
Me.cbProfileID.Requery
Forms![KEYSTONEqryrpt].Form.Requery
Me.cbProfileID.SetFocus
Cancel = True
End If

Else
Me.Bookmark = rs.Bookmark

End If

rs.Close
Set rs = Nothing

End Sub

 

[johnlute]

I've been pecking at this and arrived at the following:

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "txtProfileID='" & Me.lstProfilesAssocs.Column(0) &
"'"

If rs.NoMatch Then
If MsgBox("Sorry! Unable to navigate to this ID because the
form is currently filtered." & vbCrLf & _
"Click OK to remove the filter and navigate to this ID.",
vbOKOnly + _
vbQuestion) = vbOK Then
[cbqrytypes] = Null
Me.cbqrytypes.Requery
Me.cbProfileID.Requery
DoCmd.OpenForm "KEYSTONEqryrpt", acNormal, _
WhereCondition:="txtProfileID='" &
Me.lstProfilesAssocs.Column(0) & "'"
Forms![KEYSTONEqryrpt].Form.Requery
Me.cbProfileID.SetFocus
Cancel = True
End If
Else
Me.Bookmark = rs.Bookmark

End If

Set rs = Nothing

End Sub

This allows me to clear the message box and navigate to the record as
selected in lstProfilesAssocs however it also results in the form
being filtered to that single record. The desire is to maintain a
completely open recordset while navigating records between cbProfileID
and lstProfilesAssocs.

Does anybody see any way I can achieve this?

Thanks in advance!

 

[Dirk Goldgar]

I've been pecking at this and arrived at the following:

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "txtProfileID='" & Me.lstProfilesAssocs.Column(0) &
"'"

If rs.NoMatch Then
If MsgBox("Sorry! Unable to navigate to this ID because the
form is currently filtered." & vbCrLf & _
"Click OK to remove the filter and navigate to this ID.",
vbOKOnly + _
vbQuestion) = vbOK Then
[cbqrytypes] = Null
Me.cbqrytypes.Requery
Me.cbProfileID.Requery
DoCmd.OpenForm "KEYSTONEqryrpt", acNormal, _
WhereCondition:="txtProfileID='" &
Me.lstProfilesAssocs.Column(0) & "'"
Forms![KEYSTONEqryrpt].Form.Requery
Me.cbProfileID.SetFocus
Cancel = True
End If
Else
Me.Bookmark = rs.Bookmark

End If

Set rs = Nothing

End Sub

This allows me to clear the message box and navigate to the record as
selected in lstProfilesAssocs however it also results in the form
being filtered to that single record. The desire is to maintain a
completely open recordset while navigating records between cbProfileID
and lstProfilesAssocs.

Does anybody see any way I can achieve this?

John -

Is "KEYSTONEqryrpt" the name of the form that is running this code? If so,
you have no need to reopen it -- you can just remove the filter and then try
again to find the desired record. Something like this:

'------ start of revised code -------
Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)

With Me.RecordsetClone

.FindFirst "txtProfileID='" & Me.lstProfilesAssocs.Column(0) & "'"

'QUESTION FOR JOHN: IS lstProfilesAssocs BOUND COLUMN
'SET TO 1? IF SO, YOU CAN SIMPLIFY THE ABOVE TO:
' .FindFirst "txtProfileID='" & Me.lstProfilesAssocs & "'"

If .NoMatch Then

If MsgBox( _
"Sorry! Unable to navigate to this ID because the " & _
"form is currently filtered." & vbCr & vbCr & _
"Click OK to remove the filter and navigate " & _
"to this ID.",
vbOKOnly + vbQuestion) _
= vbOK _
Then

'QUESTION FOR JOHN: ARE THE FOLLOWING 3 STATEMENTS
'NECESSARY?
Me.cbqrytypes = Null
Me.cbqrytypes.Requery
Me.cbProfileID.Requery

' Remove the current filter.
Me.FilterOn = False

' After removing the filter, use new recordsetclone
' to search the form.
With Me.RecordsetClone
.FindFirst "txtProfileID='" & _
Me.lstProfilesAssocs.Column(0) & "'"
If .NoMatch Then
MsgBox _
"ERROR: Can't find the ID you selected!", _
vbExclamation,
"Profile Not Found!"
Else
Me.Bookmark = .Bookmark
End If
End With

End If

Else
Me.Bookmark = .Bookmark
End If

End With

Me.cbProfileID.SetFocus

End Sub
'------ end of revised code -------

That may need some tweaking, but something like it should work.

 

[johnlute]

Muchas gracias, Dirk!

Is  "KEYSTONEqryrpt" the name of the form that is running this code?  If so,
you have no need to reopen it -- you can just remove the filter and then try
again to find the desired record.

Easy for you to say!

I tweaked your code accordingly:

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
With Me.RecordsetClone
.FindFirst "txtProfileID='" & Me.lstProfilesAssocs & "'"
If .NoMatch Then
Beep
If MsgBox("Sorry! " & vbCr & vbCr & _
"Unable to navigate to this ID because the " &
_
"form is currently filtered." & vbCr & vbCr &
_
"Click OK to remove the filter and navigate "
& _
"to this ID.", vbOKOnly + vbQuestion) _
= vbOK _
Then

'I tinkered around and found that these 3 are
absolutely needed here.
'Without them the action jumps to the MsgBox ERROR
message below.
'With them the entire event behaves as a tiny miracle.
'cbqrytypes has an AfterUpdate event that requeries
cbProfileID (txtProfileID)
'and the form

Me.cbqrytypes = Null
Me.cbqrytypes.Requery
Forms![KEYSTONEqryrpt].Form.Requery

' Remove the current filter.
Me.FilterOn = False

' After removing the filter, use new recordsetclone
' to search the form.
With Me.RecordsetClone
.FindFirst "txtProfileID='" & Me.lstProfilesAssocs
& "'"
If .NoMatch Then
Beep
MsgBox "ERROR: Can't find the ID you
selected!", _
vbExclamation, "ID not found!"
Else
Me.Bookmark = .Bookmark
End If
End With

End If

Else
Me.Bookmark = .Bookmark
End If

End With

Me.cbProfileID.SetFocus

End Sub

I don't know how to thank you!