Sorting in 2003 and 2007

[chelm]

I’m having a bit of trouble with sorting records in descending order. In the “Row Source” of the properties of the form field I have:

SELECT tblDisserationToCitationJoin.JoinID, tblDisserationToCitationJoin.CitationID FROM tblDisserationToCitationJoin ORDER BY [JoinID] DESC;

This works perfectly in access 2007, it sorts in the form and when it is linked as a subform. However when I open it in 2003 it doesn’t sort when the form is linked as a subform, but does when it is not a subform. I’m not sure where I’ve gone wrong, and a user can’t sort the list manually because the field that it is sorted on is hidden.

Any advice is greatly appreciated.

If anyone is interested in seeing what I’m talking about you can download the .mdb from here:
http://c-helm.com/access/
The form is: frmCitationSubfrm
The field is: JoinID
The above form is used as a subform in: frmAddCitationsToDissertations

 

[boblarson]

The problem is that you have lookups defined at table level. Get rid of them (see here for more about them:http://www.mvps.org/access/lookupfields.htm) . Then, after that go into the subform and into the query and add the descending back to JOIN ID. You may also need to remove a persistent filter, from the form's filter property, and then save the form.

 

[RuralGuy]

Why not put the sort order directly in the RecordSource SQL rather than in the OrderBy property of the form?

 

[chelm]

Why not put the sort order directly in the RecordSource SQL rather than in the OrderBy property of the form?

You'll probably laugh at me, I'm new to access, VBA and SQL, but if you can point me in the right direction I'll gladly put in the time and try it.

 

[RuralGuy]

In the frmCitationSubFrm in design mode go to the properties sheet, data tab and click the "..." button on the RecordSource property row. Then in the query builder that comes up click in the Sort row of the JoinID column and select Descending. This will put an OrderBy clause right in the RecordSource. Close the query builder and answer YES to "Do you want to save the changes?". That should also remove the value that was in the OrderBy property of the form. Now try it.

 

[chelm]

SWEET! That totally worked. I can't thank you enough, I really didn't want to have to redesign the whole thing because of lookups.

You're a rock star in my book.

 

[boblarson]

SWEET! That totally worked. I can't thank you enough, I really didn't want to have to redesign the whole thing because of lookups.

You're still not "out of the woods" as far as the lookups are concerned. You will find that they will come back to bite you at some point. I would get rid of them ASAP, regardless. Use lookups at FORM level, not table level. Your users should not be working in tables directly anyway, so it shouldn't be a concern for them as they will be working in your forms, which can have lookups.

 

[chelm]

They seem like the most effective way to eliminate a LOT of redundancy. In addition giving the data numeric values makes it easy to move over to SPSS for statistical analysis. I'm not really sure how I could eliminate them and still easily export the data to SPSS. I'm open to suggestions though.

 

[boblarson]

Use a query to pull the data together for export. But, if you want - don't get rid of them but I'll tell you that you will find places where you have problems with them. Did you read the article?

 

[RuralGuy]

SWEET! That totally worked. I can't thank you enough, I really didn't want to have to redesign the whole thing because of lookups.

You're a rock star in my book.

I agree with Bob on the Lookup fields in your tables. They *will* cause you grief down the road. All they do is confuse the programmer so use ComboBoxes on forms instead.

 

[chelm]

Use a query to pull the data together for export. But, if you want - don't get rid of them but I'll tell you that you will find places where you have problems with them. Did you read the article?

Yes I did read the article. The main reason I'm using them is this is nothing more than a one step in a research process, the end goal is to export this to SPSS for statistical analysis. SPSS has to have numbers to crunch so the lookups using numbers seem like a perfect solution. I'll look into it more in depth before moving forward.

If I were to remove the lookups then the data will be the text values won't it? If that is the case I have the problem of assigning each piece of data (3 fields worth of some 25,000 records) all unique number values, while watching for redundancy. I would have no idea how to even start doing something like that. As I said I'll do some research and see what I can figure out.

Thanks for your advice and help. I take both very seriously, as you are both obviously experts in this area.

 

[boblarson]

If I were to remove the lookups then the data will be the text values won't it?

Actually, no it won't be the text values it stores it will be the ID numbers (just like it is doing now). The problem comes when you go to export data, or use it in a report, etc. that when you refer to that field it will many times use the LOOKUP value and not the ID, which is the value you need usually.

 

[chelm]

Oh, I think I understand what you are saying. Please correct me if I'm wrong. But are you saying to delete the lookup function, as in delete the relationship it creates when you use it and instead query in the unbound boxes for the values?

I think I'm actually using queries in all of them anyway, so I should just be able to delete the relationships and it will get rid of the lookups won't it?

 

[RuralGuy]

Oh, I think I understand what you are saying. Please correct me if I'm wrong. But are you saying to delete the lookup function, as in delete the relationship it creates when you use it and instead query in the unbound boxes for the values?

I think I'm actually using queries in all of them anyway, so I should just be able to delete the relationships and it will get rid of the lookups won't it?

NO! Wait for Bob to answer here.

 

[chelm]

Don't worry I always make a copy before doing anything like this.

I tested the theory to see what would happen. I deleted the relationships caused by the lookup function, then changed the fields to text, and back to number in the tables. Now all the tables are populated by the actual numbers and not the labels they relate to.

The forms still work the exact same way as they did before. I re-established the relationships, less the lookup.

I won't actually use this until someone tells me to though.

 

[boblarson]

All you really needed to do is to go into the tables, click on the field in design view, and then go down to the tab which says LOOKUP and then change it from ComboBox to Text Box and then click SAVE. That's it, no changing of anything else is necessary.

 

[RuralGuy]

I believe all you needed to do was go to the Lookup tab of the field and change it to a TextBox. The tables still have the same relationship. The controls on the form are still ComboBoxes with the same RowSource as was in the Lookup ComboBox.

 

[chelm]

Doh! I guess that would have been a lot easier wouldn't it. Thanks for the tip, this actually will work better for export to spss.