RE: Storing a value for later use in vba or a Macro

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" wrote:

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



.