Posts tagged: stackoverflow

Apr 19 2017

Answer by Seth Spearman for MS Access tabbed form multiple OnCurrent firings

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.

Apr 19 2017

Answer by Seth Spearman for MS Access tabbed form multiple OnCurrent firings

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.

Nov 09 2016

Comment by Seth Spearman on Is there a way to aggregate a variable range of dates in SQL using a SET operation

...and it is incredibly fast...it runs in less than a second against my several hundred thousand data set. Genius.
Nov 09 2016

Comment by on Is there a way to aggregate a variable range of dates in SQL using a SET operation

...and it is incredibly fast...it runs in less than a second against my several hundred thousand data set. Genius.
Nov 09 2016

Comment by Seth Spearman on Is there a way to aggregate a variable range of dates in SQL using a SET operation

Wow...this totally worked! I love the simplicity. No need to avoid holidays so I am giving you the answer.
Nov 09 2016

Comment by on Is there a way to aggregate a variable range of dates in SQL using a SET operation

Wow...this totally worked! I love the simplicity. No need to avoid holidays so I am giving you the answer.
Nov 09 2016

Comment by on Is there a way to aggregate a variable range of dates in SQL using a SET operation

Wow...this totally worked! I love the simplicity. No need to avoid holidays so I am giving you the answer.
Nov 08 2016

Comment by Seth Spearman on Is there a way to aggregate a variable range of dates in SQL using a SET operation

@Hogan I see your point.
Nov 08 2016

Comment by on Is there a way to aggregate a variable range of dates in SQL using a SET operation

@Hogan I see your point.
Nov 08 2016

Comment by Seth Spearman on Is there a way to aggregate a variable range of dates in SQL using a SET operation

@Shnugo...my actual table has 367K rows.