Export datasheet results with user defined sort order

[Snowflake68]

I am having trouble exporting data in the correct sort order that is specified by the user.

I have some code that allows the user to specify the sort order of up to 6 columns that is displayed in a datasheet of a split form. Once they have sorted the data they need to be able to export the data in the same specified order.

Not all of the fields in the datasheet are required in the export so I have created a query on the table to specify which fields are needed and I needed to give some of the field names alias (as the field names in the table are not user friendly). I then use a macro to export the query (ExportWithFormatting) to an Excel file. No real issue here.

However I need to be able to tell the query to sort the data in the same sort order as the user defined on the datasheet view of the form as the two are separate objects.

Does anyone know how I can easily do this. I am pretty new to VBA and dont really understand it too much although I my system is using a lot of VBA code that I have copied and pasted from different forums and played around with it to get it to work for my purpose but that's as far as my skills allow.

I have tried so many different methods but nothing is working. The one that I thought would work is by adding some code to the OrderBy property of the query but it doesn't work and doesn't produce an error either.

This is the line I am putting in the Order By Property of the Query to tell it to use the same sort order as the form, but its not working.

[Forms]![MyFormName].[OrderBy]

Any ideas please?

 

[Trevor G]

Have you tried to append the datasheet into a temp table and if that shows the data in the correct order you can then use the temp table to export.

 

[arnelgp]

then what is the text content of [OrderBy] textbox?

 

[Snowflake68]

Have you tried to append the datasheet into a temp table and if that shows the data in the correct order you can then use the temp table to export.

Hi Trevor

How do I append the data from the datasheet into a table? I have tried to create a query but there doesn't seem to be an option to query a form.

 

[Snowflake68]

then what is the text content of [OrderBy] textbox?

Hi There, thanks for your reply.

I am unsure what you mean.

The Order By property on the form updates each time the user changes the sort order and I want the query to reference the values in the forms order by property.

I was thinking that each time the user updates the sort order that I can get the form to resave and then when the user chooses to create the export it would run a query to export the data but I just need it to be sorted using the same Order By values that are in the forms property. This changes each time so needs to be dynamic.

 

[gemma-the-husky]

I would have a column in the query called sortorder, and sort on this

populate this with a function to return the records sorted in the order you want.

It makes it completely dynamic.


when I do this, I use drop down combo boxes to set the sort order. Not sure if you can use it interactively, although you may be able to use the forms "order" property.

 

[Snowflake68]

I would have a column in the query called sortorder, and sort on this

populate this with a function to return the records sorted in the order you want.

It makes it completely dynamic.


when I do this, I use drop down combo boxes to set the sort order. Not sure if you can use it interactively, although you may be able to use the forms "order" property.

Thanks Gemma, however I am unsure how that would work because the user sorts on up to 6 different columns. There is no issue with sorting the data in the form, the issue is exporting it out in the same order. I dont want to export the datasheet of the form as I only want specific fields to be exported, so Im using a new query on the same table as the form uses but just cant see how I can tell the query how to sort it. Not sure Im ever going to achieve this and Im really under pressure from the customer now

 

[gemma-the-husky]

how exactly do you achieve the re-order?

can you give us some examples of any code, and an example of what the sort order looks like.

 

[spikepl]

Your post is unclear how you make this query. Do you write SQL or do you use a QueryDef object to modify it or how exactly?

adding some code to the OrderBy property of the query

How do you do this?

Provide something solid to look at, i.e. examples, screenshots, not just stories.

 

[Snowflake68]

Your post is unclear how you make this query. Do you write SQL or do you use a QueryDef object to modify it or how exactly?



How do you do this?

Provide something solid to look at, i.e. examples, screenshots, not just stories.

Thanks Spikepl, I really appreciate any help I can get and apologies if my explanation was a little vague. Ill try and explain a little better with screenshots as you have suggested.

Basically I have used a select query to select the columns that I want from the table and I am export that Query via a macro to an Excel spreadsheet. Which works ok but I need the export to be sorted in the same sort order as the data on the form.

I have tried to put something in the query's Order By Property which I access from the query's design view by right clicking on the blank area where the tables are displayed and I bring up the properties of the query, and there is an Order By property. See screenshot below.



I just thought that I could put some code in order by of the query that tells it to reference the Order By property of the form that contains the datasheet (this is the part that changes as and when the user sorts the datasheet). (Screenshot below) but the code I have put in there doesnt work but it also doesnt through up any errors either.

Below is the property sheet of the form showing the Order By at this moment in time but this changes for each customer.



How to I get the Order By property of the Query to dynamically update with the same values as the Order by property of the form?

I have also tried a different approach by simply exporting the datasheet of the form which works for the sort order however the end result is very messy and I don't want to export all of the columns that are on the form plus I need to use the aliases of the column names and not the field names of the database as well as have the columns in a particular order which it doesn't do.

Are you able to suggest another way please? I'm finding it really difficult to export data to Excel in the format I need. When I use the macros to export a query or form the end result is not pretty.

Thanks again for any assistance with this.

 

[spikepl]

You want to export a stored query. So you need to change it dynamically. The way to do that is to use a querydef object (google it) and change the SQL of the query. There you can swap parts of the SQL concerning Order By with your Filter text.

Your current method does not work: the field Order By is only used for displaying the query in datasheet view only when you look at it and that's all.

 

[Snowflake68]

You want to export a stored query. So you need to change it dynamically. The way to do that is to use a querydef object (google it) and change the SQL of the query. There you can swap parts of the SQL concerning Order By with your Filter text.

Your current method does not work: the field Order By is only used for displaying the query in datasheet view only when you look at it and that's all.

Thanks, just googled it and although it maybe the way to go I think this is way too much for someone with limited skills such as me

I dont know VBA or SQL and I wouldnt know where to apply the code let alone know how to go about writing it, but thanks anyway. Im just going to have to find another way.

 

[Snowflake68]

Have you tried to append the datasheet into a temp table and if that shows the data in the correct order you can then use the temp table to export.

Hi Trevor

Thanks for your reply. This seems to be an excellent idea and I have been working on trying to do this. I have managed to find some code that appends data from a form into a table but it only appends the first row of the datasheet.

Are you able to advise me on how to append all of the data that is on the datasheet of a Split form?

This is the code that I am using and is working for me but I need all rows appended.

Private Sub cmdAdd_Click()
Dim strSQL As String
strSQL = "INSERT INTO Temp (Type, BIN, Vint) VALUES ('" & Me!Type & "', '" & Me!BIN & "', '" & Me!Vintage & "');"
DoCmd.RunSQL strSQL
'Debug.Print strSQL
'CurrentDb.Execute strSQL
End Sub

Hope you can help.

 

[Snowflake68]

Hi Trevor

Thanks for your reply. This seems to be an excellent idea and I have been working on trying to do this. I have managed to find some code that appends data from a form into a table but it only appends the first row of the datasheet.

Are you able to advise me on how to append all of the data that is on the datasheet of a Split form?

This is the code that I am using and is working for me but I need all rows appended.

Private Sub cmdAdd_Click()
Dim strSQL As String
strSQL = "INSERT INTO Temp (Type, BIN, Vint) VALUES ('" & Me!Type & "', '" & Me!BIN & "', '" & Me!Vintage & "');"
DoCmd.RunSQL strSQL
'Debug.Print strSQL
'CurrentDb.Execute strSQL
End Sub

Hope you can help.

PROGRESS UPDATE:

I have now managed to get it to move through each row on the form. It append all of the records to a Temp table with the exception of the very last record. Is there a way of ensuring it also appends the last one.

Below is my updated code. (I do intend to add more fields to the append but just want to get it working first).

Private Sub cmdAdd_Click()
DoCmd.SetWarnings False
Dim strSQL As String
DoCmd.GoToRecord , , acFirst
While Me.CurrentRecord < Me.Recordset.RecordCount
strSQL = "INSERT INTO Temp (Type, Item_No_, Vintage, [Customer Price Per Bottle ex Vat]) VALUES ('" & Me!Type & "', '" & Me!BIN & "', '" & Me!Vintage & "', '" & Me![Net Price] & "');"
DoCmd.RunSQL strSQL
DoCmd.GoToRecord Record:=acNext
Wend
End Sub

I hope someone can help with the last piece of my puzzle as I feel I am getting so close to resolving this now.

 

[Trevor G]

Can you upload a sample of your database it will be easier to help you with a solution that will work for you. Strip away any sensitive data.

 

[Snowflake68]

Can you upload a sample of your database it will be easier to help you with a solution that will work for you. Strip away any sensitive data.

Hi Trevor

I really appreciate your offer to help me further, however I am unable to upload the database as it is so complex and to remove the sensitive data would take a long time. I'm not sure my customer would be very happy if I did that either as they own the copyright to it.

I am still playing around with ideas and although I now have it working its just not very efficient. Basically I have code that moves through each of the records and appends them one by one to a table. If the datasheet has hundreds of records then this takes far too long to run and also the end user can see each record being selected as it scrolls down through them on the form which doesn't look professional at all.

This is the code that does everything from clicking a button to sort the data.

Private Sub cmdSetSort_Click()
'Set user defined sort order
Dim strSQL As String, intCounter As Integer
'Build strSQL String
For intCounter = 1 To 6
If Me("cboSort" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
If Me("Chk" & intCounter) = True Then
strSQL = strSQL & " DESC"
End If
strSQL = strSQL & ", "
End If
Next

If strSQL <> "" Then
'Strip Last Comma & Space
strSQL = Left(strSQL, (Len(strSQL) - 2))
'Set the OrderBy property
Forms![frmPreviewSelectionSubForm].OrderBy = strSQL
Forms![frmPreviewSelectionSubForm].OrderByOn = True
Else
Forms![frmPreviewSelectionSubForm].OrderByOn = False

End If

'Macro to create the structure for the temporary tables
DoCmd.RunMacro "SortOrder_CreateTempTables"

'Append data to temporary table
DoCmd.SetWarnings False
Dim strSQL2 As String
DoCmd.GoToRecord , , acFirst
While [Forms]![frmPreviewSelectionSubForm].CurrentRecord < [Forms]![frmPreviewSelectionSubForm].Recordset.RecordCount
strSQL2 = "INSERT INTO TempSortOrder (BIN, ListReference) VALUES ('" & [Forms]![frmPreviewSelectionSubForm]!BIN & "', '" & [Forms]![frmPreviewSelectionSubForm]!ListReference & "');"
DoCmd.RunSQL strSQL2
DoCmd.GoToRecord Record:=acNext
Wend
DoCmd.GoToRecord Record:=acLast
strSQL2 = "INSERT INTO TempSortOrder (BIN, ListReference) VALUES ('" & [Forms]![frmPreviewSelectionSubForm]!BIN & "', '" & [Forms]![frmPreviewSelectionSubForm]!ListReference & "');"
DoCmd.RunSQL strSQL2
DoCmd.GoToRecord , , acFirst

'Rebuild ItemPreview table (this is the table that the form uses)The macro runs a query that deletes the data from the original table and then appends the sorted data back into the original table.
DoCmd.RunMacro "SortOrder_RebuildItemPreview"
DoCmd.SetOrderBy "ReportOrder"
[Forms]![frmPreviewSelectionSubForm].Requery

End Sub

What I would really prefer to do is find a way that simply selects all of the records and then appends them to the temp table. I just cant figure out how to do it right now.

 

[Trevor G]

What if you use the following at the beginning of the code;

Application.Echo False

This is the same as using Screen Updating in Excel.

 

[Snowflake68]

What if you use the following at the beginning of the code;



This is the same as using Screen Updating in Excel.

Thanks Trevor. Ive just tried that and although it appears to work it freezers up the application and the only way out is to close it and open it again.

Any thoughts?

 

[smig]

I didn't read the entire discussion here

If you use dropDown boxes on your form to sort it you can read these dropDown in your query.
Use your query builder to read this data (Right click the criteria, Build...)

 

[Snowflake68]

Thanks Trevor. Ive just tried that and although it appears to work it freezers up the application and the only way out is to close it and open it again.

Any thoughts?

Hi Trevor

Just thought Id let you know that I managed to get everything working and without your suggestion of appending the records to a new temporary table I really don't think I would have succeeded, so a big THANK YOU to you.

This was a very challenging objective for me given I have very limited VBA skills but here is the complete code below in case anyone else wishes to to use.

Private Sub CmdSetSort_Click()

Application.SetOption "Show Status Bar", False
Application.Echo False

Dim strSQL As String, intCounter As Integer
'Build strSQL String
For intCounter = 1 To 6
If Me("cboSort" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
If Me("Chk" & intCounter) = True Then
strSQL = strSQL & " DESC"
End If
strSQL = strSQL & ", "
End If
Next

If strSQL <> "" Then
'Strip Last Comma & Space
strSQL = Left(strSQL, (Len(strSQL) - 2))
'Set the OrderBy property
Forms![frmPreviewSelectionSubForm].OrderBy = strSQL
Forms![frmPreviewSelectionSubForm].OrderByOn = True
Else
Forms![frmPreviewSelectionSubForm].OrderByOn = False

End If

DoCmd.RunMacro "SortOrder_CreateTempTables"

DoCmd.SetWarnings False
DoCmd.GoToRecord , , acFirst
Dim strSQL2 As String
While [Forms]![frmPreviewSelectionSubForm].CurrentRecord < [Forms]![frmPreviewSelectionSubForm].Recordset.RecordCount
strSQL2 = "INSERT INTO TempSortOrder (BIN, ListReference) VALUES ('" & [Forms]![frmPreviewSelectionSubForm]!BIN & "', '" & [Forms]![frmPreviewSelectionSubForm]!ListReference & "');"
DoCmd.RunSQL strSQL2
DoCmd.GoToRecord Record:=acNext
Wend
DoCmd.GoToRecord Record:=acLast
strSQL2 = "INSERT INTO TempSortOrder (BIN, ListReference) VALUES ('" & [Forms]![frmPreviewSelectionSubForm]!BIN & "', '" & [Forms]![frmPreviewSelectionSubForm]!ListReference & "');"
DoCmd.RunSQL strSQL2
DoCmd.GoToRecord , , acFirst

DoCmd.RunMacro "SortOrder_RebuildItemPreview"
DoCmd.SetOrderBy "ReportOrder"

[Forms]![frmPreviewSelectionSubForm].Requery

Application.Echo True

Application.SetOption "Show Status Bar", True

End Sub


The first part of the code is the Sorting functionality that I found here;
http://www.databasedev.co.uk/sorting_reports.html

followed by the code that appends each record into a temporary table with a report order ID number and then appends it back into a copy of the original table. I only append two fields from the original table to cut down on the code and then use a query to append the rest of the fields that were in the table (there are around 30 fields).

I had several issues to overcome, which are outlined below so if anyone needs more details then please ask me and Ill do my very best to help because I have received so much help from the guys on this site so I would like to give something back.

• Couldnt get the last record to append into the table
• Wanted to hide when each record was being selected
• Display of records on the form wasnt updating
• kept getting an error saying cannot go to specified record

An additional note surrounding the error 'cannot go to specified record'. It took me 3 days of playing around with the code only to find out that it wasn't the actual code that was an issue after all. I had a feeling that it was down to the actual form because I actually have two main forms that I need this to work on and it was working perfectly on the first one but not on the second one which I found odd so I knew it had to be something with the form itself. It was in fact that I have another subform within the header of the main form that had a tab stop and a tab order of 0 (the first tab stop) so each time the main form loaded its focus was on the subform. I simply changed the property of the offending sub form to Tab Stop 'No' and for safety set the tab order to the highest value that it would allow me.

I am now able to move back and forth between the two main forms sorting the data in either one and the sort order is kept and carried through to the next main form and more importantly I can also export the data in the same sort order that the user specified due to the fact that the data is sorted within the underlying table.