Valid OrderBy Parameter

[gray]

Hi

Access2002/2007

I've spent days on this and I've run out of ideas.

My form has a combobox ("My_Table_Unique_No_Combobox") which the user can choose to sort upon (via context menu). The combobox is populated with an SQL string

SELECT Table_Name As Category FROM Tbl_Names WHERE etc

When they use that option an OrderBy of the following appears in the form properties:-

[Lookup_My__Table__Unique__No__Combobox].[Category] DESC

Note the double underscores...

When the form is next opened I re-apply that Orderby.. however a parameter value box appears asking for

Lookup_My__Table__Unique__No__Combobox.Category

If I give it a valid value the form carries on fine for the rest of the session so there can't be too much wrong.

Any idea why Access prompts for that value on the first OrderByOn=True please? I am at a complete standstill on this one... thnx

 

[BobMcClellan]

have you tried...
setting the orderby = null and orderbyon = false prior to resetting it?
hth,
..bob

 

[boblarson]

To go with what Bob wrote - How are you setting the Order By? It sounds like you are modifying the query somehow instead of using the

Me.OrderBy = Chr(34) & Me.ComboBoxNameHere & Chr(34)
Me.OrderByOn = True

 

[gray]

Hi

I use the ApplyFilter and Timer events to store the latest Filter, FilterOn, OrderBy and OrderByOn values as chosen by the user. I write them into a table by grabbing the strings from the me.orderby or me.filter properties.

I close my form with acSaveNo (but I also clear down the OrderBy and Fllter values from the properties by performing a acHidden edit to ensure they are clean).

When my form fires up, I grab the latest strings from my table and set them e.g.

Dim NewOrderBy as String
 
NewOrderBy = Dlookup( etc, etc...
me.OrderBy = NewOrderBy
me.OrderByOn = Dlookup(etc etc

This works fine until the OrderBy is of the "[Lookup__" type. When they are present, an Enter Parameter Value box pops up as soon as OrderByOn is set to true.

I'm sure the double__underscores added by Access are the culprit... when I run the AdvancedFilterSort, I notice the double underscores are not present in the query.

Why does Access turn my single underscores into doubles? Any idea?

Thanks gents

 

[boblarson]

This works fine until the OrderBy is of the "[Lookup__" type.

What do you mean "Lookup_ " type? I don't understand what you mean.

Why does Access turn my single underscores into doubles? Any idea?

Not sure but what you posted there has two underscores.

 

[gray]

Hi

What do you mean "Lookup_ " type? I don't understand what you mean.

Have to admit this is a new one on me too... but... when using the right_click context menu to add a sort to my form, Access adds a very strange OrderBy to the Form properties...

I've attached 4 screen prints to illustrate this...

1. OrderBy_Screen01.JPG - The Name property of my Combo

2. OrderBy_Screen02.JPG - The Rowsource property of that same Combo showing how it is populated

3. OrderBy_Screen03.JPG - The resulting OrderBy applied to the Form when the right_click context menu is used in that Combo.

4. OrderBy_Screen04.JPG - The Enter Parameter Value prompt generated immediately when OrderByOn is set to True

Hope the jpgs are clear enough to see that Access takes the name of my Combo, adds extra "_"s to it and stores the result in the OrderBy property...? It tags 'Category' on the end which is the alias of the 1st field in the SQL Select.

I kinda' assumed this was normal but maybe no-one else has seen this before either?

Thanks

 

[boblarson]

Well, for one I would check to see if it does it if the table name is named with CamelCase instead of with underscores. Normally you can use underscores but perhaps in this case it is just overloading the system with so many. I would see if you have the same results if you rename your table:

LookupMainRecParentTableUniqueNoComboBox

and see if anything changes (make sure that Name Auto Correct is turned OFF before doing this (I don't trust it as it has been known in the past to cause corruption but even still. You probably don't want all references to that table changed until you know for sure. In fact you could just copy the table and name it with Camel Case instead and then set it as the source for the code.

 

[gray]

Hi Bob

Curiously, the "LookupMainRecParentTableUniqueNoComboBox" part is not the name of a table... in fact it's the name of the combobox but with the prefix of "Lookup_" added by Access... I assume that Access parses this, sees the "Lookup_" prefix and calls some internal code to use the combobox's rowsource as an OrderBy.

I've attached a further screen print from RunCommand acCmdAdvancedFilterSort. As you can see it writes the name of the combo in there and not a table name....

Just in case, I renamed the combo losing the "_"s but unfortunately the Enter Parameter Value box now justs asks for the modified name i.e.

Lookup_MainRecParentTableUniqueNoCombobox.Category

I am utterly baffled.....

Thanks for you continued assistance...

 

[boblarson]

Can you post a copy of the database? I don't think I can go further without it.

 

[gray]

Thanks very much Bob....Attached is a very stripped down version... here are some quick notes about it...

Design Aim
Permit Addrs_Dtls_Form and Addrs_Dtls_SubForm, to use the same recordsource, filters and orderbys.. and to keep their current records in-step.

To See the Orderby Issue
1. Open Addrs_Dtls_Form. This will open it's subform in datasheet view
2. Right click on the Parent Category column in the datasheet... take the option to "Sort Z to A"
3. When the Enter Param Value box pops up, use 1452 (which is a valid value)
4. Click Exit to close the form and then re-open it. As soon as the OrderBy is applied the Enter Param Value box appears.
5. The custom Filter and Sort buttons permit the user to switch sorting and filtering on and off amd/or to deleted them altogther.

Briefly, How the Forms Work
1. Mainform Open event calls "Build_Form_RecordSource" function. This builds an SQL string which is applied main and sunform
2. The ApplyFilter and Timer events of both forms grab the lastest orderbys and filter params and store them in User Preferences table.
3. The above events then apply the filters and orderbys across both main and subforms
4. When opening the main form, the Build_Form_RecordSource function calls the mainform timer event to apply the latest orderby and filter params from the User Preferences table.
5. When exiting the form, it is opened in hidden edit mode and these properties of the form are cleared (to get rid of "phantom" filters and order strings).

rgds

 

[boblarson]

Okay, my first question is why in the world are you doing all of this? I've never seen anyone go to this extent around filters and order by's. Why does it have to save every 100 milliseconds? Pardon me for saying so but this seems ludicrous. I've saved settings for users before but it is only as the form is closed that I save them and then apply them as it opens. Very simple stuff. I am confused beyond belief after stepping through your code.

Now, I know you have an idea of why you are doing this but frankly I am just baffled (and therefore not sure what is happening because the concept is just too confusing to me - and it does take quite a bit to confuse the hell out of me, but congratulations - you've done it.)

 

[gray]

Believe me.. I am confused too...

This may be because I am the product of being completely self taught in Access.

But let me try to rationalise my plan it a little...

Save Period
I don't save every 100 millisecs, the timer counter gets knocked back to 0...the save only gets done when the ApplyFilter event of the main or subform gets actioned. I record each change (rather then just at form closure) in case the form, Access or the host PC crashes...

Filter/OrderBy Application
1. User selects new filter on, say, subform...
2. ApplyFilter Event fires.. at this point the new filter can't be extracted... so set timer...
3. Timer event grabs newly applied filter and writes into table... and fires the main form timer
4. Main fom timer grabs new filter from table and applies it.

easy really...

Why Save filters to a table anyway?
All my attempts to delete existing filters and/or orderbys simply would not work..

I ensured filter was cleared in design mode...
My delete filter button set me.filter=""
I closed with acSaveYes...
But... when the form re-opened the preceeding filter re-appeared... I thought I was hallucinating for 3 days!

It seems that Access simply will not accept a zero length string in those properties..so how on earth do can they be deleted in VB?...
I tried setting them to vbNullString ...
I trued using RemoveAllFilters and RemoveAllSorts... and still the little blighters came back...
I then tried to figure out how to call the built-in deletes using Access' own menu options .. that is a complete nightmere

I found articles confirming these "phantom" filters together with advice suggesting the manual management of filters and orderbys and so I arrived here.

Why have Main and Subforms co-ordinated this way?
I have many custom buttons on the real version but of course they can't be used on a form in datasheet view (which is the most useful view of all) .... so I came up with the main/subform idea.

But In order to use the navigation buttons on the main form it's necessary to keep the filters and orderbys co-ordinated. If not, sequentially movng thru the main form might see the datasheet record leaping all over the place.

phew! I'm open to any advice or tips chap!

 

[gray]

Ah.. I have fixed it!! .... it was entirely my own fault... the My_Table_Unique_No_Combobox
referenced by Lookup_My__Table__Unique__No__Combobox.Category not being populated properly... hence the parameter prompt...