Parent and subform Linking issue

[ShaunG]

OK. What you can do is assign the job number to the Job Number field on the order entry form when it is first opened, and don't clear it when you do a save. You can prevent users from changing it by setting the "Locked" property to "Yes". If a control (field) is Locked, users can't make changes to it, but VBA can.



No - you'll need it if users are going to enter multiple orders for the same job. You don't want them having to reopen the form for each new order.

One thing you'll need to account for - if a user enters an order, presses "Save Record", and then presses "Save Record" again or presses "Save and Exit", what happens? The save procedure should check that there is data to be saved - you'll have to decide what fields have to be filled in before the record is "Valid" and can be saved.

Actually, the button you don't need is "New Entry". If you clear the data after saving (with the "Save Record" button), then you effectively have a New Entry already.

Yeah no worries sounds like that would work good..... can the same be done with the Row No and so it automatically inputs the next New No. - Once saved I want it to clear the other details so people don't save over the top accidentally.

with the save record button could that be change to a New Order button and could code work so once button is pressed the order details that were just put in are saved, the Job No is still assigned but the Row No displays the next new No and the details below are clear?

yeah I know which fields we will need to have data in it to be saved

[ShaunG]

no I don't have code Steve I put the Row No in myself at the moment, my coding knowledge is bare minimal to nothing, cheers for the help

[ssanfu]

Aha.... old school!!

[ShaunG]

and slowly achieving the goals lol

[John_G]

can the same be done with the Row No and so it automatically inputs the next New No.

Yes it can. But before giving a suggestion, there's one thing to ask - will there ever be more than one user at a time entering orders for the same job? That's an important thing to know, because it affects how and when new row numbers and order numbers are assigned.

[ShaunG]

Just to add to above question is it possible to do what I asked and also then add the Job No to it automatically so how you described Previously the Row No becomes the Order No. that way everytime the Order Entry Form is Loaded or New Entry Button is Pushed the Row No/Order No Field auto inserts 11111, 11112, 11113....

if this achievable there would be no need to have the Job No or Row No on the Order Entry Form Just the Order No

the database is going to be put on a server so that round 10 different computers can access it... generally different people aren't working on the same Job No at the direct same time so I don't think this will be an issue but can we record Lock the Job No subform so if someone is recording an Order into Job No 1111 Via the Order Entry Form... then another computer cannot open The Order Entry Form for that particular Job No, But can be open Order Entry Form for other Job Nos

[John_G]

everytime the Order Entry Form is Loaded or New Entry Button is Pushed the Row No/Order No Field auto inserts 11111, 11112, 11113....

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.

After that, every time an order record is saved, increment the row number and Order Number by 1 after saving:

me![RowNumber] = me![RowNumber] +1
me![OrderID] = me![OrderID] + 1

In all the above explanation and code, names in italics will have to be changed to correspond to the names you are using.

there would be no need to have the Job No or Row No on the Order Entry Form Just the Order No

Since the form is unbound (no record source), you do need to keep that data (Job ID and Row No) so the order number can be calculated. You could put them in form controls that are hidden (.visible = "No"), or you could use variables, both will work. I have used hidden form controls in the code above.

[ShaunG]

cheers John Im just having a issue with the union of the 2 querys it freaked out and code reset so subform isn't showing correct data, ill try sort this issue out and give the above a crack

[John_G]

...it freaked out and code reset so subform isn't showing correct data

Just a note about that - whenever you get a VBA runtime error that you have not accounted for with an error handler, you can never rely on what follows being correct; it is always best to close and reopen the form. You will also sometimes get weird error messages about Access not being able to save a record or something like that.

[ShaunG]

is there code to join 2 query's together so that even if the 2nd Query doesn't have any data in it will still show up in the union form..... the issue im having is Orders do not show up until the received section is filled in. it was working today but now the SQL code reset it self.

[John_G]

Are you really using a Union query, or are you using that term in another context? Not trying to be flippant there - just needing to understand what it is you are trying to do.

An actual Union query has specific requirements, the main one being that the number of fields (and usually their type) and their position must be the same in the queries or tables being combined. So I suspect you may want a query which joins the other two, but does not combine them into one, which is what a union query does.

What is it you want to show in the form?

Do you really need two tables, Orders and Received? Usually you would only need one, unless orders often need more than one delivery to complete. Can you provide more detsils? Specifically, if you can show the structure of the Orders and Received tables, that would be a big help.

[John_G]

the issue im having is Orders do not show up until the received section is filled in.

A common and normal situation, and easily resolved. See my previous post for some addition information/questions

it was working today but now the SQL code reset it self.

I think you mean VBA code, not SQL. It would be very helpful if you could tell us the error messages you are getting when the runtime errors happen. It narrows down what we have to look for to resolve the issue.

[ShaunG]

Are you really using a Union query, or are you using that term in another context? Not trying to be flippant there - just needing to understand what it is you are trying to do.

An actual Union query has specific requirements, the main one being that the number of fields (and usually their type) and their position must be the same in the queries or tables being combined. So I suspect you may want a query which joins the other two, but does not combine them into one, which is what a union query does.

What is it you want to show in the form?

Do you really need two tables, Orders and Received? Usually you would only need one, unless orders often need more than one delivery to complete. Can you provide more detsils? Specifically, if you can show the structure of the Orders and Received tables, that would be a big help.

yeah I am using a Union query, a mate set it up as I the coding was set up to how I explained previously where Received Order fields needed data.... the code was in the SQL view

I initially set it up as one table but when I clicked the Option Edits off so data couldn't be accidentally changed I would input the order details and then when I tried to update when received goods come in I could not update. this is why I created 2 tables Order Details and Received Details and then created Querys combining the 2 tables.....

I would prefer to have 1 table as less things to go wrong and less in the data base. (if I can do what I've ask below using 1 table ill change it straight away and I can do away with the query's)

what my ideal outcome is -

• To show the Order details and Received details all in one subform - so all in one Row.....

• Orders can be done with a Order Entry Form for the specific Job No the Parent Form is Currently on, with an automated Order No inserted (which we are working on).

• When Goods sre Received, the Order No can then be located by a filter Using the Order No... when the Order No is found they can double click on the No that will open a Received goods Entry with the No that was Clicked on Inserted Automatically allowing them to only change the Received Details.

• also though once received Details are inserted and the Order is complete, I don't want it to be able to edited....

• once I have this sorted im aiming to have an admin button protected by a password, this button to Edit any data that it needs....

Another thing I just realised generally when we Order something we may Order 2 or 3 things within 1 Order No - I will need to show each item on a separate row for ex. if we order 3 sheets of board, 2 sheets of laminate and 12 handles these items might come in on different days so need to record received for each item.

let me know if you still want to see the structure

[ShaunG]

Apologies if this is a bit of run around John..

I have just had an idea with my ordering issue, is it easier if I create a separate table for Order No's alone... then I link some how orders to an Order No with the Order No Linked Job No?

was thinking if I Had a separate column on the left of Order Details which would have any Order No's in it then when a Order No is selected the orders for that No show up in the Details

[ssanfu]

Maybe you could post an image of the relationship window???