me.orderby not working (1 Viewer)

E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
I have a form based on a table containing organization's member data. In order to allow the user to sort on any column, I add a hidden field for each column, then set an onclick event as follows (using the first name field as an example):
Code: 
Private Sub First_Label_Click()
If LabelFirstMarker <> 2 Then
    Me.OrderBy = "First, Last"
    LabelFirstMarker = 2
Else
    Me.OrderBy = "First DESC, Last DESC"
    LabelFirstMarker = 1
End If
Me.Requery
End Sub
The db was written as a standalone db, but have decided to migrate tables to MySql. As originally written, the "sort on column" routines worked perfectly. After changing to the MySql tables, the me.orderby operations do not work. I have made the LabelFirstMarker visible so I could determine if it was actually going through the vba code, and it does alternate between 1 and 2 when First_Label is clicked. The order of the table does not change, however. I know the me.requery executes because the form blinks.

Also, in the onopen event of the form, I execute a Me.OrderBy = "Last, First" which doesn't work either. It seems like it just has to have something to do with MySql, but I cannot figure it out.
 
Minty

Minty

AWF VIP
Local time
Today, 21:48
Joined
Jul 26, 2013
Messages
10,366
Is the form connected directly to the linked table or a query ?
When you linked the table did you identify the primary key ?
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
I have tried linking both to the table itself and to a query but the same behavior occurs with either. There is a primary key, and it does appear in the form but is hidden. After reading your reply, I tried deleting it from the form but that makes no difference. FYI, the order of members is the ascending order of the table ID, but I don't know why that order overrides all attempts to reorder.
 
Minty

Minty

AWF VIP
Local time
Today, 21:48
Joined
Jul 26, 2013
Messages
10,366
Okay, that is weird.

I'm now clutching a straws, but what ODBC driver are you using and which version of Access ?
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
ODBC driver is 32 bit version, and Access is Office 365. I also have another db that runs with the same on the same computer, and column heading sorts work great (I lifted the previous programming idea from it). Anyway, I haven't thought that anything there might be the cause.
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
Another head-scratcher: another form will not let me add a record in the MySql version, but will in the local table version. The table is based upon a query with a constructed field (s: val(eventday) ) set to ascending. This field does not appear in the form. I guess I am more surprised that the non MySql version allows record additions than I am that the MySql version doesn't. How can I sort the form without using a query? Me.OrderBy = "Mo, val(EventDay)" in the onopen event doesn't work. If I set the form's data to be the underlying table, I can add a record but cannot sort it on the two fields.
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
Just to follow up, the column sorts are now working. I changed nothing--what I did was right-click on a field in the open form and chose sort A-Z. After that, all of my column sorts worked, the form opened in the proper order, etc. I have no idea why doing this fixed all that ailed it! If any of you have a clue, please let me know. Meanwhile, I still need to know how to sort the other table and the value of another. Thanks!
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 06:48
Joined
Jul 4, 2013
Messages
2,771
For me.orderby to work, the orderByOn property of the form must be true
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
I had set that at one time. Perhaps right-clicking and sorting a field turned it on if it were off? Dunno, but glad it is working now. Can you offer any help as to how to sort a form on val(field) and still be able to add records? This shouldn't be so hard!
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
I figured out a workaround--at the time of entering/changing the EventDay field I load a newly added table field with val(EentDay). After that I can use it in a sort.

Thanks to all of you for your help!
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 06:48
Joined
Jul 4, 2013
Messages
2,771
You don't need to have val() in the expression provided EventDay is a date/time field in the table. If it isn't, I would have it so.


Then
Code: 
me.orderby ="EventDate"
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
The reason for having it other than Date/Time is so if an event is a two day event for example, the user can enter 28/29, By sorting on val(EventDay) it will still put it in the proper order. I can't just sort on Event Day because day 2 of the month would end up after day 10.
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 06:48
Joined
Jul 4, 2013
Messages
2,771
In that case, I'd have two date fields for EventStart and EventEnd. With a week long event, say 7/14 July, it's very hard to select which events were running on 10 July.


Also
val("1 Feb 2019") => 1
val("2 Jan 2019") => 2
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
You are correct, of course. However, my needs aren't that complicated. This is just a list of golf events, the longest of which will be 3-day event. This little program is for my own use (and perhaps another next year), and as with every db I write, I learn something. I am in awe of those of you who know so much and who are willing to help people like me. Thank you.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 16:48
Joined
Jan 23, 2006
Messages
15,379
Just a thought for consideration.

What if you had EventStartDate (dateTime) and EventDuration (number) representing the length of the Event in days.

Does that offer anything new?
I haven't worked with MySQL for a long time. I'm wondering why you have moved BE to MySQL.
 
E

ellenr

Registered User.
Local time
Today, 16:48
Joined
Apr 15, 2011
Messages
397
I mainly set it up the way I did to simplify entry and a calendar printout. As to why move the BE to MySql--just because I could! I can use it on my two computers--if I go out of town I still have access to the current data. I also plan to give the db to other officers of the group. This db also contains a cdo email routine that allows individual email messages to go to all members or subsets of the group.
 
You must log in or register to reply here.

Similar threads

Z
Sort listbox
Replies
8
Views
203
MajP
MajP
W
Solved Copy Recordset
Replies
10
Views
506
Pat Hartman
P
S
Report sort in vba is breaking query update
Replies
1
Views
351
Pat Hartman
P
E
Problem with linking
Replies
4
Views
336
ellenr
E
M
Sorting a continuous form based on a field
Replies
18
Views
739
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom