Solved Help setting up a new Investment Portfolio database using normalization

[HalloweenWeed]

Thank you for your time @Pat Hartman , that means no I can't use the before update event for my purpose.
Perhaps I should rephrase. I have the following form:

and it is a "single form" with record selectors, you can either edit or add transactions. However, there are rules about what is acceptable in each field depending on the setting in other fields. These rules are too complicated to put into control validations, and even if I did that would severely limit the order that the items could be entered (entry would become a tedious process with error messages popping up). For instance, for cash-only type transactions (deposits/withdrawals) the shares must be 0 and there should not be a "security account" entry (I guess that could be ignored with that transaction type when doing the account and statistic calculations later). For dividends, the shares must be 0. For all transaction types that are not splits the numerator and denominator must be 1.

One could put the transaction type in the validation of the other controls, but this doesn't undo the settings of the other controls when a different transaction type is selected.


I don't want to need to delete the "oops" records because of shoddy validation, and it seems Access saves a new record on the first field change. I don't want the record there at all if it was not valid. How do I go about this?

I am going to try the Workspace (.CommitTrans and .Rollback) method in vba, but when do I fire my vba validation rules? If it's on close, that won't work because the user can edit a number of records before closure, that would be rude to undo all of them. The user could also edit existing records before trying to add one, in which case I would need to do .CommitTrans before the user begins making an added record, what event do I use for that? Or am I making this too complicated, is there an easier way??? I could create different forms for edit & add record...


I have never used the Workspace (.CommitTrans and .Rollback) methods before, although I have studied what I think would be the syntax.

 

[HalloweenWeed]

ABSOLUTELY NOT...

In other forms, when adding a record, when I put in one value in any field, and close the form, it saves. I don't know how to stop that save.
In the past, I have made the form work entirely in vba recordset to mitigate this. I am looking for the ability to abort the use of the form altogether (after a dialog box selection), without saving the new record, if the fields do not pass validation, before saving the new record.

 

[HalloweenWeed]

The problem is almost certainly the two subforms. I can't figure out what they are for. I'm getting an error message that I can't save a record because a related record is required in tblActiveDirectory. Looks like the relationship is backwards.

The top form is the Broker header subform. It works off the default broker (Broker table), depending on the setting of the default Broker in the database default settings table.

The other subform is the cash account, which is also set by the Broker table, according to the default broker. I yet need to add code to save that AcctID value, it messes up the subform display if I link the subform fields, so it needs to be entered on record save (vba works for me). I am actually working on replacing that subform with simple unbound fields using embedded SQL as the control source.

EDIT: No that didn't work. The subform stays there.

 

[HalloweenWeed]

The problem is almost certainly the two subforms. I can't figure out what they are for. I'm getting an error message that I can't save a record because a related record is required in tblActiveDirectory. Looks like the relationship is backwards.

OK, it works (before entry) now. IDK if it works on entry yet, not tested, not the point. But I needed to remove the linking between the master and child forms, then put in this bit of code:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()

    If txtCashAcctID < 1 Then txtCashAcctID = Me.Form.subAcctDfltCash![AcctTblKey]

End Sub

This sets the Cash Account in the transaction. The field is only ever zero on a new record.

Latest build attached.

 

[HalloweenWeed]

Put validation code in the form's BeforeUpdate event to ensure that the record will not save if it is incomplete. I don't know how else to say it. This is the instruction you have been given several times in this lengthily thread. Only YOU know what constitutes a valid/complete record. Access can't know this. If you dirty a field and erase what you typed, Access will save the record. YOU are the one who has to decide that this is invalid and the Form's BeforeUpdate event is the place to stop the save.

OK, thank you. I'm currently working the bugs out of the transaction form.

 

[HalloweenWeed]

I had to remove "enforce referential integrity" on 3 of my relationships to get the transaction entry form working (frmXactnEntryMstr):

Is this bad? I read somewhere something that made me think it was strongly inferring that all relationships should have "enforce referential integrity."

However, I still need to code the validation rules. In doing so, I see that if the form throws an error on save, closing the form without saving is possible (of course), with a couple of error dialog boxes thrown up. I've entered 10 transactions successfully (for all that's worth). I see I would want the ability to enter a new account while the transaction form is open, as I had one time the form created a new record before I was able to select the account.

Edited 8/10: added name of transaction entry form.

 

[HalloweenWeed]

Then you need to rethink what you are doing. RI is a very important aspect of proper database design.

Did you see my comment that the relationship between AcctDirectory and Xactin is BACKWARDS? That would certainly cause the form to not work correctly.

Yes, I read all comments carefully, and give them thought.
I tried reversing the relationship, tried only incl. rows where both fields equal, and still it refused to make a new record. So I had to remove enforce referential integrity, it was the only option that worked for me. Sorry, but I have to do what works, right or wrong. If you can tell me, or show me, something that works here I would be happy to fix it.

I have never completely understood the language, in the way that it applies to my tables: "include all records from tbl1 and only those records from tbl2 where the joined fields are equal." When I google it, I get too many explanations I don't understand or are the same. If somebody would just tell me: are the arrows supposed to point to the table keys, or away from them? That I can understand. I have chosen the best fit to the wording above to my best understanding. When reversing it doesn't help (and I reversed all of them, because if my understanding is backward, then all of them are backward), it doesn't matter that you stated they're backward, because it doesn't work either way. Sorry, but I will not do what doesn't work even if somebody tells me to.

Here's what I have now:

Latest build:

 

[MajP]

I have never completely understood the language, in the way that it applies to my tables: "include all records from tbl1 and only those records from tbl2 where the joined fields are equal.

These are excellent articles to help you. Please read. Explained in detail with examples. @isladogs

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

isladogs.co.uk

Note that in the Relationship builder the Join type (inner, outer, left, right) has nothing to do with referential integrity. The only purpose is for creating a "default" join to assist when making queries. It is only a default and can be changed in any query. Most people simply make this an inner join (only return records where both are equal). If you need something else in a query then fix it there.

 

[mike60smart]

Yes, I read all comments carefully, and give them thought.
I tried reversing the relationship, tried only incl. rows where both fields equal, and still it refused to make a new record. So I had to remove enforce referential integrity, it was the only option that worked for me. Sorry, but I have to do what works, right or wrong. If you can tell me, or show me, something that works here I would be happy to fix it.

I have never completely understood the language, in the way that it applies to my tables: "include all records from tbl1 and only those records from tbl2 where the joined fields are equal." When I google it, I get too many explanations I don't understand or are the same. If somebody would just tell me: are the arrows supposed to point to the table keys, or away from them? That I can understand. I have chosen the best fit to the wording above to my best understanding. When reversing it doesn't help (and I reversed all of them, because if my understanding is backward, then all of them are backward), it doesn't matter that you stated they're backward, because it doesn't work either way. Sorry, but I will not do what doesn't work even if somebody tells me to.

Here's what I have now:
View attachment 109383

I have done some work on my transaction form, and am still beta testing the workspace portion of the workings, so it may not be ready for testing relationship changes:

I always apply Option 1 in the Relationship Window.
In Queries you can apply whichever Option depending on requirements.

 

[HalloweenWeed]

These are excellent articles to help you. Please read. Explained in detail with examples. @isladogs

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

isladogs.co.uk

Note that in the Relationship builder the Join type (inner, outer, left, right) has nothing to do with referential integrity. The only purpose is for creating a "default" join to assist when making queries. It is only a default and can be changed in any query. Most people simply make this an inner join (only return records where both are equal). If you need something else in a query then fix it there.

OK, it seems my understanding of relationships was not flawed, and I didn't know about the corresponding SQL type associated, but I did perceive the similarity previously.

I always apply Option 1 in the Relationship Window.
In Queries you can apply whichever Option depending on requirements.

Thank you @mike60smart , that's very helpful.

THIS:

1. Update the data in ALL the tables to remove 0 as a value for the foreign keys (columns that point to the PK of a different table)
2. Update the design of all the tables to change the default for ALL foreign keys to null.
3. When the FK is required, set required to true. When it is optional, set required to no.
4. Remove all the join types. They are just confusing the diagram.
5. Create all the relationships that are missing. If you get an error message, you still have bad data in the table. That must be fixed before you can create the relationship.

MS has elected to make the default for all numeric datatypes 0. While this helps novices who do not understand how to handle null values, it gets in the way of creating foreign keys since 0 is NEVER a valid value for a FK since the autonumber doesn't generate a 0 value automatically.

This is what I needed to know! TY very much @Pat Hartman !
So, I take it by your instruction "Remove all the join types" you mean what @mike60smart said? Option 1? OK, sounds like a good plan. This is what I have been missing from the beginning!

 

[HalloweenWeed]

So, just to confirm I understand this correctly: I can leave indexing ON for these FK's? That won't cause a fail to make (add) a record if the value is Null?
EDIT for clarity: I mean "indexed (duplicates OK)" and field not "required."

 

[HalloweenWeed]

HalloweenWeed said:
"One thing though, I hate Access comboboxes. I tend to get elaborate with the vba to avoid their use."

Do you hate them because you don't understand how they work? They are the absolutely simplest way to select an item from a list. Do you not understand how to use a table to manage the list of items? There are things to hate about Access but I would never have thought of including combos in that list.

Just another reason I hate them: I was getting the wrong AcctID value from my AcctID combobox in frmXactnEntryMstr. It was throwing a 'entry needed in tblAcctDirectory' error. I found that I had bound the wrong column to the AcctID. This complicated matters for me when troubleshooting the forementioned problems with the table relationships, it could've been that it might have took me hours longer to debug. So I did the proper thing: I edited the "bound column" in the combobox properties (the column I needed was there). But sometimes it returned a "2" (there is no account #2) and other times (different accounts) it returned the proper account number (13, 15, or 16). It took me 2 hours of troubleshooting to figure out that nothing else was wrong, I just needed to delete the control and remake the combobox from scratch. Boom, problem solved, works fine now. #*&&(&@#((&@(&*#$ comboboxes. Grrr. FYI.

Relationship now:

Thread fin.

 

[HalloweenWeed]

NEVER add your own index for a FK. This is not clear in any documentation but Access makes its own index when you define relationships using the relationship window. If you also add an index, you end up with two. I haven't experimented with this. Access may be smart enough to not add a second index if you create the index in the table first and then add the table and relationship to the relationship window. But, if you add the index to the table after you have created the relationship, you end up with two indexes and that can cause corruption.

So now that I already have, should I turn off indexing, in (table) relationships, for the FK's?
I made the FK's indexed first, before adding the relationship(s). Except maybe CashAcctID, I'm just not sure about that one.