Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    relating form and subform (Access 2000)

    I want to add on additional information to my form Customers and that is about rigs.I imagine the form Customers in such a way as to show the rigs for each customer.Thereforre i have made a new table called rigs and tried to relate it with the table customers.But how to connect these two tables and how to open the form rigs from the table Customers so that to be always related to the opened customer.Will you help me ? In my attachement i have sent what i have tried to do.

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

    Re: relating form and subform (Access 2000)

    The easiest way is to place frmRigs as a subform on frmCustomers. Access will automatically link them on CustomerID.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relating form and subform (Access 2000)

    Thank Hans.I already have a subform there, therefore i tried with a popup form.I put the folllowing in the OnCurrent event :
    Dim stLinkCriteria As String
    stLinkCriteria = "RigID = Forms![frmRigs]!RigD"
    But it seems not to have any efect when i choose difefrent customers.Does it ?

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

    Re: relating form and subform (Access 2000)

    No, that won't have any effect, since stLinkCriteria is only known within the On Current event, and it is not used elsewhere. You should define stLinkCriteria in the On Click event of the command button and use it in the DoCmd.OpenForm instruction.

    The way your tables are set up, a customer can have only one rig, and one rig can have several customers. Is that what you want?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relating form and subform (Access 2000)

    No,i do not want that but really i am confused as to what i want. So allow me to explain in other words.If i open on Customer 1, then i want to write on the pop form what kind of rig he has, the maturity date, the sum, etc.
    When i open on customer 2, then again i i could write on the pop form the rig he has, the conditions, etc.So for each customer i must be able to enter the information about the rigs this customer has.This is my task, to gather information about the rigs available in the customers.I am not sure shall i have 2 tables at all, or shall i gather the whole information on the table Customers.I think i have grave errors even in the table designs too.What do you think ?

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

    Re: relating form and subform (Access 2000)

    The first question you should ask yourself is whether one customer can have more than one rig.

    If not, i.e. each customer has only one rig, and one rig belongs to only one customer, there is no need to have a separate Rigs table. You can add the fields to the Customers table.
    You could put a tab control on the Customers form, with the general custom information in the first tab page and the rig information in the second tab page.

    If a customer can have more than one rig, you shouldn't have a Rig field in the Customers table. Instead, link the tables on the CustomerID field.
    In this situation, place the Rigs form as a subform on the Customers form.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relating form and subform (Access 2000)

    Thank you so much Hans! Now it is all clear to me. I do not need another table at all.I will put all the data on the table Customers
    Peljo

Posting Permissions

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