Next Record in Query Results via Form

  • Thread starter Thread starter William Wisnieski
  • Start date Start date
W

William Wisnieski

Hello Everyone,

Access 2000,

I have a main unbound form [frmSearch] with a bound datasheet subform
[sfrmSearchResults]. The subform is bound to a query [qrySearchResults]
that returns records based on criteria in the main form. The user then
double clicks a row on the datasheet subform to open yet another form
[frmDetails] bound to a table [tblStudents]. These two forms are linked by
the [StudentID] field. So far so good.

Here' is where I'm stumped. I'd like to put a Next Record navigation button
on the [frmDetails] form so the user can go to the next record of the query
results listed in the datasheet form [sfrmSearchResults] instead of closing
the form and going to the next record of the datasheet. Does anyone know
the code for that? Here is what I've tried to no avail. I get a "compile
error--expected array."

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click

DoCmd.GoToRecord , , acNext([StudentID] =
[Forms]![frmSearch]![sfrmSearchResults]![StudentID])

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub

Thank you for any and all help.

William

P.S. A "luxury" at this point would be if the user got a "end of record"
error message when they tried to click on the Next button but there were no
records left.
 
Hi William

you need to get the Recordset Object from the [sfrmSearchResults] form and then execute MoveNext/MovePrev method of the Recordset

Put something like this on the Next button Click event

on error resume nex
Dim rst As DAO.Recordse
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordse
rst.MoveNex
if err = 3021 then
msgbox "You pass the last record or at new record
els
msgbox err & ": " & err.descriptio
end if
err.clea


hope this hel
----- William Wisnieski wrote: ----

Hello Everyone

Access 2000

I have a main unbound form [frmSearch] with a bound datasheet subfor
[sfrmSearchResults]. The subform is bound to a query [qrySearchResults
that returns records based on criteria in the main form. The user the
double clicks a row on the datasheet subform to open yet another for
[frmDetails] bound to a table [tblStudents]. These two forms are linked b
the [StudentID] field. So far so good

Here' is where I'm stumped. I'd like to put a Next Record navigation butto
on the [frmDetails] form so the user can go to the next record of the quer
results listed in the datasheet form [sfrmSearchResults] instead of closin
the form and going to the next record of the datasheet. Does anyone kno
the code for that? Here is what I've tried to no avail. I get a "compil
error--expected array.

Private Sub cmdNext_Click(
On Error GoTo Err_cmdNext_Clic

DoCmd.GoToRecord , , acNext([StudentID]
[Forms]![frmSearch]![sfrmSearchResults]![StudentID]

Exit_cmdNext_Click
Exit Su

Err_cmdNext_Click
MsgBox Err.Descriptio
Resume Exit_cmdNext_Clic

End Su

Thank you for any and all help

Willia

P.S. A "luxury" at this point would be if the user got a "end of record
error message when they tried to click on the Next button but there were n
records left
 
Trias,

Thank you very much for your reply. I tried your code and got a message box
with this text: "0:" even though there are about 100 records in the record
set. So I took the code out after the line "rst.MoveNext" and tried that.
It works...sort of. The next button moves through the recordset in the
subform [sfrmSearchResults] (which remains open) but does not move to the
next record in the current form [frmDetails]. I forgot to mention these are
all pop up forms in my original post....not sure if that makes a difference
or not. It feels like I'm heading in the right direction! Any ideas on
what could be wrong?

William



Trias said:
Hi William,

you need to get the Recordset Object from the [sfrmSearchResults] form and
Click to expand...
then execute MoveNext/MovePrev method of the Recordset.
Put something like this on the Next button Click event:

on error resume next
Dim rst As DAO.Recordset
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordset
rst.MoveNext
if err = 3021 then '
msgbox "You pass the last record or at new record"
else
msgbox err & ": " & err.description
end if
err.clear


hope this help
----- William Wisnieski wrote: -----

Hello Everyone,

Access 2000,

I have a main unbound form [frmSearch] with a bound datasheet subform
[sfrmSearchResults]. The subform is bound to a query [qrySearchResults]
that returns records based on criteria in the main form. The user then
double clicks a row on the datasheet subform to open yet another form
[frmDetails] bound to a table [tblStudents]. These two forms are linked by
the [StudentID] field. So far so good.

Here' is where I'm stumped. I'd like to put a Next Record navigation button
on the [frmDetails] form so the user can go to the next record of the query
results listed in the datasheet form [sfrmSearchResults] instead of closing
the form and going to the next record of the datasheet. Does anyone know
the code for that? Here is what I've tried to no avail. I get a "compile
error--expected array."

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click

DoCmd.GoToRecord , , acNext([StudentID] =
[Forms]![frmSearch]![sfrmSearchResults]![StudentID])

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub

Thank you for any and all help.

William

P.S. A "luxury" at this point would be if the user got a "end of record"
error message when they tried to click on the Next button but there were no
records left.
Click to expand...
 
Hi William

glad if it helps you. I make a mistake in the first code, so here is the correct code which will not open "0" msgbox and to sync. frmDetails data. Note: I am assuming you open frmDetails using the following code: Docmd.Openform "frmDetails", , , "StudentID=" & me.StudentI

on error resume nex
Dim rst As DAO.Recordse
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordse
rst.MoveNex
if err<>0 then ' ADD this code
if err = 3021 then
msgbox "You pass the last record or at new record
'close FrmDetails and return to subfor
docmd.clos
els
msgbox err & ": " & err.descriptio
end if
err.clea
exit su
end if 'ADD this cod

'Sync FrmDetail
me.filteron=fals
me.filter ="StudentID=" & rst.fields("StudentID"
me.filter = tru

'Clean u
rst.clos
set rst = nothin

HTH
----- William Wisnieski wrote: ----

Trias

Thank you very much for your reply. I tried your code and got a message bo
with this text: "0:" even though there are about 100 records in the recor
set. So I took the code out after the line "rst.MoveNext" and tried that
It works...sort of. The next button moves through the recordset in th
subform [sfrmSearchResults] (which remains open) but does not move to th
next record in the current form [frmDetails]. I forgot to mention these ar
all pop up forms in my original post....not sure if that makes a differenc
or not. It feels like I'm heading in the right direction! Any ideas o
what could be wrong

Willia



Trias said:
Hi William
you need to get the Recordset Object from the [sfrmSearchResults] form an then execute MoveNext/MovePrev method of the Recordset
Put something like this on the Next button Click event
on error resume nex
Click to expand...
Dim rst As DAO.Recordse
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordse
rst.MoveNex
if err = 3021 then
msgbox "You pass the last record or at new record
els
msgbox err & ": " & err.descriptio
end i
err.clea----- William Wisnieski wrote: ----
Hello Everyone
Access 2000
I have a main unbound form [frmSearch] with a bound datasheet subfor
Click to expand...
[sfrmSearchResults]. The subform is bound to a quer [qrySearchResults
that returns records based on criteria in the main form. The use the
double clicks a row on the datasheet subform to open yet another for
[frmDetails] bound to a table [tblStudents]. These two forms ar linked b
the [StudentID] field. So far so good
Here' is where I'm stumped. I'd like to put a Next Record navigatio
Click to expand...
butto
on the [frmDetails] form so the user can go to the next record of th quer
results listed in the datasheet form [sfrmSearchResults] instead o closin
the form and going to the next record of the datasheet. Does anyon kno
the code for that? Here is what I've tried to no avail. I get "compil
error--expected array.
Private Sub cmdNext_Click( On Error GoTo Err_cmdNext_Clic
DoCmd.GoToRecord , , acNext([StudentID] [Forms]![frmSearch]![sfrmSearchResults]![StudentID]
Exit_cmdNext_Click Exit Su
Err_cmdNext_Click
Click to expand...
MsgBox Err.Descriptio
Resume Exit_cmdNext_Clic
End Su
Thank you for any and all help
Willia
P.S. A "luxury" at this point would be if the user got a "end o
Click to expand...
record
error message when they tried to click on the Next button but there were no
records left.
Click to expand...
 
the better code looks like this:
Dim rst As DAO.Recordset
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordset
rst.MoveNext
if rst.eof then 'this checks whether you are in new record in subform
msgbox "You pass the last record or at new record"
'close the FrmDetails
docmd.close
exit sub
end if

me.FilterOn = false
me.filter = "StudentID=" & rst.Fields("StudentID")
me.FilterOn = True

'clean up
rst.close
set rs=nothing
end sub

HTH

----- William Wisnieski wrote: -----

Trias,

Thank you very much for your reply. I tried your code and got a message box
with this text: "0:" even though there are about 100 records in the record
set. So I took the code out after the line "rst.MoveNext" and tried that.
It works...sort of. The next button moves through the recordset in the
subform [sfrmSearchResults] (which remains open) but does not move to the
next record in the current form [frmDetails]. I forgot to mention these are
all pop up forms in my original post....not sure if that makes a difference
or not. It feels like I'm heading in the right direction! Any ideas on
what could be wrong?

William



Trias said:
Hi William,
you need to get the Recordset Object from the [sfrmSearchResults] form and then execute MoveNext/MovePrev method of the Recordset.
Put something like this on the Next button Click event:
on error resume next
Click to expand...
Dim rst As DAO.Recordset
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordset
rst.MoveNext
if err = 3021 then '
msgbox "You pass the last record or at new record"
else
msgbox err & ": " & err.description
end if
err.clear----- William Wisnieski wrote: -----
Hello Everyone,
Access 2000,
I have a main unbound form [frmSearch] with a bound datasheet subform
Click to expand...
[sfrmSearchResults]. The subform is bound to a query [qrySearchResults]
that returns records based on criteria in the main form. The user then
double clicks a row on the datasheet subform to open yet another form
[frmDetails] bound to a table [tblStudents]. These two forms are linked by
the [StudentID] field. So far so good.
Here' is where I'm stumped. I'd like to put a Next Record navigation
Click to expand...
button
on the [frmDetails] form so the user can go to the next record of the query
results listed in the datasheet form [sfrmSearchResults] instead of closing
the form and going to the next record of the datasheet. Does anyone know
the code for that? Here is what I've tried to no avail. I get a "compile
error--expected array."
Private Sub cmdNext_Click()
Click to expand...
On Error GoTo Err_cmdNext_Click
DoCmd.GoToRecord , , acNext([StudentID] = [Forms]![frmSearch]![sfrmSearchResults]![StudentID])
Exit_cmdNext_Click: Exit Sub
Err_cmdNext_Click:
Click to expand...
MsgBox Err.Description
Resume Exit_cmdNext_Click
End Sub
Thank you for any and all help.
William
P.S. A "luxury" at this point would be if the user got a "end of
Click to expand...
record"
error message when they tried to click on the Next button but there were no
records left.
Click to expand...
 
Back
Top