Check boxes Access 2002 FE SQL Server 2000 BE update problem

[aaj]

Hi all

I have a continuous bound form and on each record is a tick box.

The user ticks the boxes and these boxes define the batch. for future
operations

before they leave the page I count the number of ticks using (ADO)

rst.Open "SELECT COUNT(update_po_ref) AS update_qty FROM dbo.tbl_forecasted
GROUP BY update_po_ref HAVING (update_po_ref = 1)"

and notify the operator. This works OK.

So to improve things, in the footer of the form I but a text box, and on
each check box, use the 'after update' event to call the function where the
above code lives and then update the text box.

So in theory,
user clicks the checkbox
access bound form updates sql server backend
when data has been written the 'after update' event triggers
after update event runs the select and counts the number of ticks and
updates the screen

so finally on to the problem.... The count is always one behind the form. If
you click on a check box, the event fires ok ( I can use the break to trace
it), but even though its the after update event, when I run the select, the
database doesn't seem to have been updated.

I've tried doevents before running it and that didn't work. The only way it
works is if I use me.requery or me.refresh after the click but before the
select. This seems to force the write to the database The problem with this
is the list always defaults back to the top. I know I could remember which
record the last update was on and then find it again, but it seems an awful
long way of doing things

thanks in advance for any ideas

Andy

[Trevor Best]

aaj wrote:

Hi all

I have a continuous bound form and on each record is a tick box.

The user ticks the boxes and these boxes define the batch. for future
operations

before they leave the page I count the number of ticks using (ADO)

rst.Open "SELECT COUNT(update_po_ref) AS update_qty FROM dbo.tbl_forecasted
GROUP BY update_po_ref HAVING (update_po_ref = 1)"

and notify the operator. This works OK.

So to improve things, in the footer of the form I but a text box, and on
each check box, use the 'after update' event to call the function where the
above code lives and then update the text box.

So in theory,
user clicks the checkbox
access bound form updates sql server backend
when data has been written the 'after update' event triggers
after update event runs the select and counts the number of ticks and
updates the screen

so finally on to the problem.... The count is always one behind the form. If
you click on a check box, the event fires ok ( I can use the break to trace
it), but even though its the after update event, when I run the select, the
database doesn't seem to have been updated.

I've tried doevents before running it and that didn't work. The only way it
works is if I use me.requery or me.refresh after the click but before the
select. This seems to force the write to the database The problem with this
is the list always defaults back to the top. I know I could remember which
record the last update was on and then find it again, but it seems an awful
long way of doing things

The problem is that after you have ticked the box, the record is dirty,
i.e. not yet saved, therefore will not show up in any query based on the
underlying table.

You can force the record to save in the afterupdate event before
triggering your counting query.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[aaj]

your the man Trevor 8-)

the following did the trick

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
many thanks

Andy

"Trevor Best" <nospam@localhost> wrote in message
news:41***********************@auth.uk.news.easyne t.net...

aaj wrote:

Hi all

I have a continuous bound form and on each record is a tick box.

The user ticks the boxes and these boxes define the batch. for future
operations

before they leave the page I count the number of ticks using (ADO)

rst.Open "SELECT COUNT(update_po_ref) AS update_qty FROM dbo.tbl_forecasted GROUP BY update_po_ref HAVING (update_po_ref = 1)"

and notify the operator. This works OK.

So to improve things, in the footer of the form I but a text box, and on
each check box, use the 'after update' event to call the function where the above code lives and then update the text box.

So in theory,
user clicks the checkbox
access bound form updates sql server backend
when data has been written the 'after update' event triggers
after update event runs the select and counts the number of ticks and
updates the screen

so finally on to the problem.... The count is always one behind the form. If you click on a check box, the event fires ok ( I can use the break to trace it), but even though its the after update event, when I run the select, the database doesn't seem to have been updated.

I've tried doevents before running it and that didn't work. The only way it works is if I use me.requery or me.refresh after the click but before the select. This seems to force the write to the database The problem with this is the list always defaults back to the top. I know I could remember which record the last update was on and then find it again, but it seems an awful long way of doing things

The problem is that after you have ticked the box, the record is dirty,
i.e. not yet saved, therefore will not show up in any query based on the
underlying table.

You can force the record to save in the afterupdate event before
triggering your counting query.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\