none
How do I filter an Access 2010 web form using a combo box macro? RRS feed

  • General discussion

  • Edit:  To any new folks joining us, this thread has gotten to be a bit of a mess.  To be honest I had considered asking Microsoft to delete the entire thing, but there is some good information here that would be a shame to dismiss.  So, I'm in the process of trying to salvage the situation by presenting a recap/clarification section down at what is, as of right now (about 11:45pm 9/26/11), the bottom of the thread.  It is my hope that we can come together and get that part complete, so we can later delete the posts where it all went sideways.

    Original:

    As simple as the thread title sounds, I'm having trouble finding how to do this in my various searches...


    I have a database that I've converted to a web database for deployment to SharePoint, and have everything working except the filter logic on the web version of my form. (To be honest, my other problem is I never bothered learning Macro, because VBA was always an option.)

    The standalone version of my form includes a set of combo boxes to filter the results in any order (kind of like cascading combo boxes except the selection order is not limited). The VBA for that is relatively simple, mainly manipulating string variables (which I have read can now be done in Macro), looks something like this:

    ...

    Private Sub Form_Load()

    ' Some of this is not important until you hit the "Reset" button (not shown here), which calls this function to reload the page.

    ' Clear the filter.
    Me.Filter = ""

    ' Populate the first combo box, make sure it is enabled for use, and set the value to blank.
    Me.cbx1.RowSource = "SELECT DISTINCT [MyQuery].Field1 FROM [MyQuery]"
    Me.cbx1.Enabled = True
    Me.cbx1.Value = ""

    ' Repeat the above code for each combo box; I have 5 corresponding to the 5 fields I'm filtering on.

    End Sub

    Private Sub cbx1_AfterUpdate()
    ' Make one of these for each combo box, modifying as appropriate.

    ' String to store the filter.
    Dim strFilter As String

    ' Strings to store the row sources of the sibling combo boxes.
    Dim strCbx2_RS As String
    Dim strCbx3_RS As String
    Dim strCBx4_RS As String
    Dim strCbx5_RS As String

    ' Catch/handle Null or Empty values, and store the selected value appropriately.
    If IsNull(Me.cbx1.Value) Then
    strFilter = "(Field1 Is Null)"
    Else
    If IsEmpty(Me.cbx1.Value) Then
    strFilter = "(Field1 Is Empty)"
    Else
    strFilter = "Field1 = '" & Me.cbx1.Value & "'"
    End If
    End If

    ' Grab the row source from each of the siblings.
    strstrCbx2_RS = Me.cbx2.RowSource
    strstrCbx3_RS = Me.cbx3.RowSource
    strstrCbx4_RS = Me.cbx4.RowSource
    strstrCbx5_RS = Me.cbx5.RowSource

    If Me.Filter = "" Then
    ' This is the first selection, assume no Where clause specified for the siblings yet.

    ' Update the filter
    Me.Filter = strFilter

    ' Add the Where clause and filter string to the sibling row source strings
    strCbx2_RS = strCbx2_RS & " WHERE " & strFilter
    strCbx3_RS = strCbx3_RS & " WHERE " & strFilter
    strCbx4_RS = strCbx4_RS & " WHERE " & strFilter
    strCbx5_RS = strCbx5_RS & " WHERE " & strFilter

    Else
    ' Not the first selection made, assume the Where clauses have been added.

    ' Add to the filter string, using an And clause.
    Me.Filter = Me.Filter & " AND " & strFilter

    ' Same for these guys...
    strCbx2_RS = strCbx2_RS & " AND " & strFilter
    strCbx3_RS = strCbx3_RS & " AND " & strFilter
    strCbx4_RS = strCbx4_RS & " AND " & strFilter
    strCbx5_RS = strCbx5_RS & " AND " & strFilter
    End If

    ' Update the sibling combo boxes.
    Me.cbx2.RowSource = strCbx2_RS
    Me.cbx3.RowSource = strCbx3_RS
    Me.cbx4.RowSource = strCbx4_RS
    Me.cbx5.RowSource = strCbx5_RS

    ' Disable the combo box (avoids duplicate entries, thus SQL errors).
    Me.cbx1.Enabled = False

    ' Fire in the hole!
    Me.FilterOn = True
    End Sub
    ....

    But, again, where I'm stuck is how to take this algorithm and translate it to Macro; the even shorter set of actions available for Access Services applications is no help either.

    Is there anybody out there that could point me in the right direction with this?

    Thanks

    • Changed type Bill Nesbitt Thursday, September 22, 2011 2:26 AM This is turning into more of a thought experiment, many follow-up questions.
    • Edited by Bill Nesbitt Thursday, September 29, 2011 7:51 AM Just realized time is listing as GMT, fixed the one reference.
    Friday, September 9, 2011 11:31 PM

All replies

  • It's not quite clear why are actually setting the sql of the combo box with code (you do not have that ability in web based forms, but I do not see the need for this anyway).

    Also keep in mind that the queries you build are running on the server side, but the code you're running is running in the web browser on the client side (say on your iPad). What this means is web based queries cannot reference a form directly. I don't think you have any forms! references in your query, but just in case this is a FYI here as to why web query cannot use or have an expresison that is a reference to a form that running on the other end such as a iPad or smartphone. So forms! expressions are NOT allowed in a query.  You can do cascading combo boxes (but again it not clear if you doing as such and that is a differnt question again).

    Also, I do not see the field names that you filtering on. (are the 5 combo boxes to filter on 5 different columns in the database)?

    So, for 2 combo boxes, the code to filter would be:

    SetLocalVar  (c1,[cboCity])
    SetLocalVar  (c2,[cboState])

    SetFilter (  ([City] = LocalVars!c1)
                      And
                    ([State] = LocalVars!c2) )

    Note careful in the above that we not using a string for the filter. You MUST use a expression for the filter expression, NOT a string. The expression is going to be evaluated SERVER side, and thus the expression is not built at runtime and sent from the web form running on your smartphone to the server side.

    It also not clear if a combo box(s) to be optional.  But you could re-write above and have combo city or combo state optional with:

    SetLocalVar  (c1,[cboCity] & "*")
    SetLocalVar  (c2,[cboState] & "*")

    SetFilter (  ([City] Like LocalVars!c1)
                      And
                    ([State] Like LocalVars!c2) )


    Another nice tip is note how in above I stuff the control values into a local var. This eliminates the need for hard coded and messy forms!formControl references.

    For web forms we do not have ME to represent the current form. If you then start using forms!someform name etc. in your code then a re-name of the form or or even placing a form inside of the new navigation control will mean the form reference is now incorrect (forms placed into the nav control in fact become sub forms). This means forms! refs can can really get messed up fast in web applications. By stuffing control values into variables you note how no forms ref is required. We thus eliminate this forms ref issue and quite much wind up with something near as good as "me" in VBA to reference the current form in question.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Saturday, September 10, 2011 11:02 PM
  • Albert,

    Having read some of your posts here and elsewhere, I'm glad you kept this one short :)

    Kidding aside...

    You are correct in your assumption on the second example; in that part of what I'm trying to do is have the combo boxes be optional. And, assuming I am interpreting the effect of this code correctly, it seems like a good start. Though as mentioned, I'm still learning Macro, so the syntax is still somewhat weird to me. (If it’s not too much a bother, could you provide a pseudo code example of what & and Like mean here?)

    What I don't see is the next step from there, which is to filter the combo boxes, based on previous selections made by their siblings. (I think this is where you might have gotten confused where I was going with manipulating the RowSource properties of the combo boxes in my VBA example.)

    I'm pretty sure I'll need some If-Else tests for which combo boxes have been triggered, possibly Is Null() as the conditional (assuming that is how "no value" is represented here), and finally a SetFilter() against each of the affected combo boxes. But it’s the specific parameters that elude me at the moment.

    In your examples, what exactly is being stored in c1 & c2? The arguments passed to SetLocalVar appear to include references to the entire combo box objects, yet SetFilter seems to read the variables as if only the Value properties were stored. Is that in fact the case, or is there some implicit referencing going on? (I'm thinking that might be part of the key to resolving where I'm stuck.)

    The other thought that occurs to me, where would the insertion point be for this? Am I still embedding it under the AfterUpdate event, or would I need to move it elsewhere?

    I know I'm asking a lot, but sadly Microsoft's API documentation is woefully inadequate. (http://msdn.microsoft.com/en-us/library/ff835438.aspx)

    Thanks

    Sunday, September 11, 2011 5:52 AM
  • In the above example we simply have two combo boxes on a form (one for state, and another for city). I take the values from the two combo boxes and stuff the results into two variables (c1 and c2). So these are just two controls on a form we going to grab the user entered values and use for the filter. Thus c1 and c2 are some text values here.

    Note that you do not have to introduce the extra step of stuffing the values into variables as I did. You are free to filter/reference the forms!formName!controlName directly in the setfilter command.

    You do not have to do this extra step, but I EXPLAINED in the above post as to WHY I did this. I did this to remove the need for hard coding the forms!name in the expression).

    So you could go:

     SetFilter ([City] = Forms!frmMyFilter!cboCity
                                  And [State] = Forms!frmMyFilter!cboState)

    So there is not a need to stuff the value of the control into a variable.

    The problem with the above is that the forms ref is hard coded, and we do not want to do that. Worse, if you place the form inside of a navigation control, then the above becomes something like:

     SetFilter ([City] = Forms![Navagation Form].form.[NavagationSubForm]!fcboCity
                                  And [State] = Forms![Navagation Form].form.[NavagatonSubFrom]!cboState)

    So you do not have use temp vars as per my example, but the idea and beauty of setting a TempVar is you do not need to use a qualified form reference. However for filters if you reference a form you MUST use a full qualified forms expression. Thankfully filters do allow the use of a variables so the above makes this code far simpler.

    As for making the combo box optional, again that ius why I posted the two examples. As noted, since the expression has is to be run server side (not client side), then you cannot build the filter as a string, it has to be an expression. By changing the = to "like" with a *, then if you leave the combo box blank, your expression will evaluate to this

    [City] like [TempVars]![c1]

    And that then becomes:

    [City] like "*"

     

    So, if you leave it blank, it will match anything (it is in effect ignored).

    But if you enter a city, then you get something like this:

    [City] like "Edmonton*"

    So use of the like and a asterisk (* is wild card) saves a lot of coding here.  So the optional trick is based on using * and "like" in place of:

     

    [City] = [TempVars]![c1]

     

    In the above, you HAVE to enter a value into c1 and it not optional.

    You do not have to use either suggestion here.

    There is another example is outlined here where my above suggestion was not used:

    http://blogs.office.com/b/microsoft-access/archive/2010/04/21/macros-search-form.aspx

    I just think using "like" as above is much less code for optional text boxes (or combos). As noted since the expressions are pre built expressions and they are not a string that you build in code. So you can drop like idea and simply code in all the possibilities.

    However with 3 choices that is 6 possible expressions. (3 factorial). With 4 choices you up to 24 possible filter expressions. So using the "like" idea is a nice short cut in code. The above blog article also has an examples but you can see my response in the above and I suggest the "like" idea to reducde the amount of code. So you now have two good examples here to work with.

     

    I would get some basic fitler working and grasp the use of simple things like the Setting of temp vars etc. Once you get these basic skills up and running then you can takle the cascading combo box.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada


    Sunday, September 11, 2011 7:10 AM
  • The, rather obvious, answer to my first question might have occurred to me before posting last week, had I not been on the tail end of an all-nighter at the time...
    "&" is the string concatenation operator, same as in VBA, and "Like" is the same as it is in SQL.

    As for the other (more pertinent) question, regarding Macro syntax, some clarification might be in order...

    Does, for example, SetLocalVar(c1,[cboCity]) reference the object, as in VBA?
    c1 = Me.cboCity

    Or, does it make an implicit reference to the object's Value property, as in:
    c1 = Me.cboCity.Value

    Thus, if the former is true, could one then reference the properties of the stored object?
    Example: SetLocalVar(rs, c1.RowSource)

    Or, if the latter is true, could an explicit reference to the desired property be passed instead?
    Example: SetLocalVar(c1,[cboCity].[RowSource])

    • Edited by Bill Nesbitt Wednesday, September 21, 2011 5:19 AM
    Wednesday, September 21, 2011 5:04 AM
  • A really great question. Answer is it same as me.cboCity.value. So no ref to object, just the value. And thus no column() property is available for web.

    Albert k

    Wednesday, September 21, 2011 7:35 AM
  • Here's another question you might like...

    SetLocalVar (c1,[cboCity] & "*")
    SetLocalVar (c2,[cboState] & "*")

    SetFilter ( ([City] Like LocalVars!c1)
    And
    ([State] Like LocalVars!c2) )

     

    What if I've got null values in some of the fields?

    In the real thing, unfiltered I've got 65117 records, but lets say I have the SetFilter action running at form load (so, everything is set to [FieldName] Like *).  I now only have 64807 records, because the wildcard appears to omit null values.

    (BTW Strangely, this thread was the top Google hit on a few of my searches for the answer.)


    Edit: Partial answer to my own question, just hit me...

    The expresion being passed to SetFilter, in this example, is the SQL WHERE clause part of the filter.  (Duh!)  So, I need to cram an "Or [FieldName] Is Null" in here somewhere.

    • Edited by Bill Nesbitt Thursday, September 22, 2011 3:09 AM
    Thursday, September 22, 2011 2:47 AM
  • Simple solution to the Null value problem is something like this...

    frmMyFilter : cboState : After Update

    If IsNull([cboState])
       SetFilter([State] Is Null)
    Else
       SetFilter ([State] = [Forms]![frmMyFilter]![cboState])
    End If

    The drawback being, to filter multiple values, I'm locked into using a linear cascade using nested If-Else statements...

    frmMyFilter : cboCity : After Update

    If IsNull([cboState])
       If IsNull([cboCity])
          SetFilter(([State] Is Null) And ([City] Is Null))
       Else
          SetFilter(([State] Is Null) And (City] = [Forms]![frmMyFilter]![cboCity]))
       End If
    Else
       If IsNull([cboCity])
          SetFilter(([State] = [Forms]![frmMyFilter]![cboState]) And ([City] Is Null))
       Else
          SetFilter (([State] = [Forms]![frmMyFilter]![cboState]) And (City] = [Forms]![frmMyFilter]![cboCity]))
       End If
    End If

    That gets more and more crazy the more controls you're using.  So, here's what I'm toying with...

    Taking a page from my original VBA example, is there any way to store the expresion passed as the Where condition of SetFilter as a variable?  I'm not sure how this would work in Macro, but in most languages literal strings evaluate as thier contents.  So theoretically something like this...

    SetTempVar (strFilter,'some expression to evaluate later')

    SetFilter(TempVars!strFilter)


    Then the only test necesary is whether strFilter is empty (as it would be heading into the first After Update triggered), or if a ' And some other expression' needs to be added to it.

    • Edited by Bill Nesbitt Thursday, September 22, 2011 8:01 PM
    Thursday, September 22, 2011 7:49 PM
  • Finally got one of my searches to return this handy peice of info...

    http://www.utteraccess.com/forum/Cascading-Combo-Boxes-M-t1959043.html

    Still not exactly what I'm looking for, but at least good enough to get a linear cascade working.

    I don't suppose there's any getting out of using separate queries for each of the combo boxes, though?

    Thursday, September 22, 2011 10:57 PM
  • Bill and Albert,

    You folks are discussing the one thing that is really challenging me with converting Access forms to web pages. I have a page that uses 3 comboboxes to filter the displayed records. When I developed the form I used macros with Tempvars to filter the rowsource queries for the Comboboxes so after selecting in combo1 the records in combo2 were filtered and the same with combo3. Turns out that when publishing the forms to the sharepoint it throws errrors saying that I can't use tempvars in queries. I modified some of my forms to use filters but can't find a way to make cascading comboboxes work. Any thoughts?

    Thanks

    Doug

    Thursday, September 22, 2011 11:02 PM
  • Doug,

    Not sure the error messages you got, but the cake is a lie, as far as it being TempVars themselves.  It sounds like you did what I'd planned on doing originally, update the combo box values via manipulation of the RowSource property.  Unfortunately, that was on the list of the many, many things Microsoft deemed "unsafe" and chose to block the use of in web databases.

    http://www.msoffice.us/Access/PDF/Macros%20in%20Access%202010.pdf

    The good news is, it sounds like you're trying for a traditional linear cascade, so the link in my most recent post should put you on the right track for that.

    Thursday, September 22, 2011 11:47 PM
  • What if I've got null values in some of the fields?


    I did not realize you're going to filter on a column that may have nulls. Yes, we can handle this issue. I am a rather busy person, and often I cannot get back here for a few days.

    If you read my post I spent quite a bit of time talking about combinations and factorials and how this problem gets very complex if we cannot make the conditions optional. On the other hand if we could have passed and built a string expression why on earth would I be wasting my time here making long posts and speaking about factorials if we could have used a string expression as a filter in the first place?

    The architecture of this web system is this:

    WT3-1wT

    If you look at the above diagram your query and processing is occurring on the server side, but the code you are running in a form is running on the client's desktop inside of their browser. So we could be talking about a safari browser on an iPad. Last time I looked there is no recordsets or even the concept of a rowsource for a combo box in that browser.

    To make a long story short the filter command runs server side, but the values come from the browser side. Notice how when you are writing form code there is FEW functions (bring up the expression builder). However for the filter command in that form code you'll notice all of sudden there is near FULL VBA like function set. The reason why you get such a large dynamic change of functions while your coding from one line to the next code is due to the filter command is going to run server side. The code and expression is not evaluated browser side.  In theory a string filer could be added, but it would have to be evaluated two times (once browser side, and then again server side)

    Any way to make a longer story even shorter, you'll have to put in a condition for allowing null values. I used "like" as per above as it is rather short and simple.

    If you want to include nulls, then add a flag that you set.

    The filter command will now look like this:

    (  ([FirstName]=[LocalVars]![sFirst]) Or [LocalVars]![nFirstNull])
       And
    (  ([LastName]=[LocalVars]![sLast]) Or [LocalVars]![nLastNull])

    In the above I used = in place of like, but "like" is fine if you looking to match the start of the first few chars of a person's name. Note that the above scales (avoids factorial) and you can add as many optional conditions as you like. 

    The above will also work for numbers, and the above keeps indexing intact.

    The code for the above filter command looks like this:

    fitleropt

     

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Friday, September 23, 2011 11:00 AM
  • Do you have a reference or page number that says this is not available due to being "unsafe"?


    Actually, it is not really an issue of unsafe as compared to where the data comes from and how web browsers work.  A safari web browser running on your smart phone does not have a row source setting nor recordsets for a combo box.

    Also, as noted in my post here to you cascading combo boxes are possible, but as I stated lets get the filtering part down and working first…

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Friday, September 23, 2011 11:11 AM
  • Not sure what happened to my post here last night, might have saved Albert some time replying to now irrelevent info...

    Still not the non-linear cascade I've been looking for, but at least have something presentable now.

    Unfortunately, the web compatibility check is tossing error ACCWeb103902 on my combo box RowSources.

    What is wrong with this picture? 

    SELECT DISTINCT [FieldName] FROM [QueryName];


    Edit: Saw a suggestion elsewhere to use GROUP BY instead, but unfortunately, same result.

    Friday, September 23, 2011 2:53 PM
  • From what I have read you can't use GROUP BY or DISTINCT in web queries.

    My basic question is simple, but would allow complex constructions if it can be done. My question is how do you filter the rowsource query for a combobox based on some control action. This pertains to a web form, not client form? Parameters seem the logical way but I can not find a way to set a parameter for anything but a form or report. Tried using the same query for form and combobox on the form and using a parameter set in a BrowseTo macro statement but that did not work with the combobox rowsource query. There must be a way to do this. I understand that if you are going to have casecading comboboxes that a trip to the server and back is required for every combobox update, but it should be possibly you would think. This is easy to do with ASP.NET, but unfortunately I am limited to using Access for this task.

    Friday, September 23, 2011 4:50 PM
  • Yeah...  I'm with you there.

    This is a perfectly normal activity for web interfaces coded in other languages like .NET, Java, PHP, etc., or even thick client applications coded in VB or members of the C family.  I'm sure it must be tied in some way to why Microsoft chose to only offer a crippled version of Macro, rather than VBA, which would have been more organic to web developers.

    Though the one key difference is that in other languages, you need to specify a connection object (ODBC, for example) to link the client to the database.  I'd assumed, by its absence, that Access and/or SharePoint was handling this internally.  But, now I'm beginning to wonder if that's what's going on here.

    To be honest, part of me is tempted to toss Tomcat up on the server, translate my VBA implementation into Java, and rig a page viewer web part in SharePoint to encapsulate that instead.  The only things holding me back are the time I've already burned getting this far, and that I'm so close now.


    Friday, September 23, 2011 5:57 PM
  • Actually yes you can cascade combo boxes.

    If you read what been posted here so far:

    <quote>
    You can do cascading combo boxes (but again it not clear if you doing as such and that is a different question again).
    </quote>

    And this:

    <quote>
    I would get some basic filter working and grasp the use of simple things like the Setting of temp vars etc. Once you get these basic skills up and running then you can tackle the cascading combo box.
    </quote>

    And

    <quote>
    Also, as noted in my post here to you cascading combo boxes are possible, but as I stated lets get the filtering part down and working first…
    </quote>

    I might have missed more, but I suspect my posts are not being read too close.

    First of all I have well noted a web based query running on the server side can not have a reference to a form that is browser on your desktop (what happens if the person clicks on a ebay link and that same browser session now doesn't exist?). Furthermore what happens that queries running and you power down your smartphone? How can you have a forms reference to the browser on your smartphone if you power down the smart phone?

    OK now that we've have a little bit more understanding of this, it means we're going to have to force the query and the references to be placed in the form, and not use a web based query that is saved on the server side.

    The server side query cannot simply call out to the internet and figure out which browser on your computer it should attempt to reference some form when in the mean time the browser been closed or is now a URL of shopping on ebay.

    Furthermore as noted (assuming what I posted here is being read), we really want to adopt a development practice in which we don't hardcode forms references in our code. 

    Ok, now, lets code up something that will allow a cascading combo box.

    Lets assume we want to enter city into a text box, and then the combo box will display all of the stores in that city.

    So we have a form with a text box called txtCity. (it could be bound or not).

    We have a combo box that will pull data from tblStores

    In the after update even of txtCity we can have this code:

    aupdate

    Eagle eye observers will notice that I used a global (tempvar) as opposed to LocalVar. LocalVar SHOULD work and in fact does for client side, but fails for the web (this is a bug I have to get around to filing, but it was file that bug or post here – I guess the bug file will have to wait another week or so).

    Also note while I said global var, the local (locavar) and global (tempvars) are in fact local ONLY TO THE CURRENT browser session. So if you launch another browser, then your global + local vars are again separate (this is exactly the same as launching two front ends in a standard access setup, the variables for each front end are separate even in VBA applications).

    Anyway, the above simply sets a variable to the city we want to filter for the combo box. The next step is to build a combo box, and do NOT use a server side query. Simply fire up the query builder and save the sql DIRECT into the combo box. You get this:

    clientquery

    When you close the query builder, you can view/see the property sheet like this:

     

    cboqu2

     

    Note how a variable can be used. The reason why this works should be clear in the diagram (but I guess such things even when drawn out with cute pictures are not so clear).

    So this is not a saved web query but I saved the SQL right into the web forms rowsource.

    The same above approach works for if you want to do cascading combo boxes.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada


    • Edited by Albert D. Kallal Friday, September 23, 2011 10:19 PM remove condensending tone
    Friday, September 23, 2011 8:15 PM
  • Hello Albert,

    I would never call you anything negative. It looks like you have contributed a lot to this sparse community. I got errors when I used tempvars in saved queries. This points up the problem with doing effective searches on the web. I have searched on many terms and never came across your article on doing this. Wish I had as I have spent a lot of time trying to do this.

    I'll try them in the  rowsource of the combobox. I thought I had tried this but I have gone down so many false paths that it is very possible I did not. If this works you have made my day. Thanks.

    I actually went another route and created a web form that works by using continuous subforms as list boxes. Isn't real pretty, but it works down three levels.

    Friday, September 23, 2011 9:28 PM
  • Actually, I just edited that post – I was not intending to give the idea that ANYONE here is calling me anything. I am here to be a professional and represent the best of on line communities.

    It would be MOST unkind to accuse this community of calling me names (and if they were, so what, I spent 10+ years in on-line forums, and as such I can take a punch.). So to be clear, I not stating anyone called me anythere here, and my apologies to everyone if that how my post read.

    I think what got me going here is that we just did not have enough time as of yet to deal with the cascading combo, but I had stated yes, we can do this several times.

    We eventually got down to having the chance to showing how this can work, and at the end of the  day that is fine enough for me.

    I really should write up a article about the null flag trick for filters, as I not seen that anywhere else.

    Regardless, I hope this all helps everyone here and everyone - you all have a great weekend.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Friday, September 23, 2011 10:22 PM
  • What is the null flag trick?
    Friday, September 23, 2011 11:20 PM
  • Albert,

    Apology accepted. Though on a personal note, some of your posts do come off as more than a little defensive or even arrogant.

    Case in point:  In your second post in this thread you're arguing the merits of using variables vs. fully qualified references. To be honest, I didn't know what to make of that. It had nothing to do with my follow up questions, yet it reads like you were either responding to an attack on your previous post (defensive), or that you assumed my lack of mentioning it implied I didn't already understand the concept (arrogant).

    Of course, after taking a week off to work on something else, it hit me that maybe you just misunderstood what I was asking (hey, I'm not perfect either), posted the rewording of it, and we both moved on.

    The point is, not to get too worked up, it’s not productive.
    Life's too short; take a deep breath; walk it off; insert euphemism here :)


    • Edited by Bill Nesbitt Saturday, September 24, 2011 12:38 AM
    Saturday, September 24, 2011 12:08 AM
  • Doug,

    I think that's a reference to an issue I was posting about earlier, where some of my filter combos had selectable null values.

    But, before everything went sideways, I was about to post that it went away when I restructured them following the suggestions at that link I posted last night.  In that, the null values don't come up anymore.

    Albert's mention of it reads like a To Be Continued, and I'm looking forward to what he comes back with.

    But, in either case my RowSource to populate the combos was the same in both implementations.  So, for me, the compatibility check error is the bigger concern.

    Your subform implementation isn't something I've tried yet; sounds interesting.  Do you have a link to a guide somewhere?


    Thanks
    • Edited by Bill Nesbitt Saturday, September 24, 2011 12:36 AM
    Saturday, September 24, 2011 12:35 AM
  • *BUMP*

    Doug,

    The Null Trick is right here...

    It stems from the implementation Albert had been describing up to this point. Except (need to squint to see it) using text boxes instead, but that's not important.

    Albert,

    I'd already moved on to the other implementation when you posted this, since it gets around the nesting If-Else logic.

    But I think I follow what you're doing here.  Though, I'm straining a little bit to read how your variable assingments are set up.  Can you blow that up for us?

    Thanks

    Saturday, September 24, 2011 1:13 AM
  • Albert,

    Now that I've had a chance to read this the rest of the way through...

    Yes. That is the implementation I picked up from the link posted last night, and already switched to. (BTW typo... I think you mean to have the Requery on cboStores.)

    Where I've been stuck since this morning is web compatibility check doesn't like DISTINCT in my RowSource, which is necessary so my users don't have to go through 100 listings for Denver before finding Detroit. And the other bit I touched on earlier, filtering them this way eliminates the null values.

    One key difference I noticed is you are including the ID field in your query, whereas what was in the suggestions I followed appears to work without it. Does that get me around either of the problems I'm having? Or is that just because you have both Store and City in the one query for both controls?

    Side Note: That's not a bug. Local variables (regardless of the language) only have scope within the object or subroutine they are declared in.  So to have one object base its values on another object, you would have to use something with wider scope. Temporary variables (in the few languages that bothered to include them) have even smaller scope, typically only exist within the loop they are instantiating. What is bugged is the mind of the idiot at Microsoft that got LocalVar right, but decided to name the global variable type TempVar. :)

    Finally, just to put this mess to bed, you missed several inflammatory comments in your "remove condensending tone" edit. You might want to give that another pass.

    There are many, but this might have been the worst:

    Note how a variable can be used. The reason why this works should be clear in the diagram (but I guess such things even when drawn out with cute pictures are not so clear).

    Particularly, when you consider the fact that it’s the first time you've brought up this part of the conversation.

    And while you're at it, might be a good idea to cut everything north of the Requery example; nobody asked you to repeat anything, or to read your whining about it, and the discussion was already beyond the bits you're repeating anyway.


    • Edited by Bill Nesbitt Saturday, September 24, 2011 3:58 AM
    Saturday, September 24, 2011 3:57 AM
  • What is the null flag trick?


    Great question.

    The question and problem was how to have several text boxes on a form where the user can "optionally" enter some values to filter the forms data.

    I posted a simple solution, but then it was pointed out that some of the columns we are to filter on might have null values (my solution had not assumed that possibility and those null values where NOT appearing in the filtered results when no filter on that collum was entered).

    So for the case where null values exist in the table then I simply added a flag to allow all null values to show up in the filter anyway.  The reason why the above is important since people here were considering solutions that meant for each possible text box to filter on then we have to write every combination (actually the correct term is permutations) for the filter.

    If you don't follow the above code example then to filter just 5 text boxes you have to write 5 x 4 x 3 x 2 x 1 possible different if/then else conditions in your code. The number of 5! (5 factorial) is an amazing value of 120 possible different coding permutations you would have to code! Is not math cool here? (did a lot of math while taking computing science and I still love how math tells me things!).

    So the null trick solves the issue of nulls in the table and thus allowed us to ONLY require 5 if/endif statement to solve this problem (only one if/then need be written for each text box filter on the form). So the following code shows the use of the null flag in a variable idea:

    fitleropt

    Now in the above we only had two text boxes, but we can add more text box filters by just adding more if/then sets for each text box. We don't have to code for permatations of possbile filters.

    edit: some has commented the code is too small. Here is the same again but larger:

    largefilterp1

    large2

    Albert k.

    Monday, September 26, 2011 4:28 AM
  • Thanks for the summary, I was just thinking about the loops developing in the thread :)

    One correction though, it would actually be 2^n vs. 2n permutations; each "if" is one and each "else" is another.  But, otherwise correct (32 > 10).

    Here's a good one for ya...  I know in standalone databases you can cram that into a module and have it called by After Update.  Is that still allowed in web databases?

    Edit: Just had a "duh" momment...  The above code assumes values have been selected on both controls.  So, a module wouldn't fit here, short of adding some extra tests for which boxes have been triggered so far; needless complication. 

    So, how this works is: After Update on the first control would require one test, the second two, and so on.

    Monday, September 26, 2011 5:29 AM
  • Where I've been stuck since this morning is web compatibility check doesn't like DISTINCT in my RowSource, which is necessary so my users don't have to go through 100 listings for Denver before finding Detroit. And the other bit I touched on earlier, filtering them this way eliminates the null values.

    I think the best solution for this type of situation is to create a table with a list of cities in it (you can even use a set of table triggers to maintain this atomic list of cities when a person enters or edits a customer record).

    I mean for most combo pick type lists the more normalized data design one has then there going to be a corresponding increased possibility that some table of atomic values to select from will exist for that combo box anyway.   This approach will thus reduce the need for group by/distinct.

    I would be the first to admit that in the past I not normalized my databases to the point where I have a cities table, but now for web based I could easy be convinced that such a practice would not only be a good idea but one I likely will adopt. In other words since we have lack of group by/distinct here then we have to rely on better and more normalized data designs to make up for this shortfall.

    One key difference I noticed is you are including the ID field in your query, whereas what was in the suggestions I followed appears to work without it. Does that get me around either of the problems I'm having? Or is that just because you have both Store and City in the one query for both controls?

    That combo box is to simply let the user select a store ID. In most cases the first column of 99% of my combo boxes are always the PK value (that should be the norm in most applications, not just web based). That value going to come from a (hopefully) atomic/normalized data table. So the 3 columns I am use for the combo box to select a store is:

    Id (pk)   This is the primary key value of the store from tblStores.

    Store (text) (this column perhaps should be called StoreName) This is the name of the store

    City (text) – this is just the city column from tlbstores.

    So this issue of using ID is not related to nulls issue at all. This is just a simple example in which we wanted to restrict the combo box to a list of stores based on a city choosen from a given city in a text box called txtCity on the same form.

    As noted, in most cases a value returned from a combo box should in fact be a PK value if we have a good normalized database. So use of ID is simply the PK value and is quite much the norm for most Access applications.

     

    Side Note: That's not a bug. Local variables (regardless of the language) only have scope within the object or subroutine they are declared in. So to have one object base its values on another object, you would have to use something with wider scope. Temporary variables (in the few languages that bothered to include them) have even smaller scope, typically only exist within the loop they are instantiating. What is bugged is the mind of the idiot at Microsoft that got LocalVar right, but decided to name the global variable type TempVar. :)

    Well then how come using LocalVars works when the database is un-published works just fine then?

    The reason why it works is we are setting a variable and then executing a query. We are NOT calling another routine. That query is being executed in the context of the CURRENT code routine and variable scope.  If we were calling another macro or another routine, then fine, the local value should not be available.

    As noted this variable scope works just fine in the unpublished client. If you take my above example, and change TempVars to LocalVars (in both the query and the code that runs that query), then you will find this works JUST FINE when unpublished.

    I suppose you could consider this somehow a bug when this is un-published, but we not called any other routine here (and we are not using a saved query). The fact that this works in the client means the client assumes the current local variables are in scope and the server does not. You have to explain then why the query sees the value in client based applications then?

    I 100% accept the obvious fact that the scope is clearly not the same in both cases and will bring this up with the Access team next time I see/talk to them. 
     
     

    There are many, but this might have been the worst:

    Note how a variable can be used. The reason why this works should be clear in the diagram (but I guess such things even when drawn out with cute pictures are not so clear).

    Particularly, when you consider the fact that it’s the first time you've brought up this part of the conversation.

    How is suggesting that I posted a diagram in a PREVIOUS post in this thread arrogant?

    It is not the first time I brought the diagram up here! If I had posted the diagram in the SAME response and stated such and such concept was obvious then I would be 100% nutty. I did not do that. I simply stated I took the time to post a cute diagram that states how the architecture is setup and thus that diagram should answer and make a lot of the questions in this thread more clear. 

    How is asking people to have spent some time to refer to a PREVIOUS post in this thread arrogant when such a diagram helps explains the additional question(s) being asked? We are talking about a diagram helps answers a LOT of questions BEING ASKED AFTER I POSTED the diagram.

    AFTER the diagram was posted - got it?

    I really am not grasping what being stated here since the cute picture was posted WAY UP PREVIOUS. I was simply pointing out some logical and obvious conclusions that one can make by looking at that diagram.

    You could not possible be standing here that I am to ONLY point out things in writing that one can conclude from that diagram? You are in fact suggesting that I am to assume everyone else here cannot think and learn from that diagram then?

    You mean I am not supposed to post additional information until it is the "first time"? As pointed out, it not the first time and this is like pointing out we dropped a hammer, and you have to come back and tell me I have to spell out to everyone that the hammer will then hit the ground? I have to assume basic reason is in place here, but you are suggsting otherwise? When I say I dropped a hammer, I actualy DO assume veryone here knows it will hit the ground and I do not have to spell it out.

    This is the difference between learning to add numbers and look up a answer in a book. You are suggesting a course of action in which people need to go look up in a math book what the answer to 2 + 2 = as opposed to learning how to add numbers. If one can add then one does not need that book or have to find a book with the answer for 2 + 3. 

    Teaching the concepts here means I am answering 100's of answers and not just 2 + 3 = 5.

    As for the other additional info posted, I simply saw another post with forms!referneces and thought it a great idea to point out to readers that this is not a great practice if you can avoid such forms! references. Are you saying this should not be done? So if next week another post appears with forms references I should not take the time out to point out a better way? Why and how is this not relevant?

    The "worry" that you grasped this reference concept or not is rather selfish on your part. So what if you did, or did not? This community is not about only you. What is important here is that the community can learn new things here and we do that by simply looking at posts here and showing how such examples etc. can be improved.  I saw a post with a poor practice and simply pointed out a better way. This is what we do in these forms and I see this as nothing but a good thing.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Monday, September 26, 2011 7:29 AM
  • >cram that into a module and have it called by After Update.  Is that still allowed in web databases?

    Yes you can. This will eliminate the need if you had 5 text boxes to have to repeat the same filter code in each text box after update event.

    The above is only setup for two "optional" filter text boxes, but if you take that above code and place/save it in a standard web macro you create from the ribbon (not a data macro and not a embedded form macro, just new macro from ribbon), then you can now call this code from the after update event of the 5 text boxes and you only have ONE copy of this filter code.

    Note that when you call such a saved macro form a form macro (they are called UI macros) the code as posted will work since all controls of the current form REMAIN in scope when you make this call. Once again this means if you call a saved web macro (non form macro) and it reference controls as per above code in that macro, the references to the controls on the form will work fine (they carry over in scope to the saved macro). 

    As for the 2^n?  I was simply stating that if you cannot make the filter optional, then one will in fact have to create a separate filter for each possible 5 text box permutations. I noted they are an if/then/else set of code, but the basic point is with 5 text boxes then any combination of the boxes being optional with a value or not would mean 120 possible different filters are required in your code for 5 text boxes.

    The above appoarch means I only have ONE filter command and I only need one if/then/else per optional text box.

    >The above code assumes values have been selected on both controls. 

    No, it assumes they are optional. The isNull() test in the above code is what makes this work. As noted the previous example I had each text box filter was optional. However, we wanted to include null values. 

    If the user leaves the text box blank, then no value for that column is to be filtered. (we want all values). So we set the true/false flag (nLastName = true). This is true/false flag local variable and is NOT a column in the data.

    And note how it is a local var!

    Note how the filter command for this part is NOT using or looking at the table at all. In effect we are doing this:

     

    setfilter (like a where clause)

           Where (LastName = [LocalVars]![sLast]) or [LocalVars]![nNlastNull).

     

    So "sLast" is the value of what the user entered into the text box the text box (in this case nothing, so we put in a empty string).

    The above of course then evaluates to this:

    (LastName = "") or (True)

    Well, the above will thus always allow anything for last name and hence no filter. That null flag is not even looking at the table. It is simply an single expression. It in effect 100% negates anything for the last name column.

    The "trick" here is that we can filter on an expression that has NOTHING to do with the table. We are setting a filter based on the value of a local var.

    We could even do this silly example:

    Setfilter

        ([LocalVars]![nLastNull)

    That becomes:

    SetFilter

       (True)

    So you can use a filter on a variable expression that does not even look at the table. That is what I am doing here. So the expression is still "hard" coded, but adding a flag to each column condition means it really optional based becuase I added a "flag" to the expression.  I used  local var called nLastNull and nFirstNull in this example. You will need such a flag for each text box filter. I just cooked up placing a "n" in front, but one can use any variable naming idea here.

    So the "True" part is always true no matter what in the text box or the table. We set that expression = true when the user does not enter a value into a given text box.

    I also re-posted the above code as a larger image for ease of reading.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Monday, September 26, 2011 9:00 AM
  • Albert,

    In regards to the ugly bit...

    I don't know why you're biting my head off, when I was trying to help you save some face.  I'd like to think we're both better than this and could work it out, but that part of the conversation is unproductive and inappropriate for a public forum.  I don't see a private messaging feature here, but I believe I saw you had a publically available email somewhere.  I'll try to ping you there, and if you want to continue that discussion there great, if not so be it.

    In closing, one last piece of advice...  Please do yourself and your fellow members the courtesy of proof reading your posts, particularly your tone.  We are all professionals here, not idiots.  There is never an excuse for talking down to people; not in the real world, and certainly not online.

    As for getting back to the productive part of the conversation, I will post back when I've had time to cool my own jets.

    Monday, September 26, 2011 5:25 PM
  • I think some of the confusion in the thread is we're getting different topics jumbled up, so its not always clear what is being covered and when.  (Or, by whom?)  For example, we've got two different issues with handling null values: nulls in filtered results, and nulls in the combo boxes doing the filtering (in turn, compounded by discussion about the filtering of the combo box values).   So, I'm going to try to break it down here.

    Disclaimer: I'm not normally one for crazy long posts, either writing or reading them, but I think some recap/clarification is in order here.  So, I will probably break this into multiple posts, one topic at a time.  Please feel free to respond as I'm throwing them up, but I would ask that you please keep your responses short and on topic.

    Thank You

    (Doug, if you're still out there, it sounded like you were having some of the same issues.  Feel free to add to the list, if you see something I missed or haven't gotten to yet.  Same for any new folks just joining us.)

     

    Monday, September 26, 2011 11:45 PM
  • Topic #1: (Resolved?) Removal of DISTINCT/GROUP BY from RowSource SQL

    (Edit: I'd originally conceived having this series of posts follow a guide format, but somewhere between plan and practice that was forgotten. What follows is more like the intended format, updated with current info.)

    To begin, an example...

    In the after-market auto parts manufacturing world, there exists a logical construct called an "application", which maps a specific vehicle (identified by a combination of year, make, and model), to a general part of a vehicle (let's say, a radiator), and the part manufacturer's part number for that application.

    tblApplication
    ---------------------------------------
    ID (PK)
    PartID (FK: tblPart)
    BaseVehicleID (FK: tblBaseVehicle)
    PartNumber

    tblBaseVehicle
    --------------------------
    ID (PK)
    YearID (FK: tblYear)
    MakeID (FK: tblMake)
    ModelID (FK: tblModel)

    tblPart
    ----------------
    ID (PK)
    PartName

    tblYear
    ------------
    ID (PK)
    ModelYear

    tblMake
    ------------
    ID (PK)
    MakeName

    tblModel
    -------------
    ID (PK)
    ModelName

    Now, let’s say I want to look up the part number(s) for a radiator matching a 1967 Dodge Dart. So, for that I need 4 combo boxes, and subsequently (extending from the UtterAccess example http://www.utteraccess.com/forum/Cascading-Combo-Boxes-M-t1959043.html) four queries that look like this:

    Parts
    ---------------
    Field: PartName
    Table: Part
    Sort: Ascending

    (Note: To restrict the output of this query to parts for which there are matching applications, it is necessary to include tblApplication in the data map.)


    ModelYears
    ---------------------------------------
    Field: ModelYear
    Table: Year
    Sort: Ascending

    Field: PartName
    Table: Part
    Criteria: [Forms]![frmMyForm]![cboPart]

    Makes
    ---------------------------------------
    Field: MakeName
    Table: Make
    Sort: Ascending

    Field: PartName
    Table: Part
    Criteria: [Forms]![frmMyForm]![cboPart]

    Field: ModelYear
    Table: Year
    Criteria: [Forms]![frmMyForm]![cboYear]

    Models
    ---------------------------------------
    Field: ModelName
    Table: Model
    Sort: Ascending

    Field: PartName
    Table: Part
    Criteria: [Forms]![frmMyForm]![cboPart]

    Field: ModelYear
    Table: Year
    Criteria: [Forms]![frmMyForm]![cboYear]

    Field: MakeName
    Table: Make
    Criteria: [Forms]![frmMyForm]![cboMake] And, gluing it all together, the following RowSources for each combo box:

    cboPart.RowSource : SELECT [Parts].[PartName] FROM [Parts];
    cboYear.RowSource : SELECT [ModelYears].[ModelYear] FROM [ModelYears];
    cboMake.RowSource : SELECT [Makes].[MakeName] FROM [Makes];
    cboModel.RowSource : SELECT [Models].[ModelName] FROM [Models];

    And then finally, the After Update calls to Requery for each combo box on the next box down the line.

    (Note: To keep it simple here, I'm dispensing with other topics we've covered in the thread: optional fields, null values, etc. Just a simple linear cascade.)

    Everything looks good here, but... whoops! On the very first combo box (cboPart), I have to scroll through 14000+ entries for "AC Condenser" before I get to the next part in the list. As anybody experienced with SQL will tell you, the go-to move when something like this happens is toss a DISTINCT or GROUP BY in those SELECT queries, which works perfectly in standalone, but Access Services doesn't like them.

    Also, consider this: As the table structure above implies, the data has all been normalized. So, why is this happening at all? The answer, it turns out, might not be so obvious at a glance, but is fairly simple once you spot it. Read on...

    Albert's solution -- to replace the reference to the combo box objects on the criteria of the filter queries to a reference to a TempVar, and modify the RowSoures accordingly -- is a nice idea. (Especially when you consider that we need to store that same TempVar anyway for the data filtering to work; separate topic.)

    So, in your After Update for cboMake, instead of just the Requery call on cboModel, you might now have...

    SetTempVar(Make, [cboMake])
    Requery(cboModel)

    Additionally, it is required that we embed the queries into the combo boxes themselves in order for this method to work, as variables are only stored on the client side.

    Side Note: To code oriented folks, it might not be obvious how to do this, because (let's be honest) we do not have to muck about with building control queries via the GUI in VBA; not that we couldn't. But, suffice it to say the tiny button marked "..." that shows up in Property Sheet --> RowSource, and the ribbon button Create --> New (Web) Query are not the same thing.

    So, by way of both of these suggestions, the query embedded in cboModel (for example) will instead have structure like this:

    Field: ModelName
    Table: Model
    Sort: Ascending

    Field: PartName
    Table: Part
    Criteria: [TempVars]![Part]

    Field: ModelYear
    Table: Year
    Criteria: [TempVars]![Year]

    Field: MakeName
    Table: Make
    Criteria: [TempVars]![Make]

    Now, if I were to switch to Form View to test this, I'll still have the same result as above. But, here's the big clue...

    If we take a peek at the RowSource auto-generated when we built the embedded query for the first box (another neat trick of doing it this way) we see:

    SELECT tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    Pay dirt!!! The reason for the multiple values appearing is a by-product of the inner join between tblPart and tblApplication, which is necessary to restrict the values populating cboPart to parts for which there are applications referencing them (as mentioned above). This of course (duh), was what the equivalent web query above was doing. However, this fact was obscured by the required manual entry of the RowSource property.

    To me at least, this is the kind of no-brainer that might have been more obvious if this were built on a code-oriented platform, rather than a GUI based one; wherein one is more inclined to put some thought into the query design beyond: pick a table, pick a field. (Though, perhaps the code-like visibility into what the embedded queries are doing completes the law of threes, in terms of cool things Albert's solution does provide.)

    Ok...

    So, now we know where the problem comes from in this scenario, but how do we fix it in the real thing?

    Two things to consider here:

    First, the tables are imported from an industry standard database maintained by a third party source; so editing the tables could be hazardous, particularly at update time.

    Second, my client only manufactures a subset of the literally thousands of different types of parts a vehicle might have; having to scroll 9,560 entries to find one of a handful that map to applications is no solution. So, removing the restriction on what populates cboPart is out.


    (On the other hand, I am running out of time/patience with this, might be worth chopping out the unused records in tblPart, and deal with the consequences later.)
    Monday, September 26, 2011 11:46 PM
  • Topic #2: (Resolved) Manipulation of the RowSource property to produce cascading combo boxes.

    Ok...

    Maybe this should have been first, given it was what I was after in the original post, but I'm trying to take it easy and post these as they come to me.

    Short Answer:

    Locked down, can't do it. Try the methods outlined in Topic #1 instead.

    Not-As-Short Answer:

    Basically, the idea is to mimic in Macro what I was doing in the VBA in the original post. One might expect this to be doable using the SetProperty action, but use of the RowSource property by this means is not allowed in web databases.

    Before the thread started, I'd done some research on this and came up with some vague info, but at least a couple references from Microsoft that some things in Macro got modified because they were deemed "unsafe" for the web. (Will post some links up later, when I get a chance to reconstruct my searches and find them again.)

    Albert pointed out that some things had to be modified because of the introduction of the client-server relationship, which past experience as a software developer tells me could be true to some extent. (Though VB has been an available option for web applications for more than 10 years now, one must wonder why VBA wasn't provided as the coding language if this was the main problem.)

    The truth is probably somewhere in between (never under estimate the power of corporate deadlines over design decisions), and maybe we will see the missing parts added back in with later versions.

    But, the point is... For now, this is a non-starter.

    Tuesday, September 27, 2011 3:07 AM
  • Topic #3a: Display of null values in combo box filters.

    To be honest, in the interest of moving forward, I kind of abandoned this when the solutions in Topic #1 resolved larger issues.  So, it kind of got glossed over in here.

    But, because with any such application there are potential situations where Null has meaning, best practice demands it be addressed.  So, here we go...

    When I started out, I had the combo boxes simply pointing via the RowSource property at thier respective fields within the main data query like so:

    SELECT [MyQuery].FieldName FROM [MyQuery];

    The resultant set included any null values present.

    However, after changing this up so that the combo boxes point to separate queries, as in Topic #1... POOF.  The null values are gone.  So, what happened?

    Off the top of my head, I'm thinking the major difference is whereas the combo box queries have criteria specified, the data query does not.  But, that is a guess.

    Hard to say for sure, since the first box I'm using (which, by nature of the cascade, has no criteria assigned) has no nulls in its respective table.  But, I think I can test this by swapping its position in the cascade with one of the boxes that should be displaying null as an option  (Will post back when I've had a chance to try that.)


    • Edited by Bill Nesbitt Tuesday, September 27, 2011 11:25 PM
    Tuesday, September 27, 2011 8:06 PM
  • Topic #3b:  Handling null values in combo box criteria.

    Just placing a stub here for this, for the time being, pending some testing.

    I had this working at one point, but don't remember at what stage of the discussion we were at, and doesn't look like it was covered directly.

    Will delete this later if it turns out to be a non-issue, or update if not.

     

    Tuesday, September 27, 2011 11:48 PM
  • Topic #1: Removal of DISTINCT/GROUP BY from RowSource SQL

    Here's the weird part...  My tables are already normalized, so I'm not sure where the redundant entries are coming from.

     

    There is no reason I can think of that you going to get a bunch a redundant data in a query based on a single table. This makes no sense at all.

     

    That's why I was wondering about including the table PK in the query/queries, if maybe that would solve the issue.

    I don't think I've written a combo box in about 10 years were the first column is not the primary key of the table in question.  I'm not suggesting to include the primary key as the first column because it might remove redundant data, I am saying you can't build a coherently functioning and practical database system if you are not retrieving a row of data that is identified by a PK. If that combo box is being driven by a table then when the user selects a value the only practical and logical and coherent way to select that row of information is to use the PK value (and this is the case even for not storing the combo value but when we going to use the combo box for a filter). 

     As a general rule that PK value will be hidden from user view and they can search by a text column (usually the second column of the SQL for the combo box). So as a general rule for any database system the idel goal here is to identify rows of data by primary keys.

     

    And, glueing it all together, the following RowSources for each combo box:

    cboPart.RowSource : SELECT [Parts].[PartName] FROM [Parts];

    Every thing looks good here, but... whoops! On the very first combo box (cboPart), I have to scroll through 14000+ entries for "AC Condenser" before I get to the next part in the list. As anybody experienced with SQL will tell you, the go-to move when something like this happens is toss a DISTINCT or GROUP BY in those SELECT queries, which works perfectly in standalone, but Access Services doesn't like them.


    If there is not repeating data in the table, then you're not going to get repeating data in the combo box, and that's the end of the story.  If the data is normalized, then you are not going to have repeating data and throwing a distinct or group by is a band aid solution that is the result of not dealing with the problem at hand.  As I pointed out, the combo box(s) in most cases should be returning a pk anyway. I Might want a 3/4 bolt, but there might be more then one such bolt. So you cannot use the part name for the selection but you're going to use the primary key value that identifies that single row in the parts table.  The reason being is you can have several parts of the same name.

    PK      PartName              Type
    34       ¾ bolt                  stainless steel
    78       ¾ bolt                  aluminum

    In the above we have a part name call ¾ bolt. So the combo box will have to return a PK of 34 or 78 depending if I want a stainless steel or aluminum bolt. We don't necessarily have to filter by the PK, but the other columns in the main form's table that we plan to filter from the this combo box if done correctly are going to have parts or whatever based on storing a FK value of 78 for aluminaum bolt and not the actual text description values in that table. So it not clear why PK values are not being returned for the eventual filtering of the main data file on foreign key values here.

     

    Albert's solution -- to replace the reference to the combo box objects on the criteria of the filter queries to a reference to a TempVar, and modify the RowSoures accordingly -- is a nice idea. (Especially when you consider that we need to store that same TempVar anyway for the data filtering to work; separate topic.) But, what about the first combo box RowSource, which is not being filtered?


    As I pointed out you can't modify the RowSources at runtime, but we can make the expressions and conditions for that particular RowSource optional by using a boolean flag. If the first combo box RowSource does not need to be filtered, then don't use any TempVar vars or expressions that will cause any filtering for that combo.

     

    Edit: On further experimentation with this, I noticed something that was missed before. (See bottom half of Albert's post 9/23/11 8:15pm) The RowSource example targets the table, not the query that was created in the preceding screenshot. Huh? Why bother making the query, if you're not going to reference it? Some clarification is in order here.


    Look closely, I fired up the query builder on the ROW SOURCE of the combo box in question, I did not fire the query builder to create a web query. We are NOT building a web query. I'd clicked on the expression builder button or little the [...] that appears when you to enter into the row source property for the combo box properfity sheet on the form. I clearly explained I'm doing this because we want to force the SQL and RowSource to be forced to be "put together" in the browser side, not the server side.

    Localvars and TempVars DO NOT exist on the sharepoint server, they ONLY exist on your desktop web browser running on your iPhone or FireFox web browser. The query that was created in the preceding screenshot is in fact the combo box row source and is NOT A SAVED WEB query. When I close down the query builder, the sql text is shoved direclty from the query builder into the row source of the combo box (try doing this).

    The reason why we're doing this way, is again and I have to keep repeating myself again is that the TempVars and localvars do not exist and do not run on server side. This means a web saved query cannot use expressons with TempVars but a CLIENIT SIDE sql expression CAN use such expressions. 

    again: The temp variables ONLY exist in your web browser inside of your UI macro code. This is code that is running client side and in your browser on your desktop as opposed to code that's going to run server side on SharPoint. By the way this also explains why TempVars variables cannot be utilized by a data macro (because data macros run server side on SharePoint and they do not know anything about the variables that exist in a web browser running on your smartphone half way accross the world).


    So read and look clearly and read that part again. We are to invoke the query builder on the combo box were RowSource. By the way this is not a new feature, and you could do this in classic VBA applications for I believe for the last 18 years. So you don't have to base a combo box on a saved query, you can shove the "sql query" directly into the row source of the, box itself, and this is allowed in web based systems just like the client VBA applications. The only difference is we can't modify the actual SQL text at runtime, but as I pointed out we can use expressions that allows us to optionally have filters or conditions in that combo box sql anway. 

    However the issue of optional filters (from values entered into a text box or combo box) on a seach form is certainlly possible as my city seach example shows. As I stated, you should get a couple example optional filter systems working here, and then the next step would to be tackle the ascading combo box problem, but as I've pointed out both are entirely solvable here and my example does restrict the combo box to a given city typed into a text box, and we could thus further use that combo box to filter the form.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Wednesday, September 28, 2011 11:01 PM
  • Topic #2: (Resolved) Manipulation of the RowSource property to produce cascading combo boxes.

    Ok...

    Maybe this should have been first, given it was what I was after in the original post, but I'm trying to take it easy and post these as they come to me.

    Short Answer:

    Locked down, can't do it. Try the methods outlined in Topic #1 instead.

    You can't set the rowsouce at runtime, but as all my examples show, you can add a Boolean expression that makes the row conditions you wish to filter optional and thus at the end of the day this means we can cascade the combo boxes and you can "optional" include criteria in that combo box.

     

    one must wonder why VBA wasn't provided as the coding language if this was the main problem.)

     

    There is a good many reasons. This is like asking why Boeing choose one type of engine for their plane over another. Somebody would make the large investment to make VBA work on the web. The problem is such a language was never designed and architected to work anything remotely like the web systems today. That langugae was created 20 years ago.

    If we had VBA then things such as dimming of variable types would not be the same (they are not the same). Even basic objects and things like a recordsets do not exist in a web browser. Last time I looked at the Firefox browser running on your iPad, you will not find such browsers allow or even have recordsets. This is why you have to call server side routines for record processing, or what we call in access web services a named data macro. A form can pass values to a named data macro, and values can also be returned back from that server side code but web browser does not have recordsets. So record processing code needs to be written to run server side.

    Worse is web browsers have few of the VBA functions anyway. We would wind up with something that resembles the VBA syntax but with so many changes and likely more frustration.  The reason why the setup works the way it does, is because the code you're writing inside of a form has to run on the browser on the user DESKTOP and not server side.  You can see this when you bring up the expression builder inside of an access web form and you see this:

    webm


    I count about a dozen functions (if you remove functions that are duplicates perahps less). Talk about a "bare" cupboard. The reason why the function set is so small because it has to be compatible with all web browsers (including running on your smartphone browser).  So once again you don't have all those VBA functions inside of a web browser anyway.

    I suppose one solution would be able to have all of your code running server side, but then you not really doing a correct web based designed system. The Access web forms are real AJAX web forms and thus they respond very well because much code runs on the user's desktop without having to communicate with the server. For example when you use the SetTempVar command that command does NOT cause any communication with the web server and those variables are not transferred to SharePoint but remain running LOCAL on the user's desktop inside of the browser (or on your Ipad).  The server has no knowledge of these Temp variables you set in code. So the act of setting TempVars in a browser does not cause network traffic for those variables to be sent up to SharePoint.

    The problem becomes however some code does have to run server side. If you look at the function set within a web query or even within a filter command, you can instantly see inside of access that such code does run server side, and all the sudden you get in near VBA like function set.  You'll see this list of functions:

    filterfunc


    And by the way, the access team did not have anything remotely close to the budgets to build all of those above VBA functions. They used the excel reporting services in which Microsoft did deem the investment necessary to make a lot of the excel functions work on the web (and that was done over SEVERAL versions of SharePoint). 

    At the end of the day Microsoft been working on these web systems and sharepoint for about 10 years. Thus any web development system for Access is going to have to be based on these systems and current infrastructure technologies unless you want about 10 years of investment to occur for the next version of Access (and you would have to wait a very long time).

    Another challenge here is to build an editor in which you don't need years of .net framework training to figure out how to make code run server side vs. browser side. In fact at the end of the day most people likely don't want to care or bother to understand these differences so Access does this for you.

    The macro editor also had to keep code compatibility with the previous macro language. This editor also automatically restrict functions depending on where you code going to run as the above two screen shots show (same editor, but VERY different function list depending where you are).  You can see this when you write server side code (named data macro) you don't see options like message box. The reason is because such a msgbox command makes no sense in code that is running on a server when the msgbox command needs to run on the local web browser.

    VBA for the web would dramatically force you to change your coding practices for the web anyway. Little VBA code and few VBA functions would exist in the web browser and worse such common things like recordset would not work since a web browser does not support these things. So you would need to "change" how you write software anyway.

    The choices made were based on sound engineering principles and concepts and the choices only make sense if time is taken to understand all these issues. I suppose I would love to be able to write JavaScript directly in the editor for the web forms, but that would still not give you record sets or anything much else like VBA anyway.


    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada



    Thursday, September 29, 2011 2:16 AM
  • Top part:

    Yeah, that's what I'm saying. It should not be happening. I'd hoped (still do) maybe somebody would see something I'd missed.  (Edit: See update in previous post.)

    Middle part:

    I was gearing up for an update to this, but you beat me to it...

    It would seem that, to code oriented folks, it is not entirely obvious that "..." and Create --> (Web) Query are not the same thing. You are correct that it is nothing new, but in that same time frame you mention, I've only rarely run into anything that VBA couldn't do better and without messing with queries short of where I'd otherwise use a temporary join table in a normal SQL environment. Just something else I'll have to get used to with VBA out of the picture, I guess.

    But, yes, once I figured out that was the bit you'd left out the solution made more sense. (Just so we're clear, I'm not pointing a finger, I'm just saying it would have helped to know that earlier.) I'm working on redoing that part and crossing my fingers in the hope it takes care of the odd-ball repeating value problem, since it’s not happening to you.  (Edit: See update in previous post.)

    Bottom part:

    As for repeating things, that is exactly the bullet I was trying to dodge with starting this section of the thread. Concept being, we've got posts above here that cover multiple topics and not necessarily in any particular order. Get it all here, sorted logically, and we should only need anything once. From there, edit/delete out the ugly bits.

    But (again, I'm trying to help here), people don't come to forums for 20,000 foot explanations. That's what books are for. Unfortunately, as the saying goes, time is a luxury. So, when people come to developer forums, they are looking for someone to show them what they are missing, or doing wrong, so that they can get it done and move on.

    It is a very good thing that you bring these topics up, so we have them in the back of our minds, and can process that information as we move forward; and we are all better for it. But, the human brain doesn't typically process the "big picture" immediately; we break it into smaller chunks and build our understanding over time. (Not entirely unlike how computers process information.)

    So, when someone asks a simple follow-up question, it does not necessarily mean that they have not read, or that they didn't understand what you have posted. It may simply mean they have not processed it yet. Or, let's be honest -- humans are also inherently selfish, and it may not have made sense to them at the time in terms of resolving whatever chunk they were tunnel-visioned on just then. Be patient, give them a chance, and they will figure it out, I assure you.

    The other phenomenon to consider is tone. It’s one thing to give someone a nudge, like "if you scroll up to such-and-such a spot, you should see I showed how this is done", you'll find that people appreciate this. Another good tip is to do so, yourself, and see if there was anything left out or might have been better explained, and add that in. Extending from there, if you don't get what it is they are asking, the polite way to handle it is to just respond with your own follow up, even a simple "I'm not sure I follow you. Could you please rephrase the question?" No repetition necessary.

    But, no good can come from repeating the same things verbatim until you are blue in the face, or shouting (and that is how it reads in many of your posts, especially this latest of yours) things like "I already EXPLAINED this". Seriously, think about it. Would you speak that way to someone you just met in the real world? It’s no different online, especially not on a forum where you're supposed to be one of the experts.

    Ultimately, if the concepts of providing helpful information, or speaking to your colleagues as adults is that difficult for you, what are you doing here at all?

    In fact, now that I think of it... Outside of this last ditch attempt to salvage this thread for the sake of others, I for one would much rather never hear from you again.

    Edit:  One more thing, you might want to take a look in the mirror before insinuating that people aren’t reading/understanding things you’ve posted.  For, example, this statement made in my previous post, in reference to an earlier section of the conversation…

    My tables are already normalized, so I'm not sure where the redundant entries are coming from.  That's why I was wondering about including the table PK in the query/queries, if maybe that would solve the issue.

    …clearly implies that I got what you said, and the idea had been discarded.  It does not imply that I somehow need to be explained what keys are or how tables work.  Seriously, are you trying to be insulting?



    Thursday, September 29, 2011 4:59 AM
  • FYI -- I have something planned for covering optional values in its own topic, I think you might appreciate.  Stay Tuned.
    Thursday, September 29, 2011 5:15 AM
  • FYI -- The info in Topic #1 has been updated directly to avoid an additional long post for people to have to read through.

    Friday, September 30, 2011 5:27 AM
  • My tables are already normalized, so I'm not sure where the redundant entries are coming from.  That's why I was wondering about including the table PK in the query/queries, if maybe that would solve the issue.

    …clearly implies that I got what you said, and the idea had been discarded.  It does not imply that I somehow need to be explained what keys are or how tables work.  Seriously, are you trying to be insulting?




    Actually your response shows you did not understand what I was saying.

    My response was that if your data is normalized, then 99% of the time a combo box will (and should) return the PK (id) of the row source that combo box is based on.

    My point here is you talking about using group by and distinct to remove repeating values and my point is with normalized designs, then two things:

    One:
    you should not be seeing repeating values (but you are)

    Two:
    (this one is MORE of my point)
    Your combo boxes should return the PK [ID] column of the combo sql row source.

    If you did understand what I said, then why did you not come back and give a explain as to why your combo boxes are not now based on the primary key? You did not do that. In other words this is rather unusual setup. The only conclusion I could assume then is you did not grasp this concept or you did not understand what I was saying. Thus I gave an explain as to WHY those combos would in a normalized desing return a id collum. The point not about normalizing, the point is how a combo box will work in such desings and that is a HUGE difference of a point I am making.

    I guess I can take your suggestion and when I see something you did not understand, I shall than simply ask for more clarification in place of giving a long explain.

    My suggestion here was simply that your combo boxes should have always worked this way and if your designs are normalized then you could not really make them work any other way. In other words, the basic design pillar of an application will in almost all cases return a pk and thus the pk value by design will have to be included in the sql rowsouce.

    However in the interest of doing things your way, then explain why your combo boxes are not returning id values and why the SQL would not be including the pk id as the first row of that sql?

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Friday, October 7, 2011 11:20 PM
  • Why are the combo boxes not based on PK values? Really? The answer should be blatantly obvious.

    PK values in Access Services are always autonumber, and except in rare cases, that number will be meaningless to the user.

    What good is it to give a user a choice of 965, 784, 230, etc, when what they are looking to do is filter a list by Gretzky, Roy, Yzerman, etc?

    In the example you provided earlier (9/25/11 8:15pm) and expanded on in your follow-ups from there; yes, this works fine because the store number (tblStores.ID) potentially has meaning to the user. And, no, you should not have duplicate values appearing, in this case, because you are using a single table. However, in general practice this structure is the exception, not the rule.

    (To be fair, the example was also in regards to the separate issue of filtering controls down the cascade, which it does brilliantly. So, I doubt what we are talking about here was at the front of your mind until it came up in the follow up discussion; no fault there, we're all human.)

    Now, as I pointed out above (9/26/11 11:46pm, edited 9/30/11 4:33am), this gets tricky when you are working with multiple normalized tables and suddenly to get at the useful values you need to use joins to link everything together. (Contrary to your suggestion, there is nothing "unusual" about this design at all; in fact, its fairly textbook.)

    So, for example:

    SELECT tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    Matches the subset of parts in tblPart that are referenced (via keys) in tblApplication.  (Sure, you could do this with a WHERE clause instead, but this is the code Access spat out, so I'm running with it.)

    The problem with this, of course, is that there may be multiple references to each of those parts in the parent table (tblApplication). 

    To get around this problem, most SQL implementations include constraints on the SELECT statement such as DISTINCT or UNIQUE (or both), and/or GROUP BY.  Unfortunately, for whatever reason, Microsoft did not implement these for Access Services.  So, that's where the problem lies.

    It did not seem relevant at the time, but earlier I'd left out that I'd experimented with including the PK (tblPart.ID) in the SELECT clause like so:

    SELECT tblPart.PartName, tblPart.ID FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    But of course, this has no affect other than to load the extra column into the resultant record set; which in turn, is completely ignored by the combo box object is only interested in the first column values.


    • Edited by Bill Nesbitt Wednesday, October 12, 2011 2:19 AM
    Monday, October 10, 2011 10:06 PM
  • /sigh

    Ok... Let's try this... again.

    I have not changed any settings as yet in Access, and therefore am using the defaults. As such, I can confirm the behavior you are describing does not occur by default.

    The result of setting a combo box with this select statement:

    Select id,FirstName,LastName,City from tblPeople.

    is a list of ID numbers, not any of the other fields listed in the statement.

    If there is some setting that allows for multi-field combo boxes, or hiding fields in the select statement from view, how about you do something helpful for once and post where it is?  The closest you come to doing so is to indirectly reference some wizard, which makes exactly zero sense as it’s a drag-and-drop operation to create a combo box.

    Neither are any of my keys visible to the user, nor would they be of any meaningful significance to the user if they were (as again they are autonumbers). Where are you getting that I am doing otherwise?

    You on the other hand, stated point blank in a follow up to your own example, to which I was referring, that your PK is visible and does have meaning:

    (9/26/11 7:29am)

    That combo box is to simply let the user select a store ID.

    And yet, here you are trying to preach to me best practice?

    Check your facts before posting, or don't post at all; its rubbish like this that has dragged this thread out for a full month now.

    Why are you asking why I included the id when it really cannot work any other way?

    First of all, I didn't ask that in any of my recent posts.  Inclusion of the ID field was already resolved as not having anything to do with the matter several posts back.  Why are you still on about this?

    Second, of course combo boxes work without including the ID field. If they didn't we wouldn't be having this discussion.

    Finally, let's pretend this does work the way you suggest, with the ID field not appearing in the output:

    SELECT tblPart.ID, tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    Ok, so what? It's still matching the same records in the parent table, thus producing the same duplicate items in the combo box.

     

    Tuesday, October 11, 2011 10:15 AM
  • >I have not changed any settings as yet in Access, and therefore am using the defaults. As such, I can confirm the behavior you are describing does not occur by default.

    Try reading again. this is what I stated:

    >> you been using the defaults in Access client databases, you also will have a autonumber id. And Access by default then HIDES the pk column for you when building that combo box with the wizard.

    I CLEARLY stated WHEN you use the combo box wizard. So the default we are talking about is WHEN you use a combo box wizard. So when creating a table, the pk column is [id] (another default by the way). And ALSO when using the combo box wizard, the first pk column by DEFAULT is hidden for you (so we talking about the wizard hiding this column).

    The wizard (client apps) looks like this:

    tblcolorsw1

    near last screen shot of the wizard looks like this:

    tblcolors

     

    NOTE in the above the "default" I was talking about (the check box to "Hide Key column - recommended).

    And the length column setting you are asking for (works in both web and client) is this:

    tblcolorsprop

     

    The above was produced by the wizard, but if you do not use wizards, then just set the length for the column in question to zero as shown above. So you DO NOT need to use a combo box wizard to hide columns in a combo box, and as noted Access has always had this ability. As noted, no wizards for web combo, but the concept of setting columns is the same in client or web systems anyway.

    The point here is every book and article and suggestion you get from anyone is that you do not show or allow users to see these pk values.

    So you hide the PK value by setting the combo box format of the first column ([id]) to a length of zero. You do not need a wizard to accomplish this goal, but if your building databases then it rather hard to imagine building any system without using the pk [id].

    The issue thus becomes how to hide such id collums

    >>That combo box is to simply let the user select a store ID.

    >And yet, here you are trying to preach to me best practice?

    Yes that combo box will let the user select the store ID, but that DOES NOT imply they will see the ID column. As I CLEARLY pointed out if you want to expose a store number for the users, then create a Store number column. So yes, we ALWAYS should be selecting the ID column, but we NEVER let the users see that.

    So yes, as a matter of common knowledge for just about any developer who claims they understand basic database normalizing, you will as a general rule select the ID column, or in this case from table stores, the store "ID" column.

    >First of all, I didn't ask that in any of my recent posts.

    Yes you did ask this question, and I stated if you understood normalizing when using autonumber id's then your answer and examples should have been CLEARY showing that when selecting values for relationships (even with a combo box), then you going to grab the pk [id] column.

    The fact that you asked why one should select the [ID] is 100% fundamental to this whole issue and this shows everyone here you not grasped a basic concept of how autonumber [id] work in databases. Grasping this concept also removes the issues of those combo boxes needing group by or distinct in "most" cases.

    >Ok, so what? It's still matching the same records in the parent table, thus producing the same duplicate items in the combo box.

    Because you can then remove the inner join and just use a where clause and the result will be the desired list of atomic values. I suppose it depends on the given case what is being attempted here, but at the end of the day EVEN if we did have a join that produces duplicates we STILL ARE going to be returning hidden pk [id] value from that table and that is STILL a fundamental goal here.

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

     

    Tuesday, October 11, 2011 11:30 AM
  • No... Please... Stop... No...

    Why are you bringing up a wizard at all, if what you are suggesting there merely requires a change to one property that would take less than half a second to execute? (Think before posting.)

    Moreover, and I'm trying to give you the benefit of the doubt here, changing the Column Widths property as you suggest only results in an empty combo box. If I undo that change, I get the first column. And in fact, confirming what I've stated earlier, if I then widen the combo box out to give it more than enough room to display the two columns, I still only get the first column. You insist this should work, so I assume it to be correct. But if so, then clearly, there is some other setting(s) at play here.

    The point here is every book and article and suggestion you get from anyone is that you do not show or allow users to see these pk values.

    I have never stated otherwise; why do you keep repeating this, as if I had? (Read before posting.)

    Yes that combo box will let the user select the store ID, but that DOES NOT imply they will see the ID column. As I CLEARLY pointed out if you want to expose a store number for the users, then create a Store number column. So yes, we ALWAYS should be selecting the ID column, but we NEVER let the users see that.

    No. You did not state this until your post yesterday. Correcting yourself is all fine and good, but denying you made the error and tossing the correction back at the person that called you out on the error, isn't helping anybody. (Read, think, re-read, before posting.)

    So yes, as a matter of common knowledge for just about any developer who claims they understand basic database normalizing, you will as a general rule select the ID column, or in this case from table stores, the store "ID" column.

    I have over 20 years’ experience that suggests otherwise.

    First of all, "common knowledge" regarding whether the tables are normalized or not has no bearing on any decision, for or against, including the PK in the selection statement of a combo box. And, in either case, I have already posted my table structure demonstrating that the tables are normalized. Why do you keep coming back to suggesting they are not?

    Second of all, just because you choose to build your applications a certain way does not make it a "general rule". Quite the opposite, in fact. I have literally worked with thousands of applications over the years both of my own design and as many, if not more, those of others. And not once have I ever seen a case where anything other than the field the user will be selecting from was included in the selection statement of a combo box.

    Now, on the other hand, I have not stated any opposition to the idea of including the PK in the selection statement either; simply that it is not producing the results you are suggesting.

    Lastly, this is however the first time I've encountered an SQL implementation that did not include DISTINCT or an equivalent constraint. And have repeatedly identified this as being the issue at hand. If inclusion of the PK in the combo box is somehow related to a workaround to that problem then, GREAT, I'm on board with that. Please provide that solution.  If not, stop making irrelevant posts.

    The fact that you asked why one should select the [ID] is 100% fundamental to this whole issue and this shows everyone here you not grasped a basic concept of how autonumber [id] work in databases. Grasping this concept also removes the issues of those combo boxes needing group by or distinct in "most" cases.

    ROFL!!! Wow. Way to dig yourself deeper, buddy. Yet again, here I am, having to correct your misinformation...

    It is necessary for keys to exist in the tables, so that the query test constraints can be used to isolate matching records across the tables. However, there is no such requirement that the table key(s) be return values, or anything other than what you want the combo box to display for that matter.

    This:

    SELECT tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    matches the same records as this:

    SELECT tblPart.ID, tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    Or, if you really want to load your record set up with garbage data:

    SELECT tblApplication.PartID, tblPart.ID, tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    Or, even more:

    SELECT tblApplication.ID, tblApplication.PartID, tblPart.ID, tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    It is completely irrelevant whether we include keys from either table in the SELECT clause, and thus the resultant record set. So, why are you dragging us around in circles?

    Because you can then remove the inner join and just use a where clause and the result will be the desired list of atomic values.

    Mmmkay... Let's explore this for a moment, using the WHERE clause equivalents to the examples above:

    SELECT tblPart.PartName FROM tblPart, tblApplication WHERE tblPart.ID = tblApplication.PartID;

    SELECT tblPart.ID, tblPart.PartName FROM tblPart, tblApplication WHERE tblPart.ID = tblApplication.PartID;

    SELECT tblApplication.PartID, tblPart.ID, tblPart.PartName FROM tblPart, tblApplication WHERE tblPart.ID = tblApplication.PartID;

    SELECT tblApplication.ID, tblApplication.PartID, tblPart.ID, tblPart.PartName FROM tblPart, tblApplication WHERE tblPart.ID = tblApplication.PartID;

    All of these produce identical results to their counterparts.

    Sure, we’ve normalized the tables so each match corresponds to a single record.  But we’re only giving the user visibility on the data (tblPart.PartName).  And regardless which way we make that happen, this means the user will see duplicates.

    This is how SQL works; it is by design. Subsequently, this is why constraints such as DISTINCT or GROUP BY are included in nearly every SQL implementation.

    I suppose it depends on the given case what is being attempted here,

    Exactly what don't you understand about what is being attempted here? I already gave you the full explanation, various attempts to clarify, and at the moment am drawing a blank as to how to dumb it down further. But, if it will help get us out of this rut, I'm willing to try.

    but at the end of the day EVEN if we did have a join that produces duplicates we STILL ARE going to be returning hidden pk [id] value from that table and that is STILL a fundamental goal here.

    No... The fundamental goal is to implement whatever it was the Access Services team had in mind when they made the bizarre choice to disallow the use of DISTINCT or GROUP BY.

    I want to have faith that such a workaround exists, but I've been hunting for weeks, finding nothing but this thread and a handful of other folks stuck in the same place; nothing from Microsoft.

    And, unless you get some weird sadomasochistic pleasure out of all of this, I have to assume you don't know either; or else you would have posted it by now.

    In closing, I will ask this… one… last… time.

    If you are either incapable or unwilling to be helpful and provide useful information, don’t post here; nobody wants to read your trash talk.

    • Edited by Bill Nesbitt Wednesday, October 12, 2011 2:14 AM
    Wednesday, October 12, 2011 2:12 AM
  • >Why are you bringing up a wizard at all, if what you are suggesting there merely requires a change to one property that would take less than half a second to execute? (Think before posting.)

    Because you asked how and why one would include a primary key in a combo box, and I said this is quite much how the wizard defaults have worked in Access for 18 years. So anyone reading this who ever used a wizard to create a combo box will EASLY grasp ths concept.

    And I ALSO pointed out that this is quite much how a well designed database is going to work . And I ALSO well pointed out that you can simple change the collum widths without using a wizard. So what is the problem suggesting to use  wizard or doing this manually? 

    > the Column Widths property as you suggest only results in an empty combo box

    I simply said if you use the combo box wizard in a client database, you'll see the text description or second collum as per my example The primary key will be hidden and you not see a blank combo box. This is an obvious and standard fare to every access database.
     
    >I have never stated otherwise; why do you keep repeating

    Because you asked a silly question as to why I'm including an ID or primary key value in my combo box? I simply stated this is the natural and default way access has worked from day one.

    >No. You did not state this until your post yesterday. Correcting yourself is all fine and good,

    Please quote my text where I ever said other wise? I simply suggested as a matter of sensible action and coherent designs a combo box most of the time should return the [id] column from the table it is based on.

    >" regarding whether the tables are normalized or not has no bearing on any decision, for or against, including the PK in the selection statement of a combo box.

    Actually it does, if you are using a combo box to select a row from a table, how can one reliable include and identify an individual atomic a row from from that other  table then?

    >Now, on the other hand, I have not stated any opposition to the idea of including the PK in the selection statement either;

    I never claimed or you stated any opposition, but I certainly claimed that you are confused and should have ZERO reaons to ask why I am including a PK when in fact it should have been the norm and obvious to anyone who understood this concept.

    >. However, there is no such requirement that the table key(s) be return values, or anything other than what you want the combo box to display for that matter.

    There is no such requirement, but if you're going to identify a row from another table that is driving the combo box, it's pretty much a given conclusion and foregone concept or a "reasonable" idea that you should identify such selections by pk or [id] in this case.

    Simply put, if you set the length of the primary key to zero in the row source that is driving the combo box, then you should not be complaining or making wild speculations about how the user will see the primary key value, as a simple matter of fact they will not.

    So you failed to explain everybody here, why you're not hiding the primary key and worse you not explained why  when everyone uses the combo box wizard (or as you pont out advoid the wizard and manually set the first column format length = 0) you are seeing a blank combo box?

    >f you are either incapable or unwilling to be helpful and provide useful information, don’t post

    If you're looking for an example how a combo box with the first collum set to zero length for the pk collum, then download the sample here:

    And there is two examples, one for web, one for client.

    Try here:

    http://www.kallal.ca/msaccess/DownLoad.htm

    scroll down and download try the last example called:
    Simple combo box that hides pd [id] column (both client and web example)

    Do the users see the primary key when they select their favorite color above?

    Answer,  they do not!

    So why are you having A problem and claiming that you cannot hide the primary key value in a combo box when I stated clearly that access wizards or the settings in the width columns have worked this way for 18 years?

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, October 12, 2011 4:33 AM
  • Albert,

    My wild guess is that the OP may not want to use the ID in the table. Perhaps the OP is saying that instead of a ID, he wants the second field/column of the combobox in the list, since by default Access bounds to the first field/column. Maybe bound to second field/column?

    Perhaps the OP may be using a un-normalized table to input data without any ID.

    Wednesday, October 12, 2011 5:05 AM
  • AV,

    Thanks for the post. Always glad to have fresh input.

    I'll admit right off that, as I'm typing, I've not yet read the post you are replying to.  So, apologies in advance, if I'm misunderstanding any of your points here.

    Actually, all the tables come from and are maintained by, a third party source, and are normalized. There is no input from the user, so that part of the form is easy :) Just disable the text boxes, so the users can't muck anything up.

    In a nut shell, here's what's up with the combo boxes...

    Let's say we have two tables:

    tblApplication
    -----------------------
    ID (PK)
    PartID (FK: tblPart)
    (FKs to other tables)
    (some other data)

    tblPart
    --------------------
    ID (PK)
    PartName
    (some other data)

    Next we need a combo box that will later be used to filter the form by tblPart.PartName, but only those values referenced via tblApplication.PartID à tblPart.ID; because of the M:1 relationship, we know to expect some duplicate values from our Row Source query, but none the less academic:

    SELECT DISTINCT tblPart.PartName FROM tblPart INNER JOIN tblApplication ON tblPart.ID = tblApplication.PartID;

    Or this, if you prefer:

    SELECT DISTINCT tblPart.PartName FROM tblPart, tblApplication WHERE tblPart.ID = tblApplication.PartID;

    We now have a combo box with a single reference for each part we make; sweet.

    For the sake of discussion, let’s fast forward to where we are publishing out to SharePoint.

    Uh-oh!  Web compatibility check is throwing ACCWeb103902.  After a hunt on the net, and experimentation to confirm, it was revealed that DISTINCT is what’s triggering the error.  A little more of the same, and nope, GROUP BY is out too.

    So now the question is, how do we workaround this limitation?

    Any ideas?

    Thanks in advance,

    "The OP"

    Wednesday, October 12, 2011 8:12 AM
  • >Why are the combo boxes not based on PK values? Really? The answer should be blatantly obvious.

    >PK values in Access Services are always autonumber, and except in rare cases, that number will be meaningless to the user.

    If you been using the defaults in Access client databases, you also will have a autonumber id. And Access by default then HIDES the pk column for you when building that combo box with the wizard.

    Read tip #7 of Access 10 commandments here:
    http://access.mvps.org/access/tencommandments.htm
    < quote>
    Thou shalt not use Autonumber if the field is meant to have meaning for thy users.
    < quote>

    >What good is it to give a user a choice of 965, 784, 230, etc, when what they are looking to do is filter a list by Gretzky, Roy, Yzerman, etc?

    No one suggested that you expose or show or use the [id] value. So this idea was not suggested by anyone here.

    If you have a table called tblPeople then you have this:

    Id
    FirstName
    LastName
    City
    etc. etc. etc.

    So you combo box will perhaps be based on this:

    Select id,FirstName,LastName,City from tblPeople.

    Your combo box will thus display and search by firstname, but will return id (first pk column) This is how Access has always worked. The end users will never see or care or know about the id column. If you don't do this then what about a combo box based where you do not return id and you have this:

    John Smith Edmonton
    John Smith Calgary

    If the above 3 column combo box does not include and return the pk ID column (that is hidden from the user), then how can you select the correct row from the table? You cannot return John, since that will not identify a single row.

    You MUST USE the id column to identify the row, but NO ONE is suggesting that users EVER SEE the pk id. So you simply include id as the first collum in teh combo box, set the format length for that collum to 0 so users never see it. The combo box will then search/display by the second collum.

    So you answer and suggesting that users will not want to see the [id] is a non issue here. Your combo box should thus still return the id column? As I pointed out, users will never see or know this this column, and it will have a format length of zero.

    The access combo box wizard for client forms combo box wizard hides the pk "id" column by default for you. As pointed out the defaults for access have worked this way for 18 years. As pointed out in the 10 commandments you NEVER want to give meaning to such numbers and in fact users should never see such numbers.

    So no one is suggesting that users ever see the pk autonumber. In fact the whole compting and IT industry suggests otherwise.

    >; yes, this works fine because the store number (tblStores.ID) potentially has meaning to the user.

    No, I never suggested iin ANY text that this was the case. More so read the 10 commandes suggtestion I gave you. The users will NEVER see that autonumbber pk. If you have an external store number that users will see and use and it is to "have meaning" then you simply add a column to the database called store number. That store number might be ABC123.

    The fact that a new store is being built and no store number not yet been issued has nothing to do with a primary key that identifies the row and allows the database to function until a store number is issued. Such external numbers NEVER have ANYTHING to do with the internal autonumber id. This is a basic concept and setup for database systems and this issue is not limited to Access at all.

    Why are you suggesting to expose an internal autonumber when every book, article and I am suggesting otherwise?

    I never suggested that such a number would be exposed to the end users so makes no sense to suggest that users would see and use a "id" in place of Gretzky. And as I pointed out in the Access client, the wizard will automatic hides the first pk "id" column for you by default.

    If you do not want users to see the pk number then hide it and users will not see the id.

    So, if you can hide the id column for a combo box, and it is the only reasonable way to identify a single row for that combo box, and again the question becomes why are you not using this PK value to identify that row? (this still remains my question).

    I am mentioning the above because YOU asked me why I am including the [id] value when my answer is in fact why would one not use the pk [id].  The examples given and concepts cannot work any other way.


    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, October 12, 2011 9:09 AM
  • >I have not changed any settings as yet in Access, and therefore am using the defaults. As such, I can confirm the behavior you are describing does not occur by default.
     
    I shall re-quote what I stated on this matter:

    >> you been using the defaults in Access client databases, you also will have a autonumber id. And Access by default then HIDES the pk column for you when building that combo box with the wizard.
     
    So I CLEARLY stated WHEN you use the combo box wizard. So the default we are talking about is WHEN you use a combo box wizard. I am NOT stating that you have to use the combo box wizard if you don't want to. I am simply stating that if you want to see the default setup I am talking about, try the combo box wizard and see the settings it puts in the property sheet.

    The above suggestion also means those reading this who used the combo box for years will also grasp and understand the suggestion and settings to be used here.  So the suggestion here is not that you "should" use the combo box wizard, but only that the settings and what we are talking about is the default behavior of the combo box wizard is to hide the pk collum.

    So when creating a table, the pk column is [id] (another default by the way). And ALSO when using the combo box wizard, the first pk column by DEFAULT is hidden for you (so we talking about the wizard hiding this column).

    So it is not clear why you are seeing a blank text in the combo box when you change the setting (try the wizard in a client app to see the setttings).
     
    The wizard (client apps) looks like this:

    tblcolorsw1

    near last screen shot of the wizard looks like this:

    tblcolors

    The above was produced by the wizard, but if you do not use wizards, then just set the length for the column in question to zero as shown above. So you DO NOT need to use a combo box wizard to hide columns in a combo box, and as noted Access has always had this ability. As noted, no wizards for web combo, but the concept of setting columns is the same in client or web systems anyway.
     
    The point here is every book and article and suggestion you get from anyone is that you do not show or allow users to see these pk values.
     
    So you hide the PK value by setting the combo box format of the first column ([id]) to a length of zero. You do not need a wizard to accomplish this goal, but if your building databases then it rather hard to imagine building any system without using the pk [id].
     
    The issue thus becomes how to hide such id collums
     
    >>That combo box is to simply let the user select a store ID.
     
    >And yet, here you are trying to preach to me best practice?
     
    Yes that combo box will let the user select the store ID, but that DOES NOT imply they will see the ID column. As I CLEARLY pointed out if you want to expose a store number for the users, then create a Store number column. So yes, we ALWAYS should be selecting the ID column, but we NEVER let the users see that. (please quote any text where I suggested otherwise in this thread)
      
    So yes, as a matter of common knowledge for just about any developer who claims they understand basic database normalizing, you will as a general rule select the ID column, or in this case from table stores, the store "ID" column.
     
    >First of all, I didn't ask that in any of my recent posts.
     
    You simply asked this question and it was never answered by me.  I stated if you understood normalizing when using autonumber id's then your answer and examples should have been CLEARY showing that when selecting values for relationships (even with a combo box), then you going to grab the pk [id] column.
     
    The fact that you asked why one should select the [ID] is 100% fundamental to this whole issue. Grasping this concept also removes the issues of those combo boxes needing group by or distinct in "most" cases.
     
    >Ok, so what? It's still matching the same records in the parent table, thus producing the same duplicate items in the combo box.
     
    Because you can then remove the inner join and just use a where clause and the result will be the desired list of atomic values. I suppose it depends on the given case what is being attempted here, but at the end of the day EVEN if we did have a join that produces duplicates we STILL ARE going to be returning hidden pk [id] value from that table and that is STILL a fundamental goal here.
     
    Albert D. Kallal (Access MVP)
     
    Edmonton, Alberta Canada

    Wednesday, October 12, 2011 9:25 AM
  • Bill,

    As your Heading indicates about web forms and SharePoint, I’m not familiar with these but Albert would be in better position.

     

    Hmmm….tblPart.PartName? So you are saying you want to filter by the part name. Naturally, that would be a problem, as it would give a list of duplicates values however, they may not be duplicates. DISTINCT may not filter to the correct part.

     

    To illustrate what I mean, say a part called "Spark Plug". Say you have 10 types of "Spark Plug" and they have different IDs.

     

    The question now is, "How is your query going to get the correct "Spark Plug"? without any references to the part ID.

     

    The same goes to "John Smith" in a table where there are more then one. You definitely can’t use "DISTINCT" with this one. Unless it your intention to list all "John Smith" in the filter and ignore the rest of the record.

     

    I don’t know of any solution "DISTINCT" and "GROUP BY" problems with SharePoint. So can’t help you in this area.

     

    I’m not sure why is there a need to do "tblPart.ID = tblApplication.PartID" but perhaps you’re wanting to list what is in "tblApplication" and what parts is used in history. If this were the case, "DISTINCT" and "GROUP BY" would not be the way to go as the table "tblApplication" (I assumed many) can have multiple matches. In my opinion, having this table just reduces the query performance and serves no purpose since you only want to find a part by name.

    Perhaps it would be better that you include/display the ID and PartName in your combobox query. That way, your users can pick the correct part to filter to.

    Wednesday, October 12, 2011 10:06 AM
  • You don’t have group by or distinct. The suggestion much earlier in this thread was that you simply have to setup an atomic list. In fact I even suggested one would do this in the case of the city column (and thus you could build/use a city combo box).

    And I even pointed out that I don't usually have a separate city table, but in web based systems, I would likely now include such tables and then only store the pk [id] from that city table in the main tables such as tblCustomers etc. So I already mentioned this suggestion since we don't have distinct or group by.

    And I also noted that you would and could maintain that table by use of table triggers.

    So if you need a distinct list of only parts currently in use, then I can think of two workarounds (and I sure there is many more ideas).

    One:
    Use triggers to maintain an atomic list for the combo box in a separate table.

    Two:
    Simply add a Boolean flag column to the tblParts called InUse.

    I much prefer the second idea, as it allows one to easy enter new parts without disturbing the current data setup. It also means you don't need to create a new separate table etc.

    You would thus simply add a "InUse" column to the tbl parts.

    You then write a common routine (what we call a "named macro") to "check" if the passed part [id] is in use in the main table, and if yes (or no), then the "InUse" column for that single part in tblParts can then be set true/false as needed.

    The above common routine can then be called from the 3 needed events:

    The routines will be short since all 3 table events can call the same routine to "check" and "test" if the part num based is in use.

    An "air" code example could be:

    I have tblCustomers and they have a favorColor. And we have a table called tblColors.

    tblCustomers:

    [id]

    [FirstName] etc. and

    [FavColor] fk to tblColors

    tblColors

    [id]

    [Color]

    [InUse] (our bolean flag)

     

    I then want to maintain a list of colors ONLY in use.

    The table trigger routines (in table customers) will look like:


    after insert:

    afterins


    after update would look like:

    afterupdate


    delete event:

    afterdelte

    The named macro (datamacro) the above calls looks like:

    namedr

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Wednesday, October 12, 2011 11:12 AM
  • see link below

    http://www.access-programmers.co.uk/forums/showthread.php?p=1233221#post1233221

    Thursday, February 14, 2013 2:19 PM
  • Hi Albert,

    I have used your filter example in my project and it works well, however I need to apply a filter based on six combo boxes and my set filter string has got too long after adding the third one. I have tried assigning the first filter string to a local variable strfilter1 and the second string to strFilter2 but I get an automation error. Can you suggest a way that I can manage a really long filter string?

    Regards,

    Chris

     


    Regards, Chris

    Wednesday, June 19, 2013 9:41 AM
  • Found Albert's solution here http://www.utteraccess.com/forum/lofiversion/index.php/t1996223.html What would we do without him?

    Regards, Chris

    Friday, June 21, 2013 3:56 PM
  • Thank you Albert!  This was very helpful and solved my issue with a web form.

    Jon Acampora

    Tuesday, October 14, 2014 11:16 PM