Solved Unbound subform syntax

[ClaraBarton]

I have a continuous form with lists on the left and I want to show the related books on the right.
Can you tell me what's wrong with the syntax on line 2 in each case?
Should Child0 become the source name?

Private Sub cboLists_AfterUpdate()
     Select Case Me.cboLists.Value
        Case "Authors"
            Me!Child0.SourceObject = "lstsubAuthors"
            Me!Child0.Form.BookID = Me!Books.Form.BookID
        Case "Illustrators"
            Me!Child0.SourceObject = "lstsubIllustrators"
            Me!Child0.Form.IllustratorID = Me!Books.Form.IllustratorID
        Case "Publishers"
            Me!Child0.SourceObject = "lstsubPublishers"
            Me!Child0.Form.PublisherID = Me!Books.Form.PublisherID
        Case "Series"
            Me!Child0.SourceObject = "lstsubSeries"
            Me!Child0.Form.SeriesID = Me!Books.Form.SeriesID
        Case "Status"
            Me!Child0.SourceObject = "lstsubStatus"
            Me!Child0.Form.StatusID = Me!Books.Form.StatusID
        Case "Subjects"
            Me!Child0.SourceObject = "lstsubSubjects"
            Me!Child0.Form.SubjectID = Me!Books.Form.SubjectID
    End Select

 

[Pat Hartman]

I'm having trouble figuring out what objects you are trying to control. In the combo, if the RowSource is a list of types like "Authors", "Illustrators", etc, where are you getting the ID value from? For example, is BookID the autonumber of a specific row in tblBooks?

Please talk us through this. No code. Just describe the form (a picture would be nice) and what you want to see in Child0.

 

[ClaraBarton]

 

[ClaraBarton]

The left side varies by selection but all the other ID's are hidden in each record on the right. Seems like I should be able to connect them. Only records on the right should be from the selected record on the left.

 

[MajP]

I have a continuous form with lists on the left and I want to show the related books on the right.

After rereading this it is not a syntax issue, but a design issue. That code has more problems than syntax

Private Sub cboLists_AfterUpdate()
     Select Case Me.cboLists.Value
        Case "Authors"
            Me!Child0.SourceObject = "lstsubAuthors"

        Case "Illustrators"
            Me!Child0.SourceObject = "lstsubIllustrators"

        Case "Publishers"
            Me!Child0.SourceObject = "lstsubPublishers"

        Case "Series"
            Me!Child0.SourceObject = "lstsubSeries"

        Case "Status"
            Me!Child0.SourceObject = "lstsubStatus"

        Case "Subjects"
            Me!Child0.SourceObject = "lstsubSubjects"

    End Select

This would be a lot easier if using listboxes instead of subforms because you have to put code in the current event of each of the subforms

Private Sub Form_Current
  'Author SubForm
  if not isnull(AuthorID) then
     me.parent.Books.form.filter = "AuthorID = " & me.AuthorID
     me.parent.books.form.filteron = true
else
   me.parent.books.form.filter = ""
end if

 

[ClaraBarton]

I originally had a datasheet with a subdatasheet for each record. This is a list edit form. Sometimes there are duplicate authors or illustrators. They need to be deleted but the book has to be moved first. It was working fine (move the book, then delete the author, or publisher, or whatever) but you all said, "No. You need a continuous form." So... I changed all my data sheets to continuous forms, and now I only want the books connected to the selected author to show. The Author list goes into the child0 box (on the left). The Books list (on the right) is always the same. There is a hidden book ID under the author name

 

[MajP]

Actually thinking about it a single list would not make it any easier. Your code would still have to do pretty much the same thing. The code I provided should work. It should filter Books to whatever field is selected in Child0. Assuming you add code to each subform. I would even Have a third subform. That would have the book details in single form view. Then for example you select authors from the combo and authors appears in child0. Books assigned to that author are in Books subform. And below that a single form view of the selected book. That could span the length of the bottom. That would be for editing the book such as reassigning Author, Illustrator, ... or deleting.

 

[ClaraBarton]

Thank you. I'll work on it some more. I had dual forms showing at once so clicking on the book showed all detail. It worked fairly well but I had to mess...

 

[MajP]

The reason for the detail subform is the ability to edit.
Assume you have a duplicate author
1 John Smith
17 John Smith

You want to move everything under AuthorID 1.
I click on 17 John Smith it will show all books assigned to AuthorID 17, John Smith
In the detail section my Author combo will have a two column combos showing the AuthorID, and Author Name.
Now I can pull down and select AuthorID 1, John Smith
You would do the same for illustrator, publisher, etc.

 

[MajP]

If cleaning up duplicates is what you need to do it may be easier to do it in sql.
Example. Do a group by query on Author FirstName, LastName and count of AuthorID. Where count of AuthorID > 1. That would give you the duplicate authors. Join qryDupAuthors to tableAuthors by firstname and LastName to return a list of duplicate Authors and ID

qryIDsDupAuthors
1 John Smith
17 John Smith
22 Mike Brown
25 Mike Brown

Determine if these are in fact really duplicates.
Now you can quickly go to table books. Filter by AuthorID = 17 and change to 1. Filter by authorID = 25 change to 22.
Delete 17 from tblauthors, delete 25 from tblauthors.

 

[Pat Hartman]

I don't think you improve the interface by doing this. You end up reusing objects (the subforms) for different forms. But the master/child links are different depending on which pair of forms get loaded. What does this do fo you? Just add the various forms to a menu. Then you can still use the two lists side-by side but only need one line of code to control them. You also either have to edit using the left side list or pop up an edit form. There are better solutions.

Probably a better solution is a menu with the "tyoe" options. Then a single record form with a combo to search to pick the record and a subform for the related child records.

 

[MajP]

A slightly easier would be to have just a wide continuous form with all the book information. Include comboboxes on each of the currently hidden keys allowing you to select or change the author, illustrator, Publisher, ... Subject
In the header you have a combobox for Author, Illustrator, publisher... Subject

In the after update you change the filter
example

Public Sub cmboSearchAuthor_AfterUpdate()
  if not isnull(me.cmboSearchAuthor) then
    me.filter = "AuthorID = " & me.cmboSearchAuthor
    me.filteron = True
 else
  me.filter = ""
end if
end Sub

If you want to get slick and write only a single procedure, then in each of the Header combos put the name of the Field to filter
The tag for cmboSearchAuthor is "AuthorID"

Now make a single function

Public Function FilterByCombo
  if not isnull(me.activeControl) then
    me.filter = activeControl.Tag & " = " & me.activeControl
    me.filteron = True
 else
  me.filter = ""
  me.filteron = false
end if
clearCombos(me.activeControl)
end Function

on each of the combos AfterUpdate property put
=FilterByCombo()

Also add code to blank out the not selected combos

Public Function ClearCombos(NoBlank as control)
  dim ctrl as access.control
  for each ctrl in me.controls
   if ctrl.controltype = accombobox and ctrl.tag <> "" and ctrl.name <> NoBlank.Name then
    ctrl.value = null
  end if
  next ctrl
end function