VBA module code to loop through records 4

[5tr0ud]

I'm developing a MSAccess2000 database. I've created a subform, based on a query, and I'm trying to loop through one field in all the records on the subform. The subform is a 'continuous' form, which lists 1-10 records for each person on the main form.

I've put the code in the OnCurrent and OnLoad events -- but I can only go through all the fields in the FIRST record on each subform. I want to loop through each record on the subform, and make changes to a field. Can anyone help with the code? I've consulted numerous books, and tried many different things, to no avail. Can't get it to loop.

Thank you so much.

 

[KenReay]

You will need a reference to DAO library for this code

Dim Rs as DAO.Recordset

Set Rs = Me.Recordsetclone
If Rs.REcordcount > 0 Then
Rs.MoveFirst
Do Until Rs.EOF
' do whatever here
rs.moveNext
loop
end If
rs.close
set rs = nothing

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[5tr0ud]

Thank you Ken . . . I tried your suggestion and get the following error message:
'User defined type not defined.'
I put this code in the Private Sub Form_Current() event as you suggested: CAN YOU PLEASE HELP??? Also,I'm very new to this Forum, as I've just located it on the Web. Thank you everyone!

Private Sub Form_Current()

Dim Rs As DAO.Recordset

Set Rs = Me.RecordsetClone
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Do Until Rs.EOF
' do whatever here
If Me.Mon = "N" Then
Me.Mon = ""
End If

Rs.MoveNext
Loop
End If
Rs.Close
Set Rs = Nothing

 

[PHV]

You have to reference the DAO library.
While in VBE, menu tools -> references ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[5tr0ud]

Thanks for the suggestion. I've opened a new module, and added the Microsoft DAO library. I am not familiar with Modules and have been keeping the code in the Form_OnLoad() Event. How do I complete the Module coding?

Sincerely.

 

[Zion7]

Just for the record 5tr0ud,
I don't believe Ken suggested "OnCurrent".
I say this because, it's not too common/necessary when looping through complete recordsets, to use the Current event, almost redundant.
This would suffice...
If Me.Mon = "N" Then
Me.Mon = ""
End If

I'm not sure what your objective is, but it appears your "if" statement, has nothing to do with the recordset.
It should be ...
If rs!Mon = "N" Then....

So, whatever Event you use...

Dim Rs As DAO.Recordset

Set Rs = Me.RecordsetClone
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Do Until Rs.EOF
If rs!Mon = "N" Then
rs.Edit
rs!Mon = ""
rs.Update
End If

Rs.MoveNext
Loop
End If
Rs.Close: Set Rs = Nothing


To use a standard module..

Sub UpdateMon()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblCountries", dbOpenSnapshot)

If Rs.RecordCount > 0 Then
    Rs.MoveFirst
    Do Until Rs.EOF
     If rs!Mon = "N" Then
            rs.Edit
            rs!Mon = ""
            rs.Update
        End If
        
     Rs.MoveNext
Loop
End If
Rs.Close: Set Rs = Nothing
End Sub

From the form, whatever event you chose...

Private Sub Form_Load()
Call UpdateMon
End Sub

Hope this helps, good luck!

 

[5tr0ud]

Thank you again, Zion7. I tried your code in a module, and called it from Form_Load(). I got this error message regarding the first two lines of code:
"Compile error: User-defined type not defined."
My subform is connected to a query. Would that affect the code? Any more thoughts?

What I'm trying to accomplish is just to be able to Loop through a dataset . . . in this specific case, I want to remove all of the 'N's in this calendar, so only 'Y' is indicated in each timeslot. The calendar will be imported data each semester. THANK YOU AGAIN!

Sub UpdateMon()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSSSSchedules", dbOpenSnapshot)

If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If rs!Mon = "N" Then
rs.Edit
rs!Mon = ""
rs.Update
End If

rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing
End Sub

 

[Zion7]

Sorry 5tr0ud,
Sloppy referencing

Dim db As DAO.Database
Dim rs As DAO.Recordset.

Hope this helps!

 

[5tr0ud]

Tried the 2 new lines, and get this error message at the 3rd line of code: Set db = CurrentDb()

Run-time error 13: type mismatch.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSSSSchedules", dbOpenSnapshot)

 

[Zion7]

Not sure 5tr0ud,
(works fine for me)?



Try compiling?

 

[5tr0ud]

Thank you . . . I compiled, and reran the program. When I open the Main Form, it get the error message:
Run-time Error '13': Type mismatch.
I've checked this reference for the Module:
Microsoft DAO 2.5/3.0 Compatability Library.

Again, here's my code in a Module:

Option Compare Database UpdateMon

Sub UpdateMon()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSSSSchedules", dbOpenSnapshot)

If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If rs!Mon = "N" Then
rs.Edit
rs!Mon = ""
rs.Update
End If

rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing
End Sub


Am truly grateful for any suggestions.

 

[RoyVidar]

Micorosoft DAO 3.# Object Library (# - probably 6)

Roy-Vidar

 

[PHV]

And if you're tired with Recordset, you may try this:
DoCmd.RunSQL "UPDATE tblSSSSchedules SET mon='' WHERE mon='N'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[5tr0ud]

Thank you, I made the change in the Reference, and it compiled and seemed to run without a problem. However, it did not make any changes to the subform. I tried to reopen the subform, and got this error:

If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If rs!Mon = "N" Then
rs.Edit
rs!Mon = ""
rs.Update
End If

What I'm trying to do is remove all the 'N's by changing them to "".
Example: Mon Tues Wed Thu Fri
9:00 Y N Y N Y
10:00 N Y N Y N

I MOST of all want to be able to loop through a collection of records and make changes.



Here's the call made in the OnLoad in the subform:
Private Sub Form_Load()
Call UpdateMon
End Sub


Again, I try every and all suggestions. And am really grateful . . . have been trying this for several weeks!

 

[PHV]

Replace this:
dbOpenSnapshot
By this:
dbOpenDynaset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[5tr0ud]

Kudos to you PHV -- AND to everyone else who contributed to this SUCCESS!!! I replaced dbOpenShapshot with dbOpenDynaset AND IT WORKED!!! I've been trying to learn how to loop through a recordset for the past 2-3 weeks.

THANK YOU ALL AGAIN!

 

[Zion7]

Congrats 5tr0ud,
Me, being a rookie myself, often forget to check all arguments, when opening a recordset.
Bear with me here 5tr0ud, I'm not sure if this is necessary but, due to the last statement, in your last post, I'd like to clarify why "Snapshot" didn't work.

You had the recordset opened correctly the whole time, but since you wanted to edit records, snapshot is not condusive.

In DAO, there are 5 different types of recordsets you can open. I think it's the same for ADO.

Table dbOpenTable, Fully updateable, based on a SINGLE table

Dynaset dbOpenDynaset, Fully updateable, can be based on a single table or query

Snapshot, dbOpenSnapshot, not updateable, data is retrieved quickly (on small recordsets, approx 500)

Forward, dbForwardOnly, non updateable, can only move in forward direction, when reading.

Dynamic, used predominantly, for opening recordsets in remote databases.

Hope this offers some insight!

Either way, good luck!