Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Question Unanswered: How to pass data between queries

    Hi, first post, so be kind

    I'm using Access 2002, and while I have some experience using it, I'm no coder so haven't ever ventured into using VB, I just use the front end of access to build stuff for me.

    I'm pretty sure what I want to do is easily acheived, I just don't know how to do it. If you can point me in the right direction, it would be good.

    I have a database which is going to be used to log projects, and the work carried out on them, as well as the people that are involved (participants).

    So in short I have a project table (Project ID, name etc...) a participant table (Id, name) and a linking participant_on_project table (Project ID, Particpant ID, role).

    I'm working on a form to show who's assigned to the project being displayed. I have a button then runs an append query to add a participant (that already exists in the participant table) onto the project. This works fine.

    What I want to do is to incorporate the ability to add someone to the project who isn't already listed in the participant table. So in pseudo-code what I want is:

    1.Pop up a prompt to input the participant name
    2. Query participant table to see if they exist
    3. If they do, add them to the particpant_on_project table, along with the project ID etc...
    4. If they don't, collect the rest of the detail about them (email address, phone number...) add them to the participant table
    5. Then add then to the participant on project table.

    Make sense? Likely not the most logical way to do it, feel free to suggest better /alternatives.

    However, is it do-able, if so, any guidance on where to start, cos my brain hurts.

    Many thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    ...

    Just a suggestion. I normally would not have a pop up box to take a value and compare that to a table to see if it exists. The main drawback is if someone misspells the name.

    Rather, I would have a form with a drop down box populated with names. If the user doesn't see the name, they can hit a command button to popup a box with the fields so that they can enter the info.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    you should be able to just do a form set to add mode for your Person Table. I think the query wizard can do all that for you. Then, put a command button on the Project form to add a user or if your using a combo box for the users, set the NotInList to open the other form.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with Mr. Blonde.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2008
    Posts
    8
    Quote Originally Posted by mr. blonde
    Just a suggestion. I normally would not have a pop up box to take a value and compare that to a table to see if it exists. The main drawback is if someone misspells the name.

    Rather, I would have a form with a drop down box populated with names. If the user doesn't see the name, they can hit a command button to popup a box with the fields so that they can enter the info.
    Yup I agree, that's usually what I'd do, but i'm trying to minimise the number of clicks required, and display the maximum amount of info at a glance.

    The participants on the project are therefore displayed as a datasheet subform on the main project details form. I would use a drop down in this sub form, except for some reason when I change it I get no entries in the drop down.

    Think i need to walk away for a bit now and come back to it lol

  6. #6
    Join Date
    Feb 2004
    Posts
    214

    ...

    I see what you are saying about the number of clicks for the user, but essentially have a give and a take. Easier for the user or data consitency and integrity. If you can do both, awesome, but in this case you will have to chose one or the other.

    On your combo problem, make sure that whatever value you are changing runs something to tell the combo to requery.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

Posting Permissions

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