循环记录 [英] Looping through records
问题描述
这是一段适用于单个记录的代码。它b / b
评估日期，检查或取消选中框。它可能不会很漂亮，但它有效。我的问题是我需要它来评估我的数据库中的所有记录（200+）并更改那些需要更改的b / b
。必须单独进行才能破坏开发此代码的目的。我希望能做的是1.
打开数据库按下按钮，所有记录更新或2.有它
每次自动更新db打开。它真的只需要每天做一次b
所以我倾向于＃1。所以我想我需要能够将b / b
循环遍历所有记录直到最后一条记录然后
停止。所以，请问，我该怎么做？该代码应该放在
模块中吗？我的数据库打开了一个名为TITLE PAGE的FORM。
需要更新的记录通过名为MASTER
PAGE的第二张表格进行操作。我希望能够在TITLE PAGE更新记录。
目前只需按下MASTER PAGE上的按钮即可，但只有
更新一条记录按下，按下它更新的按钮，手动
选择新的记录，按下它更新的按钮，手动选择新的
记录......这不是怎么回事应该管用。我希望它能从TITLE PAGE工作
按下按钮更新所有记录，我已经完成了
。
Private Sub test2_Click（）
Dim db1 As Database
Dim rst作为记录集
设置db1 = DBEngine（0） （0）
设置rst = db1.OpenRecordset（" Pers_Landed"，dbOpenTable）
设置rst = db1.OpenRecordset（" Pers_Posting"，dbOpenTable）
Set rst = db1.OpenRecordset（" Pers_Medical"，dbOpenTable）
Set rst = db1.OpenRecordset（" Pers_Other_Data"，dbOpenTable）
Do而rst.BOF = False和rst.EOF = False
如果（RFD）< = DATE则
SAILING = True
结束If
如果（COURSE_OUT）< = DATE则
COURSE = True
SAILING = False
结束如果
如果（COURSE_IN）< = DATE那么
课程=错误
SAILING = True
结束如果
If（COURSE）= True然后
LCA = False
SAILING = False
结束如果
If（LANDED_OUT）< =日期然后
LCA =真
SAILING = False
结束如果
如果（LANDED_IN）< = DATE然后
LCA = False
SAILING = True
结束如果
If（LCA）=假和_
COURSE = True然后
SAILING = False
结束如果
如果（ Start_Leave）< = DATE然后
CRS =真
SAILING = False
结束如果
If（Stop_Leave ）< = DATE然后
CRS = False
SAILING = True
结束如果
如果（START_DATE）< = DATE则
MEDICAL = True
结束如果
如果（STOP_DATE）< = DATE那么
医疗=假
结束如果
如果（COS_OUT_DATE）< = DATE那么
P_IN = False
ATP = False
SAILING = False
P_OUT = True
结束如果
$ b $第一个.MoveNext
循环
结束子
***通过Developersdex发送 http://www.developersdex.com ***
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.OpenRecordset("Pers_Landed", dbOpenTable)
Set rst = db1.OpenRecordset("Pers_Posting", dbOpenTable)
Set rst = db1.OpenRecordset("Pers_Medical", dbOpenTable)
Set rst = db1.OpenRecordset("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 ***
推荐答案
你必须打开你正在寻找的桌子下面的记录集
at ... ... ...
Sub UpdateMyRecords（）
dim rs as dao.recordset
set rs = dbengine（0）（0）.OpenRecordset（" SELECT ... FROM。 ..W HERE ..."，
dbOpenDynamic）
直到rs.EOF
rs.Edit''---允许编辑
''---在这里删除你的记录处理代码
''---在这里做你的编辑
rs 。更新''---保存更改
rs.MoveNext
loop
rs.Close
Set rs =没有
结束子
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).OpenRecordset("SELECT...FROM...W HERE...",
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
好的，如果我理解你的回复是我的得到了。这是基于
我对所有事物VB的有限理解。
除了我在第一篇文章中提到的表格，我就是表格
尝试编辑包含在下面的引号中。请确认
你提到的地址与第一个含义相同。我希望我
还没有理解你的回复，因为当我运行代码时，它的设置方式如下所示，我什么都没得到，甚至没有错误信息，我已经看到了他们的
份额。我觉得解决方案很接近。谢谢你的
时间。
Private Sub test2_Click（）
Dim rs As dao.Recordset
设置rs = DBEngine（0）（0）.OpenRecordset（" Pers_Landed"，dbOpenDynamic）
设置rs = DBEngine（0）（0）.OpenRecordset（" Pers_Posting"，dbOpenDynamic） ）
设置rs = DBEngine（0）（0）.OpenRecordset（" Pers_Medical"，dbOpenDynamic）
设置rs = DBEngine（0）（0）.OpenRecordset（ Pers_Other_Data，dbOpenDynamic）
直到rst.EOF
rs.Edit
If（RFD）< =日期然后
SAILING = True
结束如果
如果（COURSE_OUT）< = DATE那么
COURSE = True
SAILING = False
结束如果
If（COURSE_IN）< = DATE那么
课程=错误
SAILING = True
结束如果
如果（COURSE）= True那么
LCA = False
SAILING = False
结束如果
如果（LANDED_OUT）< = DATE Th en $ / $
LCA =真
SAILING = False
结束如果
如果（LANDED_IN）< = DATE那么
LCA =假
SAILING =真
结束如果
如果（LCA）=假并且_
COURSE = True然后
SAILING = False
结束如果
If（Start_Leave ）< = DATE然后
CRS =真
SAILING = False
结束如果
If（Stop_Leave） < = DATE然后
CRS = False
SAILING = True
结束如果
如果（START_DATE）< = DATE然后
医疗=真实
结束如果
如果（STOP_DATE）< = DATE那么
医疗=假
结束如果
如果（COS_OUT_DATE）< = DATE那么
P_IN = False
ATP = False
SAILING = False
P_OUT =真
结束如果
rs.Update
rs.MoveNext
循环
rs.Close
设置rs = Nothing
结束子
***通过开发人员指南发送 http：/ /www.developersdex.com ***
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 ***
嗯...有一件事情肯定是错的......什么是START_DATE等？
如果它们是记录集中的字段，则必须使用类似
的语法：
如果rs.Fields（ 开始日期）< =日期（）然后
....''在此处设置您的字段值。
结束如果
如果这些是简单的If语句，那为什么不只是使用一系列的
更新查询？这比通过一堆
更新循环更快。你能否用WHERE
语句创建一系列更新查询？
例如。 - 这是一个名为qupdCOS_OUT_DATE的查询
UPDATE MyTable
SET P_IN = False，
ATP = False
SAILING = False
P_Out = True
在哪里COS_OUT_DATE< =日期;
然后你可以做点什么喜欢：
DoCmd.SetWarnings False
DoCmd.OpenQuery" qupdCOS_OUT_DATE"
DoCmd.OpenQuery" Another update Query"
.....
DoCmd.SetWarnings = True
行走记录集是你不想做的事，除非你真的
必须。当你有大量记录时，它会变得非常慢，
特别是与更新查询相比。
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("Start 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_DATE"
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.SetWarnings False
DoCmd.OpenQuery "qupdCOS_OUT_DATE"
DoCmd.OpenQuery "Another update Query"
.....
DoCmd.SetWarnings=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.
这篇关于循环记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
