Next Record in Query Results via Form

[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.

 

[Trias]

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

 

[William Wisnieski]

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

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
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.

 

[Trias]

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

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

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

[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

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

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

record
error message when they tried to click on the Next button but there were no
records left.

 

[Trias]

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

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

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

[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.

 

[William Wisnieski]

Thanks again Trias. Getting closer. Here's what happens when I used your
new code:

I can move to the second record just fine. But what happens is it clears
out the query results in the datasheet subform and the only thing remaining
is #Name in all of the fields of one row. When I click the Next button
again, I get a "run time error 91 Object Variable or With block variable not
set" error message. When I click debug it goes to rstMoveNext in the code,
which is highlighted in yellow. It looks like it wipes out the record set
after I move to the next record. Any ideas?

William

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

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

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.

 

[Trias]

Hi William,

i've created a db with which i try the code. I'll send the db (in zip format) to you if you want so that you can try it and compare it with your db. Let me know where to send and here is my mail [email protected]

But did you use this code:

Private cmdNext_Click()
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
else
me.FilterOn = false
me.filter = "StudentID=" & rst.Fields("StudentID")
me.FilterOn = True
'clean up
end if
rst.close
set rs=nothing
end sub

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

Thanks again Trias. Getting closer. Here's what happens when I used your
new code:

I can move to the second record just fine. But what happens is it clears
out the query results in the datasheet subform and the only thing remaining
is #Name in all of the fields of one row. When I click the Next button
again, I get a "run time error 91 Object Variable or With block variable not
set" error message. When I click debug it goes to rstMoveNext in the code,
which is highlighted in yellow. It looks like it wipes out the record set
after I move to the next record. Any ideas?

William

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" <[email protected]> wrote in message

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

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.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm assuming you have the frmDetails RecordSource property as:
tblStudents, correct? If so, you can change the RecordSource to an
SQL statement like this (watch out for line-wrap):

SELECT * FROM tblStudents WHERE StudentID =
[Forms]![frmSearch]![sfrmSearchResults]![StudentID]

Then when the user clicks another record in the subform
sfrmSearchResults the form frmDetails will update to the correct
record. You may have to do a form Requery for frmDetails in the
sfrmSearchResults OnCurrent event procedure. E.g.:

Forms!frmDetails.Requery

This means both forms should be visible to the user at the same time.

If there isn't a matching record in the tblStudents the frmDetails
will have empty controls. You can set the frmDetails OnCurrent event
to change the form's Title Bar like this:

Me.Caption = "Student ID: " & IIf(IsNull(Me!StudentID), _
"No Record",Me!StudentID)

This will tell the user if a record is present or not.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP84rm4echKqOuFEgEQJBUgCeOGGrrqW0/BrIapd+I6M05oG0qasAoOLv
c/0SEKbzFnUWHl5xOItZFrhf
=OHzo
-----END PGP SIGNATURE-----

 

[DFS]

You left off the "Form" in your subform reference. It should be (dropping
brackets):

Forms.frmSearch.sfrmSearchResults.Form.StudentID

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm assuming you have the frmDetails RecordSource property as:
tblStudents, correct? If so, you can change the RecordSource to an
SQL statement like this (watch out for line-wrap):

SELECT * FROM tblStudents WHERE StudentID =
[Forms]![frmSearch]![sfrmSearchResults]![StudentID]

Then when the user clicks another record in the subform
sfrmSearchResults the form frmDetails will update to the correct
record. You may have to do a form Requery for frmDetails in the
sfrmSearchResults OnCurrent event procedure. E.g.:

Forms!frmDetails.Requery

This means both forms should be visible to the user at the same time.

If there isn't a matching record in the tblStudents the frmDetails
will have empty controls. You can set the frmDetails OnCurrent event
to change the form's Title Bar like this:

Me.Caption = "Student ID: " & IIf(IsNull(Me!StudentID), _
"No Record",Me!StudentID)

This will tell the user if a record is present or not.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP84rm4echKqOuFEgEQJBUgCeOGGrrqW0/BrIapd+I6M05oG0qasAoOLv
c/0SEKbzFnUWHl5xOItZFrhf
=OHzo
-----END PGP SIGNATURE-----

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.

 

[William Wisnieski]

Thanks MG.

I tried your code but no luck. It opened to the proper record, but when I
clicked next it went to a blank record. Also, although both forms are open,
I only want the frmDetails form, which is on top, to be the one the user
clicks the next button. Otherwise the way I have it is now, they have to
close
the Details form then go to the next record in the datasheet subform
[sfrmSearchResults] and doubleclick it to open the record in frmDetails.

William

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm assuming you have the frmDetails RecordSource property as:
tblStudents, correct? If so, you can change the RecordSource to an
SQL statement like this (watch out for line-wrap):

SELECT * FROM tblStudents WHERE StudentID =
[Forms]![frmSearch]![sfrmSearchResults]![StudentID]

Then when the user clicks another record in the subform
sfrmSearchResults the form frmDetails will update to the correct
record. You may have to do a form Requery for frmDetails in the
sfrmSearchResults OnCurrent event procedure. E.g.:

Forms!frmDetails.Requery

This means both forms should be visible to the user at the same time.

If there isn't a matching record in the tblStudents the frmDetails
will have empty controls. You can set the frmDetails OnCurrent event
to change the form's Title Bar like this:

Me.Caption = "Student ID: " & IIf(IsNull(Me!StudentID), _
"No Record",Me!StudentID)

This will tell the user if a record is present or not.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP84rm4echKqOuFEgEQJBUgCeOGGrrqW0/BrIapd+I6M05oG0qasAoOLv
c/0SEKbzFnUWHl5xOItZFrhf
=OHzo
-----END PGP SIGNATURE-----

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.

 

[William Wisnieski]

Yes, I used your code exactly as you typed it but still get the error
message I mentioned in my previous post. Do you think the code that is
used to open the Details form is interfering? There is code in the
doubleclick event of the sfrmSearchResults datasheet that opens up the
Details form to the corresponding record which is this:


Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_DblClick_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDetails"

stLinkCriteria = "[StudentID]=" & Me![StudentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Form_DblClick:
Exit Sub

Err_DblClick_Click:
MsgBox Err.Description
Resume Exit_Form_DblClick

End Sub

I would definitely appreciate taking a look at the db you mentioned. Here
is my email: [email protected]. Thank you!

William

Hi William,

i've created a db with which i try the code. I'll send the db (in zip

format) to you if you want so that you can try it and compare it with your
db. Let me know where to send and here is my mail [email protected]

But did you use this code:

Private cmdNext_Click()
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
else
me.FilterOn = false
me.filter = "StudentID=" & rst.Fields("StudentID")
me.FilterOn = True
'clean up
end if
rst.close
set rs=nothing
end sub

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

Thanks again Trias. Getting closer. Here's what happens when I used your
new code:

I can move to the second record just fine. But what happens is it clears
out the query results in the datasheet subform and the only thing remaining
is #Name in all of the fields of one row. When I click the Next button
again, I get a "run time error 91 Object Variable or With block variable not
set" error message. When I click debug it goes to rstMoveNext in the code,
which is highlighted in yellow. It looks like it wipes out the record set
after I move to the next record. Any ideas?

William

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.filter = "StudentID=" & rst.Fields("StudentID")
me.FilterOn = True
rst.close
set rs=nothing
end sub

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" <[email protected]> wrote in

message

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
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.

 

[Trias]

Hi

the way you open the frmDetails is as i excpected. Check your email in 15 min cause i just send my db

HTH

 

[William Wisnieski]

Many thanks to all of you who helped with this, particularly Trias. The
code he supplied in his sample database worked perfectly. Here it is:

Private Sub Command6_Click()
Dim rst As DAO.Recordset

On Error Resume Next
Set rst = Forms("frm1").Y.Form.Recordset

rst.MoveNext

If rst.EOF Then
MsgBox "End of Record at SUBFORM or New Record"
DoCmd.Close
Exit Sub
End If

Me.FilterOn = False
Me.Filter = "STUDENTID=" & rst.Fields(0)
Me.FilterOn = True

Set rst = Nothing
End Sub

Thanks Again,

William