Report not in Alphabetical order

[vb9063]

Hi Guys,
I have a form that has supplier names on it and I have now created a supplier ID as a primary key. Before I changed this the records were in alphabetical order. But when I created the ID they were sorted based on the supplier ID. I managed to change the form record source to a query that has the supplier name as a query field with the sort criteria switched to descending. This works on the form however I also have a report which is accessed from a button on the form that creates a list of all the suppliers and their details. This too is not now sorting in alphabetical order. I have tried changing the sources the same way as the form but this is not working. I have used the following code:

Private Sub cmdPrintPreview_Click()
    Dim strReportName As String
    Dim strCriteria As String
    'Me.Refresh
        strReportName = "rptSupplierDetails"
        
        strCriteria = "[Sup_Name]"
               
        
        DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
End Sub

Is there something that I am missing??? THanks v much!

 

[boblarson]

For reports you have to set the sort in the Sorting and Grouping.

You can also set the Order By property of the report via code.

 

[vb9063]

Super thanks Bob, that is the group & sort in the design part of the ribbon when in design view.
Cheers VB

 

[PNGBill]

May not apply to your version but check out.. in design view, right click on the top left hand corner of the report and you should see a property box appear which includes Sorting and Grouping.

 

[vb9063]

Oh yeah there is thanks PNGBill..right clicking on the little square does indeed show up the property box with group & sort.
THanks!

 

[vb9063]

Hi guys,
I have just noticed that when I perform a search based on a few criteria in comboboxes the output from the filter (sends records to textbox in the details section of my search form) are also not in alphabetical order. I have tried to find a place where the text box can be set to sort in alphabetical order like the reports and other areas of the database but the controlsource is set to "" via VBA code and so there is no query builder. Is there a way to set the controlsource of the textbox to sort? I have used these two expressions in my search criteria code;

Me.ser_sup.ControlSource = "ser_sup"
Me.ser_sup.ControlSource = ""

Thanks again!
VB

 

[boblarson]

You don't sort text boxes. You sort the underlying Record Source of the FORM.

 

[vb9063]

You don't sort text boxes. You sort the underlying Record Source of the FORM.

Thanks Bob I will give that a go..although i think i did try it earlier but it didnt work for some reason. I have the query with 2 columns, the first has suppliers* (all items in supplier table) and sup_name. I tried sorting the sup_name but it didnt work. Is it worth separating it all out so that there is just one sup_name and not the whole table *. Does this make sense, am I confusing it by having Suppliers* and the sup_name in there too. ?? Thank u!!!

 

[boblarson]

First of all, your form, if you want sorting, needs to be based off a query not just a table. Second, you have to watch what tables you include as it can turn your query into a non updateable query which might be fine in some circumstances but not in others.

Third, if you are storing the supplier ID in a field but want to DISPLAY the name instead of ID, you can use a combo box where the ID is the first field in the row source and the Name is the second (Row source would be a query from the suppliers table) and then the bound column would be 1 and bound to the field in your form and then the column count would be 2 and the column WIDTHs would be something like 0";2" to show the second column (the name) and not column 1 (the ID).

 

[vb9063]

First of all, your form, if you want sorting, needs to be based off a query not just a table. Second, you have to watch what tables you include as it can turn your query into a non updateable query which might be fine in some circumstances but not in others.

I think when I said table i meant within the query builder where the tables are shown and in the bottom where you can choose what fields to query...if that makes sense i need to learn Access speak! So I think perhaps I need to fix the query to only show the sup_name and then sort on that.....?

Third, if you are storing the supplier ID in a field but want to DISPLAY the name instead of ID, you can use a combo box where the ID is the first field in the row source and the Name is the second (Row source would be a query from the suppliers table) and then the bound column would be 1 and bound to the field in your form and then the column count would be 2 and the column WIDTHs would be something like 0";2" to show the second column (the name) and not column 1 (the ID).

Cool thanks I dont really need to show the supplier ID. I am only doing a search based on 3 criteria and it just outputs the supplier names that match the query. I will have to fiddle with it when I get back onto my PC...on the mac at home now! Thanks for all your help Bob!