Solved subforms not updating properly (1 Viewer)

[ClaraBarton]

I've attached a file with a form that has 3 subforms. #3 (subBooks) on the form allows me to select a record for the detail below, but the selection arrow does not move. I think it has to do with the current event on the first form (child0) but I don't know how to fix it. On the opening menu, select Edit Lists.

Private Sub Form_Current()
Dim frmBooks As Form

Set frmBooks = Forms.lstfrmEdit!subBooks.Form
        
    If Not IsNull(AuthorID) Then
     frmBooks.Filter = "AuthorIDFK = " & Me.AuthorID
     frmBooks.FilterOn = True
    Else
       frmBooks.Filter = ""
    End If

End Sub

I would also like to know where to put a requery when the #4 (subDetail) form is edited. For the author sub form (lstsubsubAuthor) I've used

Private Sub Form_Current()
    Me.cboAuthor.Requery
    Forms(Me.Parent.Parent.Name).Child0.Requery
End Sub

but it doesn't work. None of the clear (X) on the combo's seem to work with after update either.
Am I asking too much? I'm sorry the file seems large. I limited the books to just A's but it didn't change a lot.

 

[MajP]

You have over 20 forms. Can you be more specific which form to look at, and maybe a screen shot or detailed steps.
Ex
1. Open form X
2. Pull down combobox Some Title
3. Verify cannot add data
4. Try to delete record...
5.

 

[ClaraBarton]

 

[MajP]

The problem is the requery in the lstSubDetail

Private Sub Form_Current()
    'Me.Parent.subBooks.Requery
End Sub

The problem is if you requery you are moving the record to the first record not the selected book.
I would first do this in the lstSubDetail form after update. No need for the current. And do not change the selected book

Private Sub Form_afterUpdate()
    Me.Parent.subBooks.Form.Recordset.Requery
End Sub

You can simplify this code
put a hiddent text box on the parent form and call it txtlink

Private Sub Form_Current()
On Error Resume Next
Me.Parent.txtLink = Me.BookID

'delete all code'
'Dim frmDetail As Form
'Set frmDetail = Forms.lstfrmEdit!subDetail.Form
       
'    If Not IsNull(BookID) Then
'     frmDetail.Filter = "BookID = " & Me.BookID
'     frmDetail.FilterOn = True
'    Else
'       frmDetail.Filter = ""
'    End If

End Sub

now link subDetail
master link fields: [txtlink}
child link fields:[bookid]

 

[MajP]

I will have to take a harder look at this. I see what is happening, you have a lot of subforms with code in the on current event that are effecting other subforms. You are creating a death spiral. Where one subform causes another to requery which triggers the on current causing another to requery triggering the on current and so on.

 

[ClaraBarton]

Ok... followed your instructions. but to get the detail I need to make the txtlink (upper right) match the subBooks. Then I'm back to subBooks not moving again.

 

[ClaraBarton]

a death spiral... I suspected it was a mess but not deadly

 

[MajP]

I suspected it was a mess but not deadly

It is not a mess, you just have competing events causing a "perpetual loop". This commonly happens when a subform requeries the main form wich cause all the subforms to requery which requeries the mainform ..... It just takes some digging to figure out how to break the loop. You can actually see it happening with subforms continuously refreshing causing a flicker.

 

[ClaraBarton]

Flickering is exactly what the subBooks is now doing. The child0 is the subform that changes on load (from the combo). Everyone is the same opening code:

Private Sub Form_Current()
Dim frmBooks As Form
Set frmBooks = Forms.lstfrmEdit!subBooks.Form
    
    If Not IsNull(PublisherID) Then
       frmBooks.Filter = "PublisherIDFK = " & Me.PublisherID
       frmBooks.FilterOn = True
    Else
       frmBooks.Filter = ""
    End If
End Sub

 

[MajP]

Did you on purpose include the tblBookAuthors in your qryAuthors.? This does not make sense for how it is used and creates repeating values. ID 70 Bender, Carrier shows up 4 times.

 

[ClaraBarton]

It's a many to many relationship so the bridge table ties it to the books

 

[ClaraBarton]

I'd like to limit the list to unique but haven't figured it out yet. The multiple authors means multiple books

 

[MajP]

I found the culprit. You had another in the subsubDetails

Private Sub Form_Current()
    Me.cboAuthor.Requery
  '  Forms(Me.Parent.Parent.Name).Child0.Requery
End Sub

If you remove these and move them to the afterUpdate that should work.

If you want to get unique records that only exist in the tblBookAuthors you can use a subquery
either IN or Exists
Exists are faster but would work fine.

       Iif(Isnull([lastname]), [firstname], Iif(Isnull([firstname]), [lastname],
                                            [lastname] & "," & [firstname])) AS
       Author,
       tblauthors.lastname,
       tblauthors.firstname
FROM   tblauthors
WHERE  (( ( EXISTS (SELECT authoridfk
                    FROM   tblbookauthors AS B
                    WHERE  tblauthors.[authorid] = b.authoridfk) ) <> false ))
ORDER  BY Iif(Isnull([lastname]), [firstname],
          Iif(Isnull([firstname]), [lastname],
                    [lastname] & "," &
                    [firstname])),
          tblauthors.authorid;

SQL IN Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

SQL EXISTS Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

In other words you want all authors from the authors table that have a book. The foreign key is IN or Exists in the tblBooksAuthors.

 

[MajP]

See if this fix still does what you want. I fixed authors but not the other lists.

 

[MajP]

Also are you still working to clean up the duplicates? By duplicates I mean names that are spelled different, but likely the same thing.

ex
tblAuthors tblAuthors

AuthorID	ImportIDFK	LastName	FirstName
1447​	928​	Walton	Mrs. O. F.
1567​	2465​	Walton	Mrs. O.F.

tblAuthors tblAuthors

AuthorID	ImportIDFK	LastName	FirstName
1210​	1410​	Wagler	Susie
1444​	917​	Wagler	Susie, illustrator

tblAuthors tblAuthors

AuthorID	ImportIDFK	LastName	FirstName
1448​	937​	Weekly Reader	Reader
1527​	2200​	Weekly Reader	--

If you are going to do it, I have several tools that can aid in finding similar things, but your list is really not that long. I would have a clean up form where I can scroll the names and pick two like names. Such as the two Waltons (1447, 1567). Then select which one to keep. Example 1447. This will move all 1567 to 1447 and delete 1567.

 

[ClaraBarton]

I've set a composite key that's should help some. It's a mess because the data entry is being done by a 91 year old who can't see very well. I'm just trying to add tools to fix it. The point is that the Waltons would show up as duplicates in the list, I would delete the books from the one and add them to the other and delete the Walton. It's a work in progress and I very much appreciate your help. I used your fuzzyfind4 and decided to use the soundex on entering new authors.

 

[ClaraBarton]

YES! it works! Need some cleaning up but the bones are here. Thank you so much!

 

[MajP]

One thing I did not fix (only moved to after update) was when you requery you want to return to the record you were on. If not you will move to the first record.

So add some code like (names may not be correct)

dim BookID as long
dim frm as access.form
set frm = me.parent.subFrmBooks.Form

'save the current record bookID
bookID = nz(frm.bookID)
'now requery
frm.requery
'move back to the record before the requery
frm.recordset.findfirt "bookID = " & bookID

So that was part of the problem. The book subform filtered the details subform based on the current record. This caused the on current event in the detail subform to trigger which called a requery in the book subform. This caused the book subform to move to the first record. Therefore, it seemed as if you could never leave the first record. In truth you could leave it, but was immediately requeried back to the first record.

 

[ClaraBarton]

Should this go in the after update of the detail? I was just working on getting it update with no joy. Or should the Child0 be requeried instead of the subBooks? If I make a change and click in Child0 to the record above and then the current one, the subbook updates properly.

 

[ClaraBarton]

Nevermind... I'll try to figure it out.