Drop down list from query

Question

I have a form -- frmStaffWorkload -- that allows staff to list the projects they're currently working on. The entries from the form are added to a table -- tblStaffWorkload. The form includes a list of projects from a drop down list. The list is based on a query of a simple master table -- "tblProjects" that includes the fields "ProjectName" and "Status". The query limits the drop down list to display only projects whose status is "Current". Everything works fine until the project is complete. When the project is complete, staff will update "tblProjects" and change the status from "Current" to "Complete" (via another form). The drop down list removes the "Complete" project from the list -- as I want.

My problem is any previous entries in the form that had the newly-completed project entered, is now blank. The table, tblProjects, still has the entry -- it's just not showing up in the form anymore. Any help?

Answer 1

It sounds to me that you are using the Form in ContinuousFormView?

The problem you observed is consistent with the Filtered ListRows in that the Projects that have been completed are no longer in the List and records linked to the completed Projects shown on the ContinuousForm will show blank in the Text component of the ComboBox.

One of the usual techniques to solve this is to use two ComboBoxes, 1 with the unfiltered RowSource and 1 with the filtered RowSource. Let's call them cboUnfiltered and cboFiltered.

Bind both ComboBoxes to the same Field. Size the 2 ComboBoxes to be exactly the same and place them also at exactly the same position on your Form so that it looks like you have only 1 ComboBox. Click on the ComboBox and check the Properties pane to see which one was selected.  If it is cboUnfiltered, Bring it to the Front (the comand should be somewhere on the Ribbon). If it is cboFiltered, Send it to the Back (again, the command should be somewhere on the Ribbon).  After this, cboUnfiltered should be in front/on top of cboFiltered in the design of the Form.

The final step is to use Macro or code in the cboUnfiltered_GotFocus Event to send the Focus to the cboFiltered.

This way, all the rows in your Form in ContinuousFormView will show the correct Project in the Text component of the ComboBox (cboUnfiltered).  The exception is on the current row (CurrentRecord buffer where user does the data entry/data update). If he/she clicks on the cboUnfiltered, the Focus will be sent to the cboFiltered which moves the cboFiltered to the top (of cboUnfiltered) and the DropDown list will only show the "current" Pjojects.

Another alternative is to use a TextBox instead of the cboUnfiltered and the cboFiltered.  You need to modify the RecordSource of the Form to include the ProjectName Field.  Bind the TextBox to ProjectName Field.  Size the TextBox to be the same as the Text component of the ComboBox, i.e. the rectangle to the left of the down arrowhead. Similarly to the first method, make sure the TextBox is in front of cboFiltered in the design of the Form.

The final step is to use  Macro or code in the TextBox_GotFocus Event to send the Focus to cboFiltered.

The appearance/behaviour of the second method is exactly the same as the first method.

Answer 2

I was hoping I could just show the "Current" projects in the drop down list

I see no reason why not.  The RowSource of the combo box and the RecordSource of the form can act independently of each other.

Answer 3

Sounds like I need to show both the "Complete" and "Current" project names. I was hoping I could just show the "Current" projects in the drop down list. I took your advice and have the next best thing. Thank you!

Answer 4

It sounds as if the form's RecordSource is restricting the rows returned to those where status = "Current".  If so change the RecordSource property of the form to a query which restricts the rows returned to those which are "Current" OR "Complete".  The query would be something like this:

SELECT tblStaffWorkload.*

FROM tblStaffWorkload INNER JOIN tblProjects

ON tblStaffWorkload.ProjectID = tblProjects.ProjectID

WHERE Status = "Current" OR Status = "Complete";

I imagine the query is also restricted to rows from tblStaffWorkload where the employee is the current user, but I've not included this in the above as you've not given any information on how this is done.

If you have problems amending the query definition post back with the current SQL of the form's current RecordSource query.

Answer 5

I would add a sort to the dropdown record source query (display 2 columns - fields "ProjectName" and "Status") so that Completed is a the end and Current is listed first.