Using Order BY

[paddymx]

I am using the openargs to open a form in the On Load event,but, I can't get the form to sort on the "Flavor" field/control.

If OpenArgs = "One" Then



SQL = "SELECT * FROM" & _
" ECF_FlavorsT WHERE Venno = " & Forms!flavbyvenf!.ID ''<<<<................the calling form


Me.RecordSource = (SQL)

Else
SQL = "SELECT * FROM" & _
" ECF_FlavorsT"
Me.RecordSource = (SQL)


End If
DoCmd.OpenForm strDocname

I have tried every way I know to add the "Order By" to the constructed SQL and tried the "me.Order By" property of the form in the Current Event. Any ideas how I can get the form to sort on "Flavor"?

[June7]

No reason to put the SQL variable within parens.

You don't show the ORDER BY attempts so I will offer what I would use:

Me.RecordSource = "SELECT * FROM ECF_FlavorsT WHERE Venno = " & Me.ID & " ORDER BY Flavor;"

I am confused by your code. The filter code has nothing to do with the form called by the OpenForm method.

[paddymx]

No reason to put the SQL variable within parens.

You don't show the ORDER BY attempts so I will offer what I would use:

Me.RecordSource = "SELECT * FROM ECF_FlavorsT WHERE Venno = " & Me.VenNo & " ORDER BY Flavor;"

I am confused by your code. The filter code has nothing to do with the form called by the OpenForm method.

June, My fault, I forgot the "SQL=". Also I am trying to set the recordsource before the form opens.

I tried your code but as the form isn't open it can't find "ID" . ID would be the Flavors' ID and the VenNo = ID on the calling form..FlavorByVenF.

Calling form is FlavByVenF...VendorLkupT
the opening form is FlavF....ECF_FlavorsT

A little background. I was using the FlavF form with a relation to my VendorLkupT, but, when I deleted a flavor is would also delete the vendor from the VendorLkupT. So what I am try to do is base the FlavF only on the ECF_FlavorsT so it won't delete entries in the VendorLkupT.


Here is the full code:

On Error GoTo Err_GetVendor_Click
Dim strDocname As String
Dim strlinkceria As String
strDocname = "FlavF"
MsgBox OpenArgs

If OpenArgs = "One" Then
MsgBox Forms!flavbyvenf!ID


SQL = "SELECT * FROM" & " ECF_FlavorsT WHERE Venno = " & Forms!flavbyvenf!ID

Me.RecordSource = (SQL)
Else
SQL = "SELECT * FROM" & " ECF_FlavorsT"

Me.RecordSource = (SQL)


End If
DoCmd.OpenForm strDocname

Exit_VendorAbbrv_Click:
Exit Sub

Err_VendorAbbrv_Click:
MsgBox Err.Description
Resume Exit_VendorAbbrv_Click

Exit_GetVendor_Click:
Exit Sub

Err_GetVendor_Click:
MsgBox Err.Description
Resume Exit_GetVendor_Click

End Sub

[June7]

Does not clear up my confusion. The RecordSource being set is the form the code is behind, not the form opened by OpenForm.

I would not set RecordSource of the opening form. I would (and do) use the WHERE argument of OpenForm.

DoCmd.OpenForm "FlavF", , , IIf(Me.OpenArgs = "One", "Venno = " & Me.ID, "")

Why would deleting a flavor delete a vendor? Do you have Cascade Delete set in table relationship? Don't.

Why delete a flavor anyway?

[paddymx]

Does not clear up my confusion. The RecordSource being set is the form the code is behind, not the form opened by OpenForm.

I would not set RecordSource of the opening form. I would (and do) use the WHERE argument of OpenForm.

DoCmd.OpenForm "FlavF", , , IIf(Me.OpenArgs = "One", "Venno = " & Me.ID, "")

Why would deleting a flavor delete a vendor? Do you have Cascade Delete set in table relationship? Yes Don't.

Why delete a flavor anyway? Just in case I need to.

I'm confused too.....

When I tried to set OpenArgs to a var the openform would not accept it. I had to actually code "One" as the argument.

Does the "IIf(Me.OpenArgs = "One", "Venno = " & Me.ID, "") part execute after FlavF opens?

I'm going to go back the the Older FlavF and see what happens when I remove the cascading delete feature.

[paddymx]

I'm confused too.....

When I tried to set OpenArgs to a var the openform would not accept it. I had to actually code "One" as the argument.

Does the "IIf(Me.OpenArgs = "One", "Venno = " & Me.ID, "") part execute after FlavF opens?

I'm going to go back the the Older FlavF and see what happens when I remove the cascading delete feature.

Removing the cascading delete feature did the trick. Thanks a lot! Could you still answer "Does the "IIf(Me.OpenArgs = "One", "Venno = " & Me.ID, "") part execute after FlavF opens?" Might want to use that approach sometime.

Patrick

[June7]

The IIf calculates when OpenForm is executed, not after the form opens. It passes whatever value is the result of the IIf.