Find current record when moving from one form to another

[Kregg]

My problem seems simple but I have a feeling this is going to be a bigger issue that I think.

Lets say,

Form A has fields: ID, LN and FN

ID LN FN
1 A F
2 B G
3 C H
4 D I
5 E J

Form B has fields: ID, LN and TR

ID LN TR
1 A Y
2 B N
3 C Y
4 D Y
5 E N

I would like to be able to switch between these two forms and the form automatically set focus to the record that I was on the form prior. Meaning if I am on record 3 on Form A and navigate to Form B, I would like to automatically set focus to record 3. Is this possible?

A possible issue after this gets sorted out is what happens if with the same scenario above record 3 does not exits? is it possible in that instance to simply requery the form?

 

[Royce]

You can create Properties (Get, Let) on the forms to pass the data about it's current record, then write code in the OnActivate, and OnDeactivate events to sync the forms. When you leave a form OnDeactivate should set the data. When the other form is activated, OnActivate should read the data, move to the correct record, and do any other needed processing.

 

[Kregg]

I am not familiar with the get, let properties. Where are they located and how do I use them?

 

[JHB]

You can use functions build in a module to get/set the record number.
I've made a small example for you in the attached database, open form "Tbl1" and "Tbl2". Select a record in one form and click on the other form header.

 

[Kregg]

You can use functions build in a module to get/set the record number.
I've made a small example for you in the attached database, open form "Tbl1" and "Tbl2". Select a record in one form and click on the other form header.

Thanks for the reply! Your solution is the closest that I have come. The only issue I am encountering is that because it is looking at the record no it is finding the record no on the next form. The problem with this is that names change record no position because of the querys that the forms are based off of are pulling different information.

Is it possible to have it get/set the ID number since that is always constant and never changes?

My apologies for using the term record in the initial post. I am still learning the terms. I would like it to stay with the data not the record number. Thanks again for any help you can offer!

 

[Royce]

Your earlier statement about it getting complicated is very true. You'll find the best approach depends on the details of what your trying to do. However, the following should get you started. Have fun.

Modify the sample code JHB sent you to something like this. (I edited the variable names for clarity)

' This should go in both forms.
Private Sub Form_Activate()
  ' I'd always requery because you don't know what all might have changed.
  Me.Requery
  With Me.RecordsetClone
    .FindFirst "ID = " & CStr(GetId)
    If .NoMatch Then
        .MoveFirst
        MsgBox "Matching record (ID:  " & CStr(GetId) & ") Not found"
        
    Else
        Me.Bookmark = .Bookmark
    End If
    
End Sub

Private Sub Form_Deactivate()
  SetId (Me!ID)
End Sub

' This should go in the module.
  Dim mRememberId As Long

Function SetId(lngId As Long)
  mRememberId = lngId
End Function

Function GetId()
  GetId = mRememberId
End Function

If there is a primary/secondary relationship between the forms, where one form opens the other, then I like the Public property approach. This has the advantage of the coder knowing exactly which form the Id is coming from. And you don't have to worry about some other form also calling the GetId,SetId functions and breaking your code.

For example:

Public Property Get CurrentId() As Long
    CurrentId = Me!ID

End Property

Private Sub Form_Activate()
  ' I'd always requery because you don't know what all might have changed.
  Me.Requery
  Dim lngId As Long
  If CurrentProject.AllForms("Tbl1").IsLoaded Then
    lngId = Form_Tbl2.CurrentId
    
    With Me.RecordsetClone
      .FindFirst "ID = " & CStr(lngId)
      If .NoMatch Then
          .MoveFirst
          MsgBox "Matching record (ID:  " & CStr(lngId) & ") Not found"
          
      Else
          Me.Bookmark = .Bookmark
      End If
    End With
  End If
End Sub

In most cases, I've found the Property makes for easier to maintain code. However, both work, and both approaches have their place.

 

[Kregg]

I keep getting Run-time error '3021':
No current record.

I have tried your code Royce and it is not working. I have tried to combine the two pieces of code and came up with this.

Private Sub Form_Activate()
With Me.RecordsetClone
 If GetId > Me.ID Then
    Me.Requery
  Else
    Me.Requery
    Me.Bookmark = .Bookmark
  End If
  End With
End Sub

Private Sub Form_Deactivate()
  SetId (Me.ID)
End Sub
 
Option Compare Database
 Dim mRememberId As Long
Function SetId(lngId As Long)
  mRememberId = lngId
End Function
Function GetId()
  GetId = mRememberId
End Function

Any suggestions?

 

[Royce]

You have to do the FindFirst. That's what set's the .bookmark value.

 

[Pat Hartman]

This is trivial if you always close formB first.

Check to see if formB is open - close it. Then use the OpenForm method with the Where argument.

 

[Mihail]

Form A has fields: ID, LN and FN

ID LN FN
1 A F
2 B G
3 C H
4 D I
5 E J

Form B has fields: ID, LN and TR

ID LN TR
1 A Y
2 B N
3 C Y
4 D Y
5 E N

From this example seems that the relationship between this tables is 1-1.
So, why you need two tables for this ?

What about a single table?

ID  LN  FN  TR
1   A   F   Y
2   B   G   N
3   C   H   Y
4   D   I   Y
5   E   J   N

 

[Kregg]

You have to do the FindFirst. That's what set's the .bookmark value.

I continue to get invalid argument when I use .FindFirst(GetId). This is my first time using it so I am probably omitting something important, here is the updated code:

Private Sub Form_Activate()
    Me.Requery
    With Me.RecordsetClone
    .FindFirst (GetId)
    If .NoMatch Then
         .MoveFirst
         MsgBox "Matching record not found"
  Else
    Me.Bookmark = .Bookmark
  End If
  End With
End Sub

This is trivial if you always close formB first.

Pat I am not really sure what this means. I am relatively new to Access VBA. If you have more information I am open to any ideas that work.

Thanks again for everyone's help!

 

[Kregg]

I have made some adjustments to the code and it is now working one way. Meaning from FormA to FormB it will find the client information but from FormB to FormA it will not. Any suggestions on how to remedy this issue?

FormA and FormB on Active event

Private Sub Form_Activate()
Me.Requery
  With Me.RecordsetClone
    .FindFirst "ID = " & CStr(GetId)
    If .NoMatch Then
        .MoveFirst
        MsgBox "Matching record (ID:  " & CStr(GetId) & ") Not found"
    Else
        Me.Bookmark = .Bookmark
    End If
    End With
End Sub

FormA and FormB on Deactivate event

Private Sub Form_Deactivate()
     SetId (Me!ID)
End Sub

Module1

Option Compare Database
 Dim mRememberId As Long
Function SetId(ID As Long)
  mRememberId = ID
End Function
Function GetId()
  GetId = mRememberId
End Function

Any assistance is always greatly appreciated!!

--

Not to make this any stranger but there is also a FormC that has this same code. For some reason the code works without flaw between FormB and FormC but anytime you return to FormA the code does not work...:banghead: