Issues with subform and master form

[asmith533]

Have a form "Update customer request" in my database. Within that form, I have a control drop down that contains employee names. Within my sub form, I have 3 fields: Assigned to, received by and collaboration with. How do I get my control drop down to display records for that employee whether they received it, assigned it or were part of the collaboration?

[smithse]

Hi asmith533,

Which 'form' is the source of your combo box data? Do you wish to see the employee names as a list (i.e. 3 names) from the subform, or update the subform based on the combo box selection on the parent form? It sounds like the former.

[asmith533]

Hi asmith533,

Which 'form' is the source of your combo box data? Do you wish to see the employee names as a list (i.e. 3 names) from the subform, or update the subform based on the combo box selection on the parent form? It sounds like the former.

The source of the combo box is my employee table. The Employee ID in the employee table is used to populate the three fields mentioned above. I want to be able to update the subform based on the combo box selection in the parent form.

[smithse]

The source of the combo box is my employee table. The Employee ID in the employee table is used to populate the three fields mentioned above. I want to be able to update the subform based on the combo box selection in the parent form.

So, if I understand correctly, your combo box might have 10 employees, but you select one to fill the fields in the combo box below. If this is correct, then do you have another combo which chooses whether they were the Assigned To, Received By or Collaboration person, or is that one person all of those three fields?

Sorry if that confuses things, I am trying to understand what you want to achieve.

If it was me, I would be selected what the person was, i.e. the collaborator, receiver or assignee and then selecting the person. From there I would update the backend table and refresh the form to display the changes.

Let us know, and I am sure we can find a solution!

[asmith533]

Well, the employees using this database want to be able to see and update any and all requests they played a part in. They also want to do it in as few clicks as possible. (There is a submit customer feedback button that they hate as well and want to get rid of and have the fields show up on the request form, whole other issue I'm not sure I'm going to address yet). Back to the topic... The employees are asking that they be able to select their name from the drop down list and all requests associated with their name appear.

[smithse]

Oops, please excuse the double post.

So here goes another question: from your 'main form' or parent form, how many records are you displaying in your subform? If it is more than one, it gets 'ugly' if they want to do it in as few clicks as possible. If it is only one, then probably not so bad.

I am sure you can understand the reason behind me saying that you need to have a way of selecting which field the name goes into.... If it is all three fields, then is easier again, but if not, they will need to choose.

[asmith533]

The combo box in the parent form is being used as a filter, not to populate the fields. So if Employee J wanted to see all the requests he was involved with for the quarter, he would select his name in the parent form and all his requests would appear in the subform. Or if Employee K wanted to update a request she had worked on but was unsure of the request number, she could select her name on the parent form and all the requests she was involved with would appear. Does that make sense? The database is split so the user has a front end and all the data resides on the back end. The database they currently use has no switchboard or forms, they just directly manipulate the tables.

[smithse]

Ok, we are getting somewhere... LOL
I now understand what you're trying to do, and it maes sense. It's easy enough in a form to create a filter for a subform. One way to do it is to base the subform on a query which takes a parameter value from the combobox on the 'parent form'. The parent form doesn't need to be be bound to a table either.
There are a few ways to design your queries too. You could have a query which has the employee combo box providing the filter or parameter value to all three fields, or you could have 3 combox boxes, one for each.
I am picking a single combo box to display all three different categories.

If for example you want to display all requests that an employee had anything to do with, regardless of their part in the process (i.e. receiver, collaborate, assigned) then your query could be something like this:

SELECT Requests.AssignedTo, Requests.ReceivedBy, Requests.Collaborated
FROM Requests
WHERE (((Requests.AssignedTo)=[Forms]![Form1]![Combo0])) OR (((Requests.ReceivedBy)=[Forms]![Form1]![Combo0])) OR (((Requests.Collaborated)=[Forms]![Form1]![Combo0]));

Also, I take it that you're building a front-end forms application for this db...?

[asmith533]

Yes, I have all my tables built and I have several forms/reports/queries built. I have the database set up to where all the employee sees is the switchboard. They select what they want to do from that.

[asmith533]

With this query, can I build it in the form? Also, do I still need the parent child relationship?

[smithse]

It depends if your parent form is displaying data from a record, or 'bound' to a record in a table or query. If it is not, and is simply housing the controls (your comboboxes) which you're using to filter the subform, then no, it doesn't have to be a parent-child relationship.

[RayMilhon]

A simpler way is to link the main form combo box with the subform on the employee id. Then when you change the combo box the subform will show all records with that employee id. If the subform is based on a table then all records in that table for that employee will show on the subform. I advise basing the subform on a query that allows you to specify specific criteria other than the employeeid for example get all records with a status of open or maybe closed depending on what you want. You could also specify a date range say where Year(opendate) = Year(now()) gets all of the current year records or yada yada yada. However be advised that if the query is non-updateable and you want them to be able to edit the information on the subform you will have to provide a save routine.

[smithse]

A simpler way is to link the main form combo box with the subform on the employee id. Then when you change the combo box the subform will show all records with that employee id. If the subform is based on a table then all records in that table for that employee will show on the subform. I advise basing the subform on a query that allows you to specify specific criteria other than the employeeid for example get all records with a status of open or maybe closed depending on what you want. You could also specify a date range say where Year(opendate) = Year(now()) gets all of the current year records or yada yada yada. However be advised that if the query is non-updateable and you want them to be able to edit the information on the subform you will have to provide a save routine.

Yes, definitely the way to go, although the employeeid could be in 3 different fields in this case, hence my suggestion.

[RayMilhon]

You're right. I apparently left my brain at home this morning. it does specify it could be any of the 3 different fields. your suggestion definitely the way to go in this case

[asmith533]

So here is my query string as it applies to my databse. Is this right?

SELECT Tbl_Requests.TblRequests_AssignedTo, Tbl_Requests.TblRequests_ReceivedBy, Tbl_Requests.TblRequests_Collaboration
FROM Tbl_Requests
WHERE ((([Tbl_Requests].[TblRequests_AssignedTo])=[Forms]![Frm_UpdateCustomerRequest2]![Combo4])) OR ((([Tbl_Requests].[TblRequests_ReceivedBy])=[Forms]![Frm_UpdateCustomerRequest2]![Combo4])) OR ((([Tbl_Requests].[TblRequests_Collaboration])=[Forms]![Frm_UpdateCustomerRequest2]![Combo4]));


Also, where do I post this Query string to make it work on my form?