Navigate a recordset (previous-next)

[sagamw]

I have a table (tbl1), a query from that table (query1) with criteria taken from an open form, and a 2nd query (query2) with criteria the value from a combo box.

My whole design works like a charm, but I decided that I need a previous/next pair of buttons, to navigate through the recordset of my 2nd query.

I am open on ideas on how I am gonna do this...

I am not a programmer so I gather infos about my task and I found some, about openrecordset, movenext, moveprevious and such, but I had some issues.

Here's what I have done so far (and my problems) but I am open to any other suggestions...

I wrote a code similar to this:
Dim RS as recordset
Set RS=currentdb.openrecordset("query2")

Here I get error 3061 Too few parameters, Expected 2

(if -for the fun of it- I replace query2 with query1
i get 3061 Too few parameters, Expected 1)
So I think that the "criteria" in my queries is somehow the problem, but I m maybe wrong)


When I run my queries they work just fine and I get the expected results. Also they work in some other part of my form.)

 

[PHV]

Dim db As Database
Dim qdf As QueryDef
Dim prm As DAO.Parameter
Dim RS As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RS = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

Thank you PHV for your answer.

How can I move to the next (or previous) record?

So far, I put your code inside a private sub nextbutton_click() and after that I put RS.movenext

private sub nextbutton_click()

Dim db As Database
Dim qdf As QueryDef
Dim prm As DAO.Parameter
Dim RS As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("query2")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set RS = qdf.OpenRecordset 

[b]RS.movenext[/b]
...
end sub

I am getting an error 3021 NO CURRENT RECORD

I found this thread

http://www.tek-tips.com/viewthread.cfm?qid=1170787&page=1

about that error but I can really grasp what is going on.
My 2 queries work fine.
I don't think I have an empty recordset
I created 2 buttons previous/next and I get this error from both, so I am not getting this because of BOF or EOF.

:-(

EDIT: I changed the query2 with query1 (for the ..fun of it as I say!) and it worked but only for 1 record i.e. only works the next button and always takes me to the 2nd record of query1 (not the query2 that I want and I get the 3021 error)

 

[PHV]

Why not simply use a subform bound to the desired query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

I did that, and it's a continuous (sub)form.

I have build an image viewer (with thumbnails etc.) inside my db.
My main form has 2 subforms: 1 big for displaying the selected image, and 1 continuous form for the thumbs (it displays 4-5 records/thumbs at a time).
I also have a combo box in my main form to filter (with results from query2) the thumbs.

Everything works as expected, BUT for my purpose, I want to have a previous and a next button in my main form (under the combo box) to navigate through the query2.

i.e. the user selects something from the combo. It filters the results (thumbs) in the continues sumbform fine. If he presses a thumb, he will get the big image inside the big subform, also fine.
I want him to have PRE/NEXT buttons to navigate/show (one after the other) the images (results from query2) inside the big subform.

 

[PHV]

A starting point.
In the Click event procedure of the Next button:

With Me![big subform control name].Form.Recordset
  If .EOF Then
    .MoveFirst
  Else
    .MoveNext
  End If
End With

In the Click event procedure of the Prev button:

With Me![big subform control name].Form.Recordset
  If .BOF Then
    .MoveLast
  Else
    .MovePrevious
  End If
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

Thanks PHV, but I don't know how this fits in my sub I build (with your help) in the 3rd post of this thread

private sub nextbutton_click()

Dim db As Database
Dim qdf As QueryDef
Dim prm As DAO.Parameter
Dim RS As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("query2")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set RS = qdf.OpenRecordset

????
????

end sub

The pre/next buttons are in my main form, not the bigimage subform. The thumbs (results from query2, after I do a selection in the combo box) are in the continuous sub form.

 

[PHV]

get rid of this sub explaining how to avoid the 3061 error and use my previous suggestion.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

I get an Error 91: Object variable or With block variable not set...

 

[PHV]

Well, what is YOUR actual code (all: mainform and subforms)?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886