Access Crashes When Setting RowSource in VBA (1 Viewer)

[B99]

Hi All,

I have a form with a few combo boxes that will allow the user to select values and then a subform will return results that are restricted based on the user's selections (more on that in this thread: https://www.access-programmers.co.u...-filter-not-working-prompts-for-value.310922/). One of the combo boxes is linked to one of the other combo boxes. In this case, the user will select a Regulation Set from one box and then a Regulation from the second box, where the results are filtered based on the Regulation Set selected.

I have done this exact thing on other forms that all work great. The After Update event of the first combo box triggers a requery of the second combo box, and the Row Source for the second one is simply:

SELECT tblRegulation.ID, tblRegulation.RegulationCode
FROM tblRegulation
WHERE (((tblRegulation.RegulationSetID)=[Forms]![frmResponse]![cboRegSet]))
ORDER BY tblRegulation.RegulationCode;

However, on the offending form, I am unable to get the second combo box to update. It shows no list of values when I try to set the Row Source in Design Mode as long as I am referencing the value of the first combo box as the criteria. If I remove the criteria, it returns everything. I thought I'd try using VBA to set the Row Source and clear out the value from the Design Mode properties box. When I do that and select the first combo box, Access crashes. Every time. I stripped the Where clause out of the SQL to try and just select the whole value set; it still crashes. Here is the code added to the After Update event for the first combo box:

Private Sub cboSelRegSet_AfterUpdate()
  Dim strSQL As String
  On Error GoTo Query_Error

  strSQL = "SELECT tblRegulation.ID, tblRegulation.RegulationCode " _
         & "FROM tblRegulation " _
         & "ORDER BY tblRegulation.RegulationCode;"
 
  Me.cboSelReg.RowSource = strSQL
   'Me.cboSelReg.Requery
 
ExitNow:
  Exit Sub
 
Query_Error:
  MsgBox Err.Description
  Resume ExitNow

End Sub

What is even more strange is that I put a debug statement in to print the value of strSQL to the immediate window, and if I copy/paste that exact value into the Row Source of the combo box in Design Mode, it works - meaning it returns the full value set; getting it to restrict the values will be the next step.

This form is a little different compared to the ones that work; this is only a query form where the users do not add/update/delete data and I want the users to be able to select multiple values from each combo box. On the forms where this works, the user can only select a single value for entry/update.

Any ideas on what I am missing?

 

[theDBguy]

Hi. Just a guess, but maybe your form got corrupted. Try creating a new form and try the same exact thing on it, just to see if the problem persist.

 

[B99]

Hi theDBguy; thanks for the quick response! I'll give that a try. I tried stripping out almost everything; I even went back to the test version of the DB that you helped me with last week and just added the code above but even that one crashed. I'll work on recreating it tonight. I feel a little better that it's maybe not something glaringly obvious that I overlooked!

 

[theDBguy]

Hi theDBguy; thanks for the quick response! I'll give that a try. I tried stripping out almost everything; I even went back to the test version of the DB that you helped me with last week and just added the code above but even that one crashed. I'll work on recreating it tonight. I feel a little better that it's maybe not something glaringly obvious that I overlooked!

Good luck and let us know how it goes. Cheers!

 

[MajP]

I'll work on recreating it tonight

You may not have to recreate manually. I would look at decompiling and if that does not work save to text, load from text.

Access Keeps Crashing when I go click Debug

Hi, I have just been working on Crosstab queries and reports with parameters and have got it all working (or so I thought). I am now getting an error on the Querydef function where I am setting up the parameters of the query in VBA before calling the report. I have no idea why this isn't now...

www.access-programmers.co.uk

Crash When Add Option Group

Hi, I am working on a form, everytime I add an Option Group with the value to store the selection to a table and save the form Access Crashes. If I create a new form I can create the option group no problem and save it. Can someone tell me why this might be happening? Thanks. Fen How

www.access-programmers.co.uk

 

[B99]

I think I'm closer, but still not sure how to get around it. In the data entry forms where this works, the form is bound to a table that stores an ID for the 'child' combo box value (bound to a column on that table) but the 'parent' combo box is unbound (the parent ID is a FK on the child table, so I only need the child ID). In the problem form, if I make the parent combo box unbound, the child combo box returns the filtered results as expected. However, if the combo box is unbound, I can only select one value at a time.

The form I have is based on a demo from pere_de_chipstick on UA (https://www.utteraccess.com/forum/index.php?showtopic=1984616) and he uses a temp table to bind the combo boxes to, where the columns are lookups to other tables with the list of values. I assume this is so that the multi-select functionality will work for each combo box, even though no data is ever added to the temp table. His combo boxes are all independent so the problem seems to be related to how Access determines the values of the parent combo box in order to filter the child combo box, while allowing multiple parent values to be selected.

 

[theDBguy]

I think I'm closer, but still not sure how to get around it. In the data entry forms where this works, the form is bound to a table that stores an ID for the 'child' combo box value (bound to a column on that table) but the 'parent' combo box is unbound (the parent ID is a FK on the child table, so I only need the child ID). In the problem form, if I make the parent combo box unbound, the child combo box returns the filtered results as expected. However, if the combo box is unbound, I can only select one value at a time.

The form I have is based on a demo from pere_de_chipstick on UA (https://www.utteraccess.com/forum/index.php?showtopic=1984616) and he uses a temp table to bind the combo boxes to, where the columns are lookups to other tables with the list of values. I assume this is so that the multi-select functionality will work for each combo box, even though no data is ever added to the temp table. His combo boxes are all independent so the problem seems to be related to how Access determines the values of the parent combo box in order to filter the child combo box, while allowing multiple parent values to be selected.

Hi. You might want to ping him just to see if he's got an answer for you. Just a thought...

 

[B99]

LOL, good point.

 

[theDBguy]

LOL, good point.

Only because it would take me some time to figure it out.

 

[MajP]

I would roll my own multi value checkbox instead of dealing with that. A lot clearer and far more flexible IMO.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

Would multiselect listboxes work?

 

[B99]

Thanks MajP! A multiselect list box is an interesting idea that I hadn't thought about. I think that would be acceptable, if it works in this scenario. I'll have to dedicate some time to your other post to understand it a little better. I'd prefer not to fake the MVF but I would like to have that option available.

For the time being, I backtracked a little and right now have the parent combo box set as unbound, which only allows me to select one value, but it allows the child combo box to be linked and reduce the child data set. It's not what I envisioned but it might not be that bad for the users. And it allows me to kick the can down the road a little to work on other features.

 

[MajP]

Can you post a sample DB with what you want to do, or at least a screen shot? I can do a quick demo with the "Fake MVF" or with the multiselect. I have a lot of code that makes it extremely simple to make filters from these.

 

[MajP]

So here is a better Fake Multivalue field filter. Works like an MVF using a subform. Easier to use in my opinion. Requires no additional tables or multivalue fields. Currently limited to single form views since it uses a subform as the control.

Only takes a line or two of code to use (Do not touch the code in the subform). Here is all the code to filter the subform using 3 Fake MVF controls

Private WithEvents FMVF_Suppliers As Form_FakeMVF_FilterControl
Private WithEvents FMVF_Reorder As Form_FakeMVF_FilterControl
Private SectionHeight As Long
Private Sub Form_Load()
  Set FMVF_Product = Me.MVFCtl_Products.Form
  FMVF_Product.Initialize Me.cmboProductName, "Products", "ProductName", "ProductID", fld_Numeric, 3
  Set FMVF_Suppliers = Me.MVFCtl_Suppliers.Form
  FMVF_Suppliers.Initialize Me.cmboSupplierName, "QrySuppliers", "SupplierName", "PKFIeld", fld_Text
  Set FMVF_Reorder = Me.MVFCtl_Reorder.Form
  FMVF_Reorder.Initialize Me.CmboReorderLevel, "QryReorder", "ReorderLevel", "PKFIeld"
  SectionHeight = Me.Section(1).Height
End Sub

Private Sub FMVF_Product_Updated()
FilterForm
End Sub
Private Sub FMVF_Suppliers_Updated()
  FilterForm
End Sub
Private Sub FMVF_Reorder_Updated()
  FilterForm
End Sub


Public Sub FilterForm()
  Dim ProdFilter As String
  Dim SupFilter As String
  Dim ReorderFilter As String
  Dim TotalFilter As String
  Dim combineType As CombineFilterType
  ProdFilter = FMVF_Product.GetFilter("ProductID", False)
  SupFilter = FMVF_Suppliers.GetFilter("SupplierName", False, fld_Text)
  ReorderFilter = FMVF_Reorder.GetFilter("ReorderLevel", "false", fld_Numeric)
  If Me.FrameCombine = 1 Then
   combineType = ct_And
  Else
    combineType = ct_Or
  End If
  TotalFilter = mdlCombineFilters.CombineFilters(combineType, ProdFilter, SupFilter, ReorderFilter)
  Me.txtFilter = TotalFilter
  Me.subfrmProducts.Form.Filter = TotalFilter
  Me.subfrmProducts.Form.FilterOn = True
  Me.Section(1).Height = SectionHeight
End Sub

To use.
1) drop the subform on to your form. It does not matter where it will position and show itself.
2. Declare a variable at the top of the form using with events
3. Initialize it in the forms on load

 

[arnelgp]

you may also try to:
1. add Option Explicit to your code.
2. compact & repair your db.
3. /decompile your db.

 

[B99]

So here is a better Fake Multivalue field filter. Works like an MVF using a subform. Easier to use in my opinion. Requires no additional tables or multivalue fields. Currently limited to single form views since it uses a subform as the control.

It took me a while to understand this and I do like the solution a lot. I'll have to play around with it as some of the code is new to me and while I see 'what' it does, I'm still working through understanding why; but this seems to be a better approach.

Thanks for the example and for taking the time to put it together! Much appreciated!!

 

[MajP]

In short.
It is a subform and combobox meant to look like a MVF combobox. The subform only has two controls a checkbox and a field to display the value. To reuse the subform you pass to each instance the query you want as the "Rowsource". Then the trick is that it creates what is called an adodb in memory (disconnected) recordset. It acts like a recordset, but it is created in memory only. There is no connection to a table. The subform has some custom events you can trap in the main form, and the subform also traps main form events.
You do not need to understand the code in the subform, only how to use it.
It does have some down sides. You cannot requery or even recalc the main form. For some reason the subform recordset goes out of scope. I will probably post a separate thread because others may like this for filtering. I usually would use a multiselect listbox or listboxes; however, they take up real estate.