Next and Back functionality?

[Cardinal]

I have an Access 2003 form that displays four computer labs and what
is scheduled in each room for a given day. At the top of the form is
todays date and below that are 4 data sheets (one for each lab)
showing the events taking place in the lab for that day. What would
make this even more useful is if at the top of the screen there was a
next and previous button. Therefore if someone clicked the "next"
button, it would display the next days activities for each lab and so
on. Does anyone have an idea how I might do this?

The underlying table is called RoomReservations and these are its
fields:

EventID AutoNumber
EventName Text
Location Text
ContactName Text
StartDate Date/Time
EndDate Date/Time
StartTime Number
EndTime Number
AvailableSpaces Number
Notes Memo

Thanks very much.

 

[John Spencer]

Would you care to show us the query you are using right now to display the
current data? It seems as if all you would need would be to modify the where
clause of the existing query. Or to change the filter on the form if you are
using a filter to get the records for the current date.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Wayne-I-M]

Base the form on a query sorted by date

Add a next record with error handeling

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click
DoCmd.GoToRecord , , acNext
Exit_ButtonName_Click:
Exit Sub
Err_ButtonName_Click:
MsgBox "This is the last record", vbOKOnly, "Please stop pressing me"
Resume Exit_ButtonName_Click


and back record button with error handeling

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click
DoCmd.GoToRecord , , acPrevious
Exit_ButtonName_Click:
Exit Sub
Err_ButtonName_Click:
MsgBox "This is the last record", vbOKOnly, "Please stop pressing me"
Resume Exit_ButtonName_Click

 

[Cardinal]

Would you care to show us the query you are using right now to display the
current data?  It seems as if all you would need would be to modify thewhere
clause of the existing query. Or to change the filter on the form if you are
using a filter to get the records for the current date.

My Query for one of the rooms is as follows.

SELECT RoomReservations.EventName, RoomReservations.Location,
RoomReservations.ContactName, RoomReservations.StartTime,
RoomReservations.EndTime
FROM RoomReservations
WHERE (((RoomReservations.Location)="DLR 1") AND
((RoomReservations.StartDate)=Date()))
ORDER BY RoomReservations.StartTime;

 

[Cardinal]

Base the form on a query sorted by date

Add a next record with error handeling

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click
    DoCmd.GoToRecord , , acNext
Exit_ButtonName_Click:
    Exit Sub
Err_ButtonName_Click:
    MsgBox "This is the last record", vbOKOnly, "Please stop pressingme"
    Resume Exit_ButtonName_Click

and back record button with error handeling

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click
    DoCmd.GoToRecord , , acPrevious
Exit_ButtonName_Click:
    Exit Sub
Err_ButtonName_Click:
    MsgBox "This is the last record", vbOKOnly, "Please stop pressingme"
    Resume Exit_ButtonName_Click

--

Thanks Wayne. I tied it to a query as you suggested and it did advance
it from record to record but the subforms with their data sheet views
did not change. Right now I have 4 subforms in the main form. Each
subform pulls data from a query created for it. Each query pulls
records for *todays* entries. At the top of the main form I have
todays date and the user sees four subforms. Each subform is a data
sheet listing showing what happens in that particular classroom for
that given day. Ideally when the user clicks the Next button the Date
at the top of the form would change to tomorrows date and the subforms
would all display tomorrows events in each of the subforms. Thanks.

 

[Wayne-I-M]

Thanks Wayne. I tied it to a query as you suggested and it did advance

it from record to record but the subforms with their data sheet views
did not change. Right now I have 4 subforms in the main form. Each
subform pulls data from a query created for it. Each query pulls
records for *todays* entries.

You need to link Master.Child each subform to the ID on the main form

I assume you have linked the tables or the introducted tables in the form's
query ???

If not thien do this 1st then link the subforms
Make sure you have the main form's ID on each of the sub forms or it will
not work


Good luck with your project

 

[John Spencer]

The way I might handle this is to add a control to the main form that stores
the date I want to see. I think you might already have that.

Add buttons to increment or decrement the value of the date control and
requery the subform controls when you do so.

Change the queries to read
SELECT RoomReservations.EventName, RoomReservations.Location,
RoomReservations.ContactName, RoomReservations.StartTime,
RoomReservations.EndTime
FROM RoomReservations
WHERE (((RoomReservations.Location)="DLR 1") AND
((RoomReservations.StartDate)=[Forms]![NameOfMainForm]![NameofDateControlOnMainForm])
ORDER BY RoomReservations.StartTime;


OR Change the queries so that they include the StartDate in the list of fields
and then bind the subforms to the mainform using the link child/link master
using the date control on the master and the StartDate in subform and leaving
out the date criteria in the subform query.

SELECT RoomReservations.EventName, RoomReservations.Location,
RoomReservations.ContactName, RoomReservations.StartTime,
RoomReservations.EndTime, StartDate
FROM RoomReservations
WHERE RoomReservations.Location="DLR 1"
ORDER BY RoomReservations.StartTime;

Then
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County