How to Move to a Particular Record in A RecordSet 1

[Jawad77]

I am creating a recordset in VBA using a given Table. I like to move to a particular record of my recordset based uponn a particular condition. How could I do that?

Here is the code:

Dim MYDB As Database
Dim rst As Recordset

Set MYDB = DBEngine.Workspaces(0).Databases(0)

Set rst = MYDB.OpenRecordset("InboundGraphTable", DB_OPEN_DYNASET)

rst.Move


I like to move to the record of the recordet where Week = Current Week

Week is one of the fields of the recordset
Current Week is displayed in the text box and I can reference it

Your help would be highly appreciated.

Thanks
Jay

 

[RoyVidar]

Try:

[tt]rst.findfirst "Week =" & me!CurrentWeek[/tt]

- assuming Week is numeric. If it's text, you'll need single quotes

[tt]rst.findfirst "Week = '" & me!CurrentWeek & "'"[/tt]

Roy-Vidar

 

[Jawad77]

Roy,
It doesn't move the recordset to the current week location.
Even though it doesn't give me any errors.

rst.FindFirst [Fiscal Week] = tbx_FiscalWeek.Value
Debug.Print rst![ReplannedCube]

It should give me the replanned value corresponding to the current week. But it just prints out the very first replanned value.

Do you know what's is causing it. Thanks for your earlier reply

Jay

 

[PHV]

Replace this:
rst.FindFirst [Fiscal Week] = tbx_FiscalWeek.Value
By this:
rst.FindFirst "[Fiscal Week]=" & tbx_FiscalWeek.Value

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

 

[Jawad77]

Roy,

It works. You are the man. Thanks a lot. Once I am at the desired record in the recordset, I like to move only through the next five records in the recordset (incluidng the one I am currently at). I am using this code but I am sure there should be an easier and simple way to do it.


Do While Not rst.EOF
If index < 6 Then

rst.Edit

(I will perform some calculations here)
rst.Update

irst.MoveNext
index = index + 1

Else

Exit Do
Loop

 

[PHV]

For index = 1 To 5
If Not rst.EOF Then
rst.Edit
'(I will perform some calculations here)
rst.Update
rst.MoveNext
Else
Exit For
End If
Next index

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

 

[Jawad77]

Thanks a lot PH.