Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Access (https://www.askmehelpdesk.com/forumdisplay.php?f=441)
-   -   Form / subform and record junction table (https://www.askmehelpdesk.com/showthread.php?t=297209)

  • Dec 30, 2008, 04:17 PM
    gwallace1
    Form / subform and record junction table
    Can you have a form with a subform and have both bound to the same table? I have a junction table for entering equipment issued to an employee. I want the header of the form to have a combobox to select the employee and I want the subform to view the equipment issued and be able to add new records. I had it working... sort of, it would open and generate a duplicate record that would force me to go into the transactions table and delete it to be able to go back and open it... Not exactly sure why, unless there was something on the way the form opened... Any Ideas on how to make this work.. I am mainly looking at the layout of the form... I like the one Employee Combobox on top and then a continuous form below made to look like a datasheet that I can add/edit.. . Tables are:

    Edit.. I don't want the Employee name to be recreated for every transaction record... Which is why I attempted the form/subform look.

    Employees
    EmployeeID (pk)

    Equipment
    EquipmentID (pk)

    Transactions
    TransactionID(pk)
    EmployeeID (fk)
    EquipmentID (fk)
    TransactioDate
    QtyIssued


    Thanks.
  • Dec 30, 2008, 04:45 PM
    gwallace1

    Actually I just figured it out... I just used an unbound main form and used the afterUpdate property for the combobox to open the correct record in the subform. Now it works fine... Thanks anyway.
  • Dec 31, 2008, 09:30 AM
    ScottGem

    That can work, but using a the correct form for a main form/subform would be better. From what you explained, you want to view the Employee in the main form and their equipement transactions in the subform. That's very common.

    You bind the main form to the employee table and display whatever info about the employee thatr you want. In the form header you can put a "search combo" to select a specific employee.

    You add a subform bound to the Transactions table and linked to the main form on EmployeeID. In the subform you set the EmployeeID control's Visible property to No so you don't see that. You have a combo to select the piece of equipment. The subform will display all transactions associated with the employee and allow you to add new ones.

  • All times are GMT -7. The time now is 03:08 AM.