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

    Unanswered: Pass Data between FORMS - Use key field to bring up list of records

    I have a form that has a list of records and at the end of the list each record contains a button

    like this

    ---- ----- ----- ----- ---- [GO TO FORM 2]

    ---- ----- ----- ----- ---- [GO TO FORM 2]

    ---- ----- ----- ----- ---- [GO TO FORM 2]

    ---- ----- ----- ----- ---- [GO TO FORM 2]

    I want to ONLY get records from table2 (using form2) that match ONLY that single record's key field...

    when I get to form2, it gives me ALL records that have any match for the key field between form1's foreign key and form 2's primary key...

    MY question is this:

    HOW DO I FILTER OUT THE RECORDS SO I ONLY GET THE ONES FROM TABLE2 (USING FORM2) THAT MATCH THE SPECIFIC RECORD I WANT FROM FORM1 (I am not sure if I need to do something with the button in form 1, the query used in form 2, the VB code in form 1, etc)

    HELP!!??? been stuck on this for DAYS now, putting it off and working on other things...... THANK YOU FOR YOUR HELP... if you are nearby, I'll buy you lunch for helping me..... at the very least.... Jon

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    can u show us how ur tables are set up and what the forms are based on AND what the list is based on.

  3. #3
    Join Date
    Oct 2004
    Posts
    20
    table is

    CUSTOMERS TABLE
    customerID (Prim Key)
    customer_name
    addr
    city
    state
    zip

    PROJECTS TABLE
    projID (prim key)
    customerID (relationship added in diagram to customerID in CUSTOMERS)
    proj_descrip

    Form1 is just a simple form displaying customer info as follows

    customer name addr city state zip (below is a button on form)
    Joe Smith 2 Elm St SD CA 92111 [VIEW/EDIT PROJ INFO]
    Joe Smath 6 Elm St SD CA 92111 [VIEW/EDIT PROJ INFO]
    Joe Smeth 7 Elm St SD CA 92111 [VIEW/EDIT PROJ INFO]
    Joe Smoth 8 Elm St SD CA 92111 [VIEW/EDIT PROJ INFO]

    I want the button to display all the projects for Joe Smith (all with his customer ID that exist in the PROJECTS table) - currently it keeps displaying ALL projects in the projects table that have any matching key to ANY record in the CUSTOMERS table.....

    THANKS FOR THE HELP - simple problem, but I am new to ACCESS and can't seem to figure it out.....

  4. #4
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    i think i get it now. there are a couple of ways to do this (at least).

    - first create a query that shows all the data that you want to see from the project table (at this point check that it works. it should show ALL data from your project table for the fields you've chosen).
    - next, in the same query, set *criteria* for the customer id (FK) field:
    [forms]![frmcustomer]![custID]
    (use your names of your customer form and custID field)
    - create a form based on that query.
    - your button on the customer form should open the *customer* form *Hidden* (don't close it! your project form needs to see the custID! (maybe that was the prob??)) then open the project form (based on your query)
    - to go back to your customer form: a button on your project form should close the project form and open the customer form *normal* (not hidden)

    - you could also create a subform.
    - create a projects form with the same query above but *without* the criteria (it is not necessary)
    - on your customer form add a subform control and make the your Project Form the source of the subform control
    - this way you can see the customer info and his/her project info at the same time

    is this want you need?

  5. #5
    Join Date
    Oct 2004
    Posts
    20

    Still a bit confused opening FORM with data from ANOTHER FORM

    I do have a query created - and it does work and pull ALL records from the projects table....

    when you say "set criteria" on the query - I am not sure where or how to do that....

    I am also not sure how to open the new form with the other opened *hidden*

    I open the form by calling an onClick Macro that calls "openform" with the form name in the field "formname"

    should I even be using a macro?


    I have this in the code for the button..... does that help you in helping me? I hope so - I am new at my job and I feel stupid not being able to do something so simple for a few days.... no Access experience, but either way I'd like to produce since I am new.... thanks again for your help...


    Private Sub Command16_Click()
    On Error GoTo Err_Command16_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmProjectsByCustomer"

    stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
    DoCmd.OpenForm stDocName, , stLinkCriteria, stLinkCriteria

    Exit_Command16_Click:
    Exit Sub

    Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click

    End Sub

  6. #6
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Quote Originally Posted by jumboshrimp
    I do have a query created - and it does work and pull ALL records from the projects table....

    when you say "set criteria" on the query - I am not sure where or how to do that....
    In query design, on the bottom half of the query screen, 5th line down, it says "Criteria". enter your criteria there.

    Quote Originally Posted by jumboshrimp
    I am also not sure how to open the new form with the other opened *hidden*
    also in query disign, bottom half of screen, the last line: window mode: select "hidden".

    Quote Originally Posted by jumboshrimp
    I open the form by calling an onClick Macro that calls "openform" with the form name in the field "formname"

    should I even be using a macro?
    macro is fine. but you say you have code for the button? you use either code or a macro. if you use a macro attach the name of the macro to the OnClick event for the button. if you use code the OnClick event for the button will say Event Procedure and will use code. is suggest using a macro for now. later you can convert the macro to code.

    i'm pretty sure you don't want LinkCriteria for now.

    w

    Quote Originally Posted by jumboshrimp
    I have this in the code for the button..... does that help you in helping me? I hope so - I am new at my job and I feel stupid not being able to do something so simple for a few days.... no Access experience, but either way I'd like to produce since I am new.... thanks again for your help...


    Private Sub Command16_Click()
    On Error GoTo Err_Command16_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmProjectsByCustomer"

    stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
    DoCmd.OpenForm stDocName, , stLinkCriteria, stLinkCriteria

    Exit_Command16_Click:
    Exit Sub

    Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click

    End Sub

Posting Permissions

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