Work around control.Visible property

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.....

Access 2003 ...Windows XP Pro

In maintaining my record position within a recordset, to use after a
Requery, I am using the following code which is doing the job.

'save value of primary key
If Not Me.NewRecord Then
mPrimaryKey = Nz(Me.txtOurPeopleID)
End If

Me.FilterOn = False
Me.RecordSource = "qryReturnedMailFollowUpProcessor"
Me.Requery

'go back to record you were on
If mPrimaryKey <> 0 Then
Me.txtOurPeopleID.SetFocus
DoCmd.FindRecord mPrimaryKey
End If

My problem is that when I set

Me.txtOurPeopleID .Visible = False

I get an error because SetFocus and Visible = False creates a conflict. I
get why this is happening.

As would be obvious, I do not want to display the txtOurPeopleID control.
Other than out of view of the display window for the form, are there other
options.

Thanks as Always......
 
Use the form's Bookmark Property to return to the original record. Try:

Private Sub RequeryBtn_Click()

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim mPrimaryKey As Long
Dim fOpenedRecSet As Boolean

If Not Me.NewRecord Then
mPrimaryKey = Nz(Me.txtOurPeopleID)
End If

Me.FilterOn = False
Me.RecordSource = "qryReturnedMailFollowUpProcessor"
Me.Requery

'go back to record you were on
' If mPrimaryKey <> 0 Then
' 'Me.txtOurPeopleID.SetFocus
' Me.txtPID.SetFocus
' DoCmd.FindRecord mPrimaryKey
' End If

Set recSet = Me.RecordsetClone
recSet.FindFirst "PID = " & mPrimaryKey

If (Not (recSet.NoMatch)) Then
Me.Bookmark = recSet.Bookmark
End If

CleanUp:

If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If

Set recSet = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in RequeryBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub ' RequeryBtn_Click( )

.. . . where PID is the name of the primary key field, which is a Long data
type.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Check Access VB Help on the BookMark Prpoerty of the Form. There is sample
code in the Help topic to use the BookMark Property of the Form (and the
RecordsetClone) to navigate to the required Record.
 
Thanks Gunny,

I'm a little slow responding, but I like to give a response only after
employment of a suggested method.

Suggestion, solution and even code with my control names! How could anyone
be more helpful than? I truly appreciate your time and effort, and it is
even better when it all works perfectly , as it did here. Great help. Again
my thanks.
 
Back
Top