Modify TopValues Property in code 1

[JoeMiller]

Ok, so this is one I've had for a while that I don't have an answer to. The TopValues property of a query according to the Microsoft Help is unable to be set directly in code. So I was wondering if anyone has developed any snazzy little function that proves them wrong.. I'd like to provide users with a text box that they can fill in with their desired TopValue and then change the query automatically. Anyone?

Joe Miller
joe.miller@flotech.net

 

[Aivars]

Hi, Joe!

I'm not sure but maybe this would give you any idea?

Me.RecordSource = SelectTopValXwithAllFields("Test", Me.txtFieldForTop.ControlSource, 12, 2)

It's function:
public Function SelectTopValXwithAllFields(strTdfName As String, _
strTopFieldName As String, _
Optional lngTopValX As Long = 10, _
Optional intSortOrder As Integer = 1) _
As String
'strTdfName -> table name
'strTopFieldName -> field name for TopValues
'lngTopValX -> Top amount
'intSortOrder -> query Order By -> 1 = "ASC", 2="DESC"
Dim strSQL As String
Dim tdf As TableDef
Dim fld As Field

If lngTopValX = 0 Then
lngTopValX = 1
End If

strTdfName = Trim(strTdfName)
For Each tdf In CurrentDb.TableDefs
If tdf.Name = strTdfName Then
For Each fld In tdf.Fields
If fld.Name <> strTopFieldName Then
If strSQL <> &quot;&quot; Then
strSQL = strSQL & &quot;, &quot;
End If
strSQL = strSQL & &quot;[&quot; & strTdfName & &quot;].[&quot; & fld.Name & &quot;]&quot;
End If
Next fld
Exit For
End If
Next tdf
strSQL = &quot;Select TOP &quot; & lngTopValX & &quot; [&quot; & strTdfName & &quot;].[&quot; & strTopFieldName & &quot;], &quot; & strSQL & &quot; From [&quot; & strTdfName & &quot;] Order By [&quot; & strTopFieldName & IIf(intSortOrder = 1, &quot;] ASC&quot;, &quot;] DESC&quot & &quot;;&quot;
SelectTopValXwithAllFields = strSQL

End Function

Aivars

 

[JoeMiller]

Aivars:

This works great for taking a table, but I was hoping to use the existing query i have and just find a way to change the TOP predicate only. I know I can build the whole thing on the fly, just didn't want to have to go to all the trouble if there was some way to just change the TOP value. But thanks for trying, and I'm going to keep your function because I will probably use it if I have to go the hard way!

Joe Joe Miller
joe.miller@flotech.net

 

[Aivars]

Hi, Joe again!

Maybe function in such form can help you? I tried it by using simple query - it works OK.

Function SelectTopValXwithAllFields(strTdfName As String, _
strTopFieldName As String, _
Optional lngTopValX As Long = 10, _
Optional intSortOrder As Integer = 1, _
Optional DefType As Integer = 1) _
As String
'strTdfName -> table name
'strTopFieldName -> field name for TopValues
'lngTopValX -> Top amount
'intSortOrder -> query Order By -> 1 = &quot;ASC&quot;, 2=&quot;DESC&quot;
'DefType -> opject type -> 1=Table, 2=Query
Dim strSQL As String
Dim tdf As TableDef
Dim qdf As QueryDef
Dim fld As Field

If lngTopValX = 0 Then
lngTopValX = 1
End If

strTdfName = Trim(strTdfName)
If DefType = 1 Then
For Each tdf In CurrentDb.TableDefs
If tdf.Name = strTdfName Then
For Each fld In tdf.Fields
If fld.Name <> strTopFieldName Then
If strSQL <> &quot;&quot; Then
strSQL = strSQL & &quot;, &quot;
End If
strSQL = strSQL & &quot;[&quot; & strTdfName & &quot;].[&quot; & fld.Name & &quot;]&quot;
End If
Next fld
Exit For
End If
Next tdf
Else
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strTdfName Then
For Each fld In qdf.Fields
If fld.Name <> strTopFieldName Then
If strSQL <> &quot;&quot; Then
strSQL = strSQL & &quot;, &quot;
End If
strSQL = strSQL & &quot;[&quot; & strTdfName & &quot;].[&quot; & fld.Name & &quot;]&quot;
End If
Next fld
Exit For
End If
Next qdf
End If
strSQL = &quot;Select TOP &quot; & lngTopValX & &quot; [&quot; & strTdfName & &quot;].[&quot; & strTopFieldName & &quot;], &quot; & strSQL & &quot; From [&quot; & strTdfName & &quot;] Order By [&quot; & strTopFieldName & IIf(intSortOrder = 1, &quot;] ASC&quot;, &quot;] DESC&quot & &quot;;&quot;
SelectTopValXwithAllFields = strSQL

End Function

Aivars

 

[JoeMiller]

That works great, thanks for your help!

Joe Miller
joe.miller@flotech.net