Multiple combobox selections in subform to filter other comboboxes

[lirantha]

I'm an absolute beginner (a few weeks with Google and these forums) with Access, SQL, and VBA, so my apologies if this has been answered a thousand times. (And if this question would fit better in a different section, please let me know!)



I've got a subform dealing with bibliographical information. I've just normalized the tables so publications are in their own table (tblPublication), and page number references, etc. are in another (tblPublicationEntry). There is a one to many relationship between tblPublication and tblPublicationEntry using PublicationID as the primary/foreign key.

tblPublication includes the fields Corpus, CorpusVolume, AuthorLast, AuthorFirst, and PubYear. Depending on the publication, the value for some of these fields may be null.

I want to be able to get to a single pre-existing record in tblPublication by entering data in one of three combinations of comboboxes: 1) cmbCorpus and cmbPubYear, 2) cmbCorpus and cmbCorpusVolume, or 3) cmbAuthorLast, cmbAuthorFirst, and cmbPubYear.

The first selection for each combobox tree should filter all of the others. Preferably, the comboboxes further down the tree should display the first non-null entry after selections are made in the first comboboxes, rather than displaying a blank field. (Though I still want to be able to select a blank field in order to create new records as needed.)

I tried just doing AuthorLast and AuthorFirst to start out with, following some of the tutorials I found for filtering the results of one combobox based on another, and have been running into some issues:

When I change the AfterUpdate event on cmbAuthorLast to say "Me.AuthorFirst.Requery", it seems to work when I open the subform by itself. But the second I try to do the same thing from my main form, it tells me "Field cannot be updated". I've checked the properties of the subform half a dozen times, and nothing is locked.

Similarly, I've got the Record Source for cmbAuthorFirst set to filter results based on forms!fsubPublication.cmbAuthorLast. Again, that works fine when I open the subform by itself, but when I try it from the main form it pops up the Enter Parameter Value dialog box.

Clearly I'm missing something in the syntax of my combobox references or the structure of my subform - but everything else in the subform still works when it's embedded in the main form. Any suggestions?

[June7]

Don't include the form reference as prefix in combobox RowSource sql statement.

The Me.AuthorFirst.Requery should work if the code and the referenced control are on the same form.

If you want to provide db for analysis, follow instructions at bottom of my post.

[keviny04]

When I change the AfterUpdate event on cmbAuthorLast to say "Me.AuthorFirst.Requery", it seems to work when I open the subform by itself. But the second I try to do the same thing from my main form, it tells me "Field cannot be updated". I've checked the properties of the subform half a dozen times, and nothing is locked.

When you say your subform seems to "work", do you mean your subform is updatable? And that after you use the combo boxes to filter the subform, the subform becomes non-updatable? A form would give you the "Field cannot be updated" error if its underlying recordset is non-updatable. If it is updatable before but not after, the combo box may be passing the wrong filter info to it.

Similarly, I've got the Record Source for cmbAuthorFirst set to filter results based on forms!fsubPublication.cmbAuthorLast. Again, that works fine when I open the subform by itself, but when I try it from the main form it pops up the Enter Parameter Value dialog box.

The "Enter Parameter Value" popup would indicate syntax errors in your combo box's SQL statement. Open up its RowSource property to check its SQL.

[lirantha]

Database zipped and attached - thanks in advance!

June7 - Okay, so I've currently got this for the RowSource SQL in cmbAuthorFirst:

SELECT tblPublication.AuthorFirstFROM tblPublication
WHERE (((tblPublication.AuthorLast)=[forms]![fsubPublication].[cmbAuthorLast]))
ORDER BY tblPublication.AuthorFirst;

So, without the form reference, the WHERE clause should be... "tblPublication.AuthorLast=cmbAuthorLast" ?


keviny04 - The subform is completely updateable when opened by itself, yes - both before and after filtering with the combobox. But when it's embedded in my main form (frmInscription), creating a new record and then typing into cmbAuthorLast gives me an error. This was not the case before I added the AfterUpdate event.

EDIT: ...Actually, shoot. I think it has to do with not having normalized this properly when I split tblPublication apart from tblPublicationEntry. I just rechecked an earlier version and the same error is present without the AfterUpdate event.

[June7]

Yes: [AuthorLast]=[cmbAuthorLast]

I would use GotFocus event of cmbAuthorFirst to force its requery.

Might want to use AfterUpdate of cmbAuthorLast to set AuthorFirst to Null.

These edits in your db work for me.

However, question db structure. Author names should not be duplicated in tblPublication table. Authors should be in tblAuthors table and save only ID into tblPublication.

[keviny04]

Database zipped and attached - thanks in advance!

June7 - Okay, so I've currently got this for the RowSource SQL in cmbAuthorFirst:

SELECT tblPublication.AuthorFirstFROM tblPublication
WHERE (((tblPublication.AuthorLast)=[forms]![fsubPublication].[cmbAuthorLast]))
ORDER BY tblPublication.AuthorFirst;

So, without the form reference, the WHERE clause should be... "tblPublication.AuthorLast=cmbAuthorLast" ?

[forms]![fsubPublication].[cmbAuthorLast] should be [forms]![fsubPublication]![cmbAuthorLast]. "!" instead of ".". That is what gave you the "Enter Parameter Value" error. Also, you can use the simpler syntax as June7 suggested.

keviny04 - The subform is completely updateable when opened by itself, yes - both before and after filtering with the combobox. But when it's embedded in my main form (frmInscription), creating a new record and then typing into cmbAuthorLast gives me an error. This was not the case before I added the AfterUpdate event.

EDIT: ...Actually, shoot. I think it has to do with not having normalized this properly when I split tblPublication apart from tblPublicationEntry. I just rechecked an earlier version and the same error is present without the AfterUpdate event.

l looked at your database and noticed your main form and subform have a master-child relationship, and the linking field is "HD" on both forms. But the subform does not have a "HD" field. So that is the cause of your not being able to enter data into the subform. Either unlink the two forms, or link them properly. But you can't link the two because, according to your relationship diagram, both tables are masters. I.e. they are on the "one" side of a one-to-many relationship with another table.

[lirantha]

June7 - Thank you for the syntax suggestion! Implemented. I understand the Author information is not properly normalized. At the time, splitting the Author information into its own table seemed like additional structure for little benefit, since I don't care to track Author information except in combination with Publication Year as a way to look up a particular citation. If it would make forms and queries easier to write, I could change it, but I expect the quantity of duplicate author information to be fairly low.

keviny04 - I thought the after-the-fact normalization process went too easily. I need a many-to-many relationship between tblInscription (HD=primary key) and tblPublication (PubID=primary key), which I was trying to do by using tblPublicationEntry (EntryID=primary key, with HD and PubID as foreign keys) as a junction table. So as far as I've been able to understand that concept, the tables are set up the way they need to be, but if the forms are not working, I'm not representing something there correctly. Maybe having to do with the link Master/Child fields on the subform properties?

EDIT: HD is already present in the SQL for the Record Source for fsubPublication - Master field is HD on tblInscription, Child field is HD on tblPublicationEntry. Is the problem that I'm pulling information from two different tables, one of which doesn't (can't) have HD, onto the same subform?

[keviny04]

EDIT: HD is already present in the SQL for the Record Source for fsubPublication - Master field is HD on tblInscription, Child field is HD on tblPublicationEntry. Is the problem that I'm pulling information from two different tables, one of which doesn't (can't) have HD, onto the same subform?

Now I see what the problem is. Even though HD is in the fsubPublication form's SQL, it is not on the form itself. Add a textbox and bound it to HD, and everything should work.

[lirantha]

Now I see what the problem is. Even though HD is in the fsubPublication form's SQL, it is not on the form itself. Add a textbox and bound it to HD, and everything should work.

Huh. Well, that lets me see the problem, at least: when I open the subform from within the main form and advance to a new record in the subform, HD isn't autopopulating from the main form into the bound textbox on the subform. (It does on my original version!) Any idea why?

[June7]

The subform RecordSource is a query with INNER JOIN of tables. INNER JOIN requires related records in both tables for data to display. Change join type.

A form should enter/edit data for only one table. Why include tblPublication in fsubPublication RecordSource? Should not allow edit of 'lookup' table fields from the data entry form for tblPublicationEntry. Do not bind comboboxes to tblPublication fields. You will edit the 'lookup' record and screw up your data.

The FK should not have to be in a bound textbox on the subform - as long as it is in the subform RecordSource, the Master/Child links should find it - but doesn't hurt to have in textbox (can be hidden). The subform linking seems to be working as is - the associated records are displaying.

[keviny04]

Huh. Well, that lets me see the problem, at least: when I open the subform from within the main form and advance to a new record in the subform, HD isn't autopopulating from the main form into the bound textbox on the subform. (It does on my original version!) Any idea why?

Which bound textbox on the subform? On the fsubPublication subform, I don't see a textbox bound to HD. I haven't seen your original version. Anyway, you can just add the HD-bound textbox back in.

[keviny04]

A form should enter/edit data for only one table. Why include tblPublication in fsubPublication RecordSource? Should not allow edit of 'lookup' table fields from the data entry form for tblPublicationEntry. Do not bind comboboxes to tblPublication fields. You will edit the 'lookup' record and screw up your data.

Maybe the OP really does have a need to edit the data. I notice her tblPublication table has a lot of blank first names and last names. Maybe the OP wants to fill them in by looking up past records.

The FK should not have to be in a bound textbox on the subform - as long as it is in the subform RecordSource, the Master/Child links should find it - but doesn't hurt to have in textbox (can be hidden). The subform linking seems to be working as is - the associated records are displaying.

Sorry, I forgot you guys are using Access 2010. On my Access 2007, that would not work.

[lirantha]

The subform RecordSource is a query with INNER JOIN of tables. INNER JOIN requires related records in both tables for data to display. Change join type.

Ah, thank you so much! A RIGHT JOIN fixes that issue. So now it's just back to the original multi-combobox filtering problem. (See below.)

A form should enter/edit data for only one table. Why include tblPublication in fsubPublication RecordSource? Should not allow edit of 'lookup' table fields from the data entry form for tblPublicationEntry. Do not bind comboboxes to tblPublication fields. You will edit the 'lookup' record and screw up your data.

I'm using the comboboxes to auto-complete author names (and, if I can get all the filters working, data in the other comboboxes) to speed up and standardize data entry when the same publication comes up associated with a new inscription (HD) record. But I also want to use the comboboxes to add new tblPublication records. In other words, that table isn't just a static 'lookup'; I need to add to it as I run across references to new publications. (Given my data sources, there's no way for me to isolate all possible publication information and add it all at once at the beginning. And doing it through a separate form would complicate the data entry process; I'm trying to do this all in a single stream as I enter records for individual inscriptions.)

Now, I'm not sure that that will still work the way I have it set up now. Before I split off the Author information into tblPublication, all of the tblPublication information was in tblPublicationEntry, and none of it was normalized. It worked for adding new entries, but duplicated a ton of data and didn't let me reference unique publications easily. I've been trying to do better!

So, to restate the problem: What I want is a way to enter enough information through a couple of comboboxes to be able to filter down to a single row in tblPublication - and use that to auto populate any remaining comboboxes with any values other than null contained in the record (null values can remain null). But if I enter new/different information into any one of those comboboxes from what is already contained in any preexisting tblPublication record, I want that to create a new record in tblPublication. The new record should include in its fields any values I had selected from the comboboxes up to that point as well as the new/different values just added.

The AfterUpdate part is working now (thanks again!), so it's just a question of what I need to watch out for while trying to solve the problem described above. I'm currently wondering if toggling the Unique Rows property to Yes on the Record Source query for these comboboxes would make it do some of what I want.

The FK should not have to be in a bound textbox on the subform - as long as it is in the subform RecordSource, the Master/Child links should find it - but doesn't hurt to have in textbox (can be hidden). The subform linking seems to be working as is - the associated records are displaying.

Right, that part is working.

[June7]

As noted, don't think the comboboxes should be bound to fields from tblPublication. Review my previous post again.

Need to save PubID into tblPublicationEntry. You want to use dependent comboboxes to help isolate the desired record. However, a single combobox could serve.

RowSource: SELECT PubID, AuthorLast & ", " & AuthorFirst & " : " & PubYear As AuthYr FROM tblPublication;
ColumnCount: 2
ColumnWidths: 0";2.0"
BoundColumn: 1
ControlSource: PubID (the field in tblPublicationEntry)
AutoExpand: Yes

[lirantha]

June7 - I've been playing with the above code for a little while and can't get it to do quite what I want. As written, that combobox gives me a list of all existing Author/Year combinations, but doesn't allow me to enter new data (or even type in anything to help narrow down the list) because it's tied to the PubID. (And in order to isolate some of these records, I need to search other fields; there are a number of cases where Author/Year don't exist and I need to look at Corpus and/or CorpusVolume instead. I could add that in, but it would get messy.) But my main concern is the inability to enter new data into tblPublication using that method.

I've reviewed your earlier post again, but I think I'm a bit lost. Probably I'm missing something very basic (I had to look up the difference between Control Source and Row Source just now, for instance).

I hear what you're saying about how forms should enter/edit data for only one table (though I'm not sure I fully understand why). I do understand the need to save PubID into tblPublicationEntry, which my combobox selection method isn't doing at the moment. But my ideal solution from the data entry point of view is more complex than picking a pre-existing row from a list; it's a combination of searching for that row by entering data in one or more fields, and creating a new row from the entered data if no pre-existing row matches 100%. If it's not appropriate to tie the combobox ControlSource to the corresponding row in tblPublication in order to make that possible, then I need some kind of "create new record" button that can take the information entered in the comboboxes and make a new row in tblPublication. Or some other way to accomplish the same thing; I'm very open to suggestions.