Problem with recordset.

[Fuga]

Problem with recordset.**resolved**

I´m trying to have the db going through the records in a table and, depending on values, change the values of records in another table.

Of course, it´s not working.

[vb]

Sub trend()

Dim dt As String
Dim rs2 As DAO.Recordset
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tbl1")
rs.MoveFirst
Do While Not rs.EOF
dt = rs.Fields(0)
Set rs2 = CurrentDb.OpenRecordset("select * from tbl2 where date > " & dt & " order by date asc")
If rs.Fields(1) = -1 Then
rs2.Edit
rs2![upptrend] = -1
rs2.Update
Else
rs2.Edit
rs2![upptrend] = 0
rs2.Update
End If
rs2.Delete
rs.MoveNext
Loop

End Sub

[/vb]

when I check tbl2, something has happened with the first record (which should not be selected actually), but that´s it.

Fuga.

 

[Tim K.]

Do you want to update all the records in tbl2 where the Date > the dt? If so, you may consider the Update query to update the records on each loop.

It's no doubt why only the first record in tbl2 is changed, you don't use Loop the second recordset and use MoveNext.

I prefer the Update query to do the task if no more criteria to check out doing the update. It runs faster than loop.

 

[The_Doc_Man]

This is a minor quibble, but you have the following:

dt = rs.Fields(0)

I would always use the syntax ...

rs.Fields("fieldname")

as a matter of better self-documentation. This way, you could immediately see if you were testing the wrong field. Which is one of the things I would have checked for anyway.

You also have...

where date > " & dt & " order by

But if dt is truly a date/time field, you might have a little problem with representation in the query.

First, the 'date' in that quoted portion is a reserved word, so who knows what you are testing. Did you mean 'dt'? In any case, I might have used...

where date > #" & format( [dt],"Short Date" ) & "# order by

Then there's the matter of the ....

rs2.Delete

near the end of the function body. What were you trying to do here? I would be surprised if anything changed INCLUDING the first record. It looks like you are DELETING any record you would have changed.

 

[Shep]

.Delete deletes the current record in an updatable Recordset object. I believe you should be using .Close on that line.

In addition, your code does not move through the records in tbl2.

Perhaps something along these lines?:

Dim dt As String
Dim rs2 As DAO.Recordset
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tbl1")
Set rs2 = CurrentDb.OpenRecordset("select * from tbl2 where date > " & dt & " order by date asc")
rs.MoveFirst
Do While Not rs.EOF
    dt = rs.Fields(0)
    If rs.Fields(1) = -1 Then
        rs2.Edit
        rs2![upptrend] = -1
        rs2.Update
    Else
        rs2.Edit
        rs2![upptrend] = 0
        rs2.Update
    End If
    rs.MoveNext
    rs2.MoveNext
Loop
rs.Close
rs2.Close

Oh...and see the above posts for additional concerns. This code works but does not take into account whether your dt string should actually be of type Date/Time, nor what type the source and destination fields are. An update query might serve just as well, also.

Shep

 

[Fuga]

Thanks guys!

Ok, let´s see...

Tim K:

Yes, all records in tbl2 where date > dt should be updated. Then the loop changes the dt variable and again all records in tbl2 where date > dt should change...etc.

Actually, Pat Hartman suggested I use an update query, but I couldn´t get it to work. How do I do it? I create a querydef I suppose, but then what??

As for the loop, you are probably right, it doesn´t go through the records in tbl2, but I don´t see why not. (I thought for a brief moment that it was because the rs2 had already been created, which is why I thought of the rs2.delete (not really knowing what the effect would be ) The rs2.delete is not supposed to be there, I just forgot to remove it before posting the topic. Sorry.

The_doc_man:

Yes, I have been having trouble with the where clause. For instance, I can write date > dt but not date => dt. The thing is the name of the field is date.

Is it generally a bad idea to name fields "date"?

BTW
rs.fields ("fldname")? I knew it! d.....ed helpfiles!

So to summarize:

I don´t know how to write the update query, and I don´t know how to loop...I must be the greatest.

Fuga.

 

[Fuga]

Thanks everyone.

When I finally understood that I had "inner loops" and that a recordset isn´t ordered allthough it looks like it is, I did it like this:

Sub trend()

Dim dt As String
Dim rs2 As DAO.Recordset
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("select * from tbl1 order by [date] asc")
rs.MoveFirst
Do While Not rs.EOF
    dt = rs.Fields("date")
    Set rs2 = CurrentDb.OpenRecordset("select * from tbl2 where [date] > #" & dt & " #order by [date] asc")
        If rs.Fields(1) = -1 Then
            rs2.MoveFirst
            Do While Not rs2.EOF
                rs2.Edit
                rs2![upptrend] = 0
                rs2.Update
                rs2.MoveNext
            Loop
        Else
            rs2.MoveFirst
            Do While Not rs2.EOF
                rs2.Edit
                rs2![upptrend] = -1
                rs2.Update
                rs2.MoveNext
            Loop
        End If
    rs.MoveNext
Loop

End Sub

I now have a table indicating whether a record is part of an upward trend or not. GREAT!

Fuga.