Do While Loop (1 Viewer)

[Pete490h]

Hi,
Wonder if someone can help me with a vba Do While Loop.....
Even though I’m quite experienced in Access, I’ve not done anything like this
I’ve attached a screenshot of a form which has variable results
I need some code that will change the WOClockStatus field from TRUE to FALSE
Presume the code could be added to an Event Procedure that runs on Form Open

many thanks
Pete

 

[theDBguy]

Hi Pete. Not sure I follow what you want to happen here. If you're saying you want to change all the records' values based on what you see on the form, then you should be able to use an UPDATE query and not need a Do While Loop.

 

[Minty]

Presuming your form uses a query to populate, I would simply create an Update query based on that, and run it from a command button if you wanted to see the results?

 

[The_Doc_Man]

Pete, you got the answers you did because your question was ambiguous.

IF you were thinking about changing all records using a recordset and a DO WHILE loop, you certain CAN do that, but it is usually done that way if and ONLY if you have some horrendously complex logic that doesn't translate to SQL easily.

Using an UPDATE query with a reasonable WHERE clause as your means of record selection is ALWAYS more efficient if you can define the selection criteria reasonably well. This is because using VBA loops means you are using a semi-compiled language that is then emulated by software. The UPDATE query, however, is based on compiled code. If you have only a handful of records, it is a tossup as to which might be faster. Once you get past that handful, the query wins the speed race every time.

 

[Pete490h]

Many thanks for these answers
Never considered an UPDATE, makes so much sense, sometimes the obvious is miles away

I’ll try tomorrow and let you know

 

[theDBguy]

Many thanks for these answers
Never considered an UPDATE, makes so much sense, sometimes the obvious is miles away

I’ll try tomorrow and let you know

Cheers! Let us know how it goes...

 

[Pat Hartman]

I need some code that will change the WOClockStatus field from TRUE to FALSE

An update query would be most appropriate but I think that there is a basic logic problem with what you are asking to do. Will all records be updated? That makes no sense at all. What is the criteria for changing the status from true to false? If the data used to make that decision is in the record already then the status field is redundant. You would simply use selection criteria to return records whose implied status equates to true or false.

 

[Pete490h]

Cheers! Let us know how it g

An update query would be most appropriate but I think that there is a basic logic problem with what you are asking to do. Will all records be updated? That makes no sense at all. What is the criteria for changing the status from true to false? If the data used to make that decision is in the record already then the status field is redundant. You would simply use selection criteria to return records whose implied status equates to true or false.

Hi Pat,
I’m not good in describing criteria’s and in this instance, the status field is simply text, I should have used other words as true & false have other uses
I’ve managed to solve my issue with the update query thanks, I now move on to next stage
I’m sure I’ll have more questions later

 

[Pat Hartman]

I’ve managed to solve my issue with the update query thanks,

Doesn't sound like it to me but as long as you're happy.

 

[Isaac]

I’m sure I’ll have more questions later

When it comes to the relationship between the quality of a question and the quality of answers, "You get what you pay for".
Just something to keep in mind.