Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access record update from related form (Access 2003)

    This should be a simple question. I think I'm making it far too complicated. Here's my sad story:
    I have a Hazardous Waste database containing records of containers on the floor. When the containers are full, they are moved to storage. Periodically, they are shipped offsite for disposal. My database is designed to have one record per container (Drum ID is the Primary Key). A Disposal Order record is created when it's time to make a shipment. Here's where I get confused. The Disposal Order will contain the ship-to, the cost and date of the shipment, etc., and the Disposal Order number (Primary Key). The individual containers to be shipped need to be updated with the Disposal order number to indicate they have been shipped and to give me the ability to track costs, ship dates, etc. This all has to be done using forms because the end-user has no Access knowledge. The two tables are related by DisposalID. My Access knowledge, I've come to realize, is significantly worse than I first suspected. I have made this into a very confusing project with multiple subforms. There must be an easier way. Can someone help a newbie? I think a course in Access is going to be added to my list of summertime activities for this year!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access record update from related form (Access 2003)

    Please describe your form structure. Do you have one form containing information on the drum and another that processes the disposal or is everything contained on one form?
    If everything is contained on one form, couldn't you use one table for all the drum attributes? Unless the drum is shipped to storage more than once or shipped for disposal more than once, you could make the storage and disposal fields part of the drum table. Then you could see all the drum attributes, the storage data and the disposal data all on one form and eliminate the subforms. This might greatly simplify things for you. If it doesn't work, tell me why so we can try another avenue.
    Dashiell

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access record update from related form (Access

    I have a form containing the disposal information which is used to display any completed disposals. Within this form is a subform which displays the items that were included on that disposal. What I want to create is the same kind of structure for new disposals. A form which allows entry of new disposal order information, and a subform which lists all items not yet disposed of. Ideally, you'd be able to select the drums from the subform that you want to include with the Disposal you just created. Maybe I'm trying to make this too complicated. I'm thinking it should work the same way as an order table and an order detail table where you create the order and then add items to it from inventory using a subform. Should I be using a combo box to display available drums instead of a subform? I hesitate to de-normalize all that data back into a single table. I would be carrying a lot of blank fields until the product was disposed of, and that could be several weeks at a time. Because there could be several drums on each Disposal Order, it makes more sense to have this information in a separate table from a design point of view, I think.

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

    Re: Access record update from related form (Access

    The situation is very much like that for an Orders form with an Order Details subform.

    You can use the same form (with subform) for existing disposals and for new disposals. The subform could be a continuous form, with a combo box from which the user can select a drum.
    I would use an AutoNumber field DisposalID as primary key in the disposals table. If you link the main form and subform on DisposalID, it will be automatically filled in for new subform records.

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access record update from related form (Access

    Hans, I agree, but my problem is that the subrecords already exist because they were created when the drum was opened. When I put the Disposal Order as the primary form and link in the drums as the subform, Access wants to create NEW records. I just want to add the Disposal ID to the existing Drum records. I did set Disposal ID as the primary key in my Disposal table. When I link the form and subform, I can't see the existing inventory anymore because they don't have a Disposal ID yet. It's blank when they're created. I tried to use a second subform that would not be linked, but now I'm not sure how to ensure it gets updated correctly, and the form is getting awfully complex for what should be a simple process.

    I'm not sure what you mean by "The subform could be a continuous form, with a combo box from which the user can select a drum."

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access record update from related form (Access

    Something isn't set up right in your form or subform. Why not post a stripped down copy of your database so that we don't have to guess at the source of the problem.
    Charlotte

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access record update from related form (Access

    You're right. Something was definitely not right. I started over from scratch. It works perfectly now. I blew away the old one - I have no idea why it wasn't working before. Thanks to all of you for your help, though!

Posting Permissions

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