Queries in VBA

[jayclifford]

OK, here's the deal. I have a form based on a query based on a table. In the table I have 2 fields "LAST_NAME" and "FIRST_NAME". In the query I have both fields with a field that concatenates (may be misspelled) the 2 fields FULL_NAME: [LAST_NAME] & ", " & [FIRST_NAME]. In the form I use this field so that I can see last name 1st, first name last. I have the query set to sort (ascending) the "LAST_NAME" field so that the form shows it by alpha last name. Now what I want to do is manipulate the query in VBA so that I can change the sorting to "FIRST_NAME". I have been working about 12 hours and have been all over the internet. As MS Access is extremely versatile in programing, surly there is a way to do this? Help!:banghead:

 

[boblarson]

There is a way to do it. you don't need to manipulate the query at all. Just use the form's ORDER BY property:

Me.OrderBy = "[First_Name]"
Me.OrderByOn = True

And to go back to the original query sort

Me.OrderBy = ""
Me.OrderByOn = False

 

[boblarson]

Oh, and additionally - you can use multiple fields to sort on in the order of the sort:

Me.OrderBy = "[Last_Name], [First_Name]"

or if you want to use descending on one (or both)

Me.OrderBy = "[Last_Name] DESC, [First_Name]"

Me.OrderBy = "[Last_Name] DESC, [First_Name] DESC"

 

[jayclifford]

Than you I'll give it a try and let you know!

 

[jayclifford]

OK...I tried the fix. the "Me." part of it generated a "Invalid use of Me keyword".
What now?

 

[boblarson]

OK...I tried the fix. the "Me." part of it generated a "Invalid use of Me keyword".
What now?

Where did you put this code? If it is on the form, the ME keyword should be fine. If it is in a Standard Module then you will need to use the entire form reference.

 

[Pat Hartman]

You actually don't need code at all. Just click into a control and from the right-click menu select the sort ascending or descending icon. Unless, I want to provide the ability to sort by multiple fields, I never write code to do this. I just teach the users to use the things God (oops, I mean MS) gave us.

 

[jayclifford]

Thank you all for you help. My problem is that other people who don't know anything about Access will be using this Database. I want to make all the actions controlled by control buttons. I have tried replacing the "Me." with the form name, but I am still missing something as I get an error message. I will try and get the code out to you all ASAP. Again thank you all.

 

[jayclifford]

Thank you, Thank you. I retried the form name option and it worked!!

Thank you all.

 

[Pat Hartman]

The right-click to sort is not Access-Only behavior. It is also used in Excel so the users may already be familiar with it.

 

[jayclifford]

Got it, thank you