[AccessD] VBA syntax for selecting a record

John,

This problem has two pieces, communicating back to the calling form the
current record (PKID), and having the current form move to that record.
Obviously there are many ways to do this.  First let me say that the process
is a lot easier if you use a surrogate key so that the key is nothing more
than a single long integer value.  If the key is several fields taken
together, then of course the process becomes more complex.

Since I ALWAYS use a surrogate key, the way that I tend to do this is to set
what I call a filter, which is a value stored in a collection keyed on a
specific name.  Let's take an example.

FormA opens FormB.  FormB moves to record K.  The surrogate Key for form K
is 1234.  I would store 1234 as the VALUE in a collection, using the KEY
"FormB-PKID".  

Whenever FormB changes the current record (OnCurrent) I store the PKID (the
surrogate key) into the collection, overwriting the previous value.

When FormB closes, FormA can look up the VALUE of the PKID in the collection
using the KEY "FormB-PKID".

FormA now knows what the LAST PKID FormB was on.

Does that make sense?

That is a global solution to the problem, i.e. it can be used over and over
in a consistent manner throughout your application.  In fact this can be a
rather global issue.  Perhaps a report or query needs to pull a dataset
based on the PKID of a given form.  If the form stores it's PKID in this
collection keyed on the form's name, any other process, anywhere in the
application "knows" what record that form is on simply by looking up the
form name in the collection.  Many people use "form references" in queries
to accomplish this, which of course works.  However that requires a form to
remain open and on that record for the query to work.  Using a filter of the
type I describe allows you to close the form, or even to manually set the
filter in the debug window if you desire.

I use a "Filter" function to perform this storage for me.  I have shown the
code many times in the past but I will show it again.

Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
Variant
On Error GoTo Err_Fltr
Static mcolFilter As Collection

    If mcolFilter is nothing Then
        'if the collection not initialized yet, do so now
        Set mcolFilter = New Collection
    End If
    If IsMissing(lvarValue) Then
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If err <> 0 Then
            Fltr = Null
        End If
    Else
        On Error Resume Next
        mcolFilter.Remove lstrName
        mcolFilter.Add lvarValue, lstrName
        Fltr = lvarValue
    End If
Exit_Fltr:
Exit Function
Err_Fltr:
        MsgBox err.Description, , "Error in Function basFltrFunctions.Fltr"
        Resume Exit_Fltr
    Resume 0    '.FOR TROUBLESHOOTING
End Function
 
Notice that this function is completely self contained, you can just copy it
into your application and start using it.  It can set up the collection all
by itself, and stores the collection inside of itself as a static variable.
If you use the syntax:

Fltr "VarName", VarValue

Then VarValue is stored in the collection using "VarName" as the key
(without the quotes obviously).

If you use the syntax:

Fltr("VarName") then the VALUE stored in the collection under the name
"VarName" is returned.  If nothing is found, then a null is returned.

The obvious advantage of this method is that a collection can efficiently
store tens of thousands of such "variables".  You could just have every form
"store" it's PKID in the collection in the form's OnCurrent.  You can IF YOU
WISH have the form DELETE it's PKID from the collection when the form closes
simply by storing a NULL using the form's name as the key.  Obviously if you
do that then the value is not available any longer, for example from your
calling form.

Collections are wonderful things.  This Fltr function is designed precisely
for purposes like you describe here.  And of course, it is in my framework
library.  ;-)

In order to use this method of course requires that FormA open FormB MODAL,
which causes code execution to stop in FormA.  Once FormB closes, code
begins to execute on the line following the line that opened FormB.  FormA
would then use a seek to move to the correct PKID in the recordset clone.

If opening FormB modal is inconvenient, then you have to somehow "cause"
FormA to move to the correct record as FormB closes.  There are many ways to
do that and if you need that then say so and we will address that problem in
another email.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow
Sent: Thursday, January 18, 2007 11:47 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] VBA syntax for selecting a record

Hi Andy,
Thanks but I should've have been more specific (got a head cold and not
thinking real clear :o(

What would be the correct VBA syntax for selecting a record on a continuous
form using the primary key from a separate module be (not the form's own
module).

Here's what I'm after: I'm on a specific form and I change the record being
worked on via code. I want to have the selected record be the same on
another open form (which precedes this form in the work flow). That form
displays the same set of record using the continuous form style.

I want to do this so that when the current form is closed the other form
appears to be highlighting the same record that I switched to on the form
that was just closed.

I hope that is clearer.