Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 27th, 2007, 06:04 PM
Authorized User
 
Join Date: Nov 2005
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default passing parameters

I want to select the top X records using a user supplied number. How can I do that using Access 2000?
 
Old August 28th, 2007, 10:32 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You would have to do this with code.

On the form, add a text box to take the number value.
Add a button to open the query.

On the button so something like:

Dim iTop As Integer
Dim sSQL As String

iTop = Me.txtTopField

sSQL = "SELECT TOP " & iTop & " FROM ..."

Then, where and how do you want to display the results... ?



mmcdonal
 
Old August 28th, 2007, 12:56 PM
Authorized User
 
Join Date: Nov 2005
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have it show in a table. The results are then exported to Word Merge format (I know how to do that part).
 
Old August 28th, 2007, 01:13 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Okay, here is code that takes data from one table and moves it to another. It also starts by deleting all the data in tblMerge first, to make sure it is clean before it starts.

This only moves the values from one field, but you can add others. Since I do mostly Access/SQL, this is ADO working on local tables. This will also work in Access, or Access/Access, as long as the tables are linked, otherwise you will need a connection object as well.

Don't worry about my table and field names, they are from another post database.

Dim iSelect As Integer
Dim sSQL1 As String
Dim sSQL2 As String
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim i As Integer

i = 0
iSelect = Me.Text3

DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETEtblMerge"
DoCmd.SetWarnings True

sSQL1 = "SELECT TOP " & iSelect & " tblGus2.Num FROM tblGus2 ORDER BY tblGus2.Num"
Set rs1 = New ADODB.Recordset
rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs1.MoveFirst

sSQL2 = "SELECT * FROM tblMerge"
Set rs2 = New ADODB.Recordset
rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Do Until i = iSelect
        rs2.AddNew
        rs2("Num") = rs1("Num")
        rs2.Update
    rs1.MoveNext
    i = i + 1
    Loop

rs1.Close
rs2.Close


Did that help? It does work.


mmcdonal
 
Old August 28th, 2007, 01:15 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I forgot to mention, the Text3 control is formatted:

Format: Standard
Decimal Places: 0



mmcdonal
 
Old August 31st, 2007, 12:16 PM
Authorized User
 
Join Date: Nov 2005
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A little customizing and I'll be fine. I've programmed Classic ASP and VB6 pages for SQL Server databases, and this looks like it's the same thing. Thanks for the direction (=





Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing parameters tal Ajax 5 May 26th, 2007 08:07 AM
Passing more than one parameters from datagrid Lyn ASP.NET 1.0 and 1.1 Professional 6 August 29th, 2006 02:30 AM
Passing parameters ceema Crystal Reports 0 February 15th, 2006 04:03 AM
Passing Parameters spraveens Javascript 5 October 8th, 2003 07:06 AM
passing parameters spraveens Javascript How-To 1 October 8th, 2003 03:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.