open to same record as when form was closed last

[The Mecca]

When I close my form I have a code to save to a table, the last record I
looked at - that one works fine- but when I want to open the form again. It
doesn't open to that record. help please?
My code is:

TO SAVE RECORD=
Private Sub Form_Load()
Dim varID As Variant
varID = DLookup("[Value]", "tblSys", "[Variable] ='Account NumberLast'")
If IsNumeric(varID) Then
With Me.RecordsetClone
.FindNext "[Account Number] = " & varID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub

TO OPEN SAME RECORD=
Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset

If Not IsNull(Me.[Account Number]) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'Account NumberLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "CustomerIDLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.[Account Number]
.Update
End If
End With
rs.Close
End If
Set rs = Nothing

End Sub

 

[Marshall Barton]

When I close my form I have a code to save to a table, the last record I
looked at - that one works fine- but when I want to open the form again. It
doesn't open to that record. help please?
My code is:

TO SAVE RECORD=
Private Sub Form_Load()
Dim varID As Variant
varID = DLookup("[Value]", "tblSys", "[Variable] ='Account NumberLast'")
If IsNumeric(varID) Then
With Me.RecordsetClone
.FindNext "[Account Number] = " & varID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub

TO OPEN SAME RECORD=
Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset

If Not IsNull(Me.[Account Number]) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'Account NumberLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "CustomerIDLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.[Account Number]
.Update
End If
End With
rs.Close
End If
Set rs = Nothing

End Sub

When you do the AddNew, you're setting the Variable field to
"CustomerIDLast"
but you are searching for records with Variable
"Account NumberLast"

In your case, it probably won't matter, but for clarity, I
suggest that you use FindFirst instead of FindNext.

 

[The Mecca]

it still isn't working. The saving part works- But it is not pulling up the
record that is in the table. or it is not dispaying it.

When you do the AddNew, you're setting the Variable field to
"CustomerIDLast"
but you are searching for records with Variable
"Account NumberLast"

In your case, it probably won't matter, but for clarity, I
suggest that you use FindFirst instead of FindNext.

--
Marsh
MVP [MS Access]

When I close my form I have a code to save to a table, the last record I
looked at - that one works fine- but when I want to open the form again. It
doesn't open to that record. help please?
My code is:

TO SAVE RECORD=
Private Sub Form_Load()
Dim varID As Variant
varID = DLookup("[Value]", "tblSys", "[Variable] ='Account NumberLast'")
If IsNumeric(varID) Then
With Me.RecordsetClone
.FindNext "[Account Number] = " & varID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub

TO OPEN SAME RECORD=
Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset

If Not IsNull(Me.[Account Number]) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'Account NumberLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "CustomerIDLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.[Account Number]
.Update
End If
End With
rs.Close
End If
Set rs = Nothing

End Sub

 

[Marshall Barton]

You said the saving worked before, did anything change since
then?

Did you double check the saved info against the code to
retrieve the desired record?

Have you set some break points to see what's happening as
the code executes? It's a lot easier for you to perform
common debugging operations than it is for someone else to
spot problems based on a code listing and a statement that
"it still isn't working". We, here in newsgroup land,
really need more clues to go on.

What does the code look like now?
--
Marsh
MVP [MS Access]

it still isn't working. The saving part works- But it is not pulling up the
record that is in the table. or it is not dispaying it.

When you do the AddNew, you're setting the Variable field to
"CustomerIDLast"
but you are searching for records with Variable
"Account NumberLast"

In your case, it probably won't matter, but for clarity, I
suggest that you use FindFirst instead of FindNext.

When I close my form I have a code to save to a table, the last record I
looked at - that one works fine- but when I want to open the form again. It
doesn't open to that record. help please?
My code is:

TO SAVE RECORD=
Private Sub Form_Load()
Dim varID As Variant
varID = DLookup("[Value]", "tblSys", "[Variable] ='Account NumberLast'")
If IsNumeric(varID) Then
With Me.RecordsetClone
.FindNext "[Account Number] = " & varID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub

TO OPEN SAME RECORD=
Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset

If Not IsNull(Me.[Account Number]) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'Account NumberLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "CustomerIDLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.[Account Number]
.Update
End If
End With
rs.Close
End If
Set rs = Nothing

End Sub

 

[The Mecca]

sorry newsgroup land!
Well, everything seemed too complex for what i was needing. In a nutshell- I
have saved the account number in a seperate table. New code looks like this.
and the code works great! it deletes the previous records- that way it won't
get confused! hehe

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery ("delete_form")
Dim rs As DAO.Recordset
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.AddNew
![Variable] = "Account NumberLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
End With
rs.Close
Set rs = Nothing
End Sub

----- Now I am just looking for a code that will look at the account number
in the 'tblSys' and pull up the record in the form when it first loads. I
want all the records to be there- just want to start with that one.
ex. I am working this report and I go to lunch. I close the database when
I'm on record # 45. when i come back from lunch, i open the form again and
start back where I left off....

You said the saving worked before, did anything change since
then?

Did you double check the saved info against the code to
retrieve the desired record?

Have you set some break points to see what's happening as
the code executes? It's a lot easier for you to perform
common debugging operations than it is for someone else to
spot problems based on a code listing and a statement that
"it still isn't working". We, here in newsgroup land,
really need more clues to go on.

What does the code look like now?
--
Marsh
MVP [MS Access]

it still isn't working. The saving part works- But it is not pulling up the
record that is in the table. or it is not dispaying it.

 

[Marshall Barton]

I liked your original Unload procedure better. Besides, you
said that part worked before. I don't see any reason for
you to have changed it.

Actually, the code you had in both procedures looked pretty
good before you went and changed it so I don't think my
eyeballing it will solve the problem, but I still need to
see it, both procedures, so I can try to find a reason for
it to not do what you want.

I also need any debugging information that might help pin
down why the search procedure is not working. Are you
avoiding this step because you do not know how to place
breakpoints and step through the code one line at a time?

At this point, I am wondering if there is another factor
involved. Maybe you are saving the value of more than one
form but are not looking for the right form to search on??

I just had another thought. The [Value] field isn't a Text
field is it? If it were, then the FindFirst should look
like:
.FindFirst "[Account Number] = '" & varID & "' "
The way you have it, requires the [Value] field to be a
numeric type of field.
--
Marsh
MVP [MS Access]

Well, everything seemed too complex for what i was needing. In a nutshell- I
have saved the account number in a seperate table. New code looks like this.
and the code works great! it deletes the previous records- that way it won't
get confused! hehe

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery ("delete_form")
Dim rs As DAO.Recordset
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.AddNew
![Variable] = "Account NumberLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
End With
rs.Close
Set rs = Nothing
End Sub

----- Now I am just looking for a code that will look at the account number
in the 'tblSys' and pull up the record in the form when it first loads. I
want all the records to be there- just want to start with that one.
ex. I am working this report and I go to lunch. I close the database when
I'm on record # 45. when i come back from lunch, i open the form again and
start back where I left off....

You said the saving worked before, did anything change since
then?

Did you double check the saved info against the code to
retrieve the desired record?

Have you set some break points to see what's happening as
the code executes? It's a lot easier for you to perform
common debugging operations than it is for someone else to
spot problems based on a code listing and a statement that
"it still isn't working". We, here in newsgroup land,
really need more clues to go on.

What does the code look like now?
--
Marsh
MVP [MS Access]

it still isn't working. The saving part works- But it is not pulling up the
record that is in the table. or it is not dispaying it.

 

[The Mecca]

IT WORKED!!!!! I changed it back to my original code- and replaced your
FindFirst statement. It was all because my Account Numbers were stored as
text.. good find! THANK YOU! THANK YOU! THANK YOU! You have no idea how many
hours I've been working on this problem!

I liked your original Unload procedure better. Besides, you
said that part worked before. I don't see any reason for
you to have changed it.

Actually, the code you had in both procedures looked pretty
good before you went and changed it so I don't think my
eyeballing it will solve the problem, but I still need to
see it, both procedures, so I can try to find a reason for
it to not do what you want.

I also need any debugging information that might help pin
down why the search procedure is not working. Are you
avoiding this step because you do not know how to place
breakpoints and step through the code one line at a time?

At this point, I am wondering if there is another factor
involved. Maybe you are saving the value of more than one
form but are not looking for the right form to search on??

I just had another thought. The [Value] field isn't a Text
field is it? If it were, then the FindFirst should look
like:
.FindFirst "[Account Number] = '" & varID & "' "
The way you have it, requires the [Value] field to be a
numeric type of field.
--
Marsh
MVP [MS Access]

Well, everything seemed too complex for what i was needing. In a nutshell- I
have saved the account number in a seperate table. New code looks like this.
and the code works great! it deletes the previous records- that way it won't
get confused! hehe

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery ("delete_form")
Dim rs As DAO.Recordset
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.AddNew
![Variable] = "Account NumberLast"
![Value] = Me.[Account Number]
![Description] = "Last Account Number, for form " & Me.Name
.Update
End With
rs.Close
Set rs = Nothing
End Sub

----- Now I am just looking for a code that will look at the account number
in the 'tblSys' and pull up the record in the form when it first loads. I
want all the records to be there- just want to start with that one.
ex. I am working this report and I go to lunch. I close the database when
I'm on record # 45. when i come back from lunch, i open the form again and
start back where I left off....

You said the saving worked before, did anything change since
then?

Did you double check the saved info against the code to
retrieve the desired record?

Have you set some break points to see what's happening as
the code executes? It's a lot easier for you to perform
common debugging operations than it is for someone else to
spot problems based on a code listing and a statement that
"it still isn't working". We, here in newsgroup land,
really need more clues to go on.

What does the code look like now?
--
Marsh
MVP [MS Access]


The Mecca wrote:
it still isn't working. The saving part works- But it is not pulling up the
record that is in the table. or it is not dispaying it.