displaying selected number of records in a form

[Guest]

Hello,

I've just set up a form in my application where the user selects several
conditions, presses a "GO"-style command button, and a new form appears with
the record matches is descending order in a subform. Problem is, in some
cases there are over 1,000 matches and I'd like to add a combo box where the
user can select the number of records he/she would like to view. I've
figured out that it's the Row Source Type = Value List and perhaps Row Source
= 10, 50, 100, ALL in the combo box - but I'm unsure of the code needed to
tie the combo box into the existing code behind the button that executes the
search, limiting the number of records displayed.

Also, I want to make sure that if someone selects "View 50" and there are
only 30 records, that it doesn't error out. . .

Many, many, many thanks for all your help. . . .

Marika

 

[Allen Browne]

In the AfterUpdate event procedure or the combo, set the RecordSource of the
form. You can use TOP to limit the form to the number of records.

The code below tests the combo to see if it has a numeric value. If so, it
limits the number of records; if not, it shows them all.

The constant should contain whatever query you currently use for the form
without the SELECT word at the start.

Private Sub cboHowMany_AfterUpdate
Dim strPredicate As String
Const strcTail = " Table1.* FROM Table1;"

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

If IsNumeric(Me.cboHowMany) Then
strPredicate = " TOP " & Me.cboHowMany
End If

Me.RecordSource = "SELECT" & strPredicate & strcTail
End Sub