Display Result in Query based on radio button

[Kayleigh]

I have an input form which is also bringing up values from existing data. How do I choose to display a result of one field based on radio button in form?

Example: fldParentName: [fldParent1Title] & " " & [fldParent1Surname]

The options would be Parent 1 or Parent 2.

 

[MajP]

Do you have separate fields for each parent? You should not, that is an incorrect database design and would make this much more difficult. Multiple parents should be in a related "child table" (that is ironic but correct).

Are there other filters on this form? If not by far the easiest solution assuming an incorrect database design where you have multiple fields for each parent is to build a parent1 query and a parent 2 query and an all query.

Public sub OptionGroupName_afterUpdate()
  select case optionGroupName.value
  case 1
    me.recordsource = "qryParent1"
 case 2
    me.recordSource = "qryParent2"
 case 3
   me.recordsource = "qryAllParents"
 end select
end sub

 

[Kayleigh]

So you mean replicate the entire query except for the parent field?

 

[MajP]

If you have multiple parent fields then I would copy your query three times. In the first "qryParent1", I would only include the fields for parent 1. In the second "qryParent2" I would only include the fields for parent 2 (where it is not null). The third could have both fields.

Again, based on your first statement I am assuming you have separate Parent 1 and Parent 2 fields. You should have separate parent Records not separate field. If you do not want to fix it then this is what you would need to do.

If you had separate records (correct design) then you filter on some field like "Mom, Dad, Guardian, etc.)

Can you show a screen shot of your table, or post it if no real personnel information? Also a screen shot of the desired form would help too.

 

[Kayleigh]

You are right but as this is a very small detail in my DB, I cannot spend much time on it...
Screenshot of form below - radio buttons in header selects recordsource. And mother/father radio buttons displays appropriate parent name. So would I reselect the recordsource here as well?
I have posted more of this DB in thread here.

 

[Pat Hartman]

Since children can have a variety of guardians, allocating two slots for "parents" doesn't map with reality. Guardians should be a separate table and the table should include a type code if you want to distinguish.

 

[MajP]

Since children can have a variety of guardians, allocating two slots for "parents" doesn't map with reality. Guardians should be a separate table and the table should include a type code if you want to distinguish

we already addressed that and moved on.

Again, based on your first statement I am assuming you have separate Parent 1 and Parent 2 fields. You should have separate parent Records not separate field. If you do not want to fix it then this is what you would need to do.

If you had separate records (correct design) then you filter on some field like "Mom, Dad, Guardian, etc.)

 

[Pat Hartman]

Apparently your advice was ignored.

 

[Kayleigh]

You are 100% correct re having separate guardian table however it is a little hard to restructure database at this point. It will mean a VERY LOT of work from the ground up

 

[MajP]

You are 100% correct re having separate guardian table however it is a little hard to restructure database at this point. It will mean a VERY LOT of work from the ground up

1. I truly doubt it would take much time. Unless there is far more to this database then you are posting
2. I pretty much can guarantee it will save time in the long run. Trust me, I have seen this a million times before, where it becomes a band-aid on top of band-aids to do a simple task because the data is not normalized. This has already been proven. To do a simple thing like show Mother or Father, I had to demonstrate either a pretty involved Union query or build multiple subforms. You were even considering hard coding hiding columns.

So you can choose to do what you want, but you are highly unlikely to save any work IMO
.
Maybe it will never happen with your data set, but in the real world I hate to tell you there is
1 dad1 and dad2
2. mom1 and mom2
3 Mom1, Dad1, Mom2(step mom), Dad2(step dad)
4. Legal Guardian.
...
If this happens to you will have to add more fields to a table or come up with another crazy band-aid. It is a snowball.

 

[Kayleigh]

You have convinced me (!!) but will need to check if my superior is okay with the time it will take to modify.

Take note that databases I post are a small component of a very large database so changes I do will definitely have far-reaching ramifications. Though as you mentioned it is better than the quite complex 'band aids' required to sort issues which keep cropping up.

 

[Pat Hartman]

I would be very surprised if it took more than a couple of hours.

 

[Kayleigh]

Hi I'm back to working on this issue.
Would like some guidance as to how to split current parent table into two as described above.
I actually came across this thread which provides some insight: https://www.access-programmers.co.u...anagement-system-database.250232/post-1275933
See my ER diagram below:

 

[Pat Hartman]

Structure your new guardian table. Then create two append queries. One that selects the mother's data and appends it to guardians and the second selects the father's data and appends that to guardians.

Once all the data has been appended, you can get rid of this table.

 

[Kayleigh]

Done. Thanks