Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    7

    Unanswered: Extracting data from a sub-form query

    I have an MS Access database where one page will contain a summary of tasks. I've implemented this using a query which queries one table called "Tasks" and three related tables. I then added a sub-form to my form which uses that query to display a datasheet.

    What I want on my form is a few buttons that allow the user to view or manipulate the data. The user will click a row on the form and then click a button which will perform either some data manipulation or open a new window to show the user more details about the task.

    The first problem I'm having is how to know which row the user has selected in terms of the Task table. I've found a way of finding which row within the query the user has selected but this is useless because it doesn't tell me which row within the actual Tasks table that relates to. Performing a query and then using the nth row of the query isn't acceptable because the query might provide different results if another user has updated data.

    What I want is some way of getting the primary key of the Tasks table that the selected row in the query relates to. I could possibly put the primary key in the table of results but I'd rather not because it's a meaningless piece of data for the user.

    The second problem is, when the user requests to see more details this will open as a new form. How can I tell the form what row of the table to use. At this point assuming i have the first problem resolved I will have the primary key, I just need to tell the form. One way might be to have a Task text box on the form and have it change that after the form opens and have the rest of the data on the form use that in the query strings. It feels a bit of a bodge way of doing it though, is there an "industry standard" way of doing it?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider stuffing the the primary key into the tag property of a control on each row. then extract the tag from the control
    OR make certain you incldue the PK in the display, even if its hidden
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    7
    Ok but how do I extract it?

  4. #4
    Join Date
    May 2012
    Posts
    7
    Can anyone provide any more help with this? I get healdem's idea and I'll put that into practise but I don't seem to be able to figure out the simple task of getting the value of Row X, Column Y into a String.

    As a crude example I've created a query called People, which contains rows Name, Phone. A form called Main which contains a subforum called AllPeople which contains the form ShowAllPeople and a button called Go. The button called Go I want to tell me the value of cells in the query table:

    http://i.imgur.com/Tj1V8.jpg

    I've done a number of Google searches suggest I have something like:

    Forms![parts_form]![parts_list_box].Value

    But if I type Forms!, Form!, Main! or Form_Main! I don't get and dropdown which suggests VBA/Access isn't accepting this. I'm using Access 2002.

    Thanks
    Attached Thumbnails Attached Thumbnails Tj1V8.jpg  

  5. #5
    Join Date
    May 2012
    Posts
    7
    Never mind, figured it out after a bit of playing around and I didn't get any dropdowns like I do press I press '.'.

    This is my code and this seems to use the selected row which is what I want.

    MsgBox Forms![Main]![AllPeople]![Name].Value
    Attached Thumbnails Attached Thumbnails yaya.JPG  

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Working with what healdem suggested, I would rework the subforms and their source queries to include the primary key of the parent table. If you don't want to confuse the users, do this in a hidden field on the subforms. You can then use that value in your procedure.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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