Opening a form at a particular record in the set

[Kev365428]

Hi,

This might seem like an obvious question, but I can't find an answer that satisfies my requirements anywhere.

I have a table with a schedule of tasks that are required weekly for the next year. Each record has Monday's date as the Scheduled_Date" for the task. From my main form, I have a command button to open a form linked to the table, so that amendments can be added to the table for each record (i.e. - If a task is complete/incomplete/progressing etc. the user marks it so.

What I would like to do is have the form open at the task scheduled for the current week, based on the "Scheduled_Date" value. Whilst I can do this with a simple filter, I would like the form to open with all records available to the user so that they can go forwards/backwards as they wish, but the first record they see when the form opens is the one specific to this weeks date.

I'm using Access 2010.

Hope this makes sense. Any help greatly appreciated.

Cheers,

Kev.

 

[CJ_London]

but the first record they see when the form opens is the one specific to this weeks date.

if the form is a single form then in the form load event put

me.recordset.findfirst "Scheduled_Date =" & date()

If it is continuous, the above code will still work but the record will be at the bottom of the continuous form window. To move it to the top, you would need to know a) how many rows are visible and b) how many records between the one you want and the last record in the recordset. You would then need an algorithm to calculate how many times the record needs to or can move up to be at the top and use this to iterate a movenext e.g

 for I=1 to calced num
     me.recordset.movenext 
 next i

 

[Kev365428]

Thanks for the fast reply CJ.
I tried your code suggestion in the load event for the form, but it failed to open at the record with a Schduled Date for this week (Feb 9th). Instead it opened at the first record in the table.

Any ideas?

Kev.

The form is set as a single form view

 

[CJ_London]

are there any scheduled records for this week? - more importantly dated today (Feb 9th)?

you could try moving the code to another form event such as activate

temporarily, create a button on your form and put the following in the click event

form_open

or to wherever you have moved the code

then when you click it should run the code so you can check it is working

you may need to put me.refresh after the movefirst line to refresh the form

 

[CJ_London]

see this link for more info on findfirst
https://msdn.microsoft.com/en-us/library/bb221033(v=office.12).aspx