Custom row source for combo box in continuous form in Access

Question

I have searched around, and it seems that this is a limitation in MS Access, so I'm wondering what creative solutions other have found to this puzzle.

If you have a continuous form and you want a field to be a combo box of options that are specific to that row, Access fails to deliver; the combo box row source is only queried once at the beginning of the form, and thus show the wrong options for the rest of the form.

The next step we all try, of course, is to use the onCurrent event to requery the combo box, which does in fact limit the options to the given row. However, at this point, Access goes nuts, and requeries all of the combo boxes, for every row, and the result is often that of disappearing and reappearing options in other rows, depending on whether they have chosen an option that is valid for the current record's row source.

The only solution I have found is to just list all options available, all the time. Any creative answers out there?

Edit Also, I should note that the reason for the combo box is to have a query as a lookup table, the real value needs to be hidden and stored, while the human readable version is displayed... multiple columns in the combo box row source. Thus, changing limit to list doesn't help, because id's that are not in the current row source query won't have a matching human readable part.

In this particular case, continuous forms make a lot of sense, so please don't tell me it's the wrong solution. I'm asking for any creative answers.

Answer 1

Disclaimer: I hate Access with a passion.

Don't use continuous forms. They're a red herring for what you want to accomplish. Continuous forms is the same form repeated over and over with different data. It is already a kludge of Access's normal mode of operation as you can't have the same form opened multiple times. The behavior you are seeing is "as designed" in Access. Each of those ComboBox controls is actually the same control. You cannot affect one without affecting the others.

Basically, what you have done here is run into the area where Access is no longer suitable for your project (but cannot ditch because it represents a large amount of work already).

What seems to be the most likely course of action here is to fake it really well. Run a query against the data and then create the form elements programmatically based on the results. This is a fair amount of work as you will be duplicating a good bit of Access's data handling functionality yourself.

Reply to Edit:
But as they are, continuous forms cannot accomplish what you want. That's why I suggested faking out your own continuous forms, because continuous forms have real limitations in what they can do. Don't get so stuck on a particular implementation that you can't let go of it when it ceases to work.

Answer 2

What if you turn off the "Limit To List" option, and do some validation before update to confirm that what the user might have typed in matches something in the list that you presented them?

Answer 3

You could also make the value of the combo box into an uneditable text field and then launch a pop-up/modal window to edit that value. However, if I was doing that, I might be inclined to edit the whole record in one of those windows.

Answer 4

use continuous forms .. definitely. In fact you can build entire applications with great and intuitive user interface built on continuous forms. Don't listen to Toast!

Your solution of listing all options available is the correct one. In fact there is no other clean solution. But you are wrong when you say that Acccess goes nuts. On a continuous form, one control object displays multiple records, and the combobox/data is a property linked to the control, not specific to the record. This is why Access MUST display the same data in the combobox for all records!

If you need to accept only record-specific values in this combobox, please use the beforeUpdate event to add a control procedure. In case a new value cannot be accepted, you can cancel data update, bringing back the previous value in the field.

You cannot set the limitToList property to 'No' where the linked data (the one that is stored in the control) is hidden. This is logical: how can the machine accept the input of a new line of data when the linked field (not visible) stays empty?

Answer 5

I don't think that Access continuous forms should be condemned at all, but I definitely believe that they should be avoided for EDITING DATA. They work great for lists, and give you substantially more formatting capabilities than a mere listbox (and are much easier to work with, too, though they don't allow multi-select, of course).

If you want to use a continuous form for navigation to records for editing, use a subform displaying the detailed data for editing, and use the PK value from the subform for the link field. This can be done with a continuous form where you place a detail subform in the header or footer, linked on the PK of the table behind the continuous form.

Or, if you are using a continuous form to display child data in a parent form, you can link the detail subform with a reference to the PK in the continuous subform, something like:

[MySubForm].[Form]!MyID

That would be the link master property, and MyID would be the link child property.

Answer 6

I also hate Access, but you must play with the cards you are dealt. Continuous forms are a wonderful thing in Access, until you run into any sort of complexity as is commonly the case, like in this instance.

Here is what I would do when faced with this situation (and I have implemented similar workarounds before):

Place an UNBOUND combobox on the form. Then place a BOUND textBox for the field you want to edit.

Make sure the combobox is hidden behind (NOT invisible, just hidden) behind the textBox.

In the OnCurrent event fill the listBox with the necessary data. Go ahead and "Limit to list" it too.

In the OnEnter or OnClick event of the textBox give the combobox focus. This will bring the combobox to the forefront. When focus leaves the combobox it will hide itself once more.

In the AfterUpdate event of the combobox set the value of the textbox equal to the value of the combobox.

Depending on your situation there may be some other details to work out, but that should more or less accomplish your goal without adding too much complexity.

Answer 7

@[Gilligan]: That is an interesting idea ... I'll give it a try. I think I have to add another twist though - I need a hidden textbox for the bound variable, since it's just an id number, and I want the human readable version to be visible. I guess I'll just have to update both at the same time.

Answer 8

We also encounter this a lot in our applicatins. What we have found to be a good solution: Just show all rows in the comboboxes. Then, as soon as the user enters the compobox in a specific row, adjust the rowsource (with the filter for that row). When the combobox loses the focus, you can re-set the rowsource to display everything.

Answer 9

@[DGM]: That is an interesting twuist because the textBox actually hides the combobox value being changed for all rows ( I believe). In this case you may actually want two textboxes, one covering the combobox with the display value and the other hidden with the ID, then the combobox AfterUpdate will change both the visible textbox to the display value and the hidden textbox to the hidden value.

Answer 10

I have a simpler way to go than Gilligan. It seems like a lot of work but it really isn't. My solution requires having my continuous form as a subform datasheet. On my subform I have two lookup comboboxes, among other fields, called Equipment and Manufacturer. Both simply hold a Long Integer key in the data source. Manufacturer needs to be filtered by what is selected in Equipment. The only time I filter Manufacturer.RowSource is in the Manufacturer_GotFocus event.

Private Sub Manufacturer_GotFocus()

If Nz(Me.Equipment, 0) > 0 Then
    Me.Manufacturer.RowSource = GetMfrSQL()  '- gets filtered query based on Equipment
Else
    Me.Manufacturer.RowSource = "SELECT MfgrID, MfgrName FROM tblManufacturers ORDER BY MfgrName"
End If

End Sub

In Manufacturer_LostFocus I reset Manufacturer.RowSource to all Manufacturers as well. You need to do this because when you first click in the subform, GotFocus events fire for all controls, including Manufacturer, even though you are not actually updating any fields.

Private Sub Manufacturer_LostFocus()

Me.Manufacturer.RowSource = "SELECT MfgrID, MfgrName FROM tblManufacturers ORDER BY MfgrName"

End Sub

In the Enter event of Manufacturer you have to check if Equipment has been selected, if not set focus to Equipment.

Private Sub Manufacturer_Enter()

If Nz(Me.EquipmentID, 0) = 0 Then
    '-- Must select Equipment first, before selecting Manufacturer
    Me.Equipment.SetFocus
End If

End Sub

You also need to requery the Manufacturer combobox in Form_Current event (i.e. Me.Manufacturer.Requery), and you should set the Cycle property of this subform to "Current Record".

Seems simple enough, but you're not done yet. You also have to reset Manufacturer.RowSource to all Manufacturers in the SubForm_Exit event in the parent form in case the user goes to the Manufacturer combobox but does not make a selection and clicks somewhere on the parent form. Code sample (in parent form):

Private Sub sFrmEquip_Exit(Cancel As Integer)

Me.sFrmEquip.Controls("Manufacturer").RowSource = "SELECT MfgrID, MfgrName FROM tblManufacturers ORDER BY MfgrName"

End Sub

There is still one piece of this that is not clean. When you click on Manufacturer and have multiple rows in the datasheet grid, Manufacturer field will go blank in other rows (the data underneath the comboboxes is still intact) while you're changing the Manufacturer in the current row. Once you move off this field the text in the other Manufacturer fields will reappear.

Answer 11

Better...

Set you combo box Control Source to a column on the query where the values from your combo box will be stored.

Answer 12

For Me I think the best way and easiest way is to create a temporary table that has all your bound fields plus an extra field that is a yeas/no field.

then you will use this table as the data source for the continuous for. You can use onLoad to fill the temporary table with the data you want.

I think it is easy after that to loop for the choices, just a small loop to read the yeas/no field form the temporary table.

I hope this will help

Answer 13

Use OnEnter event to populate the combo box, don't use a fixed rowsource.