Setting focus on a specific record

[Gaetan]

Hi,

I have a continuous form in which I have, amongst others, a combo box
(cboActivity) and a text box (txtActivityComments). The cboActivity combo box
lets the user select a production activity. Some of these activities requires
the user to enter comments in the txtActivityComments text box.

After comments have been entered in txtActivityComments, the user still has
the possibility to change the chosen activity. If this is done, the
txtActivityComments text box is disabled by the use of a conditional
formating and it's content is cleared witn an AfterUpdate event on the
cboActivity combo box.

After messing around quite a lot with the code (being a newbie with VBA,
especially with recordsets), I have finally got it to work as I wanted,
except from a little problem. When the code runs, after remove the comments
from the txtActivityComments text box, it sets the focus on the first record
of the continuous form, even though the modified record was the fifth one.

How would I adapt my code so that the focus stays on the record that has
been modified? I have a clue that it moves to the first record because of the
requery method but without this, the txtActivityComments text box doesn't get
cleared properly.

Here's my code below...

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X
Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

End Sub

Thanks for any help you can provide me with!

 

[Jon Lewis]

Check out the Bookmark property of a recordset in Access Help.

HTH

 

[Gaetan]

Hi have added code for the bookmark, but it still won't set the focus on that
specific record. I have included the code below.... Have I done anything
wrong?

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long
Dim varBookmark As Variant

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord
varBookmark = rcdProdEntry.Bookmark

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X

Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

rcdProdEntry.Bookmark = varBookmark

End Sub

Thanks!

Check out the Bookmark property of a recordset in Access Help.

HTH

Hi,

I have a continuous form in which I have, amongst others, a combo box
(cboActivity) and a text box (txtActivityComments). The cboActivity combo
box
lets the user select a production activity. Some of these activities
requires
the user to enter comments in the txtActivityComments text box.

After comments have been entered in txtActivityComments, the user still
has
the possibility to change the chosen activity. If this is done, the
txtActivityComments text box is disabled by the use of a conditional
formating and it's content is cleared witn an AfterUpdate event on the
cboActivity combo box.

After messing around quite a lot with the code (being a newbie with VBA,
especially with recordsets), I have finally got it to work as I wanted,
except from a little problem. When the code runs, after remove the
comments
from the txtActivityComments text box, it sets the focus on the first
record
of the continuous form, even though the modified record was the fifth one.

How would I adapt my code so that the focus stays on the record that has
been modified? I have a clue that it moves to the first record because of
the
requery method but without this, the txtActivityComments text box doesn't
get
cleared properly.

Here's my code below...

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X
Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

End Sub

Thanks for any help you can provide me with!

 

[Jon Lewis]

I'm not sure what your For ..Next loop is achieving or if you're working
with your form's recordset or rcdProdEntry but you say all's working OK.
If, after Me.Requery, you want to go to the same record on your form that
you were on before the requery then:

varBookmark = Me.RecordsetClone.Bookmark
'Do something
Me.Requery
Me.Bookmark = varBookmark

BTW you can Dim varBookmark As Long (it takes up less system resource)

HTH

Hi have added code for the bookmark, but it still won't set the focus on
that
specific record. I have included the code below.... Have I done anything
wrong?

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long
Dim varBookmark As Variant

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord
varBookmark = rcdProdEntry.Bookmark

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X

Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

rcdProdEntry.Bookmark = varBookmark

End Sub

Thanks!

Check out the Bookmark property of a recordset in Access Help.

HTH

Hi,

I have a continuous form in which I have, amongst others, a combo box
(cboActivity) and a text box (txtActivityComments). The cboActivity
combo
box
lets the user select a production activity. Some of these activities
requires
the user to enter comments in the txtActivityComments text box.

After comments have been entered in txtActivityComments, the user still
has
the possibility to change the chosen activity. If this is done, the
txtActivityComments text box is disabled by the use of a conditional
formating and it's content is cleared witn an AfterUpdate event on the
cboActivity combo box.

After messing around quite a lot with the code (being a newbie with
VBA,
especially with recordsets), I have finally got it to work as I wanted,
except from a little problem. When the code runs, after remove the
comments
from the txtActivityComments text box, it sets the focus on the first
record
of the continuous form, even though the modified record was the fifth
one.

How would I adapt my code so that the focus stays on the record that
has
been modified? I have a clue that it moves to the first record because
of
the
requery method but without this, the txtActivityComments text box
doesn't
get
cleared properly.

Here's my code below...

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X
Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

End Sub

Thanks for any help you can provide me with!

 

[Gaetan]

Uhmmm... I might not be doing it right, but my form is based on a query
linking multiple tables. The recordset is based on this query.

The For... Next brings me to the selected record when I change the
cboActivity combo box so that I can delete the content of some text boxes on
that record using the recordset. The problem is that it brings me to the
first record when I do the requesry, when I would like it to stay on the
current record.

I have tried your suggestion but it brought me no results.

I'm not sure what your For ..Next loop is achieving or if you're working
with your form's recordset or rcdProdEntry but you say all's working OK.
If, after Me.Requery, you want to go to the same record on your form that
you were on before the requery then:

varBookmark = Me.RecordsetClone.Bookmark
'Do something
Me.Requery
Me.Bookmark = varBookmark

BTW you can Dim varBookmark As Long (it takes up less system resource)

HTH

Hi have added code for the bookmark, but it still won't set the focus on
that
specific record. I have included the code below.... Have I done anything
wrong?

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long
Dim varBookmark As Variant

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord
varBookmark = rcdProdEntry.Bookmark

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X

Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

rcdProdEntry.Bookmark = varBookmark

End Sub

Thanks!

Check out the Bookmark property of a recordset in Access Help.

HTH

Hi,

I have a continuous form in which I have, amongst others, a combo box
(cboActivity) and a text box (txtActivityComments). The cboActivity
combo
box
lets the user select a production activity. Some of these activities
requires
the user to enter comments in the txtActivityComments text box.

After comments have been entered in txtActivityComments, the user still
has
the possibility to change the chosen activity. If this is done, the
txtActivityComments text box is disabled by the use of a conditional
formating and it's content is cleared witn an AfterUpdate event on the
cboActivity combo box.

After messing around quite a lot with the code (being a newbie with
VBA,
especially with recordsets), I have finally got it to work as I wanted,
except from a little problem. When the code runs, after remove the
comments
from the txtActivityComments text box, it sets the focus on the first
record
of the continuous form, even though the modified record was the fifth
one.

How would I adapt my code so that the focus stays on the record that
has
been modified? I have a clue that it moves to the first record because
of
the
requery method but without this, the txtActivityComments text box
doesn't
get
cleared properly.

Here's my code below...

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X
Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

End Sub

Thanks for any help you can provide me with!

 

[Jon Lewis]

The following should work, please post the result

HTH


Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long
Dim varBookmark As Variant

lngRcdNum = Me.CurrentRecord


'****Instead of your For Next loop, if you just want rcdProdEntry to go to
the same record as Me.CurrentRecord
'****check out the FindFirst method of the recordset object in Help.

<rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X>

varBookmark = rcdProdEntry.Bookmark ' this must be set here, after
rcdProdEntry is on the correct record

Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

Me.Bookmark = varBookmark

'Don't forget:
rcdProdEntry.Close
Set rcdProdEntry = Nothing

Uhmmm... I might not be doing it right, but my form is based on a query
linking multiple tables. The recordset is based on this query.

The For... Next brings me to the selected record when I change the
cboActivity combo box so that I can delete the content of some text boxes
on
that record using the recordset. The problem is that it brings me to the
first record when I do the requesry, when I would like it to stay on the
current record.

I have tried your suggestion but it brought me no results.

I'm not sure what your For ..Next loop is achieving or if you're working
with your form's recordset or rcdProdEntry but you say all's working OK.
If, after Me.Requery, you want to go to the same record on your form
that
you were on before the requery then:

varBookmark = Me.RecordsetClone.Bookmark
'Do something
Me.Requery
Me.Bookmark = varBookmark

BTW you can Dim varBookmark As Long (it takes up less system resource)

HTH

Hi have added code for the bookmark, but it still won't set the focus
on
that
specific record. I have included the code below.... Have I done
anything
wrong?

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long
Dim varBookmark As Variant

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord
varBookmark = rcdProdEntry.Bookmark

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X

Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

rcdProdEntry.Bookmark = varBookmark

End Sub

Thanks!


:

Check out the Bookmark property of a recordset in Access Help.

HTH

Hi,

I have a continuous form in which I have, amongst others, a combo
box
(cboActivity) and a text box (txtActivityComments). The cboActivity
combo
box
lets the user select a production activity. Some of these activities
requires
the user to enter comments in the txtActivityComments text box.

After comments have been entered in txtActivityComments, the user
still
has
the possibility to change the chosen activity. If this is done, the
txtActivityComments text box is disabled by the use of a conditional
formating and it's content is cleared witn an AfterUpdate event on
the
cboActivity combo box.

After messing around quite a lot with the code (being a newbie with
VBA,
especially with recordsets), I have finally got it to work as I
wanted,
except from a little problem. When the code runs, after remove the
comments
from the txtActivityComments text box, it sets the focus on the
first
record
of the continuous form, even though the modified record was the
fifth
one.

How would I adapt my code so that the focus stays on the record that
has
been modified? I have a clue that it moves to the first record
because
of
the
requery method but without this, the txtActivityComments text box
doesn't
get
cleared properly.

Here's my code below...

Private Sub cboActivity_AfterUpdate()

Dim dbMyDB As DAO.Database
Dim rcdProdEntry As DAO.Recordset
Dim lngRcdNum As Long
Dim X As Long

Set dbMyDB = CurrentDb
Set rcdProdEntry = dbMyDB.OpenRecordset("qryProductivity")

lngRcdNum = Me.CurrentRecord

rcdProdEntry.MoveFirst
For X = 1 To lngRcdNum - 1
rcdProdEntry.MoveNext
Next X
Me.Requery

If rcdProdEntry![ActComments] = -1 Then
rcdProdEntry.Edit
rcdProdEntry![Comments] = Null
rcdProdEntry.Update
End If

End Sub

Thanks for any help you can provide me with!