Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Push data from one field to another (2000)

    Hi,
    I hope I can explain this well enough so you can understand what I'm doing. Basically I want to take data from one field and push it into the same field in another table (foreign key).
    I have a main form, PurchaseRequests, primary key is the txtPRNo field. I have a subform that is based on a query that is the combination of the Parts and PRDetails tables. So bascially I have a link between the PurchaseRequests table and OrderDetails and OrderDetails and Parts. So the subform query has an outer join so it shows all parts whether there is data in the txtPRNo field or not. The subform will list the parts I'd like to order.
    The problem is the parts are already listed in the subform. Meaning I don't add new records (Parts) to the subform to order, if it were that easy I wouldn't be here. [img]/forums/images/smilies/smile.gif[/img]
    Basically I need to push the txtPRNo field data from the PurchaseRequest main form to the txtPRNo field in the subform, so I can create that link between the PurchaseRequest order and the Parts that are on the order.
    I want to push the data after I click on the Print PurchaseRequest button.

    Thanks,
    Deb

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    The typical way to solve this problem (without writing lots of code), is to use a drop-down combo to specify the part number for each order detail record - the detail record would also contain other info such as quantity, cost per unit etc. Then the order detail record also contains the txtPRNo as a linking field that gets filled in each time you create a new order detail record. It sounds as if you are trying to display all parts in the subform - or did I miss something?
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    I don't think you missed anything. I am trying to display all parts, but not with drop down. We're talking possibly fifty parts and I don't want the person to have to choose all fifty from a drop down. I have the parts being shown in the subform by the underlying query, but I need the txtPRNo from the main form to go into each part in the subform. To create that Orer link. All parts already show so technically we don't add them.
    Thanks,
    Deb

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    Is this sort of a parts explosion - somebody orders Widget XYZ, and then you build a standard list of 50 parts that always make up that product? If that's the case, there should be some sort of link already existing between the product and the parts, and all you need to do is create an order detail for each of the parts. Or am I still missing something. Perhaps you could post a stripped down copy of your database and things would be more obvious - or you could show us a relational diagram for your tables.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    I'll give a fairly quick synopsis of the database to see if that helps. I'll also work on the scaled version, it takes me a while to get rid of everything. We rebuild machines. Every machine has what we call a DPL (defective parts list) where every part that the machine needs is listed. Some of the parts we can make ourselves, fix, clean, and then some we need to order (anywhere from 30-200 parts per machine). We need to track the parts we order, what they are, how many, when they were ordered and who from. So what happens is we create this list of parts needed ordered and then we send out quotes. Once the quotes comes back we then create the Purchase Request document to send to financing to get approval then create the contract for the vendor so the parts can get ordered.

    We really don't need to track the quotes. The feature I'm having trouble with is right now I have a Quote Form. The User selects the machine from a drop down list, and then the subform will list the parts for the machine. The user will take the quote they received from a vendor and fill in the Unit price, Vendor Part#, and Vendor Delivery Date. I would then like them to click on a button, Create Purchase Request, and this will take them to another form, Purchase Request Form, where the machine will be carried over from the Quote Form and the subform on the PR Form will also carry over the parts from the Quote Form where the user filled in the Unit price, etc. The user will then fill in the txtPRNo (basically an order #) and pick a vendor and then complete the order.

    I have most of the fields carried over but I can't get the txtPRNo from the main form to populate the txtPRNo field from the OrderDetails table (which is part of the subform query) which is the link field between the main form and the subform on the Purchase Request Form. I believe the reason for this is because the parts already exist in the subform I don't have to add them, so the PRNo isn't populated and I can't figure out a way to have this happen without code.

    Does this help? Any ideas for other suggestions are more than welcome.
    Thanks,
    Deb

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    OK, I think I understand the problem a little bit better, and I see the reason for having all the parts displayed initially when getting a request for quote ready. But I don't understand why you would display them all again when you try to place an order. It seems to me you only need to display the parts that need to be replaced at that point, and you would want to create a new set of records for parts that need replacement. At that point you should be able to make an association with an internal counter of some sort, and when you place the order, the association with an order number would be a book-keeping problem of simply assigning the external txtPRNo used for paper purposes. But I've likely missed some key points along the way, so a stripped down database with only a few records would help greatly. I'm operating on hunches at this point, but this feels like a structural problem at the moment, and the best way to sort those out is to look at the database schema and see how it hangs together. See <post#=412737>post 412737</post#> by <!profile=HansV>HansV<!/profile> for details on how to do that.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    Ok, after fighting the flu for a couple days I finally got the database scaled down. The database is attached and to recap: We send out a quote, once the quote comes back we go into frmReceiveQuoteRich, find the parts we want to order (off the quote), fill in the UnitPrice, Vendor Part#, Vendor DelDate. Then I'd like to click on the Create Purchase Request button and it goes to frmPRNewRich where the Machine should be carried over and only the parts that we want to order, basically the parts where the unitprice, vendorpart#, and vendor deldate were filled in on the Quote Form. I would then fill in the Vendor I'm ordering from and the PRNo, hence placing the order.
    Thanks,
    Deb

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    OK - I've downloaded it and looked at it briefly. I'll need some additional time to study it, but it appears to me that there are structural issues with your normalization. It will be a few hours before I get back to you with details.
    Wendell

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    Wendell thanks a ton. I truly appreciate your help.
    Deb

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    Well, it took a bit longer than I expected, but here's my view of the world. It seems to me that structural problems are indeed getting in your way. I would suggest the following issues and changes:<UL><LI>Your table tblDPL seems redundant as it has a one-to-one relationship with tblMachineInformation
    Wendell

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    Wendell,
    Some of your assumptions are incorrect.
    1. The reason I have a separate tblDPL is because the machine table is going to be linked to another database to feed the machines into this database. The DPL table only lists the machines that currently have a DPL (Defective Parts List). It is possible to have multiple repair orders for a given machine.
    2. The assumption that the same part could be used on 2 different machines is incorrect. When the user fills out the DPL it is for one particular machine and all those parts are only ordered once for that machine. When another machines DPL is created all those parts are listed and then ordered (some of the parts may be the same as some others on previous DPLs but are entered again with the new DPL. This is set up because the way this company does things. Each machine has a DPL which lists all the parts associated with just that machine. The component is important, every part has a component. A DPL has many components a component has many parts.
    4. Purchase Request and Purchase Order are the same thing. It's basically the document that is used to send to financing to place the order.
    5. I don't have a separate quotes table because they don't need or want (at this time anyway) to keep track of the quotes. They fax them out and then they get faxed back, they decide who to order from based on the cheapest price. Once that's done, they then use the database to finish the order process. Plus the other problem with the quotes is an order isn't a complete quote, meaning we may on one order take only a few parts from the quote and the rest of the parts on the quote are ordered from some place else or 2 different places.

    Those are what I see just from your statements, I'll look over your attachment.
    Thanks,
    Deb

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    Wendell,
    I don't understand the reason behind the tblRepairOrders & tblPartsUsedOn tables. Also I had the tblVendor table linked to the tblPurchaseRequest table because for each Purchase Request there will be one vendor. The Vendor table was also linked to the Parts table just to show possible vendors in an earlier screen. The actual vendor ordered will be placed in the Purchase Request table.
    Thanks,
    Deb

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    Hi Deb,
    I'm not surprised - the situation you are trying to deal with is a relatively complex one as you no doubt have figured out.
    1. <LI>I presumed that the Parts List (tblParts) was the complete list of parts that make up the machine - does it just have defective parts in it? Also, if you have multiple repair orders for a machine, how do you keep the Parts Lists straight?
      <LI>This stems primarily from the fact I was expecting the parts list to be a complete list of parts for a working machine, not just the defective parts. Given that situation the structure of parts to components to DPLs makes more sense I think. Question: Do you always replace an entire component, or do you sometimes replace one or a few parts that make up a component?
      <LI>That's what I suspected - but another question: Can one Purchase Request contain parts to be ordered from multiple vendors, or from just one vendor?
      <LI>Understood - doing quotes that way is a common process
      <LI>From your more recent post, I see that you've answered the question about Purchase Requests and Vendors
      <LI>The PartsUsedOn table was based on the assumption that all parts were listed in the Parts table, not just defective ones. The Repair Orders table essentially parallels your tblDPL, but in your schema the relationship between the Machine and the DPL was a 1-to-1 because you had the same primary key in both tables.
    Now that I have a better understanding of things let me go back and look at the forms and see if there is an easy way to assign the Purchase Request Number. I think it may hinge on the issue of whether one DPL can have multiple Purchase Requests to one vendor, but I need to think on that a bit.
    Wendell

  14. #14
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Push data from one field to another (2000)

    Yes, this database has had me pulling my hair out for months, because nothing's "standard". Basically these machines that we rebuild are different every time. What I mean by that is while we may be rebuilding 2 Lathes, those lathes will need different parts replaced/repaired.
    1. The parts list is an on going list that is added to every time a new DPL needs to be created. And it is not every single part of the machine only those that need to be repaired/replaced/made/cleaned (hence the use of the tblRecommendedSource table, that idenifies what we're doing with the part, normally this table has 10 items, clean/reuse, machine shop, electric shop, oem, vendor, bearing, etc). We basically want this database to track the parts ordered and from where but we also want to know what we did with some others. They were having trouble with not getting parts ordered (they were lost or forgotten) and having duplicate orders on some parts, so I was tasked with writing this database to try and ease those problems.
    2. The second statement is correct - "do you sometimes replace one or a few parts that make up a component".
    3. No, one purchase request is one order for one machine to one vendor. A machine can have many purchase requests though.
    The last statement - a DPL can have multiple purchase requests to multiple vendors and sometimes to the same vendor. That doesn't happen a lot but sometimes they will place an order with a vendor and a month later place another for the same machine to the same vendor. That's why I had the PROrderDetails table, that was to keep track of the parts and what order they went with and the vendor was connected to the PR table to keep track of the vendor per order.
    Thanks for helping me think through this.
    Deb

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Push data from one field to another (2000)

    Hi Deb,
    Sorry for the long delay in responding.

    I think an issue here is that the PROrderDetails table is redundant. If I understand correctly, a given part is ordered only once, so you could move the txtPRNo to tblParts. (If you are splitting parts orders, then you would need a quantity in tblOrderDetails.) Then, if you want to place a subset of parts for a specific machine to a specific machine, I think you need to do that on a form with a subform which lists all the parts required for a specific machine. I would do that with a combo box which lists all the open Purchase Requests for vendors that supply a specific part. It might be possible to write a set of code which defaulted to a certain vendor, but for the initial cut, I would have the user make the choice using a combo box. Hope I haven't gone off on a tangent again - see if this makes sense to you.
    Wendell

Posting Permissions

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