Getting VBA code to cycle thru all records

  • Thread starter Thread starter Lee-Anne
  • Start date Start date
L

Lee-Anne

Sorry I'm going to post this again as my original post is
now on the 2nd page and I'm afraid this might be missed.
I'm also posting again as I failed to state that I had
exhausted all resources including the Helpdesk function
and any books I have access to...

I have written code that updates a record in one table
(Table 1) with information from another table (Table 2),
if the record meets a certain condition i.e. If (Forms!
[Table1]![Defect ID] = Forms![Table2]![Helpdesk Ref]) And
(Forms![Table2]![DateCommentAdded] = Date) Then ... My
problem is that while the code works for the first record,
I don't know how to get it to cycle/loop through all
records in both tables until it reaches the end of Table
2. At the same time, if condition (Forms![Table1]![Defect
ID] = Forms![Table2]![Helpdesk Ref])is false I want move
to the next record for Table 1 only and for this to
continue until the condition is true.


Using the example/info provide by the Help facility I
produced the following code which doesn't work - it still
only updates the first record. If
someone could tell me where I've gone wrong that would be
most helpful:

Set dbSystemAdmin = CurrentDb
Set rsHelpdesk = dbSystemAdmin.OpenRecordset("Helpdesk")
Set rsProbMan = dbSystemAdmin.OpenRecordset("Problem
Management")

rsProbMan.MoveFirst
rsHelpdesk.MoveFirst

Do


If (Forms![Helpdesk1]![Defect ID] = Forms![Problem
Management]![Helpdesk Ref]) Then

Forms![Helpdesk1]![NewProbManUpdate] = False
UpdateTransfer = Forms![Helpdesk1]![RecentUpdate] &
Forms![Helpdesk1]![Updates]
Forms![Helpdesk1]![Updates] = UpdateTransfer
Forms![Helpdesk1]![RecentUpdate] = Forms![Problem
Management]![Recent Update]
Forms![Helpdesk1]![NewProbManUpdate] = True
rsProbMan.MoveNext

Else
rsHelpdesk.MoveNext

End If

Loop Until rsProbMan.EOF
 
Lee-Anne,

You switched between Table1/Table2 and
rsHelpdesk/rsProbMan but maybe this will help you along.

Basically, (no pun intended) you have to compare a record
in Table1 with each the records in Table2 until a match is
found. After doing something, goto the next record in
table1 and start at the beginning of Table2 again.

You started out defining recordsets, then were using
controls on a form. I modified your code
....(AIR CODE - untested - wave your hands in the air while
shouting WARNING, WARNING, WARNING)....
when you run the Sub, it processes each record in Table1
comparing it to the records in Table2, then stops.

Here is the code -
- Remember!! It is untested
- Watch for line wrap:


Set dbSystemAdmin = CurrentDb
Set rsHelpdesk = dbSystemAdmin.OpenRecordset("Helpdesk")
Set rsProbMan = dbSystemAdmin.OpenRecordset("Problem
Management")

If rsHelpdesk.BOF and rsHelpdesk.EOF then
MsgBox "WARNING - No records in Helpdesk"

' Clean up
rsProbMan.Close
rsHelpdesk.Close
Set rsProbMan = Nothing
Set rsHelpdesk = Nothing
Set dbSystemAdmin = Nothing
Exit Sub
End If

If rsProbMan.BOF and rsProbMan.EOF then
MsgBox "WARNING - No records in Problem Management"

' Clean up
rsProbMan.Close
rsHelpdesk.Close
Set rsProbMan = Nothing
Set rsHelpdesk = Nothing
Set dbSystemAdmin = Nothing
Exit Sub
End If

rsProbMan.MoveFirst
rsHelpdesk.MoveFirst

Do While not rsProbMan.EOF 'this is rsProbMan (table 1)

Do While not rsHelpdesk.EOF 'this is rsHelpdesk (table
2)

If (rsHelpdesk.[Defect ID] = rsProbMan.[Helpdesk
Ref]) Then
'Record found - Do something
with rsHelpdesk
.Edit
.NewProbManUpdate = False
UpdateTransfer = .RecentUpdate & .Updates
.Updates = UpdateTransfer
.RecentUpdate = rsProbMan.[Recent Update]
.NewProbManUpdate = True
.Update
End With

'Done with this Table1 record
Exit Do

Else
' matching record not found - move to the next table2
record
rsHelpdesk.MoveNext

End If

Loop ' (table 2)

'
' now goto the next record in rsProbMan (table 1) and
' goto the first record in rsHelpdesk (table 2)
rsProbMan.MoveNext
rsHelpdesk.MoveFirst

Loop ' (table 1)

' Finished - close and clean up
rsProbMan.Close
rsHelpdesk.Close

Set rsProbMan = Nothing
Set rsHelpdesk = Nothing
Set dbSystemAdmin = Nothing


BTW, Try not to use spaces in object names. It is a lot
harder working with fields when the name is like this -
"Recent Updates" than when there are no spaces -
"RecentUpdates". Reads the same, but with a lot less
Click to expand...
coding headaches......


HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
Sorry I'm going to post this again as my original post is
now on the 2nd page and I'm afraid this might be missed.
I'm also posting again as I failed to state that I had
exhausted all resources including the Helpdesk function
and any books I have access to...

I have written code that updates a record in one table
(Table 1) with information from another table (Table 2),
if the record meets a certain condition i.e. If (Forms!
[Table1]![Defect ID] = Forms![Table2]![Helpdesk Ref]) And
(Forms![Table2]![DateCommentAdded] = Date) Then ... My
problem is that while the code works for the first record,
I don't know how to get it to cycle/loop through all
records in both tables until it reaches the end of Table
2. At the same time, if condition (Forms![Table1]! [Defect
ID] = Forms![Table2]![Helpdesk Ref])is false I want move
to the next record for Table 1 only and for this to
continue until the condition is true.


Using the example/info provide by the Help facility I
produced the following code which doesn't work - it still
only updates the first record. If
someone could tell me where I've gone wrong that would be
most helpful:

Set dbSystemAdmin = CurrentDb
Set rsHelpdesk = dbSystemAdmin.OpenRecordset("Helpdesk")
Set rsProbMan = dbSystemAdmin.OpenRecordset("Problem
Management")

rsProbMan.MoveFirst
rsHelpdesk.MoveFirst

Do


If (Forms![Helpdesk1]![Defect ID] = Forms![Problem
Management]![Helpdesk Ref]) Then

Forms![Helpdesk1]![NewProbManUpdate] = False
UpdateTransfer = Forms![Helpdesk1]![RecentUpdate] &
Forms![Helpdesk1]![Updates]
Forms![Helpdesk1]![Updates] = UpdateTransfer
Forms![Helpdesk1]![RecentUpdate] = Forms![Problem
Management]![Recent Update]
Forms![Helpdesk1]![NewProbManUpdate] = True
rsProbMan.MoveNext

Else
rsHelpdesk.MoveNext

End If

Loop Until rsProbMan.EOF

.
Click to expand...
 
One simple way is to use DOCMD.RUNSQL UPDATE...You can
test it using query first, and then copy the SQL command
to VBS directly after DOCMD.RUNSQL
 
Sorry I'm going to post this again as my original post is
now on the 2nd page and I'm afraid this might be missed.
I'm also posting again as I failed to state that I had
exhausted all resources including the Helpdesk function
and any books I have access to...
Click to expand...

Responded to in m.p.a.tablesdbdesign

Tim F
 
Back
Top