Changing query definition

  • Thread starter Thread starter iridium
  • Start date Start date
I

iridium

I have table (tblcheckout). I want to update some of the records in
this table. I have created a query that brings back some of the
records and I only want to update the top N of these records. How can
I modify my query to bring back the top N (specified by the user) of
the records.

Thanks for the help

Zeki
 
Rather a opening a query, open a form based on the table and amend its
RecordSource property in code. First create a form, frmCheckout say, based
on the table. Lets assume you want the top N rows from the table sorted by a
date field in descending order, i.e. top N latest rows, then add a procedure
to a standard module along these lines:

Public Sub OpenTopN(intTop As Integer)

Dim strSQL As String

strSQL = _
"SELECT TOP " & intTop & " * " & _
"FROM tblcheckout" & _
"ORDER BY YourDateField DESC"

DoCmd.OpenForm "frmCheckout"
Forms("frmCheckout").RecordSource = strSQL
Forms("frmCheckout").Requery

End Sub

To open the form call the procedure, passing an integer number as its
argument to specify the top N rows you want returned. The best way to do
this would be to create an unbound dialogue form with a text box, txtTop say,
to enter the number and a button to open the frmCheckout form. Put the
following code in the txtTop control's KeyPress event procedure, which will
prevent the user entering any non numeric characters:

' ensure only numeric characters entered
If Not IsNumeric(Chr(KeyAscii)) Then KeyAscii = 0

Set its ValidationRule property to:
0
Click to expand...

and its ValidationText property to something like:

Enter a number greater than zero

The code for the button's Click event procedure would be like this:

Const conMESSAGE = "A non-zero number must be entered"

' first make sure a number has been entered
If Not IsNull(Me.txtTop) Then
OpenTopN Me.txtTop
' close dialogue form
DoCmd.Close acForm, Me.Name
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

Remember that if there are ties for the Nth place you'll get more than N
rows returned.

Ken Sheridan
Stafford, England
 
Back
Top