Parse through a recordset, running an update query as required (1 Viewer)

[Lyconal]

Hi Guys,

I have a continuous form that I want to parse.
I want to check each record visible in the continuous form (source is a query) and if a yes/no box is checked, then run an UPDATE query for that record, then go to the next record and do the same etc.

Any ideas on where to start?

Cheers

 

[namliam]

why run an update query per record?? Why not do an update query for all records that apply in one go?

 

[Lyconal]

Doesnt want to work for a continuous form, unless you know a way? (Need to update 3 fields to the values in unbound text boxes, if the yes/no field is true)

This is so that more than one record can be 'selected' to be included in a shipment, and therefore will inhert the shipment data from the unbound boxes above.

Cheers

 

[gemma-the-husky]

trust the mailman - here's a way to achieve this - easy for you to program, easy interface for your users

in your base table have a "selectitem" field yes/no type, so it can be a checkbox on your form

display this in the continuous form as a bound checkbox

you may want to clear the field first, or not - depends on your app

now click the "selectitem" checkbox for the ones you want to process

and finally have an update query which selects those items where "selectitem" is true

bulk queries are much easier to write, not slower, and less prone to programming error.

 

[Lyconal]

I have this at the moment, but it will only work on the currently selected record within the continuous form.

How do I make it work on the rest of the records, visible in the form?

 

[Lyconal]

Meh - Got it working

*Somebody* has put a 'WHERE HistoryID=[forms]![form]![HistoryID]' like a numpty.

Cheers for the help guys

 

[namliam]

*Somebody*

Sorry my bad, shouldnt have put it there

 

[LPurvis]

I've mentioned as much before and I feel compelled to do so again ;-)

I don't hold to the "selection field in the base table" approach at all.
It offers next to no support for multi-user applications. (i.e. you can easily experience contention issues for the same set of data).

Everyone got involved back here for example.
http://www.access-programmers.co.uk/forums/showthread.php?t=165520

Just be clear, it's not a copy of the core table that I advocate in the local FE, but an empty table which stores only the PK values of the selected rows.
Very simple, next to no code. (No more so than the local copy table anyway).

Cheers.