Strange Subform Record Behavior
| Author |
Message |
|
WPW
#1 / 6
|
 Strange Subform Record Behavior Hello Everyone, Access 97
A strange thing just happened today to our Time and Activity database that
could be catastrophic if I can't correct it.
I've got a main form [frmTA] with [tblTA] as the underlying table with about
5,000 records. The subform [sfrmTADetails] has [qryTADetails] as the
underlying query. 20,000 records are in the table [tblTADetails].
On the subform, which is continuous, I've got a combo box
[cboSelectPosition] where the user selects the employee's position using the
key field [PositionID]. (We have many employees with more than one
position). When clicked, only that employee's ([EmployeeID in main form)
positions are listed.
Here's the problem: A subform record somehow got copied into the next
sequential subform. One theory is the user was in the main form record for
Jane Doe, selected her position, then clicked to the next record which was
for John Smith, clicked on the subform combo box, and the combo box didn't
requery so Jane Doe's positions were still showing from the previous record.
Could there be any other reason why this would happen? It might not be a
requerying problem because the hours entered were also identical which
according to the paper time sheet is not the case. How could other fields
be accidentally copied like that? I can't seem to find any other records
(yet) where this has happened.
If it is a requerying problem, on what events should I put requery to ensure
the combo box is always set to the current employee in the main form. Do I
use oncurrent, onactive, onload, etc. Or all of the above?? It seems to
have happened when the user clicked from one record to the next using the
record selector button. Or should I use refresh?
Sorry for the confusion, but obviously I'm confused about what's going on.
If I don't correct this our new time and activity database is in big
trouble!
Thanks for your help.
William
|
| Tue, 06 Jul 2004 09:11:48 GMT |
|
 |
|
Arvin Meye
#2 / 6
|
Strange Subform Record Behavior Make absolutely sure that all users are running Access 97 SR-2. There is a bug which was corrected in the second service release. It only shows up if a record is deleted and then another is looked up before requerying the combo's rowsource. The service releases are available at the Microsoft download center. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com
Quote: > Hello Everyone, > Access 97
> A strange thing just happened today to our Time and Activity database that
> could be catastrophic if I can't correct it.
> I've got a main form [frmTA] with [tblTA] as the underlying table with
about
> 5,000 records. The subform [sfrmTADetails] has [qryTADetails] as the
> underlying query. 20,000 records are in the table [tblTADetails].
> On the subform, which is continuous, I've got a combo box
> [cboSelectPosition] where the user selects the employee's position using
the
> key field [PositionID]. (We have many employees with more than one
> position). When clicked, only that employee's ([EmployeeID in main form)
> positions are listed.
> Here's the problem: A subform record somehow got copied into the next
> sequential subform. One theory is the user was in the main form record
for
> Jane Doe, selected her position, then clicked to the next record which was
> for John Smith, clicked on the subform combo box, and the combo box didn't
> requery so Jane Doe's positions were still showing from the previous
record.
> Could there be any other reason why this would happen? It might not be a
> requerying problem because the hours entered were also identical which
> according to the paper time sheet is not the case. How could other fields
> be accidentally copied like that? I can't seem to find any other records
> (yet) where this has happened.
> If it is a requerying problem, on what events should I put requery to
ensure
> the combo box is always set to the current employee in the main form. Do
I
> use oncurrent, onactive, onload, etc. Or all of the above?? It seems to
> have happened when the user clicked from one record to the next using the
> record selector button. Or should I use refresh?
> Sorry for the confusion, but obviously I'm confused about what's going on.
> If I don't correct this our new time and activity database is in big
> trouble!
|
| Tue, 06 Jul 2004 12:20:35 GMT |
|
|
|
WPW
#3 / 6
|
Strange Subform Record Behavior Thanks Arvin, I will check that. In the meantime, I noticed I don't have a requery for the subform combo box. I'm trying to put it in the OnCurrent event of the main form but am getting an invalid reference error. Here is the syntax I'm using: [Forms]![frmTA]![frmTADetails].Form![cboTitleCode].Requery
Is this correct? Also, just to make sure, what event should I place this in
to make sure the combo box is requerying?
Thanks,
William
Quote: > Make absolutely sure that all users are running Access 97 SR-2. There is a > bug which was corrected in the second service release. It only shows up if a > record is deleted and then another is looked up before requerying the > combo's rowsource. The service releases are available at the Microsoft > download center. > -- > Arvin Meyer, MCP, MVP > Microsoft Access > Free Access downloads: > http://www.datastrat.com
> > Hello Everyone,
> > Access 97
> > A strange thing just happened today to our Time and Activity database
that
> > could be catastrophic if I can't correct it.
> > I've got a main form [frmTA] with [tblTA] as the underlying table with
> about
> > 5,000 records. The subform [sfrmTADetails] has [qryTADetails] as the
> > underlying query. 20,000 records are in the table [tblTADetails].
> > On the subform, which is continuous, I've got a combo box
> > [cboSelectPosition] where the user selects the employee's position using
> the
> > key field [PositionID]. (We have many employees with more than one
> > position). When clicked, only that employee's ([EmployeeID in main
form)
> > positions are listed.
> > Here's the problem: A subform record somehow got copied into the next
> > sequential subform. One theory is the user was in the main form record
> for
> > Jane Doe, selected her position, then clicked to the next record which
was
> > for John Smith, clicked on the subform combo box, and the combo box
didn't
> > requery so Jane Doe's positions were still showing from the previous
> record.
> > Could there be any other reason why this would happen? It might not be
a
> > requerying problem because the hours entered were also identical which
> > according to the paper time sheet is not the case. How could other
fields
> > be accidentally copied like that? I can't seem to find any other
records
> > (yet) where this has happened.
> > If it is a requerying problem, on what events should I put requery to
> ensure
> > the combo box is always set to the current employee in the main form.
Do
> I
> > use oncurrent, onactive, onload, etc. Or all of the above?? It seems
to
> > have happened when the user clicked from one record to the next using
the
> > record selector button. Or should I use refresh?
> > Sorry for the confusion, but obviously I'm confused about what's going
on.
> > If I don't correct this our new time and activity database is in big
> > trouble!
|
| Tue, 06 Jul 2004 17:14:32 GMT |
|
|
|
Arvin Meye
#4 / 6
|
Strange Subform Record Behavior Your syntax is correct if "frmTADetails" is the *Name* of the subform control (which may be different than the name of the subform). The current event fires whenever the form changes records. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com
Quote: > Thanks Arvin, I will check that. In the meantime, I noticed I don't have a > requery for the subform combo box. I'm trying to put it in the OnCurrent > event of the main form but am getting an invalid reference error. Here is > the syntax I'm using: > [Forms]![frmTA]![frmTADetails].Form![cboTitleCode].Requery
> Is this correct? Also, just to make sure, what event should I place this
in
> to make sure the combo box is requerying?
> Thanks,
> William
> > Make absolutely sure that all users are running Access 97 SR-2. There is
a
> > bug which was corrected in the second service release. It only shows up
if
> a
> > record is deleted and then another is looked up before requerying the
> > combo's rowsource. The service releases are available at the Microsoft
> > download center.
> > --
> > Arvin Meyer, MCP, MVP
> > Microsoft Access
> > Free Access downloads:
> > http://www.datastrat.com
> > > Hello Everyone,
> > > Access 97
> > > A strange thing just happened today to our Time and Activity database
> that
> > > could be catastrophic if I can't correct it.
> > > I've got a main form [frmTA] with [tblTA] as the underlying table with
> > about
> > > 5,000 records. The subform [sfrmTADetails] has [qryTADetails] as the
> > > underlying query. 20,000 records are in the table [tblTADetails].
> > > On the subform, which is continuous, I've got a combo box
> > > [cboSelectPosition] where the user selects the employee's position
using
> > the
> > > key field [PositionID]. (We have many employees with more than one
> > > position). When clicked, only that employee's ([EmployeeID in main
> form)
> > > positions are listed.
> > > Here's the problem: A subform record somehow got copied into the next
> > > sequential subform. One theory is the user was in the main form
record
> > for
> > > Jane Doe, selected her position, then clicked to the next record which
> was
> > > for John Smith, clicked on the subform combo box, and the combo box
> didn't
> > > requery so Jane Doe's positions were still showing from the previous
> > record.
> > > Could there be any other reason why this would happen? It might not
be
> a
> > > requerying problem because the hours entered were also identical which
> > > according to the paper time sheet is not the case. How could other
> fields
> > > be accidentally copied like that? I can't seem to find any other
> records
> > > (yet) where this has happened.
> > > If it is a requerying problem, on what events should I put requery to
> > ensure
> > > the combo box is always set to the current employee in the main form.
> Do
> > I
> > > use oncurrent, onactive, onload, etc. Or all of the above?? It seems
> to
> > > have happened when the user clicked from one record to the next using
> the
> > > record selector button. Or should I use refresh?
> > > Sorry for the confusion, but obviously I'm confused about what's going
> on.
> > > If I don't correct this our new time and activity database is in big
> > > trouble!
|
| Tue, 06 Jul 2004 20:24:50 GMT |
|
|
|
Willia
#5 / 6
|
Strange Subform Record Behavior Arvin, I think I'm doing this wrong. The name of my subform control is
[cboTitleCode]. The name of my subform is [frmTADetails]. What should it
look like?
Thanks Again,
William
Quote: > Your syntax is correct if "frmTADetails" is the *Name* of the subform > control (which may be different than the name of the subform). The current > event fires whenever the form changes records. > -- > Arvin Meyer, MCP, MVP > Microsoft Access > Free Access downloads: > http://www.datastrat.com
> > Thanks Arvin, I will check that. In the meantime, I noticed I don't
have
> a
> > requery for the subform combo box. I'm trying to put it in the
OnCurrent
> > event of the main form but am getting an invalid reference error. Here
is
> > the syntax I'm using:
> > [Forms]![frmTA]![frmTADetails].Form![cboTitleCode].Requery
> > Is this correct? Also, just to make sure, what event should I place
this
> in
> > to make sure the combo box is requerying?
> > Thanks,
> > William
> > > Make absolutely sure that all users are running Access 97 SR-2. There
is
> a
> > > bug which was corrected in the second service release. It only shows
up
> if
> > a
> > > record is deleted and then another is looked up before requerying the
> > > combo's rowsource. The service releases are available at the Microsoft
> > > download center.
> > > --
> > > Arvin Meyer, MCP, MVP
> > > Microsoft Access
> > > Free Access downloads:
> > > http://www.datastrat.com
> > > > Hello Everyone,
> > > > Access 97
> > > > A strange thing just happened today to our Time and Activity
database
> > that
> > > > could be catastrophic if I can't correct it.
> > > > I've got a main form [frmTA] with [tblTA] as the underlying table
with
> > > about
> > > > 5,000 records. The subform [sfrmTADetails] has [qryTADetails] as
the
> > > > underlying query. 20,000 records are in the table [tblTADetails].
> > > > On the subform, which is continuous, I've got a combo box
> > > > [cboSelectPosition] where the user selects the employee's position
> using
> > > the
> > > > key field [PositionID]. (We have many employees with more than one
> > > > position). When clicked, only that employee's ([EmployeeID in main
> > form)
> > > > positions are listed.
> > > > Here's the problem: A subform record somehow got copied into the
next
> > > > sequential subform. One theory is the user was in the main form
> record
> > > for
> > > > Jane Doe, selected her position, then clicked to the next record
which
> > was
> > > > for John Smith, clicked on the subform combo box, and the combo box
> > didn't
> > > > requery so Jane Doe's positions were still showing from the previous
> > > record.
> > > > Could there be any other reason why this would happen? It might not
> be
> > a
> > > > requerying problem because the hours entered were also identical
which
> > > > according to the paper time sheet is not the case. How could other
> > fields
> > > > be accidentally copied like that? I can't seem to find any other
> > records
> > > > (yet) where this has happened.
> > > > If it is a requerying problem, on what events should I put requery
to
> > > ensure
> > > > the combo box is always set to the current employee in the main
form.
> > Do
> > > I
> > > > use oncurrent, onactive, onload, etc. Or all of the above?? It
seems
> > to
> > > > have happened when the user clicked from one record to the next
using
> > the
> > > > record selector button. Or should I use refresh?
> > > > Sorry for the confusion, but obviously I'm confused about what's
going
> > on.
> > > > If I don't correct this our new time and activity database is in big
> > > > trouble!
|
| Wed, 07 Jul 2004 00:12:08 GMT |
|
|
|
Willia
#6 / 6
|
Strange Subform Record Behavior Hello Again, OK, I think I identified the problem. The syntax is right. But there is a
conflict with something I have in the OnOpen event. There is a Go to Last
Record command in the OnOpen event of the form. I believe that may have
been conflicting. Ideally, I would still like it to go to the last record.
How should I do this so that that requery command in the OnCurrent event
doesn't conflict with the goto record command in the OnOpen event?
OnOpen Event
DoCmd.Maximize
DoCmd.GoToRecord acForm, "frmTA", acLast -- I'd like to keep this
DoCmd.GoToControl "Combo136" -- this is the first field on the form.
OnCurrent Event
Forms![frmTA]![frmTADetails]![cboTitleCode].Requery
I also have the same thing in:
Before Update Event
Forms![frmTA]![frmTADetails]![cboTitleCode].Requery
Any ideas?
Thanks,
William
Quote: > Arvin, > I think I'm doing this wrong. The name of my subform control is
> [cboTitleCode]. The name of my subform is [frmTADetails]. What should it
> look like?
> Thanks Again,
> William
> > Your syntax is correct if "frmTADetails" is the *Name* of the subform
> > control (which may be different than the name of the subform). The
current
> > event fires whenever the form changes records.
> > --
> > Arvin Meyer, MCP, MVP
> > Microsoft Access
> > Free Access downloads:
> > http://www.datastrat.com
> > > Thanks Arvin, I will check that. In the meantime, I noticed I don't
> have
> > a
> > > requery for the subform combo box. I'm trying to put it in the
> OnCurrent
> > > event of the main form but am getting an invalid reference error.
Here
> is
> > > the syntax I'm using:
> > > [Forms]![frmTA]![frmTADetails].Form![cboTitleCode].Requery
> > > Is this correct? Also, just to make sure, what event should I place
> this
> > in
> > > to make sure the combo box is requerying?
> > > Thanks,
> > > William
> > > > Make absolutely sure that all users are running Access 97 SR-2.
There
> is
> > a
> > > > bug which was corrected in the second service release. It only shows
> up
> > if
> > > a
> > > > record is deleted and then another is looked up before requerying
the
> > > > combo's rowsource. The service releases are available at the
Microsoft
> > > > download center.
> > > > --
> > > > Arvin Meyer, MCP, MVP
> > > > Microsoft Access
> > > > Free Access downloads:
> > > > http://www.datastrat.com
> > > > > Hello Everyone,
> > > > > Access 97
> > > > > A strange thing just happened today to our Time and Activity
> database
> > > that
> > > > > could be catastrophic if I can't correct it.
> > > > > I've got a main form [frmTA] with [tblTA] as the underlying table
> with
> > > > about
> > > > > 5,000 records. The subform [sfrmTADetails] has [qryTADetails] as
> the
> > > > > underlying query. 20,000 records are in the table [tblTADetails].
> > > > > On the subform, which is continuous, I've got a combo box
> > > > > [cboSelectPosition] where the user selects the employee's position
> > using
> > > > the
> > > > > key field [PositionID]. (We have many employees with more than
one
> > > > > position). When clicked, only that employee's ([EmployeeID in
main
> > > form)
> > > > > positions are listed.
> > > > > Here's the problem: A subform record somehow got copied into the
> next
> > > > > sequential subform. One theory is the user was in the main form
> > record
> > > > for
> > > > > Jane Doe, selected her position, then clicked to the next record
> which
> > > was
> > > > > for John Smith, clicked on the subform combo box, and the combo
box
> > > didn't
> > > > > requery so Jane Doe's positions were still showing from the
previous
> > > > record.
> > > > > Could there be any other reason why this would happen? It might
not
> > be
> > > a
> > > > > requerying problem because the hours entered were also identical
> which
> > > > > according to the paper time sheet is not the case. How could
other
> > > fields
> > > > > be accidentally copied like that? I can't seem to find any other
> > > records
> > > > > (yet) where this has happened.
> > > > > If it is a requerying problem, on what events should I put requery
> to
> > > > ensure
> > > > > the combo box is always set to the current employee in the main
> form.
> > > Do
> > > > I
> > > > > use oncurrent, onactive, onload, etc. Or all of the above?? It
> seems
> > > to
> > > > > have happened when the user clicked from one record to the next
> using
> > > the
> > > > > record selector button. Or should I use refresh?
> > > > > Sorry for the confusion, but obviously I'm confused about what's
> going
> > > on.
> > > > > If I don't correct this our new time and activity database is in
big
> > > > > trouble!
|
| Wed, 07 Jul 2004 00:58:42 GMT |
|
| |
|
