Go to Related Record

xPaul

Registered User.
Local time
Yesterday, 20:38
Joined
Jan 27, 2013
Messages
65
Hi all,

I am trying to achieve the ability to navigate to another form with a single click, however I want it to go to the same record that it was in previously.

For example:

frmInformation has 500 records
frmDiscipline has 500 records

When I am on frmInformation (249th record) and press the appropriate button it will open up frmDiscipline on the 249th record. I have the above already coded and works well the code I used should anyone need is:

The following will take you from frmInformation to the same related record in frmDiscipline:
PHP:
DoCmd.OpenForm "frmDiscipline ", , , "[PrimaryKeyHere]='" & Forms!frmInformation!txtPrimaryKey & "'"
Should you require the reverse - frmDiscipline to frmInformation then swap frmDiscipline for frmInformation:
PHP:
DoCmd.OpenForm "frmInformation", , , "[PrimaryKeyHere]='" & Forms!frmDiscipline!txtPrimaryKey & "'"
The issue I am encountering is not that when I press on this related record button it does as I have coded and rightfully so - opens up frmDiscipline with the same record that frmInformation is sitting on (filtered). But when I press the same button on frmDiscipline to open frmInformation the form is no longer filtered but is stuck on that one record and there is no way of removing the filter, essentially the recordset has been reduced to 1.

A step by step:

  1. Open frmInformation
  2. Press on the related record button in frmInformation
  3. Opens frmDiscipline filtered to the same record as in point 1
  4. Press on the related record button in frmDiscipline
  5. Opens frmInformation unfiltered but with only the 1 record that was chosen in point 3
I am using tabbed forms and the only way to circumvent this issue is to close the affected tabs.

I need a way of letting access know that when I press on the related record button it should open the relevant form in a mode that will allow me to 'clear' the filter. Once the filter is cleared it should then be able to allow me to then select the related record in the first form.

I have tried a few things like setting cases on the click of the related record button:

(strRelatedRecordClicked is loaded with the default value of "Default")


PHP:
Select Case strRelatedRecordClicked

    Case "True"
        DoCmd.OpenForm "frmDiscipline ", , , "[PrimaryKeyHere]='" & Forms!frmInformation!txtPrimaryKey & "'"
        strRelatedRecordClicked = "False"
        
    Case "False"
        With Me
            Filter = ""
            btnGoToRelatedRecord.ControlTipText = "Clear Filter"
            lblGoToRelatedRecord.Caption = "Clear Filter" & strname
        End With
        strRelatedRecordClicked = "Default"
        
    Case "Default"
        With Me
            Filter = ""
            btnGoToRelatedRecord.ControlTipText = "Related Record"
            lblGoToRelatedRecord.Caption = "Related Record" & strname
        End With
        strRelatedRecordClicked = "True"

End Select
As you can see I end up going around in circles as when the second form opens it does not give me the ability to clear.

To summarise what I wish to achieve:

  1. Open frmInformation tab
  2. Search for an appropriate record
  3. Want to see related records in another form (frmDiscipline)
  4. Press the related record button
  5. Opens up the other form as a tab (frmDiscipline)
  6. frmDiscipline loads up with an option to clear the filter (which will unfilter the form)
  7. Once the filter has been cleared it should then allow the user to press the related record button and go back the ways to the frmInformation tab
  8. frmInformation will now appear with the clear filter
  9. So on and so fourth
Has anyone done the above before, if so how? If you haven't, is there any way of doing it?

Yes I can get around the issue by placing everything on one form, but this is a challenge and I refuse to let it beat me (us).


Best regards


Paul
 
What I like to do is put a public GoToID() method on a form so that other objects can just call that method, and the navigation is handled by the form itself. So if you have a customer form, you might give it a public method like . . .
Code:
Public Sub GoToID(CustomerID as long)
   With Me.RecordsetClone
      .FindFirst "CustomerID = " & CustomerID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub
So then you might have a different form that might, from time to time, need to display that customer record in the fCustomer form, so you can do something like . . .
Code:
Sub ShowCustomer(CustomerID as long)
   Const FN As String = "fCustomer"
[COLOR="Green"]   'open the form, if it isn't already[/COLOR]
   If Not CurrentProject.AllForms(FN).IsLoaded Then DoCmd.OpenForm FN
[COLOR="Green"]   'run its GoToID method[/COLOR]
   Forms(FN).GoToID CustomerID
End Sub
And you can get fancy, like remove filters from the target form before searching, and/or return a boolean to indicate if the record was found . . .
Code:
Public Function GoToID(CustomerID as Long) as Boolean
   Me.FilterOn = False[COLOR="Green"]   'remove existing filters at target form[/COLOR]
   With Me.RecordsetClone
      .FindFirst "CustomerID = " & CustomerID
      If Not .NoMatch Then 
         Me.Bookmark = .Bookmark
         GoToID = True   [COLOR="Green"]'return True if the record was found[/COLOR]
      End If
   End With
End Function
hth
 

Users who are viewing this thread

Back
Top Bottom