ORDER BY is not working

[jsbotts73]

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]

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

Me.OrderBy = "DE.DietExerciseID"

after you open the form.

 

[jsbotts73]

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]

You need to add the = sign.
And I think you should also add

me.orderbyon = true

 

[sneuberg]

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]

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

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

 

[MrHans]

Try it the other way around?

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

Should work with On Load event...

 

[sneuberg]

I'm not sure how that's going to react in the Form_Current event but need to be in the opposite order.

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

If that doesn't do anything try it like

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

 

[jsbotts73]

Placed them in the Query method of the form and got a dialog box asking for the value of DE.DietExerciseID"

 

[sneuberg]

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]

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]

I am new to Access. How do I place the code in the Record Source?

 

[jsbotts73]

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]

I am new to Access. How do I place the code in the Record Source?

I suggest watching this video.

 

[sneuberg]

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]

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]

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]

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.