Select & modify records using check boxes

[NZArchie]

Hi everyone,

I'm having a problem with a subform I have which shows payments for a particular supplier. These are automatically imported from the supplier, and sometimes (about 10%) they have the wrong classification (the field paymentType in my Payment table).

Is there a way I can have a check box on each detail record, with a corresponding drop down box and button to change each checked record's paymentType?

Thanks for any help,

Arch

 

[RainLover]

If you want to tag each record that is wrong you can do so and then run an Update Query later to change all of them.

You just need to realise that this will require an extra Field in the Table.

Also the Tag needs to be removed after the update. If not it will be part of every update in the future.

As an alternative you could write code that will change the value of the text box with a Double Click.

 

[NZArchie]

Thanks for the replies. Yeah I have a drop down on each record, but this functionality is for changing say 15 at a time.

What is the VBA to reference and loop through detail sections on a form?

 

[RainLover]

There is a "Tag" property for all Controls.

You could loop through all the Controls that have a Tag of "AnyString"

 

[NZArchie]

Thanks again, the classifications will be valid but incorrect. The user will select the new classification from a dropdown box on the main form (list of payments being in a subform), then click the 'change classification' button.

So in summary, I should have this as a button calling code along the lines of:

DoCmd.RunQuery "changeClass"
DoCmd.RunQuery "resetField"

? Will try this later today

 

[NZArchie]

If I can't do this through an update query, how do I reference the detail sections of the form so that I can loop through them? eg:

for each ........(something to reference a detail record)
if cboSelected.Value = "True"
    lstType.SelectedValue = lstSelectedValue.Value
endif
next

 

[NZArchie]

Thanks for the tip Pat but I do want to update the records. If probably won't be all of the records needing changing, but possibly 5 out of 7.

The difference comes about because the supplier whose statements I parse for this has a slightly different classification system than I want to use.

 

[NZArchie]

If the change has to be decided a record at a time, it should be done by changing the value of the combo.

Ok thanks again, however I still need to know how to refer to the specific records (and therefore comboboxes) represented in the detail section of the form.

My looping idea is currently through SQL, similar to the form recordsource, updating the selected field of all subrecords in THIS form. Then the idea is that the user can deselect the irrelevant ones, and the next SQL will update the actual field.

you need criteria that will identify specific rows to update.

That is the idea of the check boxes. I have placed a "selected" field in each record so that I can use SQL to update the property. However if I knew how to refer to each line/record in the form I could have unbound checkboxes, and no need for the field.

The change is not consistent, nor logically dependent on any consistent factor. It really requires human input. Most (99%) of the records don't have this anomaly, but the ones that do, will have it multiple times (70% of subrecords) on this particular form.