ORDER BY is not working (1 Viewer)

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
I have a form "frmWeightReduction". From that form I am opening another form "frmDietExercise". I have written the following SQL statement. Upon opening the "frmDietExercise" the records are in random order and not as in the SQL statement. I am a rookie. Any help would be appreciated. Here is the code:

Dim cd As DAO.Database
Dim rs As DAO.Recordset
Dim strPID As String
Dim SQL As String
Dim strLinkCriteria

If Me.Dirty = True Then
Me.Dirty = False
End If

Mglobal.gReturnToG = "WR"
Mglobal.gInsertNewRec = False
Mglobal.gReview = True

Set cd = CurrentDb
SQL = "SELECT PI.*, DE.* FROM tblPatientInformation PI, tblDietExercise DE " & _
"WHERE PI.PatientID = '" & Me.tbPatientID.Value & "' " & _
"ORDER BY DE.DietExerciseID"

Set rs = cd.OpenRecordset(SQL)
strPID = Me.tbPatientID.Value
strLinkCriteria = "[PatientID] = '" & strPID & "'"
DoCmd.OpenForm "frmDietExercise", , , strLinkCriteria

rs.Close
Set rs = Nothing
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "frmWeightReduction", acSaveYes
 

sneuberg

AWF VIP
Local time
Today, 00:32
Joined
Oct 17, 2014
Messages
3,506
I don't see any connection between the recordset rs and the form. Why don't you just specific the order by in the record source of the form or you could set the orderby property of the form something like

Code:
Me.OrderBy = "DE.DietExerciseID"

after you open the form.
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
I guess I don't understand. I posted the following code in the frmDietExercise but it gave me an error:

Private Sub Form_Load()
Me.Recordset
Me.OrderBy "DE.DietExerciseID"
End Sub
 

MrHans

Registered User
Local time
Today, 08:32
Joined
Jul 27, 2015
Messages
147
You need to add the = sign.
And I think you should also add

Code:
me.orderbyon = true
 

sneuberg

AWF VIP
Local time
Today, 00:32
Joined
Oct 17, 2014
Messages
3,506
Sorry forgot about the orderbyon thing. Here's some info on that. Also you don't need the line

Me.Recordset

Unless this is something that is going to change I suggest just specifying this in the Record Source query of the form.
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
Me.OrderByOn = True
Me.OrderBy = "DE.DietExerciseID"

I placed these in the Form_Current method. Got no error, it just did not work.
 

MrHans

Registered User
Local time
Today, 08:32
Joined
Jul 27, 2015
Messages
147
Try it the other way around?

Code:
me.orderby = "fieldname"
me.orderbyon = True

Should work with On Load event...
 

sneuberg

AWF VIP
Local time
Today, 00:32
Joined
Oct 17, 2014
Messages
3,506
I'm not sure how that's going to react in the Form_Current event but need to be in the opposite order.

Code:
Me.OrderBy = "DE.DietExerciseID"
Me.OrderByOn = True

If that doesn't do anything try it like

Code:
Me.OrderBy = "DietExerciseID"
Me.OrderByOn = True
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
Placed them in the Query method of the form and got a dialog box asking for the value of DE.DietExerciseID"
 

sneuberg

AWF VIP
Local time
Today, 00:32
Joined
Oct 17, 2014
Messages
3,506
Why are you putting it in the on query event if that's what you mean by Query method?

Why aren't you specifying this in the Record Source?
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
Sneuberg - Doing what you recommended in your last post took me to the first record, but did not let me go to any other records. It also asked for the parameter value of DE.DietExerciseID. It was placed in the Query method of the form.
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
I am new to Access. How do I place the code in the Record Source?
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
Here is what I tried:

Private Sub Form_GotFocus()
Dim SQL As String
SQL = "SELECT PI.*, DE.* FROM tblPatientInformation PI, tblDietExercise DE " & _
"WHERE PI.PatientID = '" & Me.tbPatientID.Value & "' " & _
"ORDER BY DE.DietExerciseID"
Me.RecordSource = SQL
Me.OrderBy = "DE.DietExerciseID"
Me.OrderByOn = True
End Sub

But it did not work.
 

sneuberg

AWF VIP
Local time
Today, 00:32
Joined
Oct 17, 2014
Messages
3,506
Going back to the beginning of this thread it appears that the goal was to open the frmDietExercise form and have it be on the record of the patient who was being displayed on the frmWeightReduction. But then you were concerned that the records were not in a certain order. How can that be a concern if the form was opened to just one specific record? This is what would happen if you put the patient id in the where part of the form open as you have it.


Do you want to open form with all of the records in a certain order but the current record be of a certain patient? If that the case let us know and I'll tell you how you can do that by passing the patient id to the form being opened through OpenArg? But first get the forms records source to display the records in the order you want.
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
Thank you so much, sneubery and MrHans!! You have taught me something. The problem is solved by putting the following in frmDietExercise Form_gotFocus() event:

Me.OrderBy = "DietExerciseID.Value"
Me.OrderByOn = True

If there is a special way to post a Thanks, I don't see it.
 

sneuberg

AWF VIP
Local time
Today, 00:32
Joined
Oct 17, 2014
Messages
3,506
If there is a special way to post a Thanks, I don't see it.

There's a button on the right on right of posts labelled "Thanks" and has a thumbs up. If you click on that it bumps up the forum members Thanked count.
 

jsbotts73

Registered User.
Local time
Today, 00:32
Joined
Aug 10, 2016
Messages
18
It has been over two years and I must say I was negligent in not thanking all that posted to my thread. Very thoughtless of me. The project was discontinued and I did not follow though on this thread as I should have. So, thank you Sneuberg, and Mr. Hans for your help.
 

Users who are viewing this thread

Top Bottom