Save Record Pointer or Remember Date?

[toddvb]

Please help an old X-baser who’s out-of-touch !

In Dbase/DBMAN/KMAN we used to be able to save variables to disk inside the database. This allowed me to start the application with the record pointer at the same position on subsequent runs.

Short of writing to a text file outside the DB, I haven’t found a way to do this in Access 2000.

Have many apps where I filter data based on selected month/year. Would like to open DB with the same M/Y still selected. I’d have thought this to be a common request and addressed neatly within Access (like a tie-in to the MonthView ActiveX control that recalls last state), but I can’t find any examples in the Help system or KB. I’m hope I’m just using the wrong logic or terminology.

Someone suggested everything should be in tables, so I currently use a single-record table called “CurrentStuff” to hold selected month/year. This doesn’t seem the most efficient answer, and creates a problem in Queries if the date info isn’t broken-out into separate fields in the main table (if I use calc or SQL to equate fields I can’t update table via query) as in the SQL example below where I try to relate a month [CurrentStuff.CurrentMonth] to the month of a full date [DatePart("m",[Extended]] :

SELECT Tasks.[Client Name], Tasks.Description, Tasks.[Month Due], Tasks.[Day Due], Tasks.Extended, Tasks.OutDate, Tasks.Partner, Tasks.[In-Charge], Tasks.Remarks
FROM Tasks INNER JOIN CurrentStuff ON Tasks.[Month Due] = CurrentStuff.CurrentMonth OR ((DatePart("m",[Extended]))=CurrentStuff.CurrentMonth)
ORDER BY Tasks.[Client Name], Tasks.[Month Due], Tasks.[Day Due];

This works as expected, but doesn't allow edits.

Sorry to be so long-winded.

Any thoughts would be much appreciated.

 

[FancyPrairie]

There are several way you can do this:

1. Set the RowSource of the form equal to a query that selects all of the records in the table. Then apply a filter in the OnOpen event of the form. Something like this:

RowSource..."Select * from Tasks;"

Sub Form_Open()

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset ("Select CurrentMonth From CurrentStuff;&quot

Me.Filter = " [Month Due] = " & CurrentMonth
Me.ApplyFilter = True

rst.Close
Set rst = Nothing

End Sub

2. Another way to do it is to set you RowSource Programmatically via the OnOpen event of the form. Something like this:

Sub Form_Open()

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset ("Select CurrentMonth From CurrentStuff;&quot

Me.RowSource = "Select * from Tasks Where [Month Due] = " & rst!CurrentMonth

rst.Close
Set rst = Nothing

End Sub

 

[toddvb]

Thank you very much for the quick reply.

I will attempt to apply your logic to my predicament, but I seem to have shot myself in the foot from the get-go.

I meant to post this to several forums, but not this one!

Most of my apps for this don't involve FORMS at all.

Sorry for any trouble.

Todd