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
[sfrmSearchResul ts]. The subform is bound to a query [qrySearchResult s]
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 [sfrmSearchResul ts] 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_Cli ck

DoCmd.GoToRecor d , , acNext([StudentID] =
[Forms]![frmSearch]![sfrmSearchResul ts]![StudentID])

Exit_cmdNext_Cl ick:
Exit Sub

Err_cmdNext_Cli ck:
MsgBox Err.Description
Resume Exit_cmdNext_Cl ick

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]![sfrmSearchResul ts]![StudentID]

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

Forms!frmDetail s.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!S tudentID), _
"No Record",Me!Stud entID)

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/AwUBP84rm4echKq OuFEgEQJBUgCeOG GrrqW0/BrIapd+I6M05oG0 qasAoOLv
c/0SEKbzFnUWHl5xO ItZFrhf
=OHzo
-----END PGP SIGNATURE-----
William Wisnieski wrote:

Hello Everyone,

Access 2000,

I have a main unbound form [frmSearch] with a bound datasheet subform
[sfrmSearchResul ts]. The subform is bound to a query [qrySearchResult s]
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 [sfrmSearchResul ts] 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_Cli ck

DoCmd.GoToRecor d , , acNext([StudentID] =
[Forms]![frmSearch]![sfrmSearchResul ts]![StudentID])

Exit_cmdNext_Cl ick:
Exit Sub

Err_cmdNext_Cli ck:
MsgBox Err.Description
Resume Exit_cmdNext_Cl ick

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 .sfrmSearchResu lts.Form.Studen tID
"MGFoster" <[e-mail] > wrote in message
news:tY******** **********@news read1.news.pas. earthlink.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]![sfrmSearchResul ts]![StudentID]

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

Forms!frmDetail s.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!S tudentID), _
"No Record",Me!Stud entID)

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/AwUBP84rm4echKq OuFEgEQJBUgCeOG GrrqW0/BrIapd+I6M05oG0 qasAoOLv
c/0SEKbzFnUWHl5xO ItZFrhf
=OHzo
-----END PGP SIGNATURE-----
William Wisnieski wrote:

Hello Everyone,

Access 2000,

I have a main unbound form [frmSearch] with a bound datasheet subform
[sfrmSearchResul ts]. The subform is bound to a query [qrySearchResult s]
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 [sfrmSearchResul ts] 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_Cli ck

DoCmd.GoToRecor d , , acNext([StudentID] =
[Forms]![frmSearch]![sfrmSearchResul ts]![StudentID])

Exit_cmdNext_Cl ick:
Exit Sub

Err_cmdNext_Cli ck:
MsgBox Err.Description
Resume Exit_cmdNext_Cl ick

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
[sfrmSearchResul ts] and doubleclick it to open the record in frmDetails.

William


"MGFoster" <[e-mail] > wrote in message
news:tY******** **********@news read1.news.pas. earthlink.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]![sfrmSearchResul ts]![StudentID]

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

Forms!frmDetail s.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!S tudentID), _
"No Record",Me!Stud entID)

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/AwUBP84rm4echKq OuFEgEQJBUgCeOG GrrqW0/BrIapd+I6M05oG0 qasAoOLv
c/0SEKbzFnUWHl5xO ItZFrhf
=OHzo
-----END PGP SIGNATURE-----
William Wisnieski wrote:

Hello Everyone,

Access 2000,

I have a main unbound form [frmSearch] with a bound datasheet subform
[sfrmSearchResul ts]. The subform is bound to a query [qrySearchResult s]
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 [sfrmSearchResul ts] 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_Cli ck

DoCmd.GoToRecor d , , acNext([StudentID] =
[Forms]![frmSearch]![sfrmSearchResul ts]![StudentID])

Exit_cmdNext_Cl ick:
Exit Sub

Err_cmdNext_Cli ck:
MsgBox Err.Description
Resume Exit_cmdNext_Cl ick

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.