Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014

    Unanswered: Copy filed value from one table to another

    I have create a form to enter information into material sheet usage table. The information being entered is Date, Job Number, Employee and Qty. The Cost and Description need to come from the tbl_material table.

    The tables are joined by MatID
    Click image for larger version. 

Name:	Join.JPG 
Views:	5 
Size:	29.9 KB 
ID:	15918

    The Design of form
    Click image for larger version. 

Name:	design.JPG 
Views:	6 
Size:	256.0 KB 
ID:	15919

    The user form
    Click image for larger version. 

Name:	form.JPG 
Views:	4 
Size:	51.0 KB 
ID:	15920

    The top part has the data entered after entered it will show up in lower area.

    How do I transferred the two values from one table to another?

    Thanks for your help

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 23
    Make an Append query, using the choices made on the form.
    insert into targTable select fields from srcTable where [id] = forms!frmPick!txtBox

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    does the description of the material change over time?
    if not then pull the description as required in a query using a JOIN

    at first glance you could convince yourself that the cost could also be brought across as required, but Im guessing cost will change over time, so you need some form of storage for the cost at that point in time.

    whether you do that as a column in materials sheet usage or model some cost based on time as a sub table in tbl materials is up to you

    what you could do is when using the form is to stuff the material cost into the combo as a hidden column, which coiuld be a neater way of doing it.
    OR just use dlookup to retrieve the cost in the combo's afterupdate event
    certainly that will be easier than using a hidden column inside a combo.

    incidentally you need to think carefully about your naming
    don't use reserved words, it can and probably will bite you int he backside at some stage.

    avoid using spaces in column/table names. if you want soemthign thats pretty on the eye set the columns caption property at design time. be consisten use either all lowercase_separated_by_underscore Or CamelCase
    use abbreviatiosn where appropriate but retian the legibility

    sales tax...
    how likely is sales tax to change over time. if it does and you need to monitor that then you need to define the sales tax applicable at a specific point in time, or time period. eg sales tax 1.25% from 01 Jan 2013 to 31 Aug 2014
    1.5% from 01 Sept 2014 on

    your design will beneift from understanding normalisation


    in a properly normalised db I would not expect to see stock as part of the material definition. current stock is no an attribute of the material. how you model it is upto you, but usually I'd expect to see transactions
    x qty delivered 01/09/2014
    Z qty to shop floor
    Y qty to shop floor
    w qty rejected and returned to supplier
    ... and so on

    dont' store derived data
    either store markup rate or sell price, but not both (one can be derived from the other + purchase price
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2014
    The description and cost will not change after they are entered into material sheet usage table. That will be a permitted record for that job.

  5. #5
    Join Date
    Oct 2014

    Ok want both value to be permanent. They are set for life.

    Now I build a Dlookup and it does work. but how do i get that value in the other field?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	3 
Size:	317.5 KB 
ID:	15925Click image for larger version. 

Name:	Capture.JPG 
Views:	3 
Size:	317.5 KB 
ID:	15925

Posting Permissions

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