Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Choice from Qry (2003)

    I have a query that displays its results in a spreadsheet like view. I would like to click on a value in a cell, and have that value placed into a field in a form. How does one do something like that?
    Thanks in advance

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

    Re: Choice from Qry (2003)

    To do this you would need to display the results of the query in a continuous form. Then you could write an event procedure to be run when you click inside a field.
    You can't run event procedures from the raw query results.
    Regards
    John



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

    Re: Choice from Qry (2003)

    I never display queries to end users, only forms and reports.

    Queries don't have events, so there is no way to act automatically on a mouse click in a query. You could copy and paste.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice from Qry (2003)

    Is it possible to do this from a table?

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

    Re: Choice from Qry (2003)

    You can cut and paste from the table, but cannot run event procedures.
    Regards
    John



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

    Re: Choice from Qry (2003)

    Tables don't have events either. As John suggested, you need to create a form. You can then use the On Click or On Dbl Click event of controls on the form to perform some action.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice from Qry (2003)

    OK I've created a dataview Form, Now what? I'm a novice when it comes to VB

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

    Re: Choice from Qry (2003)

    Open the form in design view.
    Select one of the text boxes, let's call it txtField.
    Activate the Event tab of the Properties window.
    If you want to act on each click in the text box, click in the On Click box.
    (It might be safer to use the On Dbl Click event, but that's up to you to decide).
    Select [Event Procedure] from the dropdown list.
    Click the builder button (the ... to the right of the dropdown arrow).
    You'll be taken to the Visual Basic Editor, with the first and last line of the event procedure already created for you:
    <code>
    Private Sub txtField_Click()

    End Sub
    </code>
    or
    <code>
    Private Sub txtField_DblClick(Cancel As Integer)

    End Sub
    </code>
    depending on the event you decided to use.
    The insertion point is in the empty line in between.
    Press the Tab key once, then type a line like this, substituting the name of the form and of the control you want the value to be copied to:
    <code>
    Forms!frmOther!txtSomething = Me.txtField
    </code>
    Note: if there are spaces in the name of the control or form, put square brackets [ ] around the name, otherwise VBA gets confused.

    Repeat for each of the text boxes you want the user to be able to (double) click to transfer its value.

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

    Re: Choice from Qry (2003)

    Just to add to Hans' reply.

    This code assumes that the form is open already.

    If not you need to open it, and find the right record, or perhaps create a new one.
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice from Qry (2003)

    Are you saying that I must put the code into each cell in the data sheet?

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

    Re: Choice from Qry (2003)

    In design view, you don't see the data sheet. You must create an event procedure for each text box in the detail section of the form that should react to a (double) click by the user.

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice from Qry (2003)

    The Form name that will receive the choice is called TBL_Pricing, the form that is providing the choice is called FRM_Load test charges,
    the txt field that will receive the value is called loadtest$. I entered this information into the VB editor and tried it. Lots of error messages.
    Please help.
    Thanks

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

    Re: Choice from Qry (2003)

    You need

    Forms!TBL_Pricing!loadtest$ = Me.txtField

    But we need to know the name of the field that is providing the value.
    this name has to go in place of txtfield in thea bove code.

    The name of the form that is providing the choice is not used, because that comes from the Me in the code.
    Regards
    John



  14. #14
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice from Qry (2003)

    All is well. Thanks to all that helped. Where can a novice like myself learn VB programing ? <img src=/S/question.gif border=0 alt=question width=15 height=15> <img src=/S/question.gif border=0 alt=question width=15 height=15>

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

    Re: Choice from Qry (2003)

    Here is an Amazon search: access 2003 visual basic.

    I always learn a lot from studying, copying and modifying existing code. Access comes with a sample database Northwind.mdb, providing examples of queries, forms, reports and VBA code.

    Microsoft has several very useful databases for download, cintaining loads of VBA code you can study.
    Building Applications with Forms and Reports (contains 2 databases with help files)
    Sample Queries
    Sample Forms
    Sample Reports
    Sample Graphs (Access 97 format)
    Sample Code (Access 97 format)

Page 1 of 2 12 LastLast

Posting Permissions

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