Selection in the form (1 Viewer)

[hfsitumo2001]

Hello, I add additional field for selection wth datatype: yes or no, default is no. When I see certain item, I tick mark if I want this to print. I chose this one by one. My question is if I want to clear the tick mark at once. How can I make it.

Thank you

 

[theDBguy]

You can use an UPDATE query.

 

[hfsitumo2001]

You can use an UPDATE query.

Can I want it to just press the button>

 

[theDBguy]

Can I want it to just press the button>

Yes. Something like:

CurrentDb.Execute "UPDATE TableName SET FieldName=0", dbFailOnError

 

[hfsitumo2001]

Yes. Something like:

CurrentDb.Execute "UPDATE TableName SET FieldName=0", dbFailOnError

The query itself in sql view is this:
UPDATE Inventory SET Inventory.Selection = False;
if I double click it in the navigation pane, it will work, but instead of doing that, I want it to be pressing a button/like command button. But what is the on click property of the button.
is it

CurrentDb.Execute "UPDATE Inventory SET Inventory.Selection = False;", dbFailOnError

 

[Pat Hartman]

theDBGuy gave you an example that showed embedded SQL. The execute method can execute a querydef if you prefer.

BUT, think very hard about actually implementing this. If you are flagging the actual records, what happens if two people are flagging a different set of records? Their data will get mixed and when one person runs the update query to reset the select fields, he will also be resetting the select marks for the other person. And even using UserID rather than a Y/N field doesn't solve the problem because it won't allow two people to select the same record.

There are two better ways to do this and it depends on how long you need the marks to persist. If this is a short process, you can use a multi-select listbox. The user can select some number of records and do somethin with the selected set. The set does not persist. It is gone when the form is closed.

If you want the selection to persist for even a short time, you need to do it in a temp table. The temp table should probably be in a temp database on each user's PC so there are no conflicts. The selection can persist as long as the user wants it to. When the task is complete, he can delete the rows from the temp table.

Tell us which of the two options will work for you and we can go into details of how to implement it.

 

[hfsitumo2001]

theDBGuy gave you an example that showed embedded SQL. The execute method can execute a querydef if you prefer.

BUT, think very hard about actually implementing this. If you are flagging the actual records, what happens if two people are flagging a different set of records? Their data will get mixed and when one person runs the update query to reset the select fields, he will also be resetting the select marks for the other person. And even using UserID rather than a Y/N field doesn't solve the problem because it won't allow two people to select the same record.

There are two better ways to do this and it depends on how long you need the marks to persist. If this is a short process, you can use a multi-select listbox. The user can select some number of records and do somethin with the selected set. The set does not persist. It is gone when the form is closed.

If you want the selection to persist for even a short time, you need to do it in a temp table. The temp table should probably be in a temp database on each user's PC so there are no conflicts. The selection can persist as long as the user wants it to. When the task is complete, he can delete the rows from the temp table.

Tell us which of the two options will work for you and we can go into details of how to implement it.

For me I just make on click on button like this to clear selection at once: DoCmd.OpenQuery "UpdateSelectiontoFalse". For us confilct between the users is not a big problem, because we have only 4 users and sit very close one each other, so they can just shout to say not to use it temporarily.

Thank you

 

[Pat Hartman]

For us confilct between the users is not a big problem, because we have only 4 users and sit very close one each other, so they can just shout to say not to use it temporarily.

There really is no excuse for a poor design choice once you know it is poor. You are assuming the user count will always remain small and they will always be in close proximity.

 

[hfsitumo2001]

There really is no excuse for a poor design choice once you know it is poor. You are assuming the user count will always remain small and they will always be in close proximity.

I do not know how to use a temporary table and it will work, can you teach me?

 

[Pat Hartman]

Create a database with a table in it to hold the selections. It probably only needs recordID. and possibly date selected. save the database in a shared folder on the server. At the start of the selection process, copy the template database to the local drive. Link to the table in the db (you only need to do this once, the first time you use the procedure).

Take a look at this example. I'm going to be busy for a few hours. I'll check back when the bridge game ends to see if' you need more help or someone else might be able to step in.

 

[hfsitumo2001]

Create a database with a table in it to hold the selections. It probably only needs recordID. and possibly date selected. save the database in a shared folder on the server. At the start of the selection process, copy the template database to the local drive. Link to the table in the db (you only need to do this once, the first time you use the procedure).

Take a look at this example. I'm going to be busy for a few hours. I'll check back when the bridge game ends to see if' you need more help or someone else might be able to step in.

Thank you Pat, I will look into it