remove filter and goto record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm learning as I go, so please be patient. My first form is a list of
activities. I have a button that allows the user to open details about the
activity on a second form (linked by PK 'id_code'). I do not want to use a
subform (don't ask). I can get the second form to open on the record that is
selected from the first form, but I want it to remove the filter on the
second form so I could also scroll through the activities there. If I use
Me.FilterOn = False, it removes the filter but goes back to the first record.
I basically want two (or more) forms that are synchronized. As I scroll
through a list, the second form changes. As I scroll through the second
forms, the list selection will scroll also. Any help is appreciated. Thanks.
 
You will need to write some code to create a form that stays in sync with
the orignal form.

Some of the issues you are likely to run into:
a) Write conflict: Trying to keep the records saved an in sync where they
can be edited in both forms can be difficult. You might consider make one
form read-only i.e. set its AllowEdits and AllowDeletions to No, and cancel
its Form_BeforeInsert event. (You cannoset set AllowAdditions to No, because
it has to be able to move to a new record to stay in sync with the other
form.)

b) Concurrency problems: If there are any memo (or hyperlink or OLE) fields
in the forms you may get errors that another user/process has that record
open.

c) New record issue: When you move to a new record in one form, presumably
you want the other form to move to a new record as well. I don't know any
way to move a form to a new record unless it has focus. That means you will
need to flash the other form to the front, move it to the new record, and
then flash back to the form you were previously working with. You can try
working around that with Echo Off, but it could be a bit disconcerting.

d) Endless loop. If you sync form B from form A, and then because the record
changed in Form B it triggers a sync of form A, which retriggers they sync
of form B, you could bring Access to it knees. Clearly, any code you use
needs to avoid this problem.

If you want to proceed anyway, it would be a matter of writing a VBA
function to call in the Current event of the form, to synchronize the other
form to the record we just arrived at.
 
This is completely untested aircode, just a concept to illustrate what the
code would look like. Problems with memo fields (as noted previously) will
probably still occur.

To keep Form1 and Form2 synchronized so they both stay at the same record,
paste the 2 functions below into a standard module (Modules tab of Database
window), and save. The illustration assumes a primary key named ClientID. In
the Current event procedure of Form1, add this line:
Call SyncForm(Me, "Form2", "ClientID", Me.ClientID)
and in the Current event procedure of Form2:
Call SyncForm(Me, "Form1", "ClientID", Me.ClientID)

If you prefer to use the On Current property of the form, it would be:
=SyncForm([Form], "Form2", "ClientID", [ClientID])

-----------------code begins-----------------------
Function SyncForm(frmSource As Form, strTargetFormName As String, _
strKeyField As String, varKeyValue As Variant) As Boolean
On Error GoTo Err_Handler
'Purpose: Keep two open forms sychronized to the same record.
'Limitations: Requires a single-field primary key. A2000 and later.
'Arguments: frmSource = a reference to form you are sync-ing from.
' strTargetFormName = name of form that should be sync'd.
' strKeyField = name of the primary key field.
' varKeyValue = the value of the primary key field.
'Return: True if sync happened. False for all other conditions _
(form not open, already there, filtered differetly)
'Usage: In Form_Current: _
Call SyncForm(Me, "Form2", "[ID]", Me.[ID])
Dim frmTarget As Form 'The other form.
Dim rsTargetClone As DAO.Recordset 'Clone set of other form.
Dim strWhere As String 'WhereCondition to match records.
Dim strDelim As String 'Delimiter for Where string.
Dim bSynced As Boolean 'True if forms synchronized.

'Do nothing if the target form is not loaded.
If CurrentProject.AllForms(strTargetFormName).IsLoaded Then
'Get a reference to the target form.
Set frmTarget = Forms(strTargetFormName)
If (frmTarget.NewRecord And frmSource.NewRecord) Or _
(frmTarget(strKeyField) = varKeyValue) Then
'Avoid endless triggering if already synchronized.
Else
'Save the record so the other form can move.
If frmTarget.Dirty Then
frmTarget.Dirty = False
End If

If frmSource.NewRecord Then
'Move the target form to a new record.
DoCmd.Echo False
frmTarget.SetFocus
RunCommand acCmdRecordsGoToNew
frmSource.SetFocus
DoCmd.Echo True
bSynced = True
Else
'Find the same record in the target form.
If Not IsNull(varKeyValue) Then
strDelim = GetDelim(varKeyValue)
strWhere = strKeyField & " = " & _
strDelim & varKeyValue & strDelim
Set rsTargetClone = frmTarget.RecordsetClone
rsTargetClone.FindFirst strWhere
If Not rsTargetClone.NoMatch Then
frmTarget.Bookmark = rsTargetClone.Bookmark
bSynced = True
End If
End If
End If
End If
End If

'Set return value
SyncForm = bSynced

Exit_Handler:
DoCmd.Echo True
Set rsTargetClone = Nothing
Set frmTarget = Nothing
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
Private Function GetDelim(varIn As Variant) As String
Select Case VarType(varIn)
Case vbString
GetDelim = """"
Case vbDate
GetDelim = "#"
End Select
End Function
-----------------code ends-----------------------
 
Back
Top