Updating Records Through Subform Row Selection

[Simbiose]

Hello again.
I've hit another bump on my project, yet again regarding subforms.

I need to be able to select one or multiple rows/records from a subform (that will be displayed in datasheet view and then do something upon the selected rows/records (insert a new record, update value(s), etc).

The subform is named as dgvFeedStockArticles and its record source comes from a view named qryArtigosMateriaPrimaAEscolher and within the view's query, I've added an expression that gives a boolean result, named as Escolher.
This is the subform's design:

The very first control is the checkbox that will have the expression as the Source Control, or it would have, because if I do set the sourcecontrol property to that expression I get an error everytime I try to tick the checkbox:

• The field is based on an expression and cannot be edited

So, I tried a workaround, such as leaving the sourcecontrol property empty and then set a default value of -1 (which means the checkbox will be empty on loading the records)

The problem as it is now is that if I tick the checkbox, just for a single record, it automatically ticks the checkbox for all the other records.

How do I overcome this, in order to only tick the checkbox for the selected record?

[pbaldy]

The checkbox would need to be bound to a field in the underlying table. You might consider a listbox instead of a subform, which will let you select multiple records more easily.

[Simbiose]

The checkbox would need to be bound to a field in the underlying table. You might consider a listbox instead of a subform, which will let you select multiple records more easily.

Hello there Paul,
As soon as I created this thread, your suggestion was a thought that crossed my mind and it's the approach I decided to go with.
Although, for the sake of this thread and considering of how limited the control is in terms of formatting, such as column head coloring and column alignment, is there no way of achieving the same with a subform?

[Gicu]

The checkbox must be bound to allow for individual selection. If you add the boolean yes\no field to the back-end table you will have issues with it in a multiuser environment. The solution is to use a local table holding just two fields: the unique ID of the records in the subform (indexed) and a IsSelected yes\no field. Now join this table to the subforms recordsource on the unique ID and bind the checkbox to the IsSelected.

Cheers,

[Simbiose]

The checkbox must be bound to allow for individual selection. If you add the boolean yes\no field to the back-end table you will have issues with it in a multiuser environment. The solution is to use a local table holding just two fields: the unique ID of the records in the subform (indexed) and a IsSelected yes\no field. Now join this table to the subforms recordsource on the unique ID and bind the checkbox to the IsSelected.

Cheers,

Oh wow, that's actually really clever. I didn't think of that.
Thanks for the help. I know what to do now

Marking this thread as solved!

[Gicu]

Glad to help ! Just some further info to help you with the setup: usually in some event prior to opening the main form (in the button used to launch it) I would add code to empty the local table then run an append to populate it with the latest unique IDs.

Cheers,

[keviny04]

The checkbox must be bound to allow for individual selection. If you add the boolean yes\no field to the back-end table you will have issues with it in a multiuser environment. The solution is to use a local table holding just two fields: the unique ID of the records in the subform (indexed) and a IsSelected yes\no field. Now join this table to the subforms recordsource on the unique ID and bind the checkbox to the IsSelected.

Cheers,

I use this method quite often myself, but I don't add separate tables. I put a Yes/No field (or fields) right in the table itself. Adding a second table could be problematic, as you have to sync the two tables constantly.

By having a Yes/No field in the table itself, any tables and fields that are joined to that table in a query will also be able to use that Yes/No field. Of course, it's best to put this field in tables that are most likely to be the "many" side of a data set, so that you can apply Yes/No to as many unique records as possible. (Tables that don't have any child tables are always the "many" side.)

[pbaldy]

As Vlad pointed out, having that field in the table itself can be problematic in a multiuser environment. If two people are making selections at the same time, they will each get the other's selections as well as their own.

[keviny04]

As Vlad pointed out, having that field in the table itself can be problematic in a multiuser environment. If two people are making selections at the same time, they will each get the other's selections as well as their own.

That can be solved by having user-specific Yes/No fields. I even have "purpose-specific" Yes/No fields in my single-user tables too. Add as many Yes/No fields as you need because it only takes up one bit per record. You don't split tables every time you have multiple users, so you certainly don't have to split tables just for Yes/No fields.

[pbaldy]

So if you have 100 users you have 100 yes/no fields in the data table, in addition to the normal data fields? And every time they hire somebody you have to modify the db to add a field? And presumably delete one if somebody leaves?

[keviny04]

So if you have 100 users you have 100 yes/no fields in the data table, in addition to the normal data fields? And every time they hire somebody you have to modify the db to add a field? And presumably delete one if somebody leaves?

And what if the original table has 1 million records and your 100 users create 100 temporary million-record tables all over the place? You can make your absurdly unlikely hypothetical situation and I can make mine. We need the best solution at hand depending on the situation, and you need to consider all possibilities and know their pros and cons.

[pbaldy]

I'll let Vlad answer your hypothetical as I don't use either method, I use a listbox.

I'm not sure how absurd my hypothetical is, I've heard of many developers supporting that 100 or more users. I support quite a few apps but most don't have any more than 20 users. If your solution works for you, great. I personally don't want to have to be involved every time they hire/fire somebody.

From another perspective, your solution isn't normalized. It's not much different than having a field for every product sold.

I know you're sold on your method, and as you say you need to consider the pros and cons to make a decision. I'm pointing out the cons so later visitors can make an informed decision.

[keviny04]

I'll let Vlad answer your hypothetical as I don't use either method, I use a listbox.

I'm not sure how absurd my hypothetical is, I've heard of many developers supporting that 100 or more users. I support quite a few apps but most don't have any more than 20 users. If your solution works for you, great. I personally don't want to have to be involved every time they hire/fire somebody.

From another perspective, your solution isn't normalized. It's not much different than having a field for every product sold.

I know you're sold on your method, and as you say you need to consider the pros and cons to make a decision. I'm pointing out the cons so later visitors can make an informed decision.

The absurdity was in your suggestion that Yes/No fields need to be removed from the table when a user leaves the company. That's almost a troll-like absurdity. You can ROTATE Yes/No fields: when one user leaves, another user takes his Yes/No fields, problem solved! That was what I meant: you had to make up this absurd scenario to prove a point.

P.S. Multi-select list boxes are a pain to deal with, from my experience. When multiple selections are made, the list box looks like a ZEBRA, and, depending on the color theme used, you may not be able to tell which items are selected and which are not (screenshot). And the properties are cumbersome to deal with: ItemsSelected(), ItemData(), Selected(), etc. "Value List" and "Table/Query" row sources also have different properties, as do single-select and multi-select list boxes. I use list boxes only if I must.

[Gicu]

@keviny04 - you seem to like your way and dismiss all the rest; I am not here to debate which method is the best.

I wouldn't use yours for a million bucks (if we get to use millions so easily ) as it would mean I would have to constantly adjust my interface to add fields to queries, forms, VBA, etc. And the tables won't be "all over the place", just one per front-end that can be re-used for multiple back-end tables.

Cheers,

[CJ_London]

Just to throw another method into the mix I have used a disconnected ADO recordset in the past. No additional fields or tables required.

However my preferred method is to add selected ids to a hidden control on the form as a csv and use conditional formatting or similar to indicate selected rows. Benefit is I have a csv of the selected ids so don’t need to do anything more with the form recordset