A tricky one?

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

Jim Franklin

Hi, I wonder if anyone can help me with this?

Using A2K, I have a continuous form, which is constantly requeried every 10
seconds using the timer event.

I would like the currently selected record to be clearly shown. I have
therefore a field [JOB_SELECTED] in my source table. This records the
currently selected record by using the following code in the forms OnCurrent
event:

If Me!JOB_SELECTED = False Then
DoCmd.OpenQuery "qry_Jobs_Reset_Selected", acViewNormal (update
query for all records on form)
Me.Refresh
Me!JOB_SELECTED = True
DoCmd.RunCommand acCmdSaveRecord
End If

I then use Conditional Formatting for each control to change colours when
JOB_SELECTED=true. This works fine.

However, when my form source data is requeried, I want the currently
selected record BEFORE the requery to remain selected afterwards (assuming
the record is still there.) My timer event code therefore is:

MyStr = Me!Ticket_Ref
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark

Without my OnCurrent event this also works fine. However, when I use the two
together, I get the following error: "Run Time Error 7878: The data has
been changed.", stopping at the Me!JOB_SELECTED = True line of my OnCurrent
event. This occurs the next time the Timer event fires after I have changed
the current record.

Hope this all makes sense!

If anyone has any suggestions, I would be very grateful. Thanks for reading,

Jim F.
 
Hi Paul,

Thanks for the solution. I have tried it, but it doesn't seem to do anything
my existing Timer event function wasn't. I'm probably being thick, but how
do I use this with conditional formatting to change the background colour of
controls in the currently selected record only?

As to your question, the final production version may requery every 30
seconds or so. It has to be done, as records are processed in multiple
stages each at separate terminals, in real time. The form will be constantly
open on one terminal, and therefore needs to constantly update to display
newly 'arrived' records fed from the previous stage.

If you know of a better way of doing it, please let me know,

Thanks,
Jim F.


Paul Overway said:
A better question...why are you requerying the recordset every 10 seconds?
Not too bright.

If you MUST do this (I can't imagine why):

1. MyStr must be module level variable
2. blnReturn must be a module level variable
2. Get rid of JOB_SELECTED...it will never work reliably ...especially in a
multi-user environment
3. In Timer event

MyStr = Me!Ticket_Ref
blnReturn = True
Me.Requery

4. In Current event

If blnReturn Then
blnReturn = False
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark
End If



Jim Franklin said:
Hi, I wonder if anyone can help me with this?

Using A2K, I have a continuous form, which is constantly requeried every 10
seconds using the timer event.

I would like the currently selected record to be clearly shown. I have
therefore a field [JOB_SELECTED] in my source table. This records the
currently selected record by using the following code in the forms OnCurrent
event:

If Me!JOB_SELECTED = False Then
DoCmd.OpenQuery "qry_Jobs_Reset_Selected", acViewNormal (update
query for all records on form)
Me.Refresh
Me!JOB_SELECTED = True
DoCmd.RunCommand acCmdSaveRecord
End If

I then use Conditional Formatting for each control to change colours when
JOB_SELECTED=true. This works fine.

However, when my form source data is requeried, I want the currently
selected record BEFORE the requery to remain selected afterwards (assuming
the record is still there.) My timer event code therefore is:

MyStr = Me!Ticket_Ref
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark

Without my OnCurrent event this also works fine. However, when I use the two
together, I get the following error: "Run Time Error 7878: The data has
been changed.", stopping at the Me!JOB_SELECTED = True line of my OnCurrent
event. This occurs the next time the Timer event fires after I have changed
the current record.

Hope this all makes sense!

If anyone has any suggestions, I would be very grateful. Thanks for reading,

Jim F.
Click to expand...
Click to expand...
 
Does it go to the record that was selected before the requery (it should)?.
So...that makes the record selected before requery the current record. The
condition is that the field has focus. Or if you want all controls,
Me!Ticket_Ref = Me!Ticket_Ref is the condition for each control. But note
that it really doesn't make any sense to do conditional formatting because
the currently selected record is ALWAYS current.

Note that this is going to cause flashing and will annoy the hell out of
your users. It is a bad idea to requery constantly like this...new records
or not. You'll be generating a lot of network traffic for nothing. A
marginally better solution would be to requery peridodically IF the form is
not dirty and there are new records in the underlying recordset. This will
prevent the requery from interrupting your user's edits and annoy them to a
lesser extent. You would need to write a function to check recordcount in
the table (recordsetclone will not work). Better yet....have a switchboard
type form with a listbox that gets requeried peridocially if new records are
present and open the form filtered on the list box selection. Close the
form after edits are completed.




Jim Franklin said:
Hi Paul,

Thanks for the solution. I have tried it, but it doesn't seem to do anything
my existing Timer event function wasn't. I'm probably being thick, but how
do I use this with conditional formatting to change the background colour of
controls in the currently selected record only?

As to your question, the final production version may requery every 30
seconds or so. It has to be done, as records are processed in multiple
stages each at separate terminals, in real time. The form will be constantly
open on one terminal, and therefore needs to constantly update to display
newly 'arrived' records fed from the previous stage.

If you know of a better way of doing it, please let me know,

Thanks,
Jim F.


Paul Overway said:
A better question...why are you requerying the recordset every 10 seconds?
Not too bright.

If you MUST do this (I can't imagine why):

1. MyStr must be module level variable
2. blnReturn must be a module level variable
2. Get rid of JOB_SELECTED...it will never work reliably ...especially
Click to expand...
in
a
multi-user environment
3. In Timer event

MyStr = Me!Ticket_Ref
blnReturn = True
Me.Requery

4. In Current event

If blnReturn Then
blnReturn = False
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark
End If



Jim Franklin said:
Hi, I wonder if anyone can help me with this?

Using A2K, I have a continuous form, which is constantly requeried
Click to expand...
every
10
seconds using the timer event.

I would like the currently selected record to be clearly shown. I have
therefore a field [JOB_SELECTED] in my source table. This records the
currently selected record by using the following code in the forms OnCurrent
event:

If Me!JOB_SELECTED = False Then
DoCmd.OpenQuery "qry_Jobs_Reset_Selected", acViewNormal (update
query for all records on form)
Me.Refresh
Me!JOB_SELECTED = True
DoCmd.RunCommand acCmdSaveRecord
End If

I then use Conditional Formatting for each control to change colours when
JOB_SELECTED=true. This works fine.

However, when my form source data is requeried, I want the currently
selected record BEFORE the requery to remain selected afterwards (assuming
the record is still there.) My timer event code therefore is:

MyStr = Me!Ticket_Ref
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark

Without my OnCurrent event this also works fine. However, when I use
Click to expand...
the
two
together, I get the following error: "Run Time Error 7878: The data has
been changed.", stopping at the Me!JOB_SELECTED = True line of my OnCurrent
event. This occurs the next time the Timer event fires after I have changed
the current record.

Hope this all makes sense!

If anyone has any suggestions, I would be very grateful. Thanks for reading,

Jim F.
Click to expand...
Click to expand...
Click to expand...
 
Hi Paul,

Thanks again. However, the Me!Ticket_Ref = Me!Ticket_Ref condition does not
work. This is not related to any control having the focus - I want the
colour of all controls to change when the record is selected, similar to how
it can on a datasheet when you select a row.

I saw a previous post on here stating you had to use a field value to get it
to do this.

If anyone can help, please and thank you!

Jim F.

Paul Overway said:
Does it go to the record that was selected before the requery (it should)?.
So...that makes the record selected before requery the current record. The
condition is that the field has focus. Or if you want all controls,
Me!Ticket_Ref = Me!Ticket_Ref is the condition for each control. But note
that it really doesn't make any sense to do conditional formatting because
the currently selected record is ALWAYS current.

Note that this is going to cause flashing and will annoy the hell out of
your users. It is a bad idea to requery constantly like this...new records
or not. You'll be generating a lot of network traffic for nothing. A
marginally better solution would be to requery peridodically IF the form is
not dirty and there are new records in the underlying recordset. This will
prevent the requery from interrupting your user's edits and annoy them to a
lesser extent. You would need to write a function to check recordcount in
the table (recordsetclone will not work). Better yet....have a switchboard
type form with a listbox that gets requeried peridocially if new records are
present and open the form filtered on the list box selection. Close the
form after edits are completed.




Jim Franklin said:
Hi Paul,

Thanks for the solution. I have tried it, but it doesn't seem to do anything
my existing Timer event function wasn't. I'm probably being thick, but how
do I use this with conditional formatting to change the background
Click to expand...
colour
of
controls in the currently selected record only?

As to your question, the final production version may requery every 30
seconds or so. It has to be done, as records are processed in multiple
stages each at separate terminals, in real time. The form will be constantly
open on one terminal, and therefore needs to constantly update to display
newly 'arrived' records fed from the previous stage.

If you know of a better way of doing it, please let me know,

Thanks,
Jim F.
Click to expand...
....especially
in
a
multi-user environment
3. In Timer event

MyStr = Me!Ticket_Ref
blnReturn = True
Me.Requery

4. In Current event

If blnReturn Then
blnReturn = False
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark
End If



Hi, I wonder if anyone can help me with this?

Using A2K, I have a continuous form, which is constantly requeried every
10
seconds using the timer event.

I would like the currently selected record to be clearly shown. I have
therefore a field [JOB_SELECTED] in my source table. This records the
currently selected record by using the following code in the forms
OnCurrent
event:

If Me!JOB_SELECTED = False Then
DoCmd.OpenQuery "qry_Jobs_Reset_Selected", acViewNormal
(update
query for all records on form)
Me.Refresh
Me!JOB_SELECTED = True
DoCmd.RunCommand acCmdSaveRecord
End If

I then use Conditional Formatting for each control to change colours when
JOB_SELECTED=true. This works fine.

However, when my form source data is requeried, I want the currently
selected record BEFORE the requery to remain selected afterwards (assuming
the record is still there.) My timer event code therefore is:

MyStr = Me!Ticket_Ref
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark

Without my OnCurrent event this also works fine. However, when I use the
two
together, I get the following error: "Run Time Error 7878: The data has
been changed.", stopping at the Me!JOB_SELECTED = True line of my
OnCurrent
event. This occurs the next time the Timer event fires after I have
changed
the current record.

Hope this all makes sense!

If anyone has any suggestions, I would be very grateful. Thanks for
reading,

Jim F.
Click to expand...
Click to expand...
Click to expand...
 
My bad...you do need a field. And using a field is not going to work.
Supposing you continued down the road you were on with JOB_Selected...if
user A has a record selected and user B has a different record selected,
both will be formatted....as JOB_Selected would be True for both records.

There is no escape from that fact that what you are trying to do is BAD
design, and redundant. There is a record selected indicator on the form, if
you are showing record selectors. Are your users really that dumb?


Jim Franklin said:
Hi Paul,

Thanks again. However, the Me!Ticket_Ref = Me!Ticket_Ref condition does not
work. This is not related to any control having the focus - I want the
colour of all controls to change when the record is selected, similar to how
it can on a datasheet when you select a row.

I saw a previous post on here stating you had to use a field value to get it
to do this.

If anyone can help, please and thank you!

Jim F.

Paul Overway said:
Does it go to the record that was selected before the requery (it should)?.
So...that makes the record selected before requery the current record. The
condition is that the field has focus. Or if you want all controls,
Me!Ticket_Ref = Me!Ticket_Ref is the condition for each control. But note
that it really doesn't make any sense to do conditional formatting because
the currently selected record is ALWAYS current.

Note that this is going to cause flashing and will annoy the hell out of
your users. It is a bad idea to requery constantly like this...new records
or not. You'll be generating a lot of network traffic for nothing. A
marginally better solution would be to requery peridodically IF the form is
not dirty and there are new records in the underlying recordset. This will
prevent the requery from interrupting your user's edits and annoy them
Click to expand...
to
a
lesser extent. You would need to write a function to check recordcount in
the table (recordsetclone will not work). Better yet....have a switchboard
type form with a listbox that gets requeried peridocially if new records are
present and open the form filtered on the list box selection. Close the
form after edits are completed.




Jim Franklin said:
Hi Paul,

Thanks for the solution. I have tried it, but it doesn't seem to do anything
my existing Timer event function wasn't. I'm probably being thick, but how
do I use this with conditional formatting to change the background
Click to expand...
colour
of
controls in the currently selected record only?

As to your question, the final production version may requery every 30
seconds or so. It has to be done, as records are processed in multiple
stages each at separate terminals, in real time. The form will be constantly
open on one terminal, and therefore needs to constantly update to display
newly 'arrived' records fed from the previous stage.

If you know of a better way of doing it, please let me know,

Thanks,
Jim F.


A better question...why are you requerying the recordset every 10 seconds?
Not too bright.

If you MUST do this (I can't imagine why):

1. MyStr must be module level variable
2. blnReturn must be a module level variable
2. Get rid of JOB_SELECTED...it will never work reliably
Click to expand...
...especially
in
a
multi-user environment
3. In Timer event

MyStr = Me!Ticket_Ref
blnReturn = True
Me.Requery

4. In Current event

If blnReturn Then
blnReturn = False
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark
End If



Hi, I wonder if anyone can help me with this?

Using A2K, I have a continuous form, which is constantly requeried every
10
seconds using the timer event.

I would like the currently selected record to be clearly shown. I have
therefore a field [JOB_SELECTED] in my source table. This records the
currently selected record by using the following code in the forms
OnCurrent
event:

If Me!JOB_SELECTED = False Then
DoCmd.OpenQuery "qry_Jobs_Reset_Selected", acViewNormal
(update
query for all records on form)
Me.Refresh
Me!JOB_SELECTED = True
DoCmd.RunCommand acCmdSaveRecord
End If

I then use Conditional Formatting for each control to change colours
when
JOB_SELECTED=true. This works fine.

However, when my form source data is requeried, I want the currently
selected record BEFORE the requery to remain selected afterwards
(assuming
the record is still there.) My timer event code therefore is:

MyStr = Me!Ticket_Ref
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark

Without my OnCurrent event this also works fine. However, when I
Click to expand...
use
the
two
together, I get the following error: "Run Time Error 7878: The
Click to expand...
data
has
been changed.", stopping at the Me!JOB_SELECTED = True line of my
OnCurrent
event. This occurs the next time the Timer event fires after I have
changed
the current record.

Hope this all makes sense!

If anyone has any suggestions, I would be very grateful. Thanks for
reading,

Jim F.
Click to expand...
Click to expand...
Click to expand...
 
Fraid so! :-) Its not exactly an office environment, so screen needs to be
very clear at a glance.

Thanks for trying anyway.

Jim


Paul Overway said:
My bad...you do need a field. And using a field is not going to work.
Supposing you continued down the road you were on with JOB_Selected...if
user A has a record selected and user B has a different record selected,
both will be formatted....as JOB_Selected would be True for both records.

There is no escape from that fact that what you are trying to do is BAD
design, and redundant. There is a record selected indicator on the form, if
you are showing record selectors. Are your users really that dumb?


Jim Franklin said:
Hi Paul,

Thanks again. However, the Me!Ticket_Ref = Me!Ticket_Ref condition does not
work. This is not related to any control having the focus - I want the
colour of all controls to change when the record is selected, similar to how
it can on a datasheet when you select a row.

I saw a previous post on here stating you had to use a field value to
Click to expand...
get
it
to do this.

If anyone can help, please and thank you!

Jim F.

form
is to
Click to expand...
recordcount
in
the table (recordsetclone will not work). Better yet....have a switchboard
type form with a listbox that gets requeried peridocially if new
Click to expand...
records
are
present and open the form filtered on the list box selection. Close the
form after edits are completed.




Hi Paul,

Thanks for the solution. I have tried it, but it doesn't seem to do
anything
my existing Timer event function wasn't. I'm probably being thick,
Click to expand...
but
how
do I use this with conditional formatting to change the background colour
of
controls in the currently selected record only?

As to your question, the final production version may requery every 30
seconds or so. It has to be done, as records are processed in multiple
stages each at separate terminals, in real time. The form will be
constantly
open on one terminal, and therefore needs to constantly update to display
newly 'arrived' records fed from the previous stage.

If you know of a better way of doing it, please let me know,

Thanks,
Jim F.


A better question...why are you requerying the recordset every 10
seconds?
Not too bright.

If you MUST do this (I can't imagine why):

1. MyStr must be module level variable
2. blnReturn must be a module level variable
2. Get rid of JOB_SELECTED...it will never work reliably ...especially
in
a
multi-user environment
3. In Timer event

MyStr = Me!Ticket_Ref
blnReturn = True
Me.Requery

4. In Current event

If blnReturn Then
blnReturn = False
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark
End If



Hi, I wonder if anyone can help me with this?

Using A2K, I have a continuous form, which is constantly requeried
every
10
seconds using the timer event.

I would like the currently selected record to be clearly shown.
Click to expand...
I
have
therefore a field [JOB_SELECTED] in my source table. This
Click to expand...
records
the
currently selected record by using the following code in the forms
OnCurrent
event:

If Me!JOB_SELECTED = False Then
DoCmd.OpenQuery "qry_Jobs_Reset_Selected", acViewNormal
(update
query for all records on form)
Me.Refresh
Me!JOB_SELECTED = True
DoCmd.RunCommand acCmdSaveRecord
End If

I then use Conditional Formatting for each control to change colours
when
JOB_SELECTED=true. This works fine.

However, when my form source data is requeried, I want the currently
selected record BEFORE the requery to remain selected afterwards
(assuming
the record is still there.) My timer event code therefore is:

MyStr = Me!Ticket_Ref
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Ticket_Ref] = '" & MyStr & "'"
Me.Bookmark = rs.Bookmark

Without my OnCurrent event this also works fine. However, when I use
the
two
together, I get the following error: "Run Time Error 7878: The data
has
been changed.", stopping at the Me!JOB_SELECTED = True line of my
OnCurrent
event. This occurs the next time the Timer event fires after I have
changed
the current record.

Hope this all makes sense!

If anyone has any suggestions, I would be very grateful. Thanks for
reading,

Jim F.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top