VBA for entering record or passing field values between forms

[lefty2cox]

Thank you also CJ for the file. I'll be opening that later as well.

I was playing around with the file and am getting the picture. Thank you. I was just wondering; simpleCosting query has a table "M". What is the purpose of that and where does it come from? Also, I noticed tblTransactions has a PK and FK for itself and that there is a relationship set up for the table to itself.



I've never seen these two techniques and was wondering their significance. I did find some information on self-referencing tables and am trying to comprehend that in general.

I'm guessing the "M" in the query is an alias for another table? Or part of a table? Thanks.

[CJ_London]

M is an aliased query - a bit like a sub query. View in the sql window and you will see


RIGHT JOIN ((SELECT tblTransactions.tranPK, tblTransactions.itemFK, tblItems.itemDesc FROM tblItems INNER JOIN tblTransactions ON tblItems.itemPK = tblTransactions.itemFK WHERE (((tblTransactions.TranTypeFK)=5))) AS M

it is used to identify machines - hence the M

I often alias table names when they are long (to make them shorter) or when you are using them more than once in the same query to give an indication of what it is returning

PK=Primary Key (every table should have one)
FK=Foreign Key used in the child table to identify it's parent

In this case the FK is pointing back to the original purchase so it is easy to see all the transactions relating to that machine purchase. I could have used an itemFK to point back to the item instead but I had already set down that path

self referencing tables have plenty of uses to create 'layers'. Building back

child>parent>grandparent>great grandparent>etc

They are all people so you use a single table to identify the next layer up - otherwise you would have to have separate tables for child, parent etc

[lefty2cox]

This is the kind of stuff I was talking about. With all keyboard time I have with this application, I've never even heard of a relating a table to itself. Seems super useful.

[lefty2cox]

I noticed in the file that there are if statements without an "end if". I've always gotten an error without the "end if". Is it not necessary?

[CJ_London]

you don't need an 'end if' if the statement is all on one line

if something=true then doThis

or

if something=true then
    doThis
end if

[lefty2cox]

Thank you. I have another remedial question that I'll try to articulate. When setting up relationships in the relationship's window; In the past I would add a table to the layout and have more than one relationship where that table was the parent. But I know you can add multiple copies of a table to the relationship window and set each of them separately. What is the difference, if any? If none, is one way better than the other?

[CJ_London]

The relationships window is just a graphical view of the relationships table so sometimes does not meet your expectations. So really doesn’t make a difference. If you have two tables linked on several fields to form one relationship then you only need one of each table. If you have a table that links to another table for different records then you need two instances of the other table

[lefty2cox]

I noticed there was an invisible textbox on frmTransactions with the value =[parent].[cboitemtype]

Is this just to make it easier when you were writing the code?

Also, in the relationship I was asking about with transactions relating to itself, are you using code to populate tranFK in the tbltransactions?

[CJ_London]

noticed there was an invisible textbox on frmTransactions with the value =[parent].[cboitemtype]

It was left over from developing and can be removed. The 'operational' one is in frmItems

Also, in the relationship I was asking about with transactions relating to itself, are you using code to populate tranFK in the tbltransactions?

yes - see the cboTranType_Exit sub (in frmTransactions)

[lefty2cox]

Thank you.

I noticed that in tblTransactions, which has several FK's, ItemFK and TransTypeFK had a default value of "0" while BuyerFK, SupplierFK, and tranFK had no default value. Is that by design?

[CJ_London]

the no default values are by design - in a relationship, every FK must have a value that exists in the related PK if you are enforcing referential integrity. If it doesn't, you get an error (and you don't get a 0 autonumber unless you force the issue by inserting a record using sql). However you can leave it as null and that can still be saved - simply a 'parent' hasn't been allocated which is different to a parent doesn't exist. For those fields, not every column need to be completed - it depends on the transaction type as with which columns are completed.

the transtypeFK I left as 0 which is the default for number field types since that field has to be completed in order to be able to complete the rest of the record. However I could have set it to no default.

Just to remind you, this is an example. It is not complete - some fields aren't indexed, not doubt other fields will be required and the concept may still not meet your actual business needs.

[lefty2cox]

Ah. I may get it...Then again, I may not. So for instance, a transaction such as issuing a part would not require a parent for BuyerFK or SupplierFK? But TransTypeFK and ItemTypeFK are required on all transactions? And by putting a default value of 0 on those, you force an error in the event they are not completed? Whereas, if BuyerFK or SupplierFK is null on say a transaction for issuing a part, all is well?

[CJ_London]

sounds about right

[lefty2cox]

This was all very helpful. Special thanks to CJ for the file. I've been picking at it and learned a lot, which has been very helpful as I move into yet another iteration of my project.

I have a new question now. Apologies if I should have started a new thread. Let me know if that's the case.

I have a set up now where I'm using a parts table with a parts transaction table using the same basic structure suggested. I've decided to include price/cost in the transactions table rather than in the parts table. I'm trying to build a query that will show me the average price/cost paid for each part. I can't figure it out.

The fields for each transaction are PartTransQty and PartTransPrice. My gut tells me I have to have an expression in which one of the variables is another expression, but I may be overcomplicating it. And then I need to group by the PartPK.

Thanks

[lefty2cox]

I think I sorted it out. A whole hour trying to figure it out and then right after I post, I figure it out. Isn't that always the way?