Last checkbox doesnt want to party

[savedelhi]

Hi,

I'm a relative newby and have a tricky problem in Access 2002.

I have a continuous form with a check box called "Select". (I now know
I shouldn't have called it that but it's way too late to change now).

I've set up a command button to changes the check box from Yes to No
with the following code:-

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Dirty = False

This manages to change all the check boxes EXCEPT one - it's invariably
the last one that I had previously clicked on. This last one only
becomes cleared when I close and re-open the form. Having scoured the
NG for solutions I saw something similar and hence put in the
Me.Dirty=False bit but that doesn't seem to work.

The open/close routine is irritating to all the users, as when it
re-opens, the form jumps to the first records. We've got something like
2600 records and the pressure is on to find a fix.

Can any one help? Thanks

Caroline

[Allen Browne]

1. Save the current record *before* executing the update query statement
(i.e. put the Me.Dirty line first.)

2. Use dbFailOnError in the execute line, so you get to hear about anything
that does not update properly, i.e.:
db.Execute "UPDATE...", dbfailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sa*******@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Hi,

I'm a relative newby and have a tricky problem in Access 2002.

I have a continuous form with a check box called "Select". (I now know
I shouldn't have called it that but it's way too late to change now).

I've set up a command button to changes the check box from Yes to No
with the following code:-

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Dirty = False

This manages to change all the check boxes EXCEPT one - it's invariably
the last one that I had previously clicked on. This last one only
becomes cleared when I close and re-open the form. Having scoured the
NG for solutions I saw something similar and hence put in the
Me.Dirty=False bit but that doesn't seem to work.

The open/close routine is irritating to all the users, as when it
re-opens, the form jumps to the first records. We've got something like
2600 records and the pressure is on to find a fix.

Can any one help? Thanks

Caroline

[Bas Cost Budde]

> I'm a relative newby and have a tricky problem in Access 2002.

I have a continuous form with a check box called "Select". (I now know
I shouldn't have called it that but it's way too late to change now).

I've set up a command button to changes the check box from Yes to No
with the following code:-

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Dirty = False

This manages to change all the check boxes EXCEPT one - it's invariably
the last one that I had previously clicked on.

The record is locked by the form and will not be updated from the query.
You are saving it (and so clearing the lock) *after* you try the update
query. Move the line one position up and you will be on air.

You can do

db.execute SQLstring, dbfailonerror

to get a trappable error message (in this case: not all records can be
updated--I hope :-) but in your case I think it is sufficient to save
the record from code.

runcommand accmdsaverecord

will do too, just before the db.execute
I still hesitate to suggest dirty=false because it doesn't look to me
like saving. Consider that a personal remark, if it works, why bother?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[savedelhi]

Good on you Allen, that's made my day! And thanks for the extra tip
too.

All this progress is going to my head and prompts me to ask if there's
an easy way to get such a command to report back the number of records
that are modified.

I could really usefully use such info on some of the other checkboxes
that are on the same continuous form.

Many thanks

Caroline

Allen Browne wrote:

1. Save the current record *before* executing the update query statement
(i.e. put the Me.Dirty line first.)

2. Use dbFailOnError in the execute line, so you get to hear about anything
that does not update properly, i.e.:
db.Execute "UPDATE...", dbfailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sa*******@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Hi,

I'm a relative newby and have a tricky problem in Access 2002.

I have a continuous form with a check box called "Select". (I now know
I shouldn't have called it that but it's way too late to change now).

I've set up a command button to changes the check box from Yes to No
with the following code:-

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Dirty = False

This manages to change all the check boxes EXCEPT one - it's invariably
the last one that I had previously clicked on. This last one only
becomes cleared when I close and re-open the form. Having scoured the
NG for solutions I saw something similar and hence put in the
Me.Dirty=False bit but that doesn't seem to work.

The open/close routine is irritating to all the users, as when it
re-opens, the form jumps to the first records. We've got something like
2600 records and the pressure is on to find a fix.

Can any one help? Thanks

Caroline

[Allen Browne]

Sure. Examine the RecordsAffected property of the database variable,i.e.:
MsgBox db.RecordsAffected

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sa*******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Good on you Allen, that's made my day! And thanks for the extra tip
too.

All this progress is going to my head and prompts me to ask if there's
an easy way to get such a command to report back the number of records
that are modified.

I could really usefully use such info on some of the other checkboxes
that are on the same continuous form.

Many thanks

Caroline

Allen Browne wrote:

1. Save the current record *before* executing the update query statement
(i.e. put the Me.Dirty line first.)

2. Use dbFailOnError in the execute line, so you get to hear about
anything
that does not update properly, i.e.:
db.Execute "UPDATE...", dbfailOnError
<sa*******@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

> Hi,
>
> I'm a relative newby and have a tricky problem in Access 2002.
>
> I have a continuous form with a check box called "Select". (I now know
> I shouldn't have called it that but it's way too late to change now).
>
> I've set up a command button to changes the check box from Yes to No
> with the following code:-
>
> Set db = CurrentDb
> db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
> Me.Dirty = False
>
> This manages to change all the check boxes EXCEPT one - it's invariably
> the last one that I had previously clicked on. This last one only
> becomes cleared when I close and re-open the form. Having scoured the
> NG for solutions I saw something similar and hence put in the
> Me.Dirty=False bit but that doesn't seem to work.
>
> The open/close routine is irritating to all the users, as when it
> re-opens, the form jumps to the first records. We've got something like
> 2600 records and the pressure is on to find a fix.
>
> Can any one help? Thanks
>
> Caroline

[SilvrT]

why not just requery the form after your code.

ie..

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Requery

[Bas Cost Budde]

Because requery certainly resets the current record to the first in the
set. That is not what she wanted.

SilvrT wrote:

why not just requery the form after your code.

ie..

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Requery

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[John Mishefske]

Bas Cost Budde wrote:

Because requery certainly resets the current record to the first in the
set. That is not what she wanted.

SilvrT wrote:

why not just requery the form after your code.

ie..

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Requery

And a .Requery will clear any Bookmarks so you can't use that to navigate back to the
record. However you could save the unique record identifier and then .Seek or .Find back
to the appropriate record after the .Requery.

--
'---------------
'John Mishefske
'---------------

[SilvrT]

Regardless, essentially when you close the form and re open it you are
requerying the records anyway. Use some means to position or display
the records the way you want them such as an SQL select statement in
the Recordsource property utilizing an "order by" statement or
something else that will work for you. Personally, I didn't quite "get"
exactly where Caroline wanted to leave the record pointer at.

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Recordsource="select * from Contacts order by <whatever>"
Me.Requery

Also, you can store a Bookmark to a variable and get back to the record
that way without closing the form.
eg.

myVar = Me.Bookmark
Me.Requery
Me.Bookmark = myVar
Only tryin to be of assistance :-)

[John Mishefske]

SilvrT wrote:

Regardless, essentially when you close the form and re open it you are
requerying the records anyway. Use some means to position or display
the records the way you want them such as an SQL select statement in
the Recordsource property utilizing an "order by" statement or
something else that will work for you. Personally, I didn't quite "get"
exactly where Caroline wanted to leave the record pointer at.

Set db = CurrentDb
db.Execute "UPDATE DISTINCTROW Contacts SET [Select] = 0 "
Me.Recordsource="select * from Contacts order by <whatever>"
Me.Requery

Also, you can store a Bookmark to a variable and get back to the record
that way without closing the form.
eg.

myVar = Me.Bookmark
Me.Requery
Me.Bookmark = myVar
Only tryin to be of assistance :-)

I don't think that will work. A .Requery clears bookmarks. From the
on-line help on Bookmark Property:

"Requerying a form invalidates any bookmarks set on records in the form. However, clicking
Refresh on the Records menu doesn't affect bookmarks."

I think the safest method is to store the unique identifier of the record and use that to
get back to the record after the .Requery.

--
'---------------
'John Mishefske
'---------------

[SilvrT]

worked for me... what can I say?

[SilvrT]

worked for me... what can I say?

[SilvrT]

But I do agree with the use of Refresh as well as using the unique
identifier.