See all in subform, but synchronize parent to current sub record 2

[JFF]

I have a simple form that shows the fields from a query, in form view at the top and a subform that shows data from the same query in a subform at the bottom of the form. The top shows - one - record and the subform shows - all - records of the same query.

I would like to ...
a) Always have the subform show - all - records in the query (unless I set a filter on the subform).
b) Have the top, form view record always show the current record I'm in on the subform as I move thru the subform.
c) If I delete a record in the top, form view, the same record is deleted in the subform and then the top form moves to the next record available in the subform instead of being blank.
d) It would be nice, but not essential if the record in the subform moved as I moved thru the top form view too.

Interesting, in other databases I've used in the past (but not as good as Access), setting up a form to synchronize the top (form view) section of the form and the subform (in datasheet view of all records) was easy and could be done in a few minutes.

I've spent many hours trying everything from a self-join to using a filter in a macro and haven't succeeded.

Any help would really be appreciated.

John

 

[MajP]

you want to make sure that the Main form and the sub are not linked.

Put this code in the Main form

Private Sub Form_AfterDelConfirm(STATUS As Integer)
  Me.subFrmCntrlPersons.Form.Requery
End Sub

Private Sub Form_Current()
  Dim rst As DAO.Recordset
  Set rst = Me.subFrmCntrlPersons.Form.RecordsetClone
  rst.FindFirst "autoPersonnelID  = " & Me.autoPersonnelID
  If Not rst.NoMatch Then
    Me.subFrmCntrlPersons.Form.Bookmark = rst.Bookmark
  End If
End Sub

the subform control is called "subFrmCntrlPersons"
I have a field called "autoPersonnelID" that I search on.

Put something like this in the subform

Private Sub Form_Current()
  Dim rst As DAO.Recordset
  Set rst = Me.Parent.Recordset
  rst.FindFirst "autoPersonnelID  = " & Me.autoPersonnelID
  If Not rst.NoMatch Then
    Me.Parent.Bookmark = rst.Bookmark
  End If
End Sub

I linked both, so if you move in the subform it moves the main form, or if you move in the main form it moves the subform.

 

[MajP]

change this line in the subform

rst.FindFirst "autoPersonnelID = " & Nz(Me.autoPersonnelID, 0)

This will help avoid an error when you pass the last record.

 

[JFF]

The code works well 90% of the time and I can move thru records in the main form and also the subform and they do synchronize fine.

When I am in the main form (not its subform) and go to Add a new record this error pops up:

"Runtime error 3077 ...
Syntax error: missing operator in expression"

Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.qMain_Form_Data_subform.Form.RecordsetClone
rst.FindFirst "xlnk = " & Me.XLNK '<<< Error occurs here
If Not rst.NoMatch Then
Me.qMain_Form_Data_subform.Form.Bookmark = rst.Bookmark
End If
End Sub

My subform is called "qMain_Form_Data_subform"

John

 

[PHV]

You may try this:
Private Sub Form_Current()
If Trim(Me!XLNK & "") = "" Then Exit Sub
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[t1hodges]

i'd like to add, I used this code. It didnt like that my FindFirst value was a string and it gave an error. I luckily my field was a number so I changed the format to numeric and it worked well.