Solved subforms not updating properly

[Pat Hartman]

How many versions of this database are we going to be working with?

You switched to using the listbox I suggested instead of the separate forms but you discarded all the other things I did with the queries to make everything work You don't need a select case for the list box query, you need to use the query from the combo to control #3 so you don't end up with a cartesian product. The Listbox should be bound to the union query which just filters on the combo/

--Series has duplicates in list #2
--#3 has duplicates when multiple authors or subjects. Joining to two unrelated tables causes a cartesian product for this table. Authors * Books * Subjects. That's why I was using separate queries for this subform.
--Author combo has duplicates when multiple books because it joins to books.

Even though I open the app holding down the shift key, something really strange happens if I open a table. When I close it, the whole app closes. Whatever is causing this is going to be a nightmare when you have do do maintenance.

I'm done wasting my time with this. I fixed it once. I'm not doing it again.

 

[MajP]

I did see what @Pat Hartman describes and fixed in mine. I was lost with all the closely named queries. I think now a lot of those are not used.
As Pat describes in the 1st subfomr you are linked to two many to many (authors and subjects). So I made 3 new queries. On for just authors (include author table no subject table), one for just subjects (vice versa), and one for the others (no subject or author tables)

I fixed the relink so you are prompted at startup if the tables are not linked and give a file browser to relink. I demoed an option group which I though was more intuitive. I added some color in the subform so the selected book is gold

Here was the issue

 

[ClaraBarton]

Oh yes. I figured out by following your sample and it works very well. I have yet to add the actual edit/delete lists for each item. I didn't copy your forms in because I wanted to see exactly what was happening. I changed the update a little because it seemed a waste not to use that table. Deleted unused queries... Loaded your sample. Loaded your sample. Loaded your sample!

Public Sub UpdateList()
    Dim lst As Access.ListBox
    Dim subFrm As Access.SubForm
    Dim txtRowSource As String
    Dim txtChildLink As String
  
    txtRowSource = DLookup("[BookListQuery]", "[tblLists]", "[List] = '" & Me!cboLists.Value & "'")
    txtChildLink = DLookup("ChildLink", "tblLists", "List = '" & Me.cboLists.Value & "'")
    Set lst = Me.lstTypeDesc
    Set subFrm = Me!Books

    lst.RowSource = txtRowSource
    subFrm.LinkChildFields = txtChildLink

Small thing but it works. I thank you very, very much.
I appreciate what Mr. Hartman did too.

 

[MajP]

Couple of minor things. I would make that list box very tall because your list of authors, subjects is very large. Short lists with lots of record are painful to scroll. Then when user selects Status you can set it to a short height and tall for everything else. I saw you had the FAYT combo. I would consider doing an FAYT Listbox for these long lists. That way if you remember a first name, part of a name you can likely find it.
There is a class and examples to do this.

Solved - FAYT for Multi Column combo code from MajP question

I have used the great code from MajP FAYT on a few forms and some work perfectly and a few have an issue. I read the post Populating Combobox with a lot of data and the link in there. The issues I am experiencing are When I refresh the underlying table (I click a button on my form and it...

www.access-programmers.co.uk

You will simply have to reinitialize for each list change.
There is a lot going on in this form, and I would maximize it and take up more realestate. It is really compact makeing it seemed cluttered. I am assuming you may not have gotten to it, since the other forms are all maximized.

 

[Pat Hartman]

I did see what @Pat Hartman describes and fixed in mine. I was lost with all the closely named queries.

I see that you are invested in this so I am NOT going to even attempt to fix the problem AGAIN. If you had looked at the version I posted, you would have seen that all the forms sync'd correctly and the books subform did not display duplicate data. The combo's RowSource contained the name of the correct query to use for the books list. NO CASE is required. If you select Authors, you set the books subform to the "Author" query. If you select Subjects, that's the query that you use. The query name is in the table that is used to populate the combo. The Listbox is bound to a union query and filtered by the Combo. THAT's all it takes. I removed ALL the code that was clearing the forms since once they were properly filtered, it was not necessary. I removed the case statement because when you control the queries using a table, it wasn't necessary.

I did NOT fix ANY other problem with any of the forms except the problem with sync'ing them.

 

[MajP]

As far as I can tell everything I posted synchs correctly, and no duplicates. Just did not notice what the OP did the first time. Not sure of what problem you have to fix? You seem kind of spun up about saving a handful of lines of code. Who cares? I went a different approach to demo an option group. More than one way to skin a cat.
More importantly I did not look at what you did because it does not work. The version you posted the form does not even open, just hangs.

 

[Pat Hartman]

The latest version seems to work finally. You can write however much extraneous code as you want to. If I can control a procedure using data from a table, that is my prefered method. But you're right. Who cares. I was only annoyed because I fixed the form on Jan 11th which was 17 days ago But it is fixed again now using a different method with more code but who cares It is fixed finally and we can all move on and I don't have to look at it again.

 

[ClaraBarton]

Oh wait wait! I am using your form! I've learned so much from you... How can I Highlight the matching row in the Edit-Delete form to the listbox? If I use master and child it filters the whole thing down to one record.

 

[MajP]

Since the list sets txtLink (or whatever it is called) to authorID then the edit delete form should link
Master Link Fields: [txtLink]
Child Link Fields: [auhorID]

But why do you need another form to edit/delete? The edit form is not editable since one is a PK the other is calculated. If you want to delete just delete from the listbox.
If it was an edit form then it should be a single form view with author details (or whatever is in the list at that time). And as stated no need for a delete subform.

 

[ClaraBarton]

Oh you're right. That list is redundant and takes up too much real estate. Why oh why can't I wait to post until I've slept on it?

 

[MajP]

A list does not make sense, but instead a single form view with Author Details, Publisher Details, etc would. That could be smaller in a more square size. Then whatever is in the list you could edit, and you could have a delete button too.