TopValues parameter from form?

W

WinDancer

Registered User.
Local time
Today, 13:05
Joined
Oct 29, 2004
Messages
290
I searched and read everything found regarding TopValues- since those answers don't fit exactly what I am trying to do here is another post :)

I have a query that selects records for a table based on a users input on a form-

They would like to be able to input the TopValues number [set to 100 for this query] from that form, also.

The code for the query is below-
*******************************************

INSERT INTO tblGeneratedRandomRecords ( recid, ACCOUNT_ID, UNIFORM_BUSINESS_ID, ACCT_ACTV_DATE, REGION_NUMBER, CountOfRISK_MAIN_SUB, SumOfPREMIUM_ASSESSED, SumOfTOTAL_UNITS, YEAR_QRTR, ACCT_STATUS_CODE, [Total DrvdFTE], FIELD_AUDIT_FLG, LAST_FIELD_AUDIT_DATE, IN_COLLECTION_FLG, OUT_COLLECTION_DATE, RandNumGen, NAICSCode, NAICSDesc )

SELECT TOP 100 tblRollUpPayRoll.recid, tblRollUpPayRoll.ACCOUNT_ID, tblRollUpPayRoll.UNIFORM_BUSINESS_ID, tblRollUpPayRoll.ACCT_ACTV_DATE, tblRollUpPayRoll.REGION_NUMBER, tblRollUpPayRoll.CountOfRISK_MAIN_SUB, tblRollUpPayRoll.SumOfPREMIUM_ASSESSED, tblRollUpPayRoll.SumOfTOTAL_UNITS, tblRollUpPayRoll.YEAR_QRTR, tblRollUpPayRoll.ACCT_STATUS_CODE, tblRollUpPayRoll.[Total DrvdFTE], tblRollUpPayRoll.FIELD_AUDIT_FLG, tblRollUpPayRoll.LAST_FIELD_AUDIT_DATE, tblRollUpPayRoll.IN_COLLECTION_FLG, tblRollUpPayRoll.OUT_COLLECTION_DATE, acbgetrandom([recid]) AS RandNumGen, tblRollUpPayRoll.NAICS_Code, tblRollUpPayRoll.NAICSDesc

FROM tblRollUpPayRoll

ORDER BY acbgetrandom([recid]);

****************************************
Any help is appreciated!
 
There are two queries their is that of any significance?

Where is this query used? Is it used as a standalone query? Is it used in a subform? Is it used in a report?

Cheers Tony...
 
This is a single stand-alone append query [qryRandomSelector].

It runs against a single table [tblRollUpPayroll].

The returned records append into a single table [tblGeneratedRandomRecords].

That table is used later in some reports and taskings.

The parameters are picked up from a user-completed form [frmParameters].

The records being appended to that table ends the process for a few days. The users are able to view the data to be sure they got what they wanted, based on the parameters the furnished.

Thanks for taking the time to look it over- any suggestions are always appreciated.

Dave
 
Append Top 20 Query in ADO DB.

Rightly or wrongly I tend to tackle problems of this Nature in code.
I have attached an example using ADO DB, I have simplifies the queries, for the purpose of the example.
I am not sure whether you will be able to use exactly the same idea in your query as your query uses several parameters, in particular I do not know what the random number function does, whether it produces just one number or a series of numbers on each iteration.


here is the code:
Code: 
Function fTop20(intTop20 As Integer)
On Error GoTo Err_ErrorHandler

Dim strSQL As String 'Build the SQL string
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

'SQL from Query1
'INSERT INTO tblContactTo ( ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag )
'SELECT TOP 20 ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag
'FROM tblContactFrom;

strSQL1 = "INSERT INTO tblContactTo ( ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag ) "
strSQL2 = "SELECT TOP "
'20
strSQL3 = " ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag "
strSQL4 = "FROM tblContactFrom "
'strSQL5 = ""

strSQL = strSQL1 & strSQL2 & intTop20 & strSQL3 & strSQL4

    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command

    Set adoCon = CurrentProject.Connection
    Set adoCmd = New ADODB.Command

        With adoCmd
            .ActiveConnection = adoCon
            .CommandType = adCmdText
            .CommandText = strSQL
            .Execute
        End With

Exit_ErrorHandler:
        adoCon.Close
    Set adoCon = Nothing
    Set adoCmd = Nothing
    
    Exit Function

Err_ErrorHandler:
        Select Case Err.Number
            Case 1 'Not sure if there is an error code (1) I have never seen it yet
                MsgBox "produced by error code (1) please check your code ! Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , conAppName
            Case Else
                MsgBox "Error From --- fTop20 --- Error Number >>>  " & Err.Number _
                & "  <<< Error Description >>  " & Err.Description, , conAppName
        End Select
    Resume Exit_ErrorHandler
End Function      '  fTop20
 

Attachments

I am also (again) guilty of not reading post correctly!
I note that you also want to preview the results before appending them to the table.
Therefore I have made a slight modification to the form, basically added an option group, and changed the code to suit.

The new code is below:-

I draw your attention to how easy it was to make this change, I think this demonstrates why I prefer doing it this way!

Code: 
Function fTop20(intTop20 As Integer)
On Error GoTo Err_ErrorHandler

Dim strSQL As String 'Build the SQL string
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

'SQL from Query1
'INSERT INTO tblContactTo ( ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag )
'SELECT TOP 20 ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag
'FROM tblContactFrom;

strSQL1 = "INSERT INTO tblContactTo ( ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag ) "
strSQL2 = "SELECT TOP "
'20
strSQL3 = " ContRef, DateAdded, Title, Firstname, Initials, Salutation, Organisation, Flag "
strSQL4 = "FROM tblContactFrom "

        Select Case optGrpSeeResults
            Case 1
                strSQL = strSQL2 & intTop20 & strSQL3 & strSQL4
                subWinSeeResults.Form.RecordSource = strSQL
                Exit Function
            Case 2
                strSQL = strSQL1 & strSQL2 & intTop20 & strSQL3 & strSQL4
        End Select

    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command

    Set adoCon = CurrentProject.Connection
    Set adoCmd = New ADODB.Command

        With adoCmd
            .ActiveConnection = adoCon
            .CommandType = adCmdText
            .CommandText = strSQL
            .Execute
        End With

Exit_ErrorHandler:
        adoCon.Close
    Set adoCon = Nothing
    Set adoCmd = Nothing
    
    Exit Function

Err_ErrorHandler:
        Select Case Err.Number
            Case 1 'Not sure if there is an error code (1) I have never seen it yet
                MsgBox "produced by error code (1) please check your code ! Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , conAppName
            Case Else
                MsgBox "Error From --- fTop20 --- Error Number >>>  " & Err.Number _
                & "  <<< Error Description >>  " & Err.Description, , conAppName
        End Select
    Resume Exit_ErrorHandler
End Function      '  fTop20
 

Attachments

You must log in or register to reply here.

Similar threads

D
Field visibility on a form
Replies
10
Views
946
Gasman
Gasman
T
2 queries in one form
2
Replies
30
Views
2,932
mike60smart
mike60smart
A
How to Properly Query and Search Multi-Valued Fields in a Form?
2
Replies
21
Views
1,558
MajP
MajP
esturgesjr
Solved HELP WITH VBA ON SUBFORM
2
Replies
30
Views
870
The_Doc_Man
The_Doc_Man
MajP
Form Instance Example
Replies
2
Views
166
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom