Open a Form showing the same Record as when closed

[Mike Wilson]

Help Please

I have created a table that holds an OrderNo
(AlphaNumeric) that is updated on unload.
I want to be able to open the form at the same record
but I am not having much success.
The event properties are :

Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset

If Not IsNull(Me.OrderNo) Then
Set rs = CurrentDb().OpenRecordset("tblSys",
dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'OrderNoLast'"
If .NoMatch Then
.Addnew
![Variable] = "OrderNoLast"
![Value] = Me.OrderNo
![Description] = "OrderNo, for form " &
Me.OrderNo
.Update
Else
.Edit
![Value] = Me.OrderNo
.Update
End If
End With
rs.Close
End If
Set rs = Nothing
End Sub


Private Sub Form_Load()
Dim varId As Variant
varId = DLookup("Value", "tblSys", "[Variable] =OrderNo")
If IsNumeric(varId) Then
With Me.RecordsetClone
..FindFirst "[OrderNo] = " & varId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

I would be grateful for any help

Thanks,

Mike

 

[Gerald Stanley]

Try
varId = DLookup("Value", "tblSys", "[Variable] =
'OrderNoLast' ")

Hope This Helps
Gerald Stanley MCSD

 

[Mike]

Thanks for the response Gerald, unfortunately no joy.

I'll look around again for another solution and repost when I find it

Many thanks for the help

Mike

Try
varId = DLookup("Value", "tblSys", "[Variable] =
'OrderNoLast' ")

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Help Please

I have created a table that holds an OrderNo
(AlphaNumeric) that is updated on unload.
I want to be able to open the form at the same record
but I am not having much success.
The event properties are :

Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset

If Not IsNull(Me.OrderNo) Then
Set rs = CurrentDb().OpenRecordset("tblSys",
dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'OrderNoLast'"
If .NoMatch Then
.Addnew
![Variable] = "OrderNoLast"
![Value] = Me.OrderNo
![Description] = "OrderNo, for form " &
Me.OrderNo
.Update
Else
.Edit
![Value] = Me.OrderNo
.Update
End If
End With
rs.Close
End If
Set rs = Nothing
End Sub


Private Sub Form_Load()
Dim varId As Variant
varId = DLookup("Value", "tblSys", "[Variable] =OrderNo")
If IsNumeric(varId) Then
With Me.RecordsetClone
..FindFirst "[OrderNo] = " & varId
If Not .NoMatch Then
Me.Bookmark = .Bookmark