UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subform Complex Sort Problem, Access 2016    
 
   
PaulD299
post Mar 4 2018, 07:25 PM
Post#1



Posts: 10
Joined: 2-September 13



I am having a problem sorting a subform from a parent form. In my parent form I have the following sub:

CODE
Private Sub SortForm()
    Me.subfrmTransactionPhotoVerify.Form.OrderBy = "[CaseID]"
    Me.subfrmTransactionPhotoVerify.Form.OrderByOn = True
End Sub


The problem is that the subform is not sorting the way I intend. The field "CaseID" is externally referenced to a "Cases" table. For each "CaseID" in the "Cases" table there is an associated "CaseNumber" string. I want the subform to sort by this "CaseNumber" string. Unfortunately, the above code sorts by the "CaseID" instead.

On screen, this column in the subform displays the "CaseNumber" string and if I choose the "sort A to Z" from the column heading drop down it sorts correctly. There has to be a way to sort by the string instead of the "CaseID" number. Maybe I should change the query that the subform is built on. But, if I add a sort option for the query it sorts by the "CaseID" too. I'm stumped.

Any ideas?

Thanks in advance!

Paul
Go to the top of the page
 
Doug Steele
post Mar 4 2018, 07:33 PM
Post#2


UtterAccess VIP
Posts: 21,783
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If you want to sort by CaseNumber, shouldn't it be

CODE
Private Sub SortForm()
    Me.subfrmTransactionPhotoVerify.Form.OrderBy = "[CaseNumber]"
    Me.subfrmTransactionPhotoVerify.Form.OrderByOn = True
End Sub

--------------------
Go to the top of the page
 
PaulD299
post Mar 4 2018, 08:06 PM
Post#3



Posts: 10
Joined: 2-September 13



Sorry. I made an error in transcribing my code. This is the code I have:

CODE
Private Sub SortForm()
    Me.subfrmTransactionPhotoVerify.Form.OrderBy = "[CaseNumber]"
    Me.subfrmTransactionPhotoVerify.Form.OrderByOn = True
End Sub


The table structure is as follows:

Table "Transactions"
Field "CaseNumber" (Referenced to "CaseID" in the "Cases" table)

Table "Cases"
Field "CaseID" - Primary Key
Field "CaseNumber" - String of the Case Number

I realize that the the field "CaseNumber" in the "Transactions" table should have been named "CaseID" instead but this is a relic of an older database where there was no "Cases" table and this field was a string. I've written too much code at this point to change the name of the field now...

Anyways, thanks again for the the help!

Paul
Go to the top of the page
 
Doug Steele
post Mar 5 2018, 08:51 AM
Post#4


UtterAccess VIP
Posts: 21,783
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Okay, so does the parent form have table Transactions as its recordsource and the form being used as the subform have table Cases as its recordsource, or vice versa?

Since CaseID is the primary key for table Cases, assuming you've linked the parent form to the subform via CaseNumber on the parent and CaseID on the child, I don't see how you can get more than one row appearing in the subform!

--------------------
Go to the top of the page
 
projecttoday
post Mar 5 2018, 10:06 AM
Post#5


UtterAccess VIP
Posts: 9,685
Joined: 10-February 04
From: South Charleston, WV


Wouldn't they all be for the same case (assuming you have linkage set)?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Doug Steele
post Mar 5 2018, 11:50 AM
Post#6


UtterAccess VIP
Posts: 21,783
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Not sure I follow what you're saying, Robert. (I blame it on this wicked cold I'm fighting...)


--------------------
Go to the top of the page
 
projecttoday
post Mar 5 2018, 12:38 PM
Post#7


UtterAccess VIP
Posts: 9,685
Joined: 10-February 04
From: South Charleston, WV


I suppose the question is what is the parent table? I would think Cases. But I don't know.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
PaulD299
post Mar 10 2018, 07:35 PM
Post#8



Posts: 10
Joined: 2-September 13



The subform has as recordsource a query, qryVerifyPhoto. The parent form does not have a recordsource.

But, it's weird. I was playing around and changed the recordsource of the subform to table Transactions. No change in the sorting. I changed it back to the query and then it started sorting correctly. I swear, that's the only change I made! I tried the same technique on some of the other forms with the same structure and about half of them sorted correctly and the other half did not. It makes absolutely no sense... Stupid Access! I'm not sure anyone can figure this out. I may have found some arcane Access bug that cannot be solved.

Anyways, if anyone has any further ideas I'm all ears. Otherwise, thanks you for the help.

Paul
Go to the top of the page
 
projecttoday
post Mar 10 2018, 08:25 PM
Post#9


UtterAccess VIP
Posts: 9,685
Joined: 10-February 04
From: South Charleston, WV


If you don't specify a sort, the records can be sorted in any random sequence.

What is the code for qryVerifyPhoto?

What is your current orderby property code?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
projecttoday
post Mar 10 2018, 08:29 PM
Post#10


UtterAccess VIP
Posts: 9,685
Joined: 10-February 04
From: South Charleston, WV


You should take the time to change Case Number to CaseID in Transactions.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
missinglinq
post Mar 11 2018, 09:47 AM
Post#11



Posts: 4,492
Joined: 11-November 02



How are you calling you SortForm() event?

I assume that subfrmTransactionPhotoVerify is the name of the Form the Subform is based on...but is it the actual name of the Subform Control? Depending on how you created the Subform, the Access Gnomes may have named the Subform Control something other than the name of the Form the Subform is based on, such as Child0, and it's the Control Name that you need to use in your code.

Linq ;0)>

--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
PaulD299
post Mar 11 2018, 07:21 PM
Post#12



Posts: 10
Joined: 2-September 13



@projecttoday:

My SQL for qryVerifyPhoto is as follows:

CODE
SELECT Transactions.TRID, Transactions.CaseDate, Transactions.CaseNumber, Transactions.Employee, Transactions.Location, Transactions.PhotosUpload, Transactions.PhotoVerified, Transactions.PhotoVerifiedEmployee, Transactions.PhotoVerifiedDate
FROM Transactions INNER JOIN Cases ON Transactions.CaseNumber = Cases.CaseID
WHERE (((Transactions.PhotoVerified)=False))
ORDER BY Cases.CaseNumber;

The sort order property is set to CaseNumber in the Cases table. I didn't remember doing that but it makes sense. That's how I wanted the query to sort. I don't understand why it works now but didn't before. But, it seems that might be the answer. Instead of using code to sort the query, I should change my other queries to match this one and sort by Cases.CaseNumber.

Also, I know I should make the correction from CaseNumber to CaseID in Transactions. I think I'm just being lazy... But I think you've inspired me to take the time and effort to do it!

@missinglinq:

When I created subfrmTransactionPhotoVerify I think I just selected qryVerifyPhoto then used the Datasheet wizard. I'm not sure what you mean by "Control." I looked on the Properties tab for the form and didn't see anything like that. I also looked in the VBA window to see if I saw anything that looked like "Child0" but didn't see it. I don't know how to answer your question. Anyways, I think I've figured it out by sorting in the query. That's probably the best way to do it.

Thanks to everyone who replied and helped me!
Go to the top of the page
 
projecttoday
post Mar 11 2018, 08:02 PM
Post#13


UtterAccess VIP
Posts: 9,685
Joined: 10-February 04
From: South Charleston, WV


You're welcome. Forms do have an OrderBy property which you can set. This is distinct from the ORDER BY clause in the query. I think Access displays records in the order received, meaning the ORDER BY clause, in the absence of an OrderBy property. I've never known it not to. I believe most developers don't use the OrderBy property. If you encounter that problem again it would be nice if you posted it.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th June 2018 - 10:57 AM