Saving current row on Form exit

I wish to save the current row on a form so that it can be
automatically selected the next time the form is opened. To do this,
I
have the following -


'save the current row
Private Sub Form_Close()
SQLString = " DELETE * FROM tblPreviousFormState;"
CurrentDb.Execute SQLString, dbFailOnError


SQLString = "INSERT INTO tblPreviousFormState(TableID) Values(" &
TableID & ");"
CurrentDb.Execute SQLString, dbFailOnError
End Sub


I'm sure there is some nice way of having a one row table but I'm not
sure about this...


TableID is the name of my text box where the row primary key is
displayed. The issue though is that TableID is always returning the
value 1 in Form_Close - I guess the control has been destroy to some
extent at this point. Anyway, I'm not sure if the above approach is a
good one anyway. Can any offer me a solution to this problem?


BTW, heres how I set the current row -


Private Sub Form_Open(Cancel As Integer)


'get the previous row
Dim recordSetPreviousFormState As DAO.Recordset
Set recordSetPreviousFormState = CurrentDb.OpenRecordset("Select
*
from tblPreviousFormState")


If recordSetPreviousFormState.RecordCount 0 Then
recordSetPreviousFormState.MoveFirst
Me.Recordset.FindFirst "TableID= " &
recordSetPreviousFormState.Fields("TableID")
End If


End Sub


Thanks again,


Aine.

Try using Form_Unload instead of Form_Close.

The approach you have should work. Here's a slightly different way to
approach the same thing:
Return to the same record next time form is opened
at:
http://allenbrowne.com/ser-18.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ps.com...
I wish to save the current row on a form so that it can be
automatically selected the next time the form is opened. To do this,
I
have the following -


'save the current row
Private Sub Form_Close()
SQLString = " DELETE * FROM tblPreviousFormState;"
CurrentDb.Execute SQLString, dbFailOnError


SQLString = "INSERT INTO tblPreviousFormState(TableID) Values(" &
TableID & ");"
CurrentDb.Execute SQLString, dbFailOnError
End Sub


I'm sure there is some nice way of having a one row table but I'm not
sure about this...


TableID is the name of my text box where the row primary key is
displayed. The issue though is that TableID is always returning the
value 1 in Form_Close - I guess the control has been destroy to some
extent at this point. Anyway, I'm not sure if the above approach is a
good one anyway. Can any offer me a solution to this problem?


BTW, heres how I set the current row -


Private Sub Form_Open(Cancel As Integer)


'get the previous row
Dim recordSetPreviousFormState As DAO.Recordset
Set recordSetPreviousFormState = CurrentDb.OpenRecordset("Select
*
from tblPreviousFormState")


If recordSetPreviousFormState.RecordCount 0 Then
recordSetPreviousFormState.MoveFirst
Me.Recordset.FindFirst "TableID= " &
recordSetPreviousFormState.Fields("TableID")
End If


End Sub


Thanks again,


Aine.