Form-with-subform from two-field relationship (1 Viewer)

[scythe33]

So I have a database that looks like this:

I'm trying to make a form that displays one record from the "middle" table with a subform that shows all of the related records from the "inner" table. Unfortunately whenever I go through the Form Wizard to do this, I end up with an empty subfom and the error message "One of your parameters is invalid.".

I'm not sure how to create subforms otherwise; Google simply pops up a deluge of blog tutorials about Access 2016 or 2013 that don't work in Access 2019. Apparently the fact that I have to match two keys means that the Form Wizard won't help me.

Also I can't seem to find any good documentation about subforms that A: is actually for Access 2019 and B: doesn't tell me to use the Form Wizard, which obviously can't be the right way to do anything complicated.

My apologies for being impatient -- but if anyone has any good documentation links, I'd be very grateful. So far it seems like I have a choice between condescendingly simple tutorials and brutally unadorned API specifications.

 

[plog]

You protecting us from obscene field names? Or perhaps the field names themselves are proprietary?

Your relationship is incorrect, in a few ways.

1. You shouldn't have circular relationships. There should be only one way to connect one table to another. Yours allows a clockwise path and a counter clockwise path. That's incorrect.

2. Either you have a 1 to many relationship or a many to many relationship between tables. Between inner and middle you have a 1 to many relationship and a many to many relationship. That's wrong as well.

I suggest you ungenerize your data, give us a one paragraph description of what your organization does without any database jarong, give us one paragraph about what this database's purpose is with minimal database jargon.

 

[theDBguy]

Hi. Welcome to AWF!

Are you able to share a demo version of your db with test data? If you don't like to use the Wizard, or it's too limited for your requirements, you should be able to "manually" create or add a subform to your main form and configure it the way you need to.

 

[scythe33]

You protecting us from obscene field names? Or perhaps the field names themselves are proprietary?

Tell me, how careful would this make you want to be?

2020-12-31 08:51 | Archive of HHS.gov

www.hhs.gov

Simply: I work for a hospital; this database tracks patient data; I do not want to go to jail. I know that field names probably should not compromise anybody's privacy (and they are not personal), but I didn't want to fall afoul of some technicality particularly w.r.t. (3), (7), or (10).

With that said, there is no actual patient data in the database right now, so maybe this is not a big deal. HIPAA scares me. I'll think about uploading a copy of the .accdb file.

Between inner and middle you have a 1 to many relationship and a many to many relationship. That's wrong as well.

I think this is just a result of the display. The field which relates outer (one) to middle (many) by a one to many is also relating middle (one) to inner (many).

I removed the outer->inner relationship, since that field is already provided by middle anyway. I wasn't sure if I should have it there, so thank you.

But having done that, the Form Wizard still gives an empty subform (the subform is generated but not displayed) and "One of your parameters is invalid" when I try to create a form displaying "middle" record and subform with related "inner" records.

you should be able to "manually" create or add a subform to your main form and configure it the way you need to.

I would like to do this, but I don't fully understand how. In particular, I'm not sure what "One of your parameters is invalid" means, so I don't know how to fix it.

 

[theDBguy]

I would like to do this, but I don't fully understand how. In particular, I'm not sure what "One of your parameters is invalid" means, so I don't know how to fix it.

Hi. The good thing about manually creating a subform is you can control what it does, so you shouldn't get an invalid parameter error because you wouldn't be putting any parameter that does not belong. Unfortunately, without seeing your setup, I won't be able to tell you what to put instead. Hopefully, though, this website could help guide you on how to do it.

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.office.com

 

[scythe33]

Cool, I think I got it working. Trying to do the "drag one form onto another" revealed that creating a form from the "middle" table gave an error even without a subform. Clicking around a little more revealed that there was an index defined for a column I had deleted earlier. Deleting that and creating an empty form from "Form Design" eventually led to something that seems to function.

 

[theDBguy]

Cool, I think I got it working. Trying to do the "drag one form onto another" revealed that creating a form from the "middle" table gave an error even without a subform. Clicking around a little more revealed that there was an index defined for a column I had deleted earlier. Deleting that and creating an empty form from "Form Design" eventually led to something that seems to function.

Hi. Congratulations! Glad to hear you got it sorted out. Continued success with your project.