0

I have an Access client to SQL Server that uses a tabbed form with several subforms. Basically, a product is selected on the main form, and then the tabs give access to edit/insert different properties of the product (options, colors, styes, etc) stored in different related tables. Each subform uses it's own record source, but is synced to the main form on a product number.

So, I have combo box in a subform, call it "Options", that I want to populate with data from a query that uses the product number in the "where" clause. That's what I need to do. How I did it was to use the OnCurrent event of the Options subform to set the RowSource equal to an SQL string. That works, but...the OnCurrent event fires every time a new product is selected in the main form, whether or not the Options subform form has the focus. Then it fires again when Options get the focus. And then, since Options uses a data sheet layout, it fires for every record selected, even though the record source doesn't need to change.

Although this works, it's a lot of unnecessary data being shot back and forth to a (very) remote SQL Server. So, is there another event to use that works for the whole subform, ideally just firing once, and not for each record within the subform? I tried Got Focus, but that never fired. And the Open and Load events don't fire when a new product is selected (of course). Or maybe there is another place I should be setting the options subform record source?

Here's the event code:

Private Sub Form_Current()
    Dim sql As String
    sql = "SELECT O.[OptionID], O.[Caption] FROM ProductOptions AS O WHERE o.[OptionTypeID] in (1,2,8,9) AND o.ProductNumber = "
    sql = sql & "'" & Nz(Forms![products main form]!ProductNumber, "99test") & "'"
    oidSelect1.RowSource = sql
End Sub

Thanks for any help.

Jim

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Jamesckel
  • 161
  • 13
  • So you are setting the RowSource of a combobox not a RecordSource as stated in the narrative? Why use code to set the RowSource of a combobox? Why not just have SQL statement in the RowSource property and then code in the combobox GotFocus event just does a Requery of the RowSource? – June7 Apr 20 '17 at 00:27
  • Hmmm, well the filter (where clause) has to be set dynamically because it changes for each product currently being edited. And wouldn't using the GotFocus for the combo box requery for each record selected? (This is a grid view, so the combo box is replicated on each row.) – Jamesckel Apr 20 '17 at 01:26
  • The combobox RowSource SQL statement WHERE clause would have reference to the ProductNumber so it would be dynamic. Seth's answer is along these lines except I would use the combobox GotFocus event to trigger the requery. He does make a good point about the dependence on main form. Usually, primary and dependent comboboxes are on same form. Also, combobox with a lookup showing an alias value won't work nice on continuous or datasheet form. – June7 Apr 20 '17 at 02:09
  • If ProductNumber is primary/foreign key link for the form and subforms, then why reference the main form, the ProductNumber should be part of the subform RecordSource. – June7 Apr 20 '17 at 02:30
  • I tried Referencing the product number on the subform, but when adding a new record, it has a null value, so yes, I'd have to store it in a hidden field. – Jamesckel Apr 20 '17 at 12:09
  • I will try using the GotFocus event of the combobox to requery. I did not know I could reference a form field in the RowSource property. That's why I have code constructing a string to stuff in there. But does anyone know if setting the RowSource as in my code actually triggers a requery? I can't find an answer to that though I seem to recall that it does. – Jamesckel Apr 20 '17 at 12:31
  • If you see the combobox list change, then I would say that answers your question. – June7 Apr 20 '17 at 17:35
  • Also, have to reference a control, not field. Name textbox different from the field, like tbxProdID. – June7 Apr 20 '17 at 18:18
  • Thanks for the comments. But on the immediately above comment, the data wouldn't change between redundant queries on the same product. So, I was trying to figure out a way to know if setting the row source actually triggers a query or just sets the property. – Jamesckel Apr 20 '17 at 19:45
  • It must requery because there is no explicit call to Requery. I would say it is irrelevant whether or not the parameter is different, behavior is the same. – June7 Apr 20 '17 at 19:56

1 Answers1

0

I believe the proper way to do this is to not dynamically change the rowsource of the combobox. Instead, set the rowsource to a query using the query designer and have the query read the value from the "main form" just as is shown in your sample code...Forms![products main form]!ProductNumber.

Then I would trigger the refresh of the combo box using the MAIN FORMs AfterUpdate event. It will be something like MainForm!MySubForm.Form("mycombobox").requery. (I may not have that syntax exactly right...my access syntax is rusty.)

BTW, I don't like my own answer. The reason is that you are hard-coding a dependency between the subform and its parent. That might be fine if you will NEVER use that subform anywhere else. To get around this some Access Developers use a hidden form that has textboxes that you set from the Main Form's After Update event. Then the query is updated to refer to the the hidden form's textbox that you created for this form.

There are other ways to do this, too (public functions for example). But sorry, that is not really what you were asking for. My first method will work. But first time you need to re-use that subform you will need to refactor and the hidden form solution works pretty well.

Seth Spearman
  • 6,710
  • 16
  • 60
  • 105