Looping through records

[Stewart Graefner]

Here is a chunk of code that works for an individual record. It
evaluates dates and checks or unchecks boxes as it goes along. It may
not be pretty but it works. What my problem is that I need it to
evaluate all the records(200+) in my db and change those which need
changing. Having to do it individually would defeat the purpose of
developing this code. What I would like to be able to do is either 1.
Open the db push a button and all the records update Or 2. Have it
update automatically each time the db opens. It really only needs to be
done once a day so I'm leaning towards #1. So I figure I need to be able
to loop this code through all the records until the last record then
stop. So heres the rub, How do I do that? Should this code be put in a
Module? My db opens up to a FORM called "TITLE PAGE" the records which
require updating are manipulated through a Second FORM called "MASTER
PAGE" I would like to be able to update the records at the TITLE PAGE.
It currently works with the push of a button on the MASTER PAGE but only
updates one record at at time, push the button it updates, manually
select new the record, push the button it updates, manually select new
the record... This is not how it should work. I would like it to work
from the TITLE PAGE Push the button updates all the records and I'm
done.

Private Sub test2_Click()

Dim db1 As Database
Dim rst As Recordset
Set db1 = DBEngine(0)(0)
Set rst = db1.OpenRecords et("Pers_Landed ", dbOpenTable)
Set rst = db1.OpenRecords et("Pers_Postin g", dbOpenTable)
Set rst = db1.OpenRecords et("Pers_Medica l", dbOpenTable)
Set rst = db1.OpenRecords et("Pers_Other_ Data", dbOpenTable)
Do While rst.BOF = False And rst.EOF = False
If (RFD) <= DATE Then
SAILING = True
End If

If (COURSE_OUT) <= DATE Then
COURSE = True
SAILING = False
End If
If (COURSE_IN) <= DATE Then
COURSE = False
SAILING = True
End If

If (COURSE) = True Then
LCA = False
SAILING = False
End If

If (LANDED_OUT) <= DATE Then
LCA = True
SAILING = False
End If
If (LANDED_IN) <= DATE Then
LCA = False
SAILING = True
End If

If (LCA) = False And _
COURSE = True Then
SAILING = False
End If

If (Start_Leave) <= DATE Then
CRS = True
SAILING = False
End If
If (Stop_Leave) <= DATE Then
CRS = False
SAILING = True
End If

If (START_DATE) <= DATE Then
MEDICAL = True
End If
If (STOP_DATE) <= DATE Then
MEDICAL = False
End If

If (COS_OUT_DATE) <= DATE Then
P_IN = False
ATP = False
SAILING = False
P_OUT = True
End If
rst.MoveNext
Loop

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

you have to open the recordset that underlies the table you're looking
at... something along the lines of...

Sub UpdateMyRecords ()
dim rs as dao.recordset
set rs=dbengine(0)( 0).OpenRecordse t("SELECT...FRO M...WHERE...",
dbOpenDynamic)
do until rs.EOF
rs.Edit '---allow editing
'---drop your record-by-record processing code here
'---do your edits here
rs.Update '---save the changes
rs.MoveNext
loop
rs.Close
Set rs=nothing
End Sub

[Stewart Graefner]

Ok, If I understood your reply below is what I've got. This is based on
my limited understanding of all things VB.
Along with the Forms I mentioned in my first post the tables I am
atempting to edit are enclosed in the quotes below. please confirm that
where you mentioned rs it has the same meaning as rst. I expect that I
have not understood your reply because when I run the code the way it's
set up below I get nothing, not even an error message, and I've seen my
share of them. I sense that the solution is close. Thank you for your
time.

Private Sub test2_Click()
Dim rs As dao.Recordset
Set rs = DBEngine(0)(0). OpenRecordset(" Pers_Landed", dbOpenDynamic)
Set rs = DBEngine(0)(0). OpenRecordset(" Pers_Posting", dbOpenDynamic)
Set rs = DBEngine(0)(0). OpenRecordset(" Pers_Medical", dbOpenDynamic)
Set rs = DBEngine(0)(0). OpenRecordset(" Pers_Other_Data ", dbOpenDynamic)
Do Until rst.EOF
rs.Edit

If (RFD) <= DATE Then
SAILING = True
End If

If (COURSE_OUT) <= DATE Then
COURSE = True
SAILING = False
End If
If (COURSE_IN) <= DATE Then
COURSE = False
SAILING = True
End If

If (COURSE) = True Then
LCA = False
SAILING = False
End If

If (LANDED_OUT) <= DATE Then
LCA = True
SAILING = False
End If
If (LANDED_IN) <= DATE Then
LCA = False
SAILING = True
End If

If (LCA) = False And _
COURSE = True Then
SAILING = False
End If

If (Start_Leave) <= DATE Then
CRS = True
SAILING = False
End If
If (Stop_Leave) <= DATE Then
CRS = False
SAILING = True
End If

If (START_DATE) <= DATE Then
MEDICAL = True
End If
If (STOP_DATE) <= DATE Then
MEDICAL = False
End If

If (COS_OUT_DATE) <= DATE Then
P_IN = False
ATP = False
SAILING = False
P_OUT = True
End If
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

Hmm... one thing that's definitely wrong... what are START_DATE etc?
If they're fields in your recordset, you have to use a syntax like
this:

If rs.Fields("Star t Date")<=Date() Then
....'set your field values here.
End If

If these are simple If statements, then why not just use a series of
update queries? that's a LOT faster than looping through a bunch of
updates. Can you not create a series of update queries with WHERE
Statements?

eg. -say this is a query called "qupdCOS_OUT_DA TE"
UPDATE MyTable
SET P_IN=False,
ATP=False
SAILING=False
P_Out=True
WHERE COS_OUT_DATE <=Date;

Then you could just do something like:
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "qupdCOS_OUT_DA TE"
DoCmd.OpenQuery "Another update Query"
.....
DoCmd.SetWarnin gs=True

Walking recordsets is something you don't want to do unless you really
have to. It will get REALLY slow when you have a lot of records,
especially compared to an update query.

[Stewart Graefner]

Good morning, Well I tried your sugestion about the queries. I think I
have it set up the way you described but when I run it the error I get
is when it gets to the word "Update" The error is "Compile error Sub or
Function Not Defined".
Same topic different approach. I have already made a whole series of
UPDATE queries useing the querie builder in MS Access. they are grouped
together and run through a Macro. They do exactly what I am trying to
achive using VB with the exception that each time a querie runs I get
two message boxes, one telling me that the querie is about to change
data and then the second asking if I want to procede. As you can tell
from the VB script that I wrote there are alot of Update queries to run
therefore it is is quite annoying, and I think not polished looking, to
have to sit there and bang through the dialog boxes answering yes each
time. Now if I can get a way to run those queries and not get the
message boxes things would be golden. Any ideas on that?

Private Sub qupdCOS_OUT_DAT E_Click()
Update Pers_Posting
Set P_IN = False
ATP = False
SAILING = False
P_OUT = True
Where Cos_Out_Date <= DATE

'Then you could just do something like:
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "qupdCOS_OUT_DA TE"
'DoCmd.OpenQuer y "Another update Query"

DoCmd.SetWarnin gs = True

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

[PC Datasheet]

Put this at the beginning of your Macro:
SetWarnings False
Put this at the end of your Macro:
SetWarnings True

BE SURE TO SET WARNINGS BACK TO TRUE!!!!
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!
"Stewart Graefner" <sg*******@east link.ca> wrote in message
news:cU******** ******@news.usw est.net...

Good morning, Well I tried your sugestion about the queries. I think I
have it set up the way you described but when I run it the error I get
is when it gets to the word "Update" The error is "Compile error Sub or
Function Not Defined".
Same topic different approach. I have already made a whole series of
UPDATE queries useing the querie builder in MS Access. they are grouped
together and run through a Macro. They do exactly what I am trying to
achive using VB with the exception that each time a querie runs I get
two message boxes, one telling me that the querie is about to change
data and then the second asking if I want to procede. As you can tell
from the VB script that I wrote there are alot of Update queries to run
therefore it is is quite annoying, and I think not polished looking, to
have to sit there and bang through the dialog boxes answering yes each
time. Now if I can get a way to run those queries and not get the
message boxes things would be golden. Any ideas on that?

Private Sub qupdCOS_OUT_DAT E_Click()
Update Pers_Posting
Set P_IN = False
ATP = False
SAILING = False
P_OUT = True
Where Cos_Out_Date <= DATE

'Then you could just do something like:
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "qupdCOS_OUT_DA TE"
'DoCmd.OpenQuer y "Another update Query"

DoCmd.SetWarnin gs = True

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

[StopThisAdvertising]

"PC Datasheet" <no****@nospam. spam> schreef in bericht news:zW******** *********@newsr ead1.news.atl.e arthlink.net...

To Steve:
WE *WILL* BE SURE TO SET WARNINGS FOR NEW USERS TO TRUE!!!!

<snipped all the advertising stuff>

To the OP: Beware of this guy!!

Steve just does *not* care about the newsgroups. He has *no ethics at all*.
Steve *only* cares about making *money*, and he acts as if the groups are his private hunting ground.

-- He abuses this group and others for job-hunting and advertising over and over again
-- He is insulting lots of people here when they ask him to stop this
-- He posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ??? while asking questions
(the latest 'star's': 'Access Resource' and Tom no***@email.com and Andy)
-- He tries to sell a CD ($125,--) with FREE code he gathered from these groups here
-- There even has been a 'Scam-alert' about him which has been explained recently in the thread 'To all':
http://groups.google.com/group/comp....954261f9?hl=en
-- Also recently it became clear that he has been spamming innocent people asking questions:
http://groups.google.com/group/comp....3e5f58ad?hl=en

So why would ANYBODY ever trust a person like him and hire him?
*************** *************** *************** ***********

Explanation and more links on this answer:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R

[Stewart Graefner]

Setting the warnings To False and Back to True worked. This Thread is
Finished. Thanks very much to all who assisted.

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

Stewart Graefner <sg*******@east link.ca> wrote in
news:fo******** *******@news.us west.net:

Setting the warnings To False and Back to True worked. This
Thread is Finished. Thanks very much to all who assisted.

Your code is completely nonsensical. I can't imagine that it would
work, unless all the fields you're operating on are in the last
recordset. That is:

Set rst = db1.OpenRecords et("Pers_Landed ", dbOpenTable)
Set rst = db1.OpenRecords et("Pers_Postin g", dbOpenTable)
Set rst = db1.OpenRecords et("Pers_Medica l", dbOpenTable)
Set rst = db1.OpenRecords et("Pers_Other_ Data", dbOpenTable)

is going to be completely equivalent to:

Set rst = db1.OpenRecords et("Pers_Other_ Data", dbOpenTable)

Everything before that is gone when you execute that line.

Secondly, there's nothing in your code that tells us what RFD,
SAILING, COURSE_OUT and so forth are. Are they fields in the form?

No one could possibly have answered your question becuase the code
makes no sense at all as posted.

But I'm glad you solved your problem on your own -- your post was
certainly completely unlikely to generate any useful help.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Stewart Graefner]

David Fenton. Thanks for taking the time to reply to my post. I posted
my questions here in order to get assistance. If I knew what I was doing
I wouldn't be here. You are obviously some one who seems to know alot
about VB Programing. If you've taken the time to responed why couldn't
you be nicer about it and ask questions in a polite maner rather than
berating me and providing me with no assistance what so ever. I would
have very much enjoyed learning from you. In a classroom there are
teachers who write on the board, students who read from the board and
goofballs that sit in the back throwing spitballs. Which one are you
David?
P.S. yes they are all fields on a form. The "Ifs" are Date Fields and
the "Thens" are check boxes. All you had to do was ask.

*** Sent via Developersdex http://www.developersdex.com ***