Help with query/subform

Flora2021

New Member
Joined
Apr 28, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am trying to create a subform in a form for employee records. This subform will pull from a table and use a query or a filter . On each employee record, there is a multivalue field that will contain dept codes. sometimes there is only one but other times there are multiple departments in this field.
In the CDI table that i want the data pulled from, these department codes are also present for each document type. Some might be a single department or others may also be for multple departments. Basically I want the filter to only pull the document types that match any of the dept codes listed in the employee record. So say Julie Smith is in dept A, and B any records that would contain A or B would need to be returned and show under this subform. I can attach screenshots but does this sound doable? Thanks so much:)
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
IMO best approach is to base any form on a query and not a table, and most often, subform relates to one table/query. If your sf records will be fields from more than one table, a query is a must but it might be read only. Start by creating this query and if you get it to return what you want, base the form on it. However, if it must be editable, test that in the query first.

mv fields are not a great idea in spite of the fact that M$ makes them enticing to use.
See what you think.

 

Flora2021

New Member
Joined
Apr 28, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi, How would you suggest I run the query? My boss is insisting that the "sub form"? be editable and be able to write back to the main table. The records for the sub form will all be from the same table called CDI though. I got this to half work but the "sub form" only pulls back one of the values on the multiform and not all of the data in the multivalued field. I appreciate any help.
 

Flora2021

New Member
Joined
Apr 28, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
The 1st image shows the form and subform- It looks like its pulling the data that matches in the "dept" field from the employee form, but the fields for the subform(cdi) are not populating the values.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    30.2 KB · Views: 6
  • cdi.PNG
    cdi.PNG
    34.2 KB · Views: 6
  • employees.PNG
    employees.PNG
    11.8 KB · Views: 6

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
It looks like its pulling the data that matches in the "dept" field from the employee form,
Not knowing your data, I'll have to take your word for that.
the "sub form" only pulls back one of the values on the multiform
Perhaps it is only seeing the first value in the multi value data.

Not sure what you're asking. If main table means the one side (e.g. customer) of the many e.g. (orders) it doesn't work that way. The main form is based on the one, the subform is based on the many. The subform control links the 2 forms through its Link Master and Link Child properties. As long as there is a parent record, you can add child records in the subform, but those records go into their own table - there is no such thing as editing/creating subform records which affect the "main" table. It just looks like that to those who don't know any better. In conjunction, the relationship between these 2 tables should be set to Enforce Referential Integrity, otherwise you can end up with orphan records (on the many side) who have no parent record (the one side).

Those are the basics. Doing research on form/subform relationship would probably be a good idea. However, when it comes to mv fields that you mentioned, I can't assist because I'd have to figure out how something I wouldn't use affects all of this. Best I can say is (using customer as the main table field for example)
- create the query that returns main records and include that in a new query (as if it were a table)
- equal join the subform table to the main query as appropriate and put only subform table fields in the query design grid
- run the query just to test. If you get records try to edit one. Then try to include main query fields in the grid and run query
- test if you can edit either main query field or subform query field

If at any point you can't edit or add records there could be several reasons. I suspect your first roadblock will be getting records at all, or if you get past that, being able to edit them - due to the mvf's.
 

Flora2021

New Member
Joined
Apr 28, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi, I am confused. So I create a new query for the main record which is the CDI_master table but I am not sure what you mean by equal join a subform table. Would this be to create a new subform for the employees table? Thanks:)
 

Attachments

  • Capture2.PNG
    Capture2.PNG
    102.3 KB · Views: 4

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
If you're saying you don't know how to join tables and queries, see if this helps.
 

Flora2021

New Member
Joined
Apr 28, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
If you're saying you don't know how to join tables and queries, see if this helps.
Hi, I know how to join queries and tables and relationships. I just was confused by the steps you mentioned. I am not sure what tables you are saying should be made into sub forms or queries.
I initially just wanted a "employee form" that is from the employee table. Then there would be a subform in the employee form that would pull the all records and all fields back from the CDI_Master table that contained any of the same values in the dept field in the employee table. I have done this previously with no issue when the matching value was just a singular value such as "Corn" or you defined multiple specific critera, such as "two", "three" etc. but I am not sure not sure how to do the query expression that would return records that contained one or all varying values in that field. I thought it would be something like... IF cdi_master.assigned to dept contains * employee.data but I am lost and could not find anything similar searching.



- create the query that returns main records( I assume this would be the CDI_Master that contains the data to pull back?) and include that in a new query (as if it were a table) ? Not sure what you mean by include in a new query?

- equal join the subform table (What would this subform table be? ) to the main query as appropriate and put only subform table fields in the query design grid
- run the query just to test. If you get records try to edit one. Then try to include main query fields in the grid and run query
- test if you can edit either main query field or subform query field
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
Perhaps you're not aware that you can put queries into the design grid of a query - same as if it were a table? Taking a step back might be a good idea because really, a main form should be bound to one query (or table) and the subform to the query/table that holds the related records as noted in post 5 and use the master/child subform control feature. My other suggestions were only to test the impact of the mvf's on your situation but those ideas don't seem to be helping.
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,408
Office Version
  1. 365
I see 5 tables in your graphic.
Can you show us
-the designs of each table, and
- A graphic showing tables and relationships with tables extended to display all columns.

I would restructure to remove the MVF also.
 

Forum statistics

Threads
1,176,090
Messages
5,901,352
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top