Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    build a query from a form (2003)

    Hi, I'd like to have a form in which the user selects a value from a combo box (ie. name of a table in the database), then have all of the records of the chosen table displayed in a query. I've been searching for a solution in the lounge, but some of the coding, etc is a bit beyond me. Is there a simple solution? Many thanks, Van

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (2003)

    Your code would be but on the Change event for the combo box.

    Private Sub cboTest_Change()
    Dim qryName As String

    qryName = Me.cboTest
    DoCmd.OpenQuery qryName

    End Sub

    I've attached a very bare bones example.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: build a query from a form (2003)

    An end user should never look directly at a table or query, so why are you trying to do this?

  4. #4
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (2003)

    The end-users (research group users) need to be able to export the data to excel for statistical analysis. I thought the easiest way would be to generate a query which they could then analyse with excel.

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (2003)

    Thanks muchly. That's just what I was after. cheers, van

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (2003)

    If there are only exporting to excel, you could use the following to create an excel file on their desktop. The file will be called the same as the query with the date and time added to the end of the name.


    Private Sub cboTest_Change()
    Dim qryName As String, strDestin As String
    qryName = Me.cboTest
    strDestin = "c:documents and settings" & Environ("username") & "desktop" & _
    qryName & " " & Format(Now(), "yyyy-mm-dd HHmm")
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qryName, strDestin


    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (2003)

    thanks to all for the help. I've implemented the last solution which works very well. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: build a query from a form (2003)

    The attachment shows a slightly different approach.

    This allows the user to preview the data to be exported, then export with the Export command button.

    The selected table is displayed in a locked subform, so no changes can be made.
    By setting the source object of the subform to "table." & Me.ComboExport , the table can be displayed directly in the subofrm.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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