I have a tbl_ProjectList that includes the project's Name and End Date . A qry_cboProject queries this table to only display projects in a combobox on Subform A where the End Date is after the selected date on Subform B , both on Mainform C . On Subform A , a macro copies ( INSERT INTO SQL) projects from Subform B's previous months into the new months. However, if an out-of-date project gets copied over to a new month, the combobox field would be empty for that record, even though the Key exists on the back-end. I've tried playing with the combobox's properties on Subform A by changing the Bound Column , Column Count , Column Widths , and Limit To List , but am only able to get the out-of-date project to display by its Key , rather than its Name . The front-end reasoning for this macro is that employees do not have to repetitively select the same projects for each month, and employees already working on out-of-date projects may still need to put in some hours to close out the project. Does anyone have any suggestions? Thank you in advance!

Reputation: 143

How to display out of list items in a combobox?

I have a tbl_ProjectList that includes the project's Name and End Date.

A qry_cboProject queries this table to only display projects in a combobox on Subform A where the End Date is after the selected date on Subform B, both on Mainform C.
On Subform A, a macro copies (INSERT INTO SQL) projects from Subform B's previous months into the new months. However, if an out-of-date project gets copied over to a new month, the combobox field would be empty for that record, even though the Key exists on the back-end.

I've tried playing with the combobox's properties on Subform A by changing the Bound Column, Column Count, Column Widths, and Limit To List, but am only able to get the out-of-date project to display by its Key, rather than its Name.

The front-end reasoning for this macro is that employees do not have to repetitively select the same projects for each month, and employees already working on out-of-date projects may still need to put in some hours to close out the project.

Does anyone have any suggestions? Thank you in advance!

Upvotes: 0

Answers (2)

Plaxerous

Reputation: 143

@Parfait - apologies for not describing my problem in more detail. There are multiple subforms on one main form to allow the user to select a date in one subform, which populates projects on a second subform and responsibilities on a third subform.

Jeffrey's suggestion made me realize that the underlying query to the combobox should be adjusted for projects that are carried over into new months, where a foreign key exists in the underlying tbl_ProjectUserEntry

Therefore, I added a WHERE criteria to the query, which uses a DLookUp function to see if the foreign key exists:

DLookUp("[DateID]","tbl_ProjectUserEntry","[DateID] =" & Forms.frm_UserEntry.tbDateID) IS NOT NULL

frm_UserEntry is the main form..

Again, apologies for my brief description to a complex problem.

Upvotes: 1

Jeffrey

Reputation: 538

The order in which fields show up in you combo box depends on how the control source is querying the information i.e. to get name and not the key to show up in a combobox using the a control source query like the following:

SELECT Key, Name FROM tbl_ProjectList

You would need to set the following attributes:

Column Count: 2
Column Width: 0"; 2"
Bound Column: 1

It sounds like you may need to requery the control source as well. This should cause all the information to update.

Upvotes: 1

How to display out of list items in a combobox?

Answers (2)

Related Questions