Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    An append query in Access 2010 with a parameter that I want to loop.

    Hi

    I have an append query with a parameter. I want to be able to run the append query several times. I want the user to be able to enter in the parameter in a pop-up box. Is it possible to use a loop command? I want to code this on a button.

    Open to other suggestions if a loop cannot be used with an append query?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Append queries themselves don't know anything about loops, but VBA does.

    The code to open/run a query would be

    DoCmd.OpenQuery "queryname" where queryname is replaced by the actual name.

    This loop will do it 5 times.

    Dim intI as integer
    for intI = 1 to 5
    DoCmd.OpenQuery "queryname"
    Next intI

    But each loop will display the parameter prompt again. Is that what you want? Otherwise use a form to provide the parameter.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    An append query in Access 2010 with a parameter that I want to loop.

    Hi John

    New to forum. Hope I am replying correctly.
    I have been testing the code on the button and I like how it works. If I do not need the five times, I do not enter anything in the pop-up box and I hit "OK" and I get the results I want. Thank you for keeping it simple.

    Jean

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Welcome to the Lounge Jean.

    If the number of times you want to run the query varies you could prompt for that too!
    (The code needs to check that what has been entered really is a number >0, so that makes it seem a bit more complicated. You could leave that out, but then you would get an error if the user typed in the wrong sort of answer.)

    Code:
    Dim intI as integer
    Dim intMax as integer
    Dim strResponse as string
    strResponse = InputBox("How many times do you want to run the query?", "How many times?",5)
    If isNumeric(strResponse) then
       intMax = Cint(strResponse)
    else
      intMax = 0
    end if
    If intMax >0 then
       For intI = 1 to intMax
          DoCmd.OpenQuery "queryname"
       Next intI
    End if
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi John

    I probably should start a new thread but I am having difficulties as to where that is on the site. You have been so helpful that I was wondering if you could help me with the following:
    I have a button that transfers a spreadsheet to Excel. Is there a way to have a message box pop-up so the user can input part of the filename. For example I would want to beable to enter in this part of the filename StudentTXCB01 but have the code put in the date and time stamp. Here is the code for the button.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Temp Student Table Export1", "C:\temp\TXC\" & "Student-" & Format(Date, "mm-dd-yyyy") & " " & Format(Time, "hh-mm-ss AM/PM") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Temp Course Table Export1", "C:\temp\TXC\" & "Course-" & Format(Date, "mm-dd-yyyy") & " " & Format(Time, "hh-mm-ss AM/PM") & ".xls", True

    You will notice in the code I only have Student and Course.

    Jean

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    At the top of the Databases (or any other) forum is a Post New Thread button.
    newThread.gif

    You can prompt using an InputBox

    Code:
    Dim strFileName as string
    Dim strInput as string
    
    strInput = InputBox("Enter Student Code", "Student")
    strFileName = "C:\temp\TXC\" & "Student-" & strInput & Format(Date, "mm-dd-yyyy") & "  " & Format(Time, "hh-mm-ss AM/PM") & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Temp  Student Table Export1", strFileName True
    
    strInput = InputBox("Enter Course Code", "Course")
    strFileName = "C:\temp\TXC\" & "Course-" & strInput &  Format(Date, "mm-dd-yyyy") & "  " & Format(Time, "hh-mm-ss  AM/PM") & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Temp  Course Table Export1", strFileName, True
    But if the queries are actually about a specific student and specific course, how did you specify what they were in the first place?

    The problem with using Input Boxes is that there is no guarantee of accuracy. What the user types in may be just wrong.

    So my preference would be to have a form with two combo boxes. One is a list of students, the other a list of courses.
    The two queries use the combos to decide which student and course to use, then the code above looks to the same two combos to set the file name.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Input Box

    Hi John

    Thank you. The input box is tied into the append query that you helped me with.

    Jean

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •