Open a Form showing the same Record as when closed

M

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
 
G

Gerald Stanley

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

Hope This Helps
Gerald Stanley MCSD
 
M

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



gcstanley@d-s- said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

open to same record as when form was closed last 6
Making a macro only work on certain files 2
Word macro that can call cells out of Excel for a find and replace 0
Dynamic Naming of Form Button 3
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Select Found Subform Record 0
Button on Form Appends Current Record to Another Table 3
Drop Down doesn't populate form 0

Top