Storing a value for later use in vba or a Macro

[Jack G]

I think this must be a very basic and simple question, but I'm stumped: How
do you programmatically store a value from one record to use in another
record? For example, say I have a form displayed which has a customer name
field in it with the name 'Joe Smith'. If I press a button for 'New Record
with Same Customer', I'd like Access to remember that name and fill it in on
the new record in a macro or vba procedure. I don't know if I have to store
Joe Smith on a form or a table somewhere, or if I can just define a
variable.

And a related question is: How could I store the most recently viewed record
and then have Access automatically start at that same record the next time I
open it?

Thanks for any guidance.

Jack

 

[Guest]

In the Click event of the button you described:

Dim strCurrentCustomer as String

strCurrentCustomer = Me.txtCustomer
Docmd.GotoRecord acNewRec
Me.txtCustomer = strCurrentCustomer

Now, the last record viewed is a little different. The issue here is which
user viewed which record last. If it is not a multi user system, it is a
little easier, but I will describe the multi user scenerio.

First, you will need a table with 3 fields (assuming you want to be able to
do this in multiple forms). Those fields are:
UserName
Used to hold the name of the user
FormName
The form the user was in
RecordIdentifier
A unique identifier that can be used to find the record

This will involve two Events in the form. The Load event, to determine
whether the user has ever been in this form before and whether the last
record viewed still existis. If the user has never been in the form or the
last record viewed has been deleted, it will start at the first record as
normal. Then in the form Close event, save the information to the table so
it will be available for the next session.

Since I don't know how you will keep track of user names, I will use a dummy
function called FindUserName. You can replace it with whatever you need.

Load.

strLastViewed = Nz(DLookup("[RecordIdentifier]", "tblLastViews", _
"[UserName] = '" & FindUserName() & "' And [FormName] = '" & _
Me.Name & "'"),"")

If Len(strLastViewed) > 0 Then
Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & strLastViewed & "'"
If Not rst.NoMatch Then
Me.BookMark = rst.BookMark
End If
Set rst = Nothing
End If

In the Close event:

Dim strUser as String
Dim strForm as String

strUser = FindUserName
strForm = Me.Name

If (DCount("*", "tblLastViews", "[UserName] = '" & strUser & _
"' And [FormName] = '" & strForm & "'") = 0 Then
CurrentDb.Execute("INSERT INTO tblLastViews (UserName, FormName, _
RecordIdentifier) VALUES (" & FindUserName() & ", " & _
Me.Name & ", " & Me.txtSomeField & ");"), dbFailOnError
Else
CurrentDb.Execute("UPDATE [tblLastViews] " & _
"SET [tblLastViews].[UserName] = " & strUser & _
", [tblLastViews].[FormName] = " & strForm & _
", [tblLastViews].[RecordIdentifier] = " & Me.txtSomeField & _
" WHERE [tblLastViews].[UserName] = " & strUser & _
" And [tblLastViews].[FormName] = " & strForm & ";"),
dbFailOnError
End If

The above (particularly the SQL) is untested "air" code, so I am sure it
will take some tweeking, but at least this should give you the general idea.

 

[Jack G]

Thanks for your detailed, helpful answer! I think this will do what I need.

One thing I wonder about -- when I split my database and put the tables on
the server, could I keep the table you're describing here at the local
computer and then not worry about recording the user name? I haven't tried
splitting the database yet, so I don't know if you can keep one of the
tables with the queries and forms or not.

Jack

In the Click event of the button you described:

Dim strCurrentCustomer as String

strCurrentCustomer = Me.txtCustomer
Docmd.GotoRecord acNewRec
Me.txtCustomer = strCurrentCustomer

Now, the last record viewed is a little different. The issue here is
which
user viewed which record last. If it is not a multi user system, it is a
little easier, but I will describe the multi user scenerio.

First, you will need a table with 3 fields (assuming you want to be able
to
do this in multiple forms). Those fields are:
UserName
Used to hold the name of the user
FormName
The form the user was in
RecordIdentifier
A unique identifier that can be used to find the record

This will involve two Events in the form. The Load event, to determine
whether the user has ever been in this form before and whether the last
record viewed still existis. If the user has never been in the form or
the
last record viewed has been deleted, it will start at the first record as
normal. Then in the form Close event, save the information to the table
so
it will be available for the next session.

Since I don't know how you will keep track of user names, I will use a
dummy
function called FindUserName. You can replace it with whatever you need.

Load.

strLastViewed = Nz(DLookup("[RecordIdentifier]", "tblLastViews", _
"[UserName] = '" & FindUserName() & "' And [FormName] = '" & _
Me.Name & "'"),"")

If Len(strLastViewed) > 0 Then
Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & strLastViewed & "'"
If Not rst.NoMatch Then
Me.BookMark = rst.BookMark
End If
Set rst = Nothing
End If

In the Close event:

Dim strUser as String
Dim strForm as String

strUser = FindUserName
strForm = Me.Name

If (DCount("*", "tblLastViews", "[UserName] = '" & strUser & _
"' And [FormName] = '" & strForm & "'") = 0 Then
CurrentDb.Execute("INSERT INTO tblLastViews (UserName, FormName, _
RecordIdentifier) VALUES (" & FindUserName() & ", " & _
Me.Name & ", " & Me.txtSomeField & ");"), dbFailOnError
Else
CurrentDb.Execute("UPDATE [tblLastViews] " & _
"SET [tblLastViews].[UserName] = " & strUser & _
", [tblLastViews].[FormName] = " & strForm & _
", [tblLastViews].[RecordIdentifier] = " & Me.txtSomeField & _
" WHERE [tblLastViews].[UserName] = " & strUser & _
" And [tblLastViews].[FormName] = " & strForm & ";"),
dbFailOnError
End If

The above (particularly the SQL) is untested "air" code, so I am sure it
will take some tweeking, but at least this should give you the general
idea.

I think this must be a very basic and simple question, but I'm stumped:
How
do you programmatically store a value from one record to use in another
record? For example, say I have a form displayed which has a customer
name
field in it with the name 'Joe Smith'. If I press a button for 'New
Record
with Same Customer', I'd like Access to remember that name and fill it in
on
the new record in a macro or vba procedure. I don't know if I have to
store
Joe Smith on a form or a table somewhere, or if I can just define a
variable.

And a related question is: How could I store the most recently viewed
record
and then have Access automatically start at that same record the next
time I
open it?

Thanks for any guidance.

Jack

 

[Guest]

If you split you database (as you should), keeping the table locally would be
an even better idea. When you do the spilt, that table will go to the back
end. All you would need to to from the original front end would be to delete
the link to it, import it into the front end, and delete it from the back end.

Good thinking, Jack.

Thanks for your detailed, helpful answer! I think this will do what I need.

One thing I wonder about -- when I split my database and put the tables on
the server, could I keep the table you're describing here at the local
computer and then not worry about recording the user name? I haven't tried
splitting the database yet, so I don't know if you can keep one of the
tables with the queries and forms or not.

Jack

In the Click event of the button you described:

Dim strCurrentCustomer as String

strCurrentCustomer = Me.txtCustomer
Docmd.GotoRecord acNewRec
Me.txtCustomer = strCurrentCustomer

Now, the last record viewed is a little different. The issue here is
which
user viewed which record last. If it is not a multi user system, it is a
little easier, but I will describe the multi user scenerio.

First, you will need a table with 3 fields (assuming you want to be able
to
do this in multiple forms). Those fields are:
UserName
Used to hold the name of the user
FormName
The form the user was in
RecordIdentifier
A unique identifier that can be used to find the record

This will involve two Events in the form. The Load event, to determine
whether the user has ever been in this form before and whether the last
record viewed still existis. If the user has never been in the form or
the
last record viewed has been deleted, it will start at the first record as
normal. Then in the form Close event, save the information to the table
so
it will be available for the next session.

Since I don't know how you will keep track of user names, I will use a
dummy
function called FindUserName. You can replace it with whatever you need.

Load.

strLastViewed = Nz(DLookup("[RecordIdentifier]", "tblLastViews", _
"[UserName] = '" & FindUserName() & "' And [FormName] = '" & _
Me.Name & "'"),"")

If Len(strLastViewed) > 0 Then
Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & strLastViewed & "'"
If Not rst.NoMatch Then
Me.BookMark = rst.BookMark
End If
Set rst = Nothing
End If

In the Close event:

Dim strUser as String
Dim strForm as String

strUser = FindUserName
strForm = Me.Name

If (DCount("*", "tblLastViews", "[UserName] = '" & strUser & _
"' And [FormName] = '" & strForm & "'") = 0 Then
CurrentDb.Execute("INSERT INTO tblLastViews (UserName, FormName, _
RecordIdentifier) VALUES (" & FindUserName() & ", " & _
Me.Name & ", " & Me.txtSomeField & ");"), dbFailOnError
Else
CurrentDb.Execute("UPDATE [tblLastViews] " & _
"SET [tblLastViews].[UserName] = " & strUser & _
", [tblLastViews].[FormName] = " & strForm & _
", [tblLastViews].[RecordIdentifier] = " & Me.txtSomeField & _
" WHERE [tblLastViews].[UserName] = " & strUser & _
" And [tblLastViews].[FormName] = " & strForm & ";"),
dbFailOnError
End If

The above (particularly the SQL) is untested "air" code, so I am sure it
will take some tweeking, but at least this should give you the general
idea.

I think this must be a very basic and simple question, but I'm stumped:
How
do you programmatically store a value from one record to use in another
record? For example, say I have a form displayed which has a customer
name
field in it with the name 'Joe Smith'. If I press a button for 'New
Record
with Same Customer', I'd like Access to remember that name and fill it in
on
the new record in a macro or vba procedure. I don't know if I have to
store
Joe Smith on a form or a table somewhere, or if I can just define a
variable.

And a related question is: How could I store the most recently viewed
record
and then have Access automatically start at that same record the next
time I
open it?

Thanks for any guidance.

Jack

 

[Jack G]

Thanks again for your help!

Jack

If you split you database (as you should), keeping the table locally would
be
an even better idea. When you do the spilt, that table will go to the
back
end. All you would need to to from the original front end would be to
delete
the link to it, import it into the front end, and delete it from the back
end.

Good thinking, Jack.

Thanks for your detailed, helpful answer! I think this will do what I
need.

One thing I wonder about -- when I split my database and put the tables
on
the server, could I keep the table you're describing here at the local
computer and then not worry about recording the user name? I haven't
tried
splitting the database yet, so I don't know if you can keep one of the
tables with the queries and forms or not.

Jack

In the Click event of the button you described:

Dim strCurrentCustomer as String

strCurrentCustomer = Me.txtCustomer
Docmd.GotoRecord acNewRec
Me.txtCustomer = strCurrentCustomer

Now, the last record viewed is a little different. The issue here is
which
user viewed which record last. If it is not a multi user system, it is
a
little easier, but I will describe the multi user scenerio.

First, you will need a table with 3 fields (assuming you want to be
able
to
do this in multiple forms). Those fields are:
UserName
Used to hold the name of the user
FormName
The form the user was in
RecordIdentifier
A unique identifier that can be used to find the record

This will involve two Events in the form. The Load event, to determine
whether the user has ever been in this form before and whether the last
record viewed still existis. If the user has never been in the form or
the
last record viewed has been deleted, it will start at the first record
as
normal. Then in the form Close event, save the information to the
table
so
it will be available for the next session.

Since I don't know how you will keep track of user names, I will use a
dummy
function called FindUserName. You can replace it with whatever you
need.

Load.

strLastViewed = Nz(DLookup("[RecordIdentifier]", "tblLastViews", _
"[UserName] = '" & FindUserName() & "' And [FormName] = '" & _
Me.Name & "'"),"")

If Len(strLastViewed) > 0 Then
Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & strLastViewed & "'"
If Not rst.NoMatch Then
Me.BookMark = rst.BookMark
End If
Set rst = Nothing
End If

In the Close event:

Dim strUser as String
Dim strForm as String

strUser = FindUserName
strForm = Me.Name

If (DCount("*", "tblLastViews", "[UserName] = '" & strUser & _
"' And [FormName] = '" & strForm & "'") = 0 Then
CurrentDb.Execute("INSERT INTO tblLastViews (UserName, FormName,
_
RecordIdentifier) VALUES (" & FindUserName() & ", " & _
Me.Name & ", " & Me.txtSomeField & ");"), dbFailOnError
Else
CurrentDb.Execute("UPDATE [tblLastViews] " & _
"SET [tblLastViews].[UserName] = " & strUser & _
", [tblLastViews].[FormName] = " & strForm & _
", [tblLastViews].[RecordIdentifier] = " & Me.txtSomeField &
_
" WHERE [tblLastViews].[UserName] = " & strUser & _
" And [tblLastViews].[FormName] = " & strForm & ";"),
dbFailOnError
End If

The above (particularly the SQL) is untested "air" code, so I am sure
it
will take some tweeking, but at least this should give you the general
idea.



:

I think this must be a very basic and simple question, but I'm
stumped:
How
do you programmatically store a value from one record to use in
another
record? For example, say I have a form displayed which has a customer
name
field in it with the name 'Joe Smith'. If I press a button for 'New
Record
with Same Customer', I'd like Access to remember that name and fill it
in
on
the new record in a macro or vba procedure. I don't know if I have to
store
Joe Smith on a form or a table somewhere, or if I can just define a
variable.

And a related question is: How could I store the most recently viewed
record
and then have Access automatically start at that same record the next
time I
open it?

Thanks for any guidance.

Jack