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.

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

[DFS]

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

Forms.frmSearch.sfrmSearchResults.Form.StudentID
"MGFoster" <[e-mail]> wrote in message
news:tY******************@newsread1.news.pas.earth link.net...

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


"MGFoster" <[e-mail]> wrote in message
news:tY******************@newsread1.news.pas.earth link.net...

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