Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    20

    Unanswered: How do I pass data between tables in MS ACCESS

    I have a CUSTOMERS table and each customer has projects in a PROJECTS table

    I want to click on a button in a FORM (CUSTOMERS form) and bring up a FORM( from the PROJECTS table) that shows all PROJECTS associated with that customer.....

    My form keeps showing ALL PROJECTS for ANY customer - I use a query that checks if CustomerID in the CUSTOMERS table = CustomerID in the PROJECTS table, but it STILL gives me ALL PROJECTS with a customerID that exists in both tables.....

    How can I remedy this?

    Thanks so much for your help

    JS....

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    use a subform for the project(s) on a main form for the customer and set the CustomerID field as the link

  3. #3
    Join Date
    Aug 2004
    Location
    Steamboat Springs, CO
    Posts
    37
    you need to put a statement in the criteria for the customerid field of the projects query underlying you second form that points to the control that contain the customerid field on your first form. You can use a subform to do this, but it's not necessarily the best way. The statement would look like this.

    [forms]![yourcustomerformnamehere]![yourCustomerIDcontrolnamehere]

    This will ensure that your query for your second form will only return records where the CustomerID equals the value selected on the textbox or combobox that is on your main form.

    Hope this helps

    Jeremy Brooks
    PSIA-Rocky Moutain

  4. #4
    Join Date
    Oct 2004
    Posts
    20

    I get a syntax error "converting from a char string to a uniqueidentifier"

    I get a syntax error "converting from a char string to a uniqueidentifier"

    should I make the CustomerID a character string? I don't understand why it doesn't use them both as uniqueidentifiers....

  5. #5
    Join Date
    Aug 2004
    Location
    Steamboat Springs, CO
    Posts
    37
    Try this so that you can better see what's happening. Create a query that contains all the fields from you projects table. Then put the criteria string that I gave you before in the CustomerID field criteria of the query design. Open your main form and click on the textbox containing the CusomerID. Now Run your query. It should only populate the orders associated with the id number of the box on which you clicked("gave the focus" is the techinical term). If you query is not coming up with only those records then you're not referencing that control (textbox) correctly.

    Jeremy

  6. #6
    Join Date
    Oct 2004
    Posts
    20
    Hi Jeremy - thanks for your help so far - I think I may be getting close....

    I put in what you said in the criteria of the query for CustomerID and it automatically converts it to this:

    = '[forms]!Customers!CustomerID'

    The query includes all fields from the PROJECTS table and one from the Customers table that I need to display (CustomerName)

    I call a macro when the button is pressed that uses "OpenForm" in the action field and down at the bottom I put in the form name "ProjectInfo"

    In the ProjectInfo form I specify the record source as "JeremyQuery" (the query I created above......

    Anything sound wrong with this so far? it gives me no records now....

    Do I need to specify "all records" from either table in the relationship in the query?

    right now neither is checked....

    thanks again so much for your help

    Jon

  7. #7
    Join Date
    Oct 2004
    Posts
    20
    Jeremy - I see you live in Steamboat Springs.... my nephew just played in a baseball tournament there a few months back (the one that ROger Clemens got thrown out of - it was all over ESPN, etc) - anyway, if you could maybe send me an email and I will write ya back if it's easier than this...I need as much help with Access as I can get..... have a few little projects I am working on all on the side of the main project I am doing here at work.... thanks again.... Jon.... jonbarnes2727@hotmail.com is my email - thanks...

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hey JumboShrimp,

    I just created a sample for you to look at as it might be something along the lines of what you are looking for. If not let me know and I'll try again.

    have a nice one,
    Bud
    Attached Files Attached Files

Posting Permissions

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