Parent and subform Linking issue

[ShaunG]

I have hidden some tables, it's pretty messy with me trying different ways to get what im after. Ive added in the table with received and ordered goods together...

[ssanfu]

Shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names. You have "/" and "&" in field names.
Example: "Colour / Finish": Better would be ""Colour_Finish" or "ColourFinish"

"ROW" and "DESCRIPTION" (as well as "DESC") are reserved words in Access and shouldn't be used for object names.

[ShaunG]

no worries Steve, I have sort the naming out and got rid of the querys and their relationships will change the field names to

[ssanfu]

Hmmm, stills shows spaces and the special characters in field names
Reserved words "Row and "Description" in table "Variation_List" still in last image.......

It is just easier to fix now that later on..... but its' up to you....


'Nuff said.....I'm off the soapbox...

[ShaunG]

cheers, that should do it!

[John_G]

I initially set it up as one table but when I clicked the Option Edits off

"Option Edits off" - where are you seeing that? I can't find anything in table properties. Are you talking about a form property?

I tried to update when received goods come in I could not update.

That I think is related to the above comment, and is a form issue, not a table issue.

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.

In that case, you are going to need a new table - OrderDetails. This table will contain one record for each row (line item) on an order, and the received data can also be in that record. The Orders table will have no data regarding the line items, or when items were received.

So what you have is this: A job has one or more orders which have one or more detail lines. 3 levels - not unusual at all.

You would handle the orders - order details data entry in much the same way as you do the Jobs - Orders. If you are up to the challenge, you can even show the order details data on a sub-sub-form, within the orders subform. It will work, if you keep the link fields correct (and you have the screen space to show it all).

But you are on the right track with the ideas you have put forward in post #28.

I'll have a look at the relationships diagram, and let you know what might need adjusting.

[John_G]

There's not much wrong with the diagram, but I suggest these changes:

• Remove Job_No from Order_Received_Goods. It is redundant, and the job number can be found in the Order_Sub_Orders table
• Move Supplier from Order_Received_Goods up to Order_Sub_Orders. Any one order can only be to one supplier (I hope)
• Order_Received_Goods should have a PK defined - I would use Order_No + Item_No (called a composite key). There are many who would disagree with me there, and say you need an autonumber as a PK, but IMO since you are not linking Order_Sub_Orders to anything else "down" the structure, it doesn't matter.
• It doesn't show in the diagram, but the ID fields in your lookup tables should have unique indexes on them, to prevent accidental duplication of ID's. Making the ID the PK for those tables will accomplish that.

You have Variation_List linked directly to the Job_Details table, but does a Variation really apply to a Job, or does it apply to an Order_Received_Goods record? I ask that because you have Order_No and Item_No in Variation_List, and both of these are in Order_Received_Goods.

(Just as an aside, I would rename Order_Received_Goods to Order_Details - it makes it clearer what it is - but that's just me).

You're on the right track - let us know how it goes.

[ShaunG]

Correct John the option is in the Form Property.


cool.. that's why I created Order_Sub_Orders as a new table I can rename it to (OrderDetails)?

def up for challenge mate im sure I can get to work.


cheers mate

[ShaunG]

There's not much wrong with the diagram, but I suggest these changes:

• Remove Job_No from Order_Received_Goods. It is redundant, and the job number can be found in the Order_Sub_Orders table
• Move Supplier from Order_Received_Goods up to Order_Sub_Orders. Any one order can only be to one supplier (I hope)
• Order_Received_Goods should have a PK defined - I would use Order_No + Item_No (called a composite key). There are many who would disagree with me there, and say you need an autonumber as a PK, but IMO since you are not linking Order_Sub_Orders to anything else "down" the structure, it doesn't matter.
• It doesn't show in the diagram, but the ID fields in your lookup tables should have unique indexes on them, to prevent accidental duplication of ID's. Making the ID the PK for those tables will accomplish that.

You have Variation_List linked directly to the Job_Details table, but does a Variation really apply to a Job, or does it apply to an Order_Received_Goods record? I ask that because you have Order_No and Item_No in Variation_List, and both of these are in Order_Received_Goods.

(Just as an aside, I would rename Order_Received_Goods to Order_Details - it makes it clearer what it is - but that's just me).

You're on the right track - let us know how it goes.

sorry I didn't under stand what you meant to begin with im changing the name Order_Received_Good to Order_Details make sense

correct one order can only have one supplier.... will move over.

the Variation order No is a different order no that the builders give us as a record for when we bill them

im mucking around with 1 to many relationships.. and is doing my head in do I need to be?

[ShaunG]

starting to get the picture I think.. lol

[John_G]

starting to get the picture I think.. lol

Great pun!

Yes - looks good.

You forgot to take supplier out of Order_Details.

I think you should move Date_Ordered and Ordered_By from Order_Details to Order_Sub_Order as well, since presumably any one order would be placed by only one person, and all on the same date. It also reduces the chance of error from having to enter the same data for every order detail line.

I think you have a pretty good handle on what your business processes and rules are, and what your forms to implement them should look like, so all that will matter are small details here and there (and maybe the occasional goofy VBA error?)

Good luck with your project.

[ShaunG]

Great pun!

Yes - looks good.

You forgot to take supplier out of Order_Details.

I think you should move Date_Ordered and Ordered_By from Order_Details to Order_Sub_Order as well, since presumably any one order would be placed by only one person, and all on the same date. It also reduces the chance of error from having to enter the same data for every order detail line.

I think you have a pretty good handle on what your business processes and rules are, and what your forms to implement them should look like, so all that will matter are small details here and there (and maybe the occasional goofy VBA error?)

Good luck with your project.

ahh Yes I just seen that!

makes sense moving the dates and ordered by as well.

starting to.... so can I create the subforms with what I have now or does it need VBA?

cheers for the help John

[John_G]

can I create the subforms with what I have now or does it need VBA?

Not sure what you mean by that. You can always create subforms with no VBA code in them, but they would be limited in what they can do. You almost always need some VBA for calculations (e.g. generating order numbers), data validation, error messages, turning properties on and off, and the like. In your case you use it to actually save data to the tables, since some of your data entry forms are unbound. Command buttons always have VBA (or a macro - ugh!) behind them. How much VBA you use depends on the complexity of your project, and how rigorous your validation and processing is.

[ShaunG]

is this how it should look.... looks back to front to me?

I also get a parameter message for Job No.. is this normal?

[John_G]

I also get a parameter message for Job No.. is this normal?

Sometimes, but in your case, no. I think you have a query somewhere (a form recordsource, maybe) that is referring to a field called Job No, which you have changed to Job_No, so the query prompts for the value.

That's a caveat I forgot to point out - when you change a table field name, you have to remember to fix any query that uses that field - Access doesn't make the changes for you. (There is an Access option called name autocorrect which is supposed to be able to do this, but many gurus recommend that it be turned off).

is this how it should look.... looks back to front to me?

I'm not sure what you have there. You have the Job_Details table open by the look of it, and it is showing you the related tables Order_Details. That's a "feature" (and I use the term loosely) that I never use and know very little about. I know it can be turned off, but I'm not sure how. But your screen is not showing you a form, that's certain.

I don't know what time zone you're in, but I'm in Eastern, so I'll look in tomorrow evening.