Continuous form help

[Opal]

Can anyone tell me how to get my cursor or focus set to
the first record on my continuous subform after a requery?

It always remains on the last record I updated and I
need the focus on the first record after I requery.

 

[Marshall Barton]

Can anyone tell me how to get my cursor or focus set to
the first record on my continuous subform after a requery?

It always remains on the last record I updated and I
need the focus on the first record after I requery.

Requery automatically makes the first record in the form's
recordset the current record. If anything else happens,
there must be some code somewhere that is making some other
record current.

I suppose the behavior might appear to be different if the
subform's DateEntry property is set to Yes. But then Requery
will only display an empty new record.

 

[Opal]

This is puzzling. The subform was created to update
training activities for 9 production lines. The SQL
behind the subform is:

SELECT Training.TrainingID, Training.CourseName,
Training.TrainingLineID, Training.RatingID
FROM Training
WHERE (((Training.TrainingLineID)=[Forms]![frmVersatility]!
[TrainingLine]));

The user selects a combo box on the main form to open
the subform to the selected production or "TrainingLine".

The information input in the "RatingID" and "CourseName" is
written to another table via VBA. When the user selects
another production line, the cursor is in the place last selected.

As I need to "reset" the form after each training input, I use
the following:

Dim strQuery As String
strQuery = "UPDATE Training " & _
"SET RatingID = 5 " & _
"WHERE RatingID <> 5"


CurrentDb.Execute strQuery, dbFailOnError

(Where Rating ID "5" is no value)

But after this I requery the subform and it does not
return the cursor to the first record.

 

[Marshall Barton]

This is puzzling. The subform was created to update
training activities for 9 production lines. The SQL
behind the subform is:

SELECT Training.TrainingID, Training.CourseName,
Training.TrainingLineID, Training.RatingID
FROM Training
WHERE (((Training.TrainingLineID)=[Forms]![frmVersatility]!
[TrainingLine]));

The user selects a combo box on the main form to open
the subform to the selected production or "TrainingLine".

The information input in the "RatingID" and "CourseName" is
written to another table via VBA. When the user selects
another production line, the cursor is in the place last selected.

As I need to "reset" the form after each training input, I use
the following:

Dim strQuery As String
strQuery = "UPDATE Training " & _
"SET RatingID = 5 " & _
"WHERE RatingID <> 5"


CurrentDb.Execute strQuery, dbFailOnError

(Where Rating ID "5" is no value)

But after this I requery the subform and it does not
return the cursor to the first record.

I am really having a tough time trying to figure out what
you are describing. I am going to out most of the day again
today. I hope that in the meantime, maybe you can explain
more about the combo box's AfterUpdate event procedure and
why you are using the form reference in the subform's record
source instead of using the subform control's
Link<aster/Child properties. Maybe what you are calling a
subform is really a separate form??

The idea of updating the training table so that every record
has a ratingID of 5 also baffles me.

 

[Opal]

Sorry for baffling you.
I have a shop with over 120 employees and we
are tracking their training versatility across 9
production lines. Each production line has
anywhere from 12 - 15 "modules" that the employees
are tracked for their completion. Each level of completion
has a rating from 1 - 5. (I simply used 5 for no rating, 1
for 25% complete, 2 for 50% complete and so on)

I have a hidden subform that queries my Versatility
table to see if there is a record for the employee.
I am using the same mainform to update both new and
existing employees. If the employee has no record
one continouse subform is "visible", if there is a record,
another continuous subform is made visible.
When the user is adding a new employee
record, values for all 9 production lines are added individually.
When one line is added and the values written to the table,
the user will select the next line to update, but the cursor
or focus is on the last line in the continuous form that
was updated. I want the cursor to return to the first line
on the continuous form.

A little clearer?

 

[Marshall Barton]

I have a shop with over 120 employees and we
are tracking their training versatility across 9
production lines. Each production line has
anywhere from 12 - 15 "modules" that the employees
are tracked for their completion. Each level of completion
has a rating from 1 - 5. (I simply used 5 for no rating, 1
for 25% complete, 2 for 50% complete and so on)

I have a hidden subform that queries my Versatility
table to see if there is a record for the employee.
I am using the same mainform to update both new and
existing employees. If the employee has no record
one continouse subform is "visible", if there is a record,
another continuous subform is made visible.
When the user is adding a new employee
record, values for all 9 production lines are added individually.
When one line is added and the values written to the table,
the user will select the next line to update, but the cursor
or focus is on the last line in the continuous form that
was updated. I want the cursor to return to the first line
on the continuous form.

A little clearer?

No, that's not any clearer to me. I don't see where you
explained the things I wanted to know.

All I can conclude at this point is that you are either not
executing a Requery, the subform is not bound to a
query/table or the requery is for some other subform or the
main form.

 

[Opal]

Hmm....

Okay, both subforms are bound to tables:

subfrmTrainingNew:

SELECT Training.TrainingID, Training.CourseName,
Training.TrainingLineID, Training.RatingID
FROM Training WHERE (((Training.TrainingLineID)
=Forms!frmVersatility!TrainingLine));

and

subfrmTrainingUpt

SELECT Versatility.TMNumber, Versatility.TrainingID,
Versatility.RatingID, Versatility.GLLink,
Versatility.TrainingLineID, Versatility.LineDesignationID
FROM Versatility
WHERE (((Versatility.TMNumber)=[Forms]![frmVersatility]![TMNumber])
AND ((Versatility.TrainingLineID)=[Forms]![frmVersatility]!
[TrainingLine]));

When the user selects the training line on the main form,
the following is in the Private sub on the form:

Me.subfrmTrainingNew.Requery

or

Me.subfrmTrainingUpt.Requery

depending on which action is taken.

 

[Marshall Barton]

Okay, both subforms are bound to tables:

subfrmTrainingNew:

SELECT Training.TrainingID, Training.CourseName,
Training.TrainingLineID, Training.RatingID
FROM Training WHERE (((Training.TrainingLineID)
=Forms!frmVersatility!TrainingLine));

subfrmTrainingUpt:

SELECT Versatility.TMNumber, Versatility.TrainingID,
Versatility.RatingID, Versatility.GLLink,
Versatility.TrainingLineID, Versatility.LineDesignationID
FROM Versatility
WHERE (((Versatility.TMNumber)=[Forms]![frmVersatility]![TMNumber])
AND ((Versatility.TrainingLineID)=[Forms]![frmVersatility]!
[TrainingLine]));

When the user selects the training line on the main form,
the following is in the Private sub on the form:

Me.subfrmTrainingNew.Requery
or
Me.subfrmTrainingUpt.Requery

depending on which action is taken.

Actually, the subforms are bound to those queries, but I get
that part of the picture.

I really want to understand why you are using query criteria
and Requery to sync the subforms with the mainform training
line control. I would expect that to done through the
subform controls' LinkMaster/Child properties. It's not
that this is going to fix the problem, but it makes me
wonder what other complications I don't know about.

Technically, the Requery lines are incomplete. I don't know
if they are working as you intended or not (depends on
Access version), but they should be:

Me.subfrmTrainingNew.FORM.Requery
and
Me.subfrmTrainingUpt.FORM.Requery

 

[Opal]

Thanks, Marsh....

Turns out it was my requery method.