Solved subforms not updating properly

[Pat Hartman]

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

True but this is a list of Authors, NOT a list of books. Therefore, you need to use ONLY the Authors to populate this form. There is a similar problem with the books subform but I didn't track it down.

I see now, why you made this form this way but you did yourself NO favor with this reuse. A simpler solution than all the code might have been to populate the other subforms with a different query depending on which of the types was selected from the unbound combo. And in that code, populate the master/child links appropriately so that Access can take care of linking the subforms. I don't have any more time to look at this now but I did notice one other problem. I've posted a picture below so you can see it.

IMPORTANT
It's not your fault, it is a problem caused by an Access "feature" designed to help you but which will kick you when you're down if you do not fully understand how it works. This feature is "Name AutoCorrect" sometimes called by experts "Name AutoCorrupt" due to what it did to you in this situation. At some point in time, you decided to change qryBooks so you renamed it and rebuilt the query. HOWEVER, Access never knew it was your intention to replace the old version with a new version. It thought you just wanted to change the name of the query so it "helped" you. EVERY place in the database where you used qryBooks in a form/report/ or other query, Access replaced the name qryBooks with zzqryBooks. So, qryBooks is NOT being used in at least the form in this picture and who knows where else you might have a similar problem.

The solution to "Name AutoCorrect" is to just turn it off. If you want to rename objects and you want its help, then turn it on, make the changes, open every single object you think might have been impacted by the changes, then and only then turn off "Name AutoCorrect". Pay attention to that previous direction. The thing that gets people in trouble is assuming that the name propagation happens immediately. Well, it does not. The name propagation does not happen until the next time Access has to open the object that got changed. That is when Access recognizes there was a change and looks in its change log table to figure out what happened and than it fixes up the object it just opened. So, with this behavior in mind, you will see that if you don't open all the objects you changed, they might not get changed for weeks. Lots can happen in that time and multiple changes might stack up on each other causing great confusion. It is quite likely that this "feature" is responsible for certain types of database corruption when unapplied changes get stacked. Also, if you think you've opened all objects and so propagated all the changes, and turn off "Name AutoCorrect", if you missed an object, it will NEVER get fixed and it will remain broken.

 

[MajP]

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.

Private Sub Form_Current()
    Me.cboAuthor.Requery
End Sub

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.

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 Sub

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.

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 Sub

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.

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

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

SELECT tblauthors.authorid,
       tblauthors.lastname,
       tblauthors.firstname,
       tblbookauthors.bookidfk
FROM   tblauthors
       LEFT JOIN tblbookauthors
              ON tblauthors.authorid = tblbookauthors.authoridfk
WHERE  (( ( tblbookauthors.bookidfk ) IS NULL ));

 

[MajP]

I see now, why you made this form this way but you did yourself NO favor with this reuse. A simpler solution than all the code might have been to populate the other subforms with a different query depending on which of the types was selected from the unbound combo. And in that code, populate the master/child links appropriately so that Access can take care of linking the subforms.

6 of one, half-dozen of another. The amount of work is going to be the same either way. If the OP choose to link the two subforms using a master child link then they would have to write that code (less familar) to change the Master/Child links after each selection. Or each subform applies a filter. Or each selection could choose a different query. Pretty similar code.

 

[ClaraBarton]

I knew you were good, MajP, because I already use a lot of your stuff but you have truly pointed me in the right direction. I also knew about subqueries but did not know this was a place I needed them. Thank you so much.

 

[Pat Hartman]

@MajP has done a lot more work than I did to fix your problem but do review the problem I pointed out. Don't just ignore it.

I also don't see that subqueries are required. Looks like a left join would work as well.

Setting the master/child link is just two lines of code for each of the two subforms in the Click event of the unbound combo as long as you include the column names for the linking field in the combo. If you don't include the column names in the RowSource of the query, then you would need a lot more code but it is still simple code. No decision logic just a Select case with 9 options and four lines of code for each option.

Setting the RecordSource for the other two subformed is one line of code each. So, it looks like four lines of code, again assuming you include the name of the RecordSource query in the RowSource of the combo. So, you might as well include the subform name for the subform control that changes also in the RowSource so that leaves us with:
1. load the subform
2. four lines to set the master/child links, 2 for each subform
3. one line each to set the RecordSource for the two extra forms

I count 7 lines of code. to manage the forms and the filters. I'm assuming the code in the editable subform already validates to ensure that the necessary foreign keys are present.

You shouldn't need requires since setting the RowSource does that automatically. However, I have never set the master/child links with code on the fly so I can't say what would happen there. Worst case is you have to manage setting the FK for the editable subform manually which is just one more line of code.

 

[ClaraBarton]

I'm not ignoring you. I actually scrapped my subforms for one subform but quit to go to bed. TMI? This morning I went back to what was working .My problem is I'm short on time. This is a card catalog I built for our parochial school some years ago. I've learned a lot since but I don't want to throw out the baby with the water. Small bites. I'll get there. Thank you for your time.

 

[MajP]

Not sure if you did it but this would limit some code. Since all your subforms basically show two fields an ID field and a Display field, the trick is to alias the fields in your query to ID, and Display. Then have a textbox bound to ID and a textbox bound to Display in your new generic subform.

Then make queries all with an alias for these fields.
something like
qrylstAuthor
Select AuthorID as ID, [LastName] & ", " & [FirstName] as Display form tblAuthors Order By....

qrylstPublisher
Select PublisherID as ID, PublisherName as Display from tblPublishers

In cboList your Select Case then simply has to set the recordsource for Child0.Form

Now you can go back to the trick of having a hidden textbox, txtLink on the main form.
in you new GenericlstSubForm in the on current event add
me.parent.txtLink = nz(me.ID) 'No matter what query is loaded they all have an alias ID

In sub books the masterlink field will always be [TxtLink]

in the cboList code you would change the childlinkField

  dim frm as access.form
  dim subFrm as access.subform
  set frm = Me!Child0.form
  set subFrm = me!subBooks
  Select Case Me.cboLists.Value
        Case "Authors"
            frm.recordsource = "qryLstAuthors"
            subFrm.linkChildFields = "AuthorIDFK"
        Case "Illustrators"
            frm.recordsource = "qryIllustrators"
            subFrm.linkChildFields = "IllustratorIDFK"
        Case "Publishers"
            frm.recordsource = "qrylstPublishers"
            subFrm.linkChildFields = "PublisherIDFK"
        Case "Series"
            frm.recordsource = "qryLstSeries"
            ...
        Case "Status"
            frm.recordsource = "qryLstStatus"
            ...
        Case "Subjects"
            frm.recordsource = "qryLstSubjects"
 
    End Select

 

[ClaraBarton]

Yes! Got it! Instead of filtering on current?

 

[MajP]

Instead of filtering on current?

It is just one technique. Or you can still filter on the current record, but then in the generic subform you would need a select case to determie what to filter.

Select case me.recordsource
case qrylstAuthor
me.subBooks.form.filter = "AuthorIDFK = " & me.ID
case qryLstPublisher
me.subbooks.form.filter = "PublisherIDFK = " & me.id
.....

 

[Pat Hartman]

I fixed the three-part form to be more straightforward and take less code to get right. instead of replacing the first control with different forms, I used a listbox bound to a union query. I modified the tblList to include the two additional fields that the process needed. Although it turns out, it only needed one additional field, the query (more on this later).

When you choose an item from the combo, the book list subform is modified to use the query from the tblList rather than a hard coded table/query. This eliminates the problem with duplicates caused by joining to the two many-side tables -- Authors and Subjects. The listbox is set to null so no value is selected and the other two forms are requeried so they are also blank. They only fill when something is selected from the listbox that links to at least one book.

The magic behind the linking of the forms is done with a where clause in the queries. The where clause of the book list subform gets its value from the listbox (it uses a different query for each type from the combo. The where clause of the book edit subform gets its value from the book list subform. So, 4 links to 3, 3 links to 2, 2 links to 1 which is the combo.

I stripped out all the unnecessary tables from the books detail form. When there is only one field from the lookup table that needs to show, the combo is all you need.

I didn't test ANYTHING I didn't change so if your validation is wrong (and I think it is), it is STILL WRONG.

You also need to add another subform to the book edit form so you can add/change/delete subjects as you do authors.

Here are ALL the changes I made. If you end up with wanting to add additional options to the combo, you need to do 3 things.
1. Add a row to tblList
2. Add a select to the Union
3. Add a query for the new option

That is how you design an application that can grow.

The code for the combo is:

Private Sub cboLists_Click()
    Me.lstTypeDesc = Null
    Me.lstTypeDesc.Requery
    Me.subBooks.Form.Requery
    Me.subDetail.Form.Requery
End Sub

The code for the listbox is:

Private Sub lstTypeDesc_Click()
    Me.subBooks.Form.RecordSource = Me.cboLists.Column(1)
    Me.subBooks.Form.Requery
    Me.subDetail.Form.Requery
End Sub

The union query is:

SELECT "Authors" AS Type, tblAuthors.AuthorID AS TypeID, IIf([LastName] & ""="",IIf([FirstName] & ""="","UnKnown",[LastName] & (", "+[FirstName])),[LastName] & (", "+[FirstName])) AS TypeDesc
FROM tblAuthors
Union SELECT "Illustrators" as Type, tblIllustrator.IllustratorID as TypeID, Illustrator As TypeDesc from tblIllustrator
Union SELECT "Publishers" as Type, tblPublisher.PublisherID as TypeID, Publisher As TypeDesc From tblPublisher
Union SELECT "Series" as Type, tblSeries.SeriesID as TypeID, Series As TypeDesc From tblSeries
Union SELECT "Status" as Type, tblStatus.StatusID as TypeID, Status As TypeDesc From tblStatus
Union SELECT "Subjects" as Type, tblSubject.SubjectID as TypeID, Subject As TypeDesc From tblSubject;

the contents of tblList is:

List    BookListQuery                       ChildLink
Authors        qryBookList_WithAuthors      AuthorIDFK
Illustrators    qryBookList_WithIllustrator   IllustratorIDFK
Publishers    qryBookList_WithPublisher    PublisherIDFK
Series           qryBookList_WithSeries         SeriesIDFK
Status           qryBookList_WithStatus        StatusIDFK
Subjects       qryBookList_WithSubjects     SubjectIDFK

The two queries Authors and Subjects have to join to the child tables to apply the criteria.
Each Where argument of the different queries, refers to a different field. The select clauses are identical

WHERE (((tblBookAuthors.AuthorIDFK)=[forms]![lstfrmEdit]![lstTypeDesc]));
WHERE (((tblBooks.IllustratorIDFK)=[Forms]![lstfrmEdit]![lstTypeDesc]));
WHERE (((tblBooks.PublisherIDFK)=[Forms]![lstfrmEdit]![lstTypeDesc]));
WHERE (((tblBooks.SeriesIDFK)=[Forms]![lstfrmEdit]![lstTypeDesc]));
WHERE (((tblBooks.StatusIDFK)=[Forms]![lstfrmEdit]![lstTypeDesc]));
WHERE (((tblBookSubject.SubjectIDFK)=[Forms]![lstfrmEdit]![lstTypeDesc]));

If I hadn't ended up adding the query names to the tblList, they could have been built using VBA and that would have required the third column I added to tblList. Since I don't like building embedded SQL, I went with making querydefs and adding their names to tblList. Six querydefs with three options was almost a wash especially once you factor in the Case statement so I went with the querydefs to eliminate the CASE and the embedded SQL. With more code reuse, I would have built the SQL with VBA.

 

[Pat Hartman]

OK, I uploaded the file

 

[Romio_1968]

Pat Hartman, this is really something !!!

yet, some error are trigered during use...
I wil try to understand your way of thinking and doing the job and if is not too much, I will come back to you for guidance or help in fixing the bugs.

Thank you and I apreciate a lor your effort and patience with me
R

 

[MajP]

Solved - Many to many

To keep it verry simple, i will describe a process> I have two tables 1. Book Tiltles. Fields are Book_ID, Title (and some other, not relevant) 2. Authors. Fields are Author_ID, Author_Name One book may have none (when it is a collective author), one, or many authors Same author can write one...

www.access-programmers.co.uk

Pat Hartman, this is really something !!!

yet, some error are trigered during use...
I wil try to understand your way of thinking and doing the job and if is not too much, I will come back to you for guidance or help in fixing the bugs.

Thank you and I apreciate a lor your effort and patience with me
R

@Romio_1968
To be clear @Pat Hartman only fixed issues in the subform. @ClaraBarton developed the database. You may want to look at the overall design and pose questions to @ClaraBarton
Also use the search feature. I thought bookstores were going the way of video stores, but I am amazed there are lots of bookstores and library questions and examples on this forum.

Help with bookstore db

Sooo I hope that someone is still reading this thread. I have gone through several forums looking for a solution to a similar problem, and many unsuccessful attempts to solve it, but none have explained why I was having this problem or how to fix it. I realize now my own database is probably not...

www.access-programmers.co.uk

Database Design for a Book Store

Hi I am designing a database for a bookstore. Customer can place and order Customers can leave a review for a books in catalogue but can only leave 1 review per book Books will be defined in one genre only For this example a book will be written by one author ( I know really you would need...

www.access-programmers.co.uk

 

[Pat Hartman]

yet, some error are trigered during use...

If you don't tell us what the errors are, we can't help you.

 

[ClaraBarton]

Here's my copy that finally works. Have at it.

 

[Pat Hartman]

It doesn't actually work.
There are duplicates in the Authors list because the query joins to the books table which it shouldn't. It probably also drops authors without books if there are any.
Worse problem for Subjects. It duplicates as well as drops.
You can't update/add records in the #2 list which is why you didn't like my solution which worked otherwise.
The forms don't go blank when nothing in the first list matches.
The Book form shows duplicates

You might want to review my solution, one more time.

 

[ClaraBarton]

imagine that!

 

[Romio_1968]

A little help, again, pls

I am trying to replicate some of ClaraBarton's database
By now, I have a main AddTitle form an a subform that should add author (not finished yet)
Without the subform, the main form works as it should (at this time, again).
Yet, after the sub is embeded, the main does not work anymore.

Problems so far:
On opening the main Add_Title_Frm form, the Title_ID control labeled Cod Tiltu is filled with the new Title_ID record from Titles table (Autonumber), as it sould
It allows me to fill all the data in the main form
When I am moving (getting focus) to the sub form, the data from the mai suddenly get messed:
All fields except Call_No (label Cota) and DomainCombo_1 (label Domeniu principal) are wiped out and the Title_ID control indicates (New)

Annother thing, wile working with the forms in design view, sometimes a new empty record is added to Title table

Please help me debugging this.
Thank You


(Please nte that I posted the same issue here> Form-Subform Conflict )

 

[ClaraBarton]

Ok, Mr. Hartman, I tried... see attached.
I didn't do it before, because frankly, yours didn't work and I didn't have time to figure out why.
Now I've spent the time and need a little more help. I think my problem is with the linkchildfield.
Thank you for your patience!
I know I'll need to add edit lists to the detail but I haven't gotten that far yet.
No rush. I have to go buy groceries.

 

[Pat Hartman]

because frankly, yours didn't work

That has no meaning. What error were you getting? What results were incorrect?

The "fixed" form opens with both 3 and 4 populated which it shouldn't since nothing has been selected from 1 and 2.
When I pick authors and then Borland, Kathryn, I get error 3071 - the expression is typed incorrectly .....
Most of the other options don't work at all

I don't know what you did but you broke the forms when you imported it. Go back to the original and import all the parts again. I spent a lot of time fixing this originally. I'm not doing it twice.

The version in this copy of the db works. Test it. If it doesn't work, I'll figure out why. Once you've messed with what I did, I have no idea what you broke.