Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need some advice a inventory database (2000)

    I have created a database with work order for mechanics. They list the parts they use and put a complete date in the work order. They are pulled the part information from a part table and it gives them the cost of the part and any other information. I would like to know how I can set up where that information never changes on that work order..If a part price changes it update the previous work orders and I do not want them to do that. Does anyone have any suggestion. The prices on the parts change daily put I do not want the work order to change.

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

    Re: Need some advice a inventory database (2000)

    Access doesn't have field-level security, so you can't lock certain fields in the table. But you *can* lock controls on the data entry form. Since your users should always use a form anyway, and not enter data directly into a table or query, that should be OK.

    I use the following method to lock controls. There was a recent thread where a Lounger had problems that may be caused by this method, but I haven't had problems with it <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>. You can lock a control if it is non-empty in the OnCurrent event of the form:

    Private Sub Form_Current()
    [txtPrice].Locked = Not IsNull([txtPrice])
    End Sub

    Of course, you can add more instructions like the above.

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need some advice a inventory database (2000)

    I went under properties and the field is locked..It is a drop box the price comes up. For example if I have a part ID 1235 and the prices is 2.25. It come up on the work order and I cannot change it. But if I go back to the table where I enter the information and change the cost to 3.25 it goes back to that work order and changes the price. I am trying to see how take old information and not change it with new information...

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

    Re: Need some advice a inventory database (2000)

    I apologize - I misread your question, so my previous reply is irrelevant.

    You will have to put a price field in the work order table. Set the Control Source of the price control on the data entry form to this field, instead of to the price field from the parts table. Use code in the AfterUpdate event of the part combo box to set this price field equal to the price corresponding to the part.

    That way, the price will be set automatically when the user selects a part, but it won't be updated when the price in the parts table is changed.

    Post back if you need more details. Somebody will no doubt help you (I'm going offline for a few hours).

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need some advice a inventory database (2000)

    On the Work Order Form The parts are on a sub form so, I went in and add the price field and did a combo box I selected table/Query and list part table as my resouce and fields I wanted. Then I went under properties and in afterupdate and chose event. I went back to the part and changed the price and it changed again...I know I am missing something. What am I doing wrong.....

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need some advice a inventory database (2000)

    I would like to thank you for your help... I am working on it.
    When I get to the query. I get a little confused.. I selected these fields

    1. Work ID.Work Order Detail Table
    2. Part ID.Work Order Detail Table
    3. PartID.Parts Table
    4. LIstPrice:Price from Work Order Detail Table

    So, far am I right

    I created relationship number 3

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

    Re: Need some advice a inventory database (2000)

    Let's see if we can get this right. This is going to be a long post, so I'll number my remarks to make it easier to refer to them in a reply.
    1. <LI>We're going to look at the tables first, the forms and subforms will come later.
      <LI>The Work Order Details table has fields (among others) for Work Order ID, Part ID and Price (having a Price field here is important). Actual table and field names will probably be different.
      <LI>The Parts table has fields (among others) for Part ID and Price.
      <LI>The Work Order Details table has a link with the Parts table on the Part ID field. Referential integrity has been set for this relationship.
      <LI>Next. we're going to the Queries tab.
      <LI>You have a query based on the Work Order Details table and the Parts table, joined on Part ID.
      <LI>The join is an outer join, set to display all records from the Work Order Details table, and only related records from the Parts table.
      <LI>The query contains all fields from the Work Order details table, and those fields from the Parts table you need in the subform.
      <LI>This query should contain the Price field from the Work Order Details table, and also the Price field from the Parts table.
      <LI>To distinguish them, insert ListPrice followed by a colon before the Price field from the Parts table, so that it looks like this:
      ListPrice:Price
      <LI>Now, we'll go to the subform containing the parts for a work order.
      <LI>The Record Source of this subform is the query just mentioned.
      <LI>The subform contains a text box bound to the Price field from the Work Order Details table.
      <LI>This text box should have its Locked property set to Yes.
      <LI>The subform contains a combo box bound to the Part ID field of the query.
      <LI>The Row Source property of the combo box is the Parts table.
      <LI>The AfterUpdate event of the combo box says Event Procedure in the Properties window.
      <LI>If you haven't done so before, click the Builder button - the three dots to the right of Event Procedure.
      <LI>The code for the AfterUpdate event procedure looks like this:

      Private Sub cboPartID_AfterUpdate()
      Me.Price = Me.ListPrice
      End Sub

      <LI>This means that when you change the part, the Price (List Price) is taken from the Parts table and copied to the Price field in the Work Order Details table.
      <LI>If you edit the Parts table later on to change the price of a part, the List Price in the query will change with it, but the Price field won't.
    I hope you're still with me... If you have questions, please refer to the numbers above.

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

    Re: Need some advice a inventory database (2000)

    The query is not quite right yet. You should have the Price fields from both tables (Work Order Detail and Parts) in the query grid. Prefix the Price field from the Parts table with ListPrice: and don't prefix the Price field from the Work Order Detail table.

  9. #9
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need some advice a inventory database (2000)

    Is there anyway that I could email you the tables and query. I know I am doing something stupid. I think I been working on this too long...

  10. #10
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need some advice a inventory database (2000)

    Try this again
    Attached Files Attached Files

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

    Re: Need some advice a inventory database (2000)

    Laurie,

    I'm very sorry, but your database left me utterly confused. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> You have created two tables with more or less the same structure, with circular lookups and without primary keys. I wasn't able to understand how it works, or was intended to work.

    Instead of trying to modify it, I have created a new database with what I think should be the key elements. I paid no attention to aesthetics, consistent naming etc.

    There are three tables: a Work Orders table with primary key WorkOrderID, a Parts table with primary key PartID, and an intermediary WorkOrderParts table that is linked to each of the other tables. This is because, in my opinion, you have a many-to-many relationship between Work Orders and Parts - one work order can involve several parts, and one part can feature in several work orders. The price charged for a part is in this intermediary table.

    There is one query, along the lines I sketched earlier, except that it is based on the intermediary and parts tables.

    To add or edit records, I have created a form based on the Work Orders table, and a subform based on the query. If you select a part from the combo box in the subform, the Price (from the WorkOrderParts table) will be made equal to the List Price (from the Parts table), but if you change the list price in the Parts table, the price in the WorkOrderParts table won't change.
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need some advice a inventory database (2000)

    I would like to thank you for your help. I went back and deleted some fields that I would not need. Would you mind if you look at the file again. The event would not work
    Attached Files Attached Files

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need some advice a inventory database (2000)

    What you should do is what Hans has suggested in the previous post, then worry about getting it to work.

    It will not work the way you currently have it, the main reason is the many-many relationship between your 2 tables.

    Pat

  14. #14
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    afterupdate field

    I went back like you said and took the relationship out and set my primary keys and did a relationship in the query. I understand what you are trying to do. I took out the fields that I am not using. The tables that I sent you I was trying different things. Would you mind giving me any other short cuts that might help me in the future. Here is a copy of the file. I am trying to learning different things. Is there a book to learn about events or that something that you just have to figure out. Thank you!
    Attached Files Attached Files

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

    Re: afterupdate field

    Hi Laurie,

    Better, but still not quite OK. Since the Parts table has Part Number as primary key, it should be in the composite primary key of the WorkOrderParts table instead of PartID (which is not the primary key in Parts now), or else PartID should be made the primary key.

    Next, you must create relationships between the three tables in the Relationships window, and set referential integrity. This means that you won't be able to create "orphan" records in WorkOrderParts.

Page 1 of 2 12 LastLast

Posting Permissions

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