MajP
You've got your good things, and you've got mine.
- Local time
- Today, 15:25
- Joined
- May 21, 2018
- Messages
- 9,998
A lot of your requeries serve no purpose, do not just shotgun these out. Especially not in the on current event.
In the lstSubSubAuthor there was a requery of the combobox, and a requery of the lstSubAuthors and the Child0 subform. This was being done in the on current of lstSubSubAuthor so every single time a record changes by code or by user input. Unless data changes and needs to be reflected than the requery is doing nothing.
This does nothing of value. The combo is based on non changing list of data
Requerying Child0 as you had it designed from the lstSubSubAuthor should only happen if the object in the ChildO is lstSubAuthors and only after an Author has been added to a book. Because if you add an author to a book and that author did not previously have a book assigned then that author did not appear in the list. But again only after adding or deleting an author. Not every time a record changes.
In the lstSubDetail again you were requerying the subBooks on the On current event. Again this does nothing of value. In fact the only thing that can change in subBooks is if the copywright or page number changes and you want to reflect that.
You only need to be doing requeries when data changes and you need to reflect that change on the screen. This should be done judiciously or you will get the problems you had.
One final thing. You need to fix your queries.
Your query qryAuthors is not a query of authors but a query of Authors and their book id.
I would make 3 useful queries for all of these (author, publisher, illustrator...)
qryAuthors (all authors)
qryAuthorsWithBooks (authors assigned to one or more books)
qryAuthorsWithoutBooks (Un assigned authors.)
You are using your qryAuthors in other queries and it is causing this duplication.
qryAuthorsWithBooks
Unassigned or qryAuthorsWithoutBooks
In the lstSubSubAuthor there was a requery of the combobox, and a requery of the lstSubAuthors and the Child0 subform. This was being done in the on current of lstSubSubAuthor so every single time a record changes by code or by user input. Unless data changes and needs to be reflected than the requery is doing nothing.
Code:
Private Sub Form_Current()
Me.cboAuthor.Requery
End SubRequerying Child0 as you had it designed from the lstSubSubAuthor should only happen if the object in the ChildO is lstSubAuthors and only after an Author has been added to a book. Because if you add an author to a book and that author did not previously have a book assigned then that author did not appear in the list. But again only after adding or deleting an author. Not every time a record changes.
Code:
Private Sub Form_AfterUpdate()
'Only needed if an author was added to a book and only if the list is authors
Dim authorID As Long
Dim frm As Access.Form
Set frm = Me.Parent.Parent.Child0.Form
If frm.Name = "lstSubAuthors" Then
authorID = frm.authorID
frm.Requery
frm.Recordset.FindFirst "authorID = " & authorID
End If
End SubIn the lstSubDetail again you were requerying the subBooks on the On current event. Again this does nothing of value. In fact the only thing that can change in subBooks is if the copywright or page number changes and you want to reflect that.
Code:
Private Sub Copyright_AfterUpdate()
RequeryBookDetails
End Sub
Private Sub Form_AfterUpdate()
RequeryBookDetails
End Sub
Private Sub Number_of_Pages_AfterUpdate()
RequeryBookDetails
End Sub
Public Sub RequeryBookDetails()
Dim BookID As Long
Dim frm As Access.Form
Set frm = Me.Parent.subBooks.Form
BookID = Nz(frm.BookID, 0)
frm.Requery
frm.Recordset.FindFirst "BookID = " & BookID
End SubYou only need to be doing requeries when data changes and you need to reflect that change on the screen. This should be done judiciously or you will get the problems you had.
One final thing. You need to fix your queries.
Your query qryAuthors is not a query of authors but a query of Authors and their book id.
I would make 3 useful queries for all of these (author, publisher, illustrator...)
qryAuthors (all authors)
qryAuthorsWithBooks (authors assigned to one or more books)
qryAuthorsWithoutBooks (Un assigned authors.)
You are using your qryAuthors in other queries and it is causing this duplication.
Code:
SELECT tblauthors.authorid,
Iif(Isnull([lastname]), [firstname], Iif(Isnull([firstname]), [lastname],
[lastname] & "," & [firstname])) AS
Author,
tblauthors.lastname,
tblauthors.firstname
FROM tblauthors
ORDER BY Iif(Isnull([lastname]), [firstname],
Iif(Isnull([firstname]), [lastname],
[lastname] & "," &
[firstname]));qryAuthorsWithBooks
Code:
SELECT tblauthors.authorid,
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;Unassigned or qryAuthorsWithoutBooks
Code:
SELECT tblauthors.authorid,
tblauthors.lastname,
tblauthors.firstname,
tblbookauthors.bookidfk
FROM tblauthors
LEFT JOIN tblbookauthors
ON tblauthors.authorid = tblbookauthors.authoridfk
WHERE (( ( tblbookauthors.bookidfk ) IS NULL ));
