Originally Posted by
ShaunG
<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
Originally Posted by
John_G
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......