Page 4 of 5 FirstFirst 12345 LastLast
Results 46 to 60 of 70
  1. #46
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    yea I meant can I create what I need now... but you answered that may need some vba...

    ill have a sus at all the names see if I can solve it.

    im having trouble with getting the form to look right but will keep on it.

    to easy mate yeah im in Adelaide... cheers for that, have a good one!

  2. #47
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Click image for larger version. 

Name:	Job3.jpg 
Views:	19 
Size:	145.6 KB 
ID:	32067Click image for larger version. 

Name:	Job4.jpg 
Views:	19 
Size:	109.8 KB 
ID:	32068

    Just an update guys I created a new database to start fresh, I think the old one had glitches from me trying to many different techniques with forms and Querys....... she works a treat, so cheers for the advise Guys.


    Now trying to get the code working on the Order Form you supplied John.

  3. #48
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Consider:
    Click image for larger version. 

Name:	Relationship1.png 
Views:	20 
Size:	137.4 KB 
ID:	32074



    An alternate design for Order Details:
    Click image for larger version. 

Name:	Relationship2.png 
Views:	20 
Size:	124.6 KB 
ID:	32075
    Maybe for "TransactionType", also have "Required for Job". Then field "Qty_for_Job" could be deleted.
    For 1 item, there could be 3 records:
    Required for Job
    Ordered
    Received.



    Do you ever order 50 of something, you receive 30 and 20 are back ordered (received at a later date)?



    My $0.02...........

  4. #49
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Micron View Post
    Not sure I get it. Type in where?
    You just opened it, so it's blank, so send what?
    Are you asking for the job number to automatically enter into the variation form when opened?
    Make the new variation record show on the main form's subform when the variation form is closed?
    The latter is a matter of requerying the subform. The former can be passing the job number to the variation form via its OpenArgs; or setting a variation form control value via main form code before making the variation form visible.
    what would the open argument look like.... I had it on the last database some how but have since deleted that ive given it a shot but cant work it out =[Job_no]"[Job_No] how far off am I?

  5. #50
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    yeah you are correct we do have back order of things I was worried I was putting to much on the database.... if I can add back order to list without making it to big that would be pretty good.

    this is basically a quick reference for employees in workshop and office to look up a job as easy as possible so they can see the orders for material or hardware for a job they working on and also the progress of it, im trying to make it as simple as possible most of these guys working here are computer illiterate, everyone needs to be able to use it for it to work... and this is the first computer database this place has seen.

    example.... mark could order 10sheets of board for job 1111 on the 9th of jan, the job may need 9 so we know we have 1 left over.... Im trying to track the date the order comes in, who receives it and the amount that comes in as well


    Required_For_Job sounds good just trying to keep the fields width as small as possible...

    For 1 item, there could be 3 records:
    Required for Job
    Ordered
    Received.


    Im trying to keep these records inline so the orders are easy to read by staff.... the picture I posted in post 47 im pretty happy with the way this getting set up, definitely open to ideas

  6. #51
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    not sure if I need a table for builders the only reason I have employee, hardware/material, suppliers is so they can be selected in a lookup table when placing an order and recording receiving details.

    I changed that name from Order_Num to WorkOrder_No its a number the builders give us with variations... if we don't have it and give them the bill for the variation we generally don't get paid as its out of the contract.

    I havent added an product description because that can be added in colour/finish or part no/type

  7. #52
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    not sure if I need a table for builders the only reason I have employee, hardware/material, suppliers is so they can be selected in a lookup table when placing an order and recording receiving details.
    Just a thought... how many builders/supervisors do you work with?

    I changed that name from Order_Num to WorkOrder_No its a number the builders give us with variations... if we don't have it and give them the bill for the variation we generally don't get paid as its out of the contract.
    Ah-ha..... understood...


    I havent added an product description because that can be added in colour/finish or part no/type
    Remember - one "thing" in one field.
    If you do something like
    Field "Colour_Finish" = "Glossy black/ this is for the base cabinets for the kitchen" violates normalization rules.!
    And actually I would have two fields: one for "Colour" and one for "FinishType".



    Remember, these are just suggestions.......
    Last edited by ssanfu; 01-12-2018 at 02:16 PM. Reason: spelling

  8. #53
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by ssanfu View Post
    Just a thought... how many builders/supervisors do you would with?


    Ah-ha..... understood...



    Remember - one "thing" in one field.
    If you do something like
    Field "Colour_Finish" = "Glossy black/ this is for the base cabinets for the kitchen" violates normalization rules.!
    And actually I would have two fields: one for "Colour" and one for "FinishType".



    Remember, these are just suggestions.......
    probably round 35-50 builders and their could be anywhere from 1 supervisor per builder to 15-20 supervisors depending on the company size.... the main reason I had builder field on the form was in case down the track need to look the job up and can only remember the builders details we can filter it.... the reason I put site supervisor and their ph no was for a quick reference for some one to look up details to call for questions if needed.

    yeah I can see what you're saying with colour_finish Ill change that to Colour_PartNo and then Finish_Type


    can you help me out with the openarg meant so when I open my order form it auto assigns the job no to it =[Job_No]"[Job_No] I know im wrong I just don't know where?

  9. #54
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ShaunG View Post
    <snip>can you help me out with the openarg meant so when I open my order form it auto assigns the job no to it =[Job_No]"[Job_No] I know im wrong I just don't know where?
    Not sure what you are talking about - I think I missed a few posts.


    In Post #22John wrote
    Quote Originally Posted by John_G View Post
    Yes, doable and not difficult.

    When you click the button to open the data entry form, include the Job ID in the OpenArgs argument as I mentioned earlier.
    If you are unsure as to what that should look like, please post the DoCmd.Openform line that the command button uses, and I'll fix it for you.)

    Now, onece you have the JobID, you need to find the current maximum row number for that job number in the Orders table, and add 1 to it for the new row number. You can do this with the DMax function:

    NextRow = nz(DMax("RowID", "Orders", "JobID = " & [JobNumber]), 0) + 1 where:

    NextRow is the (hidden) control for the row number on the form
    RowID is the field in the Orders table tat you want to return the maximum value for
    Orders is the name of the table containing the orders data
    JobID is the field in the orders table containing the Job Number
    [JobNumber] is the name of the (hidden) control on the form containing the Job Number selected on the main form

    This assumes that you keep the Job number and the row number in the orders table.

    To start off, in the On Open event of the subform, you would do this:

    me![JobNumber] = me.Openargs
    me![RowNumber] = nz(DMax("RowID", "Orders", "JobID = " & [JobNumber]), 0) + 1
    me![OrderID] = me![jobnumber] * 10 + me![rownumber] (10 assumes no more than 9 orders per job; if more than 9, multiply by 100 instead of 10)

    That initializes the data entry form for the first new order.
    I think he explained it pretty well.


    But.... you have a button(?) to open the data entry form. The code on the button click event should be something like
    Code:
    DoCmd.OpenForm "YourFormName", , , , , , Me.Job_No
    Then in the Form Open event of the data entry form you would have code like what John provided.


    BTW, It would be easier for all involved if you posted your dB... maybe with just a couple of test records. Change any sensitive data......

  10. #55
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by ssanfu View Post
    Not sure what you are talking about - I think I missed a few posts.


    In Post #22John wrote

    I think he explained it pretty well.


    But.... you have a button(?) to open the data entry form. The code on the button click event should be something like
    Code:
    DoCmd.OpenForm "YourFormName", , , , , , Me.Job_No
    Then in the Form Open event of the data entry form you would have code like what John provided.


    BTW, It would be easier for all involved if you posted your dB... maybe with just a couple of test records. Change any sensitive data......
    yeah John has explained that well mate I haven't got to the part yet... its the first step lol the DoCmd for the button that im having trouble with.... Its quite painful for me to get my head around where and what code to use so I appreciate you're guys patience!

    The First database I created I somehow Fluked the code in the where Condition behind the Macro similar but different to what I posted above(actually it was somehow inserted automatically and I ended up using it for other 2 the buttons).... but I deleted the buttons trying to get rid of a error that was coming, I couldn't fix it so that's why I started this fresh one.

    ill post the macro.. no problems posting the database I have no information in it at the moment... is it just the tables you wanna see?

  11. #56
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Attachment 32087

    the macro behind the button that opens the order entry form

  12. #57
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Attachment 32088

    This is the Job_Details Table.... is this the info you are referring to when ask to post database?

  13. #58
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Click image for larger version. 

Name:	Job3.jpg 
Views:	12 
Size:	117.8 KB 
ID:	32089

    this is the orders Table

  14. #59
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    NextRow = nz(DMax("Line", "Orders", "Job_No = " & [Job_No]), 0) + 1 where:

    this is what I come up with.... where do I insert this?

  15. #60
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    this is what I come up with.. getting comfused with JobID and jobnumber

    Job_No is assigned to every job I use, this + Line (used to be Row_No) is how I get my Order_No.... each Order_No can have multiple items from a supplier which is how John advised and I like the idea.

    me![Job_No] = me.Openargs
    me![1] = nz(DMax("Line", "Orders", "Job_No = " & [Job_No]), 0) + 1
    me![Order_No] = me![Job_No] * 100 + me![1]

    me![JobNumber] = me.Openargs
    me![RowNumber] = nz(DMax("RowID", "Orders", "JobID = " & [JobNumber]), 0) + 1
    me![OrderID] = me![jobnumber] * 100 + me![rownumber]

Page 4 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking Subform to Form issue
    By dweekley in forum Forms
    Replies: 9
    Last Post: 04-28-2017, 06:44 PM
  2. Linking Parent ID Autonumber with subform
    By banpreet in forum Forms
    Replies: 5
    Last Post: 12-19-2016, 02:54 PM
  3. Replies: 1
    Last Post: 10-04-2016, 04:29 PM
  4. Subform linking issue
    By buckwheat in forum Access
    Replies: 3
    Last Post: 08-28-2013, 01:53 PM
  5. SubForm to Parent
    By henryclay in forum Forms
    Replies: 2
    Last Post: 11-15-2006, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums