Results 1 to 3 of 3
  1. #1
    gac_100 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2018
    Posts
    2

    SQL in VBA Module that allows user to input how many records to find

    Complete Access noob here.
    I have created some VBA that includes a SQL query and I would like to be able to ask the user to input how many records they would like to download.
    Not sure if I am going about this the right way at all. Here is the code I have so far... What can I enter in XXXX to pass the variable from the input box to the TOP function in the SQL?
    Thank you in advance!

    Public Function SQL()
    Dim count As Integer
    count = InputBox("How many records do you want to download?", "Download")


    Dim db As dao.Database
    Dim qdef As dao.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT Top = XXXX Unique_ID from Main_Table ;"
    Set qdef = db.QueryDefs("Pull_Query")
    qdef.SQL = strSQL
    qdef.Close
    Set qdef = Nothing
    Set db = Nothing
    DoCmd.OpenQuery "Pull_Query", acViewNormal
    End Function

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Code:
    strSQL = "SELECT TOP " & count & " Unique_ID from Main_Table ;"
    But before you do that you may want to do some validation on the user input.

    EDIT:
    Code:
    Public Function SQL()  '<-- function but doesn't have a return value
    On Error GoTo ErrHandler
        Dim count As Variant
        count = InputBox("How many records do you want to download?", "Download")
        
        If Not IsNumeric(count) Then
            ' this should quit the function of a number isn't input by the user
            Err.Raise 1001, , "Input requires a numeric value."
        End If
        
        count = CInt(count)
    
        If count < 1 Or count > 1000 Then
            Err.Raise 1002, , "Input must be between 1 and 1000."
        End If
        
        Dim db As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim strSQL As String
        
        strSQL = "SELECT TOP " & count & " Unique_ID FROM Main_Table ;"
        
        Set qdef = db.QueryDefs("Pull_Query")
        qdef.SQL = strSQL
        qdef.Close
        
        DoCmd.OpenQuery "Pull_Query", acViewNormal
        
    ExitHandler:
        Set qdef = Nothing
        Set db = Nothing
        Exit Function
        
    ErrHandler:
        MsgBox Err.Description, vbExclamation, "Error in 'SQL' Sub: #" & Err.Number
        Resume ExitHandler
    End Function

  3. #3
    gac_100 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2018
    Posts
    2
    Thanks kd2017!
    That works, thought I tried that already but I guess not
    The validation is also a great idea.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-16-2018, 03:38 AM
  2. Replies: 1
    Last Post: 11-04-2014, 12:07 PM
  3. SELECT Records based on user input
    By Mattrob in forum Queries
    Replies: 1
    Last Post: 10-10-2013, 12:28 AM
  4. Replies: 1
    Last Post: 07-20-2012, 05:35 PM
  5. Selecting records based on criteria from user input
    By shabbaranks in forum Programming
    Replies: 1
    Last Post: 01-04-2012, 09:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums