Missing Combo Row Source Based on Query

[Reese]

I have a continuous subform with one combo box. This subform records what facilitators went on a given event. The combo box's row source is based on a query that only selects current facilitators (current/not current is recorded in the Facilitator table with a Yes/No field). This way, when selecting facilitators, the combo box only allows the user to choose from current facilitators.

This works great. The problem is that, for some reason, facilitators that aren't current aren't showing up in the subform when I look back at past events to see who went on that program. I go into the relevant table and the information is still there, it's just not showing up in the subform.

My theory is that since the non-current facilitators don't show up in the rowsource of the combo box, they aren't being displayed in the subform. Is this the most likely reason? If so, how do I fix it? If not, what might be another reason?

Here is the relevant background structure, to give more details to the situation:

The main form is bound to the Event table (Primary key is Event_ID) and the subform is bound to a Facilitation table. The names of the facilitators are in the Facilitator table (Primary key is Facilitator_ID). The Facilitation table is in a relationship with the Event table via Event_ID and with the Facilitator table via Facilitator_ID.

The subform is linked to the main form via the Event_ID, so only the facilitators of that event appear within the continuous subform.

The combo box's row source is FacilitatorComboSelectQuery, and here is it's SQL:

SELECT FaciltatorTable.Facilitator_ID, FaciltatorTable.First_Name, FaciltatorTable.Last_Name
FROM FaciltatorTable
WHERE ((FaciltatorTable.Current=Yes))
GROUP BY FaciltatorTable.Facilitator_ID, FaciltatorTable.First_Name, FaciltatorTable.Last_Name;

The combo box is bound to the Facilitator_ID field of the Facilitation table. It has 3 columns and the bound column is the first field of the query (Facilitator_ID). The bound column has a width of 0", so it is invisible in the drop-down list of the combo box and isn't visible in the field after update (the first name field is instead visible), but the Facilitator_ID is what is stored within the table, not the name.

Does anyone have any ideas? Thanks.

 

[MarkK]

My theory is that since the non-current facilitators don't show up in the rowsource of the combo box, they aren't being displayed in the subform.

Yes, exactly. The combo can't show something that doesn't appear in it's rowsource.

To 'fix' it, maybe your subform needs modes, like data-entry, and historical-view, and in those two different cases you programmatically modify the row source. Because the list of historical facilitators, and those available for selection at a new event, are different lists. Or you need different interfaces entirely for data entry and data view.

 

[Reese]

Thanks MarkK, I thought that would be the case.

To 'fix' it, maybe your subform needs modes, like data-entry, and historical-view, and in those two different cases you programmatically modify the row source.

How do you suggest going about creating different modes? I understand what you are suggesting in concept, but I don't have any idea on how I would go about doing that.

Or you need different interfaces entirely for data entry and data view.

I do have an idea on how to do this one--when an update has been complete and the facilitators, numbers of participants, etc. are entered after the event, there is a hidden "Ed_Update_Complete" yes/no field that is checked off. When opening the event and this field is marked as "Yes", I could have a duplicate but slightly different version of the current form open with a historical, instead of current, row source.

I would kind of like to avoid the second technique, just because it means another copy of the same form, but I have a feeling it would be simpler than the edit/view-only modes idea.

 

[Minty]

Have a play with putting a transparent combo control over the displayed one (set no on tab stop). On getting focus make it visible by changing the colours, use it to update your field, then on losing focus make it transparent again behind the one displaying all the names.
It depends on your form layout as to how easy it is to achieve the deception

 

[MarkK]

You could probably set the facilitator rowsource conditionally using the difference between the event date and today. If it's less than 30 days, say, only show current facilitators, else, show all. You can write a public method on the subform that receives the EventDate, does the logic, and amends the rowsource. Then, call that Sub from the current event of the main form.

Code on the main form . . .

Private Sub Form_Current()
[COLOR="Green"]   'passes the current record's EventDate to the subform method[/COLOR]
   Me.YourSubformControlName.Form.SetFacilitatorRowSource Me.EventDate
End Sub

Code on the subform . . .

Public Sub SetFacilitatorRowSource(EventDate as date)
[COLOR="Green"]   'base SQL for the row source[/COLOR]
   Const ROW_SOURCE as string = _
      "SELECT FacilitatorID, FirstName & ' ' & LastName FROM tFacilitator {0}ORDER BY LastName "

   dim where as string
   
[COLOR="Green"]   'if the event is within 30 days of today, add a where clause to the SQL[/COLOR]
   if abs(Date() - EventDate) < 30 Then 
[COLOR="Green"]      'assumes the tFacilitator table has an EndDate field, which,
      'when null, indicates the person is still current
[/COLOR]      where = "WHERE EndDate Is Null "
   end if

[COLOR="Green"]   'insert the where clause, even if it is empty, into the CONST[/COLOR]
   me.cboFacilitator.RowSource = VBA.Replace(ROW_SOURCE, "{0}", where)
   me.cboFacilitator.Requery
End Sub

See what's going on there?

 

[Reese]

Minty, I like where you were going with the transparency thing and it does work. I don't quite like how the name disappears if you tab back into the field after editing, though. I may return back to your technique, but I want to continue experimenting a bit.

MarkK, I do see where you are going with the vba code. I don't understand all of it in detail, but enough that I see what it is supposed to do and (at least I think) enough to make the appropriate changes so that the names, etc. match what is in my database.

I don't understand how it is supposed to over ride the combo box row source in properties, however. When I tried the code, I removed the query as the Row Source in the property side bar but left the Row Source Type as Table/Query. When I opened a past event's Education Update form to see the facilitators, nothing was visible in the combo box--not their names, nor anything in the drop-down menu when I clicked on the combo box's arrow.

I'm not saying that your code doesn't work, just that I think I missed a step in how to use the VBA code to over-ride the Row Source in the combo box's properties. Any suggestions?

If I can't figure out a way to make this VBA work, I will probably use Minty's suggestion, or use the alternate form technique--which I know for certain will work. Thanks.

 

[MarkK]

I'm not saying that your code doesn't work,

All the names in my code are wrong and to make it work you would have to replace all the names. Also, my code never works the first time!!!

I provided this as the quickest way to communicate the idea, which is actually quite simple. If you like the idea, use it, if not, no worries at all.

 

[Reese]

Well, I found a simple solution that is something of a mix of all the solutions mentioned so far.

I now have two versions of the combo box sitting on top of one another, one with the current-only list and one with the entire historic list. Like Minty's suggestion, one of them is invisible. I then used a VBA on the main form's current event to dictate which combo box is visible and enabled--similar to MarkK's suggestion. But instead of it being based on a date and an SQL string, I based an if/then statement on if the hidden Ed_Update_Complete checkbox is true or false--similar to my idea about opening a different form.

Simple and gets the job done. Thanks Minty & MarkK!