Display Result in Query based on radio button (1 Viewer)

Kayleigh

Member
Local time
Today, 14:31
Joined
Sep 24, 2020
Messages
706
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

You've got your good things, and you've got mine.
Local time
Today, 10:31
Joined
May 21, 2018
Messages
8,463
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.

Code:
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

Member
Local time
Today, 14:31
Joined
Sep 24, 2020
Messages
706
So you mean replicate the entire query except for the parent field?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:31
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 14:31
Joined
Sep 24, 2020
Messages
706
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.
 

Attachments

  • Covid test.png
    Covid test.png
    25.7 KB · Views: 102

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Feb 19, 2002
Messages
42,970
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

You've got your good things, and you've got mine.
Local time
Today, 10:31
Joined
May 21, 2018
Messages
8,463
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.)
 

Kayleigh

Member
Local time
Today, 14:31
Joined
Sep 24, 2020
Messages
706
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've got your good things, and you've got mine.
Local time
Today, 10:31
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 14:31
Joined
Sep 24, 2020
Messages
706
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

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Feb 19, 2002
Messages
42,970
I would be very surprised if it took more than a couple of hours.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom