How to Order Records in Form in Access Project

[bullfrog83]

I'm working in an Access 2007 Project (.adp) that is linked to a SQL Server database. I did not have this problem before until I transitioned over. I'll take one form as an example of this problem that is rather persistent throughout my app.

I have a continuous form bound to a table (DegReq). The form's detail contains a bound combobox (cboProgram) whose rowsource is simply:

SELECT CodeProgram.CodeProgramId, CodeProgram.Description
FROM CodeProgram
ORDER BY Description;
 

CodeProgramId is the bound column of cboProgram (obviously). Now I want to order the records on the form alphabetically by the program's description, not the id.

In the past, I simply changed the form's recordsource to a query and joined the table CodeProgram to DegReq and ordered by CodeProgram.Description. My first idea was to create a view linking the two tables, making the view my recordsource and using the Order By property. However, from running into errors and further research I discovered that if a view is based on multiple tables, I can't do any updates. So, how would I go about doing this? I have several forms where I want to order how record display on a form a certain way but it requires joining other tables (usually my lookup tables like the example above).

[Jim Doherty]

@bullfrog83
You do not need to LEFT or RIGHT join join on multiple tables to simply view data from an associated table typically a lookup or something like that. You should look at the SQL Server concept of 'User Defined functions' in an Access ADP project file. This is where you embed them in your view as you would typically any built in or custom function in a conventional Access query.

Once you understand this concept you will find it very easy and a rather welcome 'difference' because the usual non editable view (as is apparent by your understanding and which IS the case) becomes completely editable and not limited by the 'Unique table' setting which you would get if you were to attacking it from the standpoint of LEFT and RIGHT joins as most people are familair with just to return a dataset.

I can elaborate on this if you wish but 'User Defined' functionality or UDF,s as they are commonly known in SQL Server parlance is well documented in Books Online and the web generally.

It may well be that you are just not familiar how to hang it together. IF this is the case and you still struggle with it get back to me and I will endeavour to put something together

[bullfrog83]

@Jim Doherty
Let's say that I need/want to view data from a couple of associated tables (lookup's primarily so I can see the values). Would I have to create a UDF for each one?

[Jim Doherty]

@bullfrog83
In Short.....Yes.....Pass in a simple parameter to the UDF and return your value to the dataset as a virtual column in pretty much the same way as an embedded custom function would be implemented if you were using an query in an MDB file.

You can of course be clever if you wish and pass an additional parameter into the UDF the value of which could decide which SQL statement gets processed within the UDF and which return value gets returned and so on.

Generally though, I use an individual UDF to do a particular thing and name it accordingly so that it stacks in the list neatly together with others that do the same type of thing ie...........dbo.UDF_Lookup_tblMyTable.... that type of thing

Your SQL for your view or stored procedure on the server might look something like the following when done

SELECT
RowID,
PersonID,
dbo.UDF_GetPersonsConcatenatedName(tblInvoice.Pers onID) AS Person
InvoiceNo,
InvoiceDate,
FROM
dbo.tblInvoice
WHERE tblInvoice.InvoiceID='INV123'

Your UDF (imaginary in this instance) is embedded in the view SQL as shown above and the UDF (not included in this post) would be doing the concatenation and returning a string (combined firstname,surname, date of birth etc each feature 'coalesced' to the view

[bullfrog83]

@Jim Doherty
Ok, here's a problem that I did not expect to run into but is of the same topic. I have a pop-up form whose record source is a table and in that table is a field called SortOrder which is a textbox on my form. I want to order the records by the SortOrder on the form. I set Order By = SortOrder and Order By On Load = Yes. However, it won't order the records by SortOrder. If I open the form by itself then it does order the records by SortOrder. But if I open it by clicking on a cmd button so that it opens to filter on a particular set of records, then it doesn't order by SortOrder. Can you not order records if the recordsource is a table? Because I had no problem ordering records for another form whose recordsource is a view.

[Jim Doherty]

@bullfrog83
All of your forms should be based on views or embedded SQL statements as the recordsource or stored procedures for precisely the reason you state ( or one reason among many reasons actually) Tables are just buckets to hold the data basically you select and order your data via SQL raised as views or procedures against that data......look at it that way

[bullfrog83]

@Jim Doherty
That makes sense. Thanks!

[bullfrog83]

@Jim Doherty
I changed the recordsource to a view which is simply SELECT * FROM table and I have OrderBy = SortOrder but it's still not ordering by SortOrder. I found that I have to add Me.OrderByOnLoad = True and Me.OrderBy = "SortOrder" in the form's OnCurrent event in addition to setting these properties in the form's Property Sheet. It can be one or the other. Why is this so quirky?

[Jim Doherty]

@bullfrog83
As to the quirkiness of it ask Microsoft. Generally speaking the DoCmd line for opening a form would benefit from an optional 'OrderBy argument in addition to the Where clause argument. That is more logical in my view. They included an argumant for the 'Where' clause of an SQL statement when opening a form so why not simply extend that to include an argument for an 'OrderBy' clause but 'hey ho' its not my product soooooooo.

Personally I throw an SQL statement into a global string variable and make any form I intend to open read that when it loads, setting its recordsource on the fly, that way the flavour of the dataset presented is set by the SQL when the form loads without touching any of the individual properties of the form..... but that is just one individuals technique.

Mostly I dont touch the OrderbyOn and OrderBy properties given Access has this horrible habit of saving form properties such as the filter when the form closes unless you code against it. We all work differently favouring one way or another.

[bullfrog83]

@Jim Doherty
Well, it turns out that if I have Me.OrderByOnLoad = True and Me.OrderBy = "SortOrder" in the form's OnCurrent event, it orders the records but then I'm only able to open the first record and not the others (this form has command buttons for each record that opens another form that contains more detail for the record opened). I'm beginning to wonder if Access really was built to work well with SQL Server! Anyway, I've decided to use an embedded query as my recordsource instead of a view so I can order the records and still be able to open other records on my form other than the first. My recordsource is: "SELECT * FROM DegReqDisc ORDER BY SortOrder". This is alright in your opinion?

[Jim Doherty]

@bullfrog83
Perfectly sensible