Using VBA code on a column in a table in MS Access

[Lou_Reed]

I have a table with a column that contains numbers that go from 1 to 130 in no particular (random) order.

I do one this by hand now and that is sort that column in ascending order. That way the integrity of the spreadsheet once it is in the Access
db will be maintained. It is easy to sort a column in an Access table in ascending order.



My question is in MS Access VBA, how to I sort a column in ascending order? Can this be done? I could do it with keystrokes, but my sponsor wants it hard-coded so any one can do it, just press a button and it is done.

Finally after the column is sorted in ascending order, how do I delete this column?

Please understand that I want to delete only the first column and nothing more. I want to leave the rest of the table intact.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

A table DOES NOT have an inherent "Order". It is a bit bucket.

To "sort" the records in a table, use a query. You can pick the field(s) you want to "Order by".

To remove the "sort", remove the field from the query or in the query designer, set the sort row to not sorted.


If you have a form with a query as the record source, you can have "buttons" or some scheme to change the sort columns/order.

Using VBA, the commands could be as simple as this:

Private Sub Command3_Click()
    Me.OrderBy = "as1_18 Desc"
    Me.OrderByOn = True
End Sub

Or you can use VBA to build the sort order using controls on the form to select multiple fields and/of the sort order: ASC or DESC.

[CJ_London]

My question is in MS Access VBA, how to I sort a column in ascending order?

To be clear, do you mean sort just the column, leaving the data in the other fields unchanged, or the whole table based on that column? Or does the table just consist of one field?

[Lou_Reed]

Yes, I want to sort on one column. The leftmost column. It is used to re-order an Excel spreadsheet into the correct order. When a spreadsheet is imported into Access, Access randomly puts in the Excel rows in. They are jumbled.

So to order them I created a new column in MS Excel and numbered each row (with the exception of the first row, the header row) from 1 to 130. Now when the sheet is imported into Access
and the rows are mishmash-ed, all I need to do is sort on that row from low to high. Then the rows are in their proper order again.

However, once that is done this column is no longer needed so it can be deleted.

I could do that by hand, but the sponsor want it done pro -grammatically. So want they want they get.

Please understand that this db consists on only one table - nothing else. There are no forms - yet.

My question then is how to do it pro-grammatically? i want to make it as transparent as possible to the user of the program.

I am in new territory here because, I have never worked on only a db table. I may need to create a form to just work with the table.

Again, i want to sort on this column from low to high and then delete the column leaving only the table.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[CJ_London]

Sorry Lou, you have not answered my question but presume Steve's suggestion meets your needs

[Lou_Reed]

Ajax:

Please restate your question and I will try and answer it. I appreciate your response and you are owed an answer.

Please restate your question.

Respectfully,

Lou Reed

[CJ_London]

I asked

do you mean

1. sort just the column, leaving the data in the other fields unchanged,
or 2. the whole table based on that column?

however I'm assuming Steve's solution meets your needs - i.e. it is the whole table that needs sorting

[Lou_Reed]

The answer is 2. I want the rows to be resorted in to their correct order. By putting the sequentially numbered column in, this makes the sorting possible.

I then want to remove or delete the column once the sorting is complete and correct. It really is not a part of the spreadsheet or the newly created table. it was just a device to get
things properly sorted.

I hope this answers your question.

Respectfully,

Lou Reed

[CJ_London]

It does thanks - as previously stated, then Steve has provided the solution

Not sure why you want to delete the column, if importing a new spreadsheet to an existing table, then you'll need it anyway. And if you don't delete it, it can be ignored in queries that reference it

[Lou_Reed]

You lost me on Steve. Do you mean ssanfu?

Respectfully,

Lou Reed

[CJ_London]

yes - he signs off with Steve

[Lou_Reed]

I assume that the following code is relevant to my situation.

Private Sub Command4_Click()
Me.OrderBy = "as2_18 Asc"
Me.OrderByOn = True
End Sub

In my particular situation the heading would not be "as_18 Asc".


It would be whatever my heading was with Asc put in last.

Also, lease understand that this table will not even be there until I import the MS Excel spreadsheet into MS Access.

I can make up the form in advance and the VBA code in advance., but how to do I connect it up to a table that does not yet exist?

As I said it is only created when I perform the import task.

How do I do that?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[CJ_London]

I assume that the following code is relevant to my situation.

I guess so, why not try it and see?

[Lou_Reed]

I believe that you misunderstood my problem. I believe that this code will work.That is a secondary issue. To manipulate and sort a column on a table, I must create a form. The form must be connected to the table with the form's property control source having the table's name.

This all done in advance of running the program. But how can I give the table name as a control source property if I do not know the table's name because the table is created during run time. Its creation is dynamic not static.

I just am confused on how to do it.

I can create a list of tables on the form and the new table would surely be in there, but still the form's control source property must have the table's name. I would not know that until the spreadsheet is imported into Access and how could I have the form to be connected to the table without knowing the table's name?

I had an original way to do this and it was all by hand, but the sponsor wants everything automated if at all possible.

This may not be possible to automate.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[CJ_London]

Sorry Lou, I find your terminology confusing. The thread is about sorting a column in a form and seems to have moved on to something about forms without a stated control source. Without any idea how your app works I don't believe I can help. How to sort a form with vba has been explained, I only got involved because I read your initial post as sorting a column, not sorting a table on a column which had been answered in post #2. If you have another question I suggest you start a new thread

good luck with your project