Trying to order a report using VBA

[pacctono]

Buenos dias, amigos!

I created a report that gets inputs from a form using OpenArgs. When the report opens (Report_Open), it manipulates a query saved in a string (txtSQL) replacing the where condition.

<code>
Const txtSQL As String = "SELECT a.lo_cedula, a.tx_nombre, a.tx_nucleo, a.tx_sexo, c.lo_cedula_carga, c.tx_nombre_carga, " & _
"int((date()-a.fe_nacimiento)/365.24) as entEdad, c.tx_parentesco," & _
"p.tx_descripcion AS txtParentesco, n.tx_descripcion AS txtNucleo, c.tx_Sexo as txtSexoCarga, " & _
"c.fe_nacimiento, int((date()-c.fe_nacimiento)/365.24) as entEdadCarga, " & _
"c.fe_inscrito_aps, c.bo_aps, c.bo_servicio_funerario" & vbCrLf & _
"FROM ((ap_asociados AS a INNER JOIN cf_cargas_familiares AS c ON a.lo_cedula = c.lo_cedula) " & vbCrLf & _
"INNER JOIN ap_nucleos AS n ON a.tx_nucleo = n.tx_nucleo)" & vbCrLf & _
"INNER JOIN cf_parentescos AS p ON c.tx_parentesco = p.tx_parentesco" & vbCrLf & _
"WHERE a.id_estatus = '1' AND c.bo_estatus = True AND a.tx_nucleo LIKE 'DONDE_NUCLEO'"
</code>

The form pass "txtNucleo" for the where condition (DONDE_NUCLEO") and "txtOrdenar" to give an order to the report.

<code>
Private Sub Report_Open(Cancel As Integer)
...
txtMiSql = Replace(txtSQL, "DONDE_NUCLEO", txtNucleo)
Me.RecordSource = txtMiSql
End Sub
</code>

I tried with:
<code>
Private Sub Report_Load()
Me.OrderBy = txtOrdenar & ", c.tx_parentesco"
Debug.Print Me.Report.OrderBy
Me.OrderByOn = True
Me.OrderByOnLoad = True
End Sub
</code>

I put it, also, in the "Report_Open" but neither one works.

Do you have any suggestion? Is it possible to chage the order in the report, on the fly, with VBA?

 

[theDBguy]

Does txtOrdenar contain a name of a field in the query? If not, what does it have?

 

[pacctono]

Does txtOrdenar contain a name of a field in the query? If not, what does it have?

Sure!

Can be:
a.tx_nucleo, a.tx_nombre, a.lo_cedula, c.tx_parentesco
a.tx_nucleo, a.lo_cedula, c.tx_parentesco
a.lo_cedula, c.tx_parentesco
a.tx_nombre, a.lo_cedula, c.tx_parentesco

 

[theDBguy]

Sure!

Can be:
a.tx_nucleo, a.tx_nombre, a.lo_cedula, c.tx_parentesco
a.tx_nucleo, a.lo_cedula, c.tx_parentesco
a.lo_cedula, c.tx_parentesco
a.tx_nombre, a.lo_cedula, c.tx_parentesco

Right, but are those names of your fields in the query? That's what you'll need to have - field names.

 

[Pat Hartman]

The simpler method is to use the Where argument of the OpenReport method. That way you don't need code in the report.

Also, the report has sorting and grouping properties. Those are what control the report's sort order. You cannot modify it by modifying your query. The sort in your query runs. Then Access uses the sort defined by the sorting/grouping properties to resort the data.

 

[pacctono]

Sure!

Can be:
a.tx_nucleo, a.tx_nombre, a.lo_cedula, c.tx_parentesco
a.tx_nucleo, a.lo_cedula, c.tx_parentesco
a.lo_cedula, c.tx_parentesco
a.tx_nombre, a.lo_cedula, c.tx_parentesco

Right, but are those names of your fields in the query? That's what you'll need to have - field names.

Do you mean the report field, like this:

tx_nombre, lo_cedula, tx_parentesco

it does not work.

 

[pacctono]

The simpler method is to use the Where argument of the OpenReport method. That way you don't need code in the report.

Also, the report has sorting and grouping properties. Those are what control the report's sort order. You cannot modify it by modifying your query. The sort in your query runs. Then Access uses the sort defined by the sorting/grouping properties to resort the data.

But can I these properties:

Me.OrderBy = txtOrdenar & ", tx_parentesco"
Me.OrderByOn = True
Me.OrderByOnLoad = True

 

[Pat Hartman]

Did they work for you? All I said was you can't change the sort order by changing the query

 

[pacctono]

Did they work for you? All I said was you can't change the sort order by changing the query

They did not work.

 

[The_Doc_Man]

I'm afraid Pat is right. When you have an Order By in the query OR when you set an Order By in the report's properties, they are ignored. The only way to order a report is to use the ordering and grouping options in the report's design mode.

 

[Gasman]

If you want to do it via VBA, I believe you will at least need to group by the fields?

GroupLevel.SortOrder property (Access)

Office VBA reference topic

docs.microsoft.com

 

[pacctono]

Wow!

I Will have to have one report for each sort.

 

[theDBguy]

Wow!

I Will have to have one report for each sort.

Not really. I just did a quick test. It seems to work for me. Please try the attached and see if you can use the same approach.

 

[Gasman]

@theDBguy Any chance of a 2007 version please?
I am able to open the DB, but not the report, need 14 at least and 2007 is 12.

TIA

 

[theDBguy]

@theDBguy Any chance of a 2007 version please?
I am able to open the DB, but not the report, need 14 at least and 2007 is 12.

TIA

Sorry, I'm not sure I can do that right now from work. We'll need someone's help instead. In any case, it's just a simple report showing all the columns. And in the Open Event of the report, I just have the following:

Private Sub Report_Open(Cancel As Integer)
'thedbguy@gmail.com
'8/22/2022

If Not IsNull(Me.OpenArgs) Then
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
    
End If

End Sub

 

[theDBguy]

@theDBguy Any chance of a 2007 version please?
I am able to open the DB, but not the report, need 14 at least and 2007 is 12.

TIA

I tried saving to a MDB. See if this works.

 

[Gasman]

Sorry, I'm not sure I can do that right now from work. We'll need someone's help instead. In any case, it's just a simple report showing all the columns. And in the Open Event of the report, I just have the following:

Private Sub Report_Open(Cancel As Integer)
'thedbguy@gmail.com
'8/22/2022

If Not IsNull(Me.OpenArgs) Then
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
   
End If

End Sub

OK, that is fine, actually seeing the code. Thank you.

 

[Gasman]

I tried saving to a MDB. See if this works.

That also works. Thanks again.

 

[theDBguy]

That also works. Thanks again.

Ah, glad to hear that it's possible to do that. Thanks for letting me know.