Solved Event Click no longer working on a header label on a form

[mcomp72]

I have a continuous form, and I have set a couple of the text boxes in the Header section to sort by the corresponding field in the Detail section. It was working fine, but now it isn't, and I don't know why.

The only thing I can think of is, I changed the field name at one point, to remove a "/" character from it. But I believe I have changed everything else I need to change in order to deal with that. Possibly not.

Here's what the form looks like normally.

Clicking the "Over/Under" label used to make the form sort (highest number first) by that field, but it is no longer working. Clicking the header does make the form refresh (I assume anyway, as I can see the OverUnder field go blank for a moment), but the form isn't sorting anymore based on it.

Here's a couple of screenshots from Design View of the form.

I have the Over/Under label selected, so you can see that an event happens On Click. (I'll paste that code below.)

Here I have the OverUnder field selected in the Detail section. Its Control Source is a function, because it is doing a calculation and error checking. Essentially, it is displaying the result of Points - PointsNeeded. The Function is working properly. When I change the value in the Points column, the value in the OverUnder field changes, just as it is supposed to.

Here is the code that On Click is tied to:

Private Sub OverUnder_Label_Click()

'This will sort the results by OverUnder.
Me.OrderBy = "OverUnder"

Me.Refresh

End Sub

The Record Source of the form is a query. Here is the SQL from it:

SELECT SignInSheets.SignInSheetID, SignInSheets.TournamentID, SignInSheets.MemberID, SignInSheets.Points, SignInSheets.OverUnder, SignInSheets.Present, SignInSheets.Winnings, SignInSheets.Notes, SignInSheets.PointsNeeded, SignInSheets.ScoreRecorded, SignInSheets.DuesRecorded, SignInSheets.CurrentOnDues, Members.FirstName, Members.LastName, Members.FullName, Tournaments.TournamentDate, Courses.CourseName
FROM Courses INNER JOIN (Tournaments INNER JOIN (Members INNER JOIN SignInSheets ON Members.MemberID = SignInSheets.MemberID) ON Tournaments.TournamentID = SignInSheets.TournamentID) ON Courses.CourseID = Tournaments.CourseID;

Here is the SQL from the same query from an older version of the database, back when clicking the Over/Under header was working:

SELECT SignInSheets.SignInSheetID, SignInSheets.TournamentID, SignInSheets.MemberID, SignInSheets.Points, SignInSheets.[Over/Under], SignInSheets.Present, SignInSheets.Winnings, SignInSheets.Notes, SignInSheets.PointsNeeded, SignInSheets.ScoreRecorded, SignInSheets.DuesRecorded, Members.FirstName, Members.LastName, Members.FullName, Tournaments.TournamentDate, Courses.CourseName
FROM Courses INNER JOIN (Tournaments INNER JOIN (Members INNER JOIN SignInSheets ON Members.MemberID = SignInSheets.MemberID) ON Tournaments.TournamentID = SignInSheets.TournamentID) ON Courses.CourseID = Tournaments.CourseID;

In the SignInSheets table, the name of the field has been changed from "Over/Under" to "OverUnder".

OverUnder is Short Text because on occasion, it will hold a letter instead of a number.

I am pretty new to Access (and databases in general), so I am having no luck figuring out what is wrong. Any help would be greatly appreciated.

 

[theDBguy]

Can't test it right now, but try adding OrderByOn. For example:

Me.OrderBy = "OverUnder"
Me.OrderByOn = True

Hope that helps...

 

[mcomp72]

Thank you for the suggestion, but unfortunately it had no effect.

 

[mcomp72]

Something I have realized since I wrote the original post is, I think I am wrong about the Record Source of the form. It appears to be its own query. I am not really sure how to tell, to be honest.

Here is the SQL of that query:

SELECT SignInSheets.Points, SignInSheets.OverUnder, SignInSheets.Present, SignInSheets.PointsNeeded, SignInSheets.Winnings, SignInSheets.Notes, SignInSheets.ScoreRecorded, SignInSheets.CurrentOnDues, Members.FirstName, Members.LastName, Tournaments.TournamentID, Tournaments.TournamentDate, Courses.CourseName
FROM (Courses INNER JOIN Tournaments ON Courses.[CourseID] = Tournaments.[CourseID]) INNER JOIN (Members INNER JOIN SignInSheets ON Members.[MemberID] = SignInSheets.[MemberID]) ON Tournaments.[TournamentID] = SignInSheets.[TournamentID];

Something else that I think I've realized is, the OverUnder field on the form may not be tied to anything. So I am not sure if the code to do the sort is actually trying to sort based on the results as they appear on the form or not. That's really what should be happening. It should sort by the results in the field on the form, not what's in the OverUnder field of the query. Because I don't think the result of the function (in the OverUnder field on the form) is showing up in OverUnder field of the query. Maybe that's what I need to change?

I am clearly in WAY over my head here. I probably shouldn't have attempted to create a database with my very limited knowledge. I thought I'd be able to figure it out, but not so sure anymore.

I know it is probably difficult to diagnose the issue without actually having the file, so here is a link to download it:

Dropbox

www.dropbox.com

 

[theDBguy]

I just had a play with your DB, not sure if I did it right or not.

 

[mcomp72]

It does seem to work!

I am at a total loss. I tried entering the code you mentioned, and it had no effect. But you put it in and it works. Did you do something else? Otherwise, I am completely baffled why the code works when you put it in but not when I did.

Also, in the earlier version of the db where the sorting was working for me, I didn't have that new line of code in the On Click subs. Can you explain why it might work without that line of code sometimes, and not others?

 

[theDBguy]

It does seem to work!

I am at a total loss. I tried entering the code you mentioned, and it had no effect. But you put it in and it works. Did you do something else? Otherwise, I am completely baffled why the code works when you put it in but not when I did.

Actually, yes, I did something else, which is what I meant when I said I wasn't sure if it was correct or not. Basically, I replaced OverUnder with a simple [PointsNeeded]-[Points] instead of using the function you're using now.

 

[mcomp72]

Actually, yes, I did something else, which is what I meant when I said I wasn't sure if it was correct or not. Basically, I replaced OverUnder with a simple [PointsNeeded]-[Points] instead of using the function you're using now.

Ah. Unforunately that will not always work, because sometimes there will be text in the PointsNeeded field. So I built the function to deal with those occasions. (The function would also deal with if Points was Null, though I'm not sure if it would be required.)

 

[mcomp72]

I just opened an earlier version of the db, when the sorting was working properly. Even in that one, the function was tied to OverUnder (back when the field was called "Over/Under"). So it seems the sorting should be able to work with that function in there.

 

[mcomp72]

I decided to try a few things, just to play around. I tried deleting the OverUnder field from the form entirely, because I was going to then try to recreate it.

I removed it, but decided not to recreate it yet. Instead, I closed the form (and saved it) and reopened it. I got this prompt:

Why would I get this prompt if I had removed the OverUnder field from the form altogether?

I clicked OK without typing anything in, and the form opened. When I clicked the "Over/Under" text label in the Header of the form, it sorted it! But I don't understand why. The field had been removed from the form. How could it possibly know how to sort based on OverUnder when that field was no longer there? I would have thought this line of code would have caused an error, but it didn't:

Me.OrderBy = "OverUnder"

I think there must be something very fundamental about databases that I don't understand. If someone would be so kind as to explain what the heck is going on, I would so, so appreciate it.

 

[theDBguy]

Why would I get this prompt if I had removed the OverUnder field from the form altogether?

But did you also remove the field from the record source? Even if the field is not on your form, your code will still see it if it's in the record source.

 

[theDBguy]

Unforunately that will not always work, because sometimes there will be text in the PointsNeeded field. So I built the function to deal with those occasions.

In that case, I would modify the function to avoid using the form's data and just use the table instead. Then, add the function to the query, instead of the form.

 

[mcomp72]

In that case, I would modify the function to avoid using the form's data and just use the table instead. Then, add the function to the query, instead of the form.

Sorry, but I have never done that before, so I don't know how to go about it. Are you talking about making the OverUnder field on the table be of type Calculated, and then somehow tell it to calculate based on the function I wrote? If so, I am not sure how to do that. I know how to set a field to be of Calculated type, but not how to tell it to use a function I wrote to do the calculation. When I choose Calculated, it brings up this window:

I do not appear to be able to choose the function I wrote.

 

[theDBguy]

Are you talking about making the OverUnder field on the table be of type Calculated, and then somehow tell it to calculate based on the function I wrote?

Not quite. Instead of in the table, I am talking about to make it a calculated column in the query - just like how I did in your sample db. However, instead of how I did it with a simple subtraction, you would use a new function, because calling your function from the query won't work, because it's using the form data as a reference. Instead, modify the function to use the table data as a reference to make it work within the query.

 

[mcomp72]

Ah... got it.

This definitely pointed me in the right direction. It took me a couple of attempts, but I now have the form working the way it should! Many thanks!!!

 

[theDBguy]

Ah... got it.

This definitely pointed me in the right direction. It took me a couple of attempts, but I now have the form working the way it should! Many thanks!!!

Congratulations! Glad to hear you got it sorted out. Good luck with your project.