Problems with joins/links subforms/main form

[lford91]

I am struggling with a db and wanted to know if anyone could help me with the last few problems that I can't figure out. The more I try to fix them, the more confused I get. I don't really know code and have cobbled together what I could to get stuff to work. Seems to go along good and then hits a snag.

Right now the two problems I have are:
1. Getting joins right between tables for subforms.
2. Getting RI cascade update/delete right and programming delete button on subform to only delete record on table for subform, not whole main form.

I'm attaching the db. I'm trying to get everything working on frmMAIN

Any help would be so very appreciated! THANKS!

I'm running Access 2013.

I can't get the db to attach. Giving me error "Your submission could not be processed because a security token was missing". I could email it or will wait to hear back from administrator on why it won't upload. This is the response I got: Delivery to the following recipient failed permanently: (email of jon at site) Technical details of permanent failure: Google tried to deliver your message, but it was rejected by the server for the recipient domain access-programmers.co.uk by access-programmers.co.uk. [199.193.246.121]. The error that the other server returned was:
550 Mailbox quota exceeded. What now?

I am thinking the problem lies in relationships between three tables:
tblFamily -- PK is FAMID
tblAddressFamily -- PK is ADDRFAMID, FK is FAMID
tblIndividual -- PK is INDIVID, FK is FAMID

frmMAIN is for a church directory/db. Tabs are for things like individual's info, family info, family address, individual address, phone#s, notes, etc... Right now the one not working is the subform that does family addresses. I can't get the relationships right to work. Ideas??

 

[JHB]

Maybe you should convert it to MS-Access 2010 version to get more help.
Have you zipped the database?

 

[lford91]

It gives me the message attached.

Is there an easy way to find what is preventing the change to an earlier version so it can be changed enough to share at least what would be left?

I tried a zipped file and it gives me the same error. ???

Here's what I'm working with and struggling with links/joins:

tblIndividual
PK=INDIVID
FK=FAMID

tblFamily
PK=FAMID
FK=INDIVID

tblAddressFamily
PK=ADDRFAMID
FK=FAMID

The main form (frmMAIN) gets its data from a query that I've tried with tblIndividual and tblFamily together and another time tried with adding in tblAddressFamily also. The query works but when I add tblAddressFamily it wants to be not editable.

The subform (subADDRFAM) gets its data from:
SELECT tblAddressFamily.ADDRFAMID, tblAddressFamily.FAMID, tblAddressFamily.FAMADDRTONAME, tblAddressFamily.FAMADDRTYPE, tblAddressFamily.FAMADDR1, tblAddressFamily.FAMADDR2, tblAddressFamily.FAMADDR3, tblAddressFamily.FAMCITY, tblAddressFamily.FAMSTATE, tblAddressFamily.FAMZIP, tblAddressFamily.[FAMZIP+4], tblAddressFamily.FAMUSEFORMAILINGS, tblAddressFamily.FAMADDRNOTE, tblAddressFamily.FAMADDRHOMEPHONE, tblAddressFamily.FAMADDRHOMEPHNOTE, tblAddressFamily.FAMADDRRECDATE, tblAddressFamily.FAMADDRSTATUS FROM tblAddressFamily INNER JOIN tblStates ON tblAddressFamily.[FAMSTATE] = tblStates.STATE;

I've tried adding in FAMID from either/both tblINDIVIDUAL/tblFAMILY but neither does me any good. I have basically the exact same form for Individual's Addresses and it works, but it doesn't have the complexity of adding in the relationship with the family data. I think that's where I'm getting hung up. Suggestions? I'm guessing I'm confusing it with all the FAMID's but I'm not sure how to fix it. I've tried having the table name with it, and with or without brackets but it doesn't like that either.

I'll keep trying to figure out how to get a copy posted. Any suggestions to try would be appreciated. Thanks! :banghead:

 

[RainLover]

The fact that you can't convert SUCKS.

I use 2003 and sometime 97. Both are better than any later format. But you simply have to live with this.

Some help.

WRITING TABLE NAMES ETC IS A REAL PAIN TO HELP. I SIMPLY WILL NOT TRY TO HELP SOLVE SQL PROBLEMS WRITTEN LIKE THIS.

Your relationships look OK.
Uncheck cascade deletes and cascade updates. They can cause you more problems than what they are worth.

You can't have a join betwwen a subform and a table. However you can have a table as the Record Source for a forn. Fix this in the properties box.

Do you have the Master and Child between the Main and the sub correct. If unsure then delete the sub then reattach. You should be asked the question then.

Thats about all I can help with at the moment.

 

[lford91]

It says my file is actually saved in "Access 2007-2013 file format" but it won't upload. ??! It says "Your submission could not be processed because a security token was missing." The administrator's email returns as mailbox quota exceeded. I can't find anywhere else to post to him either. (Jon) If anyone has any suggestions, I'd be grateful.

 

[RainLover]

Check out the FAQs

There are some limits on new members to help prevent spamming.

You could try loading it up on Sky Drive. If you don't have Sky Drive I suggest you get it as it comes in handy.

If you click on the link in my signature it will take you to mine.

 

[RainLover]

It would appear as though that restriction has been lifted.

I cannot find any reference to it.

Your file must be under 2mb. Do a compact and repair before trying to zip

 

[lford91]

I signed up for Sky Drive and stuck the database there. Let me know if that doesn't work. Thank you for the suggestion Rain!!!!!


https://skydrive.live.com/redir?resid=2D6C80442CA0DD73!105

 

[Pat Hartman]

You need to remove the FK from the Family table. You are creating a pathological connection between family and individual as well as restricting the family to a single member.

Cascade delete is there because it is far better to let the database engine do this work than to entrust it to individuals to code. You simply need to understand what it does and implement it correctly. For example, a lookup table such as tblState would NEVER have a connection to an address table that specified cascade delete. If it did and someone accidentally deleted California, all your California addresses would also be deleted. So, yes it is dangerous but only if you don't understand when to use it. You would always use it for something like tblOrders/tblOrderDetails. If you delete an order, it makes perfect sense to delete all the detail items.

In your case, I'm not sure if I would implement Cascade Delete. It certainly makes sense to delete individuals and addresses if you delete a family but I would think long and hard before I actually did this. Perhaps delete shouldn't be supported at all. Only you know your business rules so you'll need to decide. Just remember - delete is permanent. There is no oops - I didn't mean it and with Cascade Delete in place also, you could loose a lot of data.

 

[lford91]

I thought I had INDIVID in the tblFamily but I don't. I have FAMID in tblIndividual and tblAddressFamily. Sorry. My brain is going around in circles at this point from having tried so hard to fix it!

I still don't have it working. Hopefully it'll make sense to someone. It'd be heaven if I was just missing a " or something easy!

 

[RainLover]

I signed up for Sky Drive and stuck the database there. Let me know if that doesn't work. Thank you for the suggestion Rain!!!!!


https://skydrive.live.com/redir?resid=2D6C80442CA0DD73!105

That is about all I can help with because of the 2013 issue.

Good Luck with the project.

 

[lford91]

Did you try to open it from my Sky Drive? It says it's 2007-2013 compatible.

 

[RainLover]

Did you try to open it from my Sky Drive? It says it's 2007-2013 compatible.

I did manage to download and open your Database.

I see you are still using upper case. I don't know why you did this. It makes it a lot harder to read than mixed case due to the reduction of white space around the characters. Imagine trying to read a newspaper that was printed completely in upper case.

I looked at at your Relationships page and you don't appear to have set any. Is this because of a conversion issue or have you simply not created any. If you havn't created any then please do.

 

[lford91]

I had copied it over and didn't realize it had gotten rid of those. They are added now.

Sorry about the CAPS. That was how the person I learned from did it and I've stuck with it. Never heard anything negative about doing that until now. I will keep it in mind. Thank you.

My Sky Drive has the new file replacing the old.

https://skydrive.live.com/redir?resid=2D6C80442CA0DD73!109

 

[RainLover]

There is not much I can do to help. I struggle to read your Table Names because of the bold situation. But that is your choice or preference and I have no place putting demands on you.

From what I could make out there are other problems with this database. I suggest that you Google "The Evils of Lookup". It will explain that you should not have Combo Boxes in Tables.

Your Naming conventions could do with an improvement. It would be nice to see which fields are Foreign Keys. Most people use ID at the end of the Name. I prefer to use FK at the end of the name. If interested there is a paper on my sky drive to do with Naming Conventions. Read it if you wish or do a Google search for other opinions.

You have Primary keys with a Data Type of Number. This is not normal. One normally would use Autonumber.

You have tblMonths and tblMonthsLongName. They look exactly the same to me.

Perhaps none of the above has anything to do with your problem as I got lost in you maze. It would be better to present your relationships in a readable manner rather than the way you have.


Sincere appoligies for not giving you the answer you were looking for.

 

[lford91]

Thanks for the advice and the attempt to help, Rain. I appreciate it. I will take a look at your paper and keep what you said in mind. I am 100% self-taught out of necessity through a previous job when I took over another person's duties. All my methods are how she did things. Since I know no other way, that's how I did it. I appreciate the advice from people who know what they are doing. I would love a class at some point and will definitely do some more educating before attempting to tackle something like this again.

 

[RainLover]

I wish you well. The Database is rather large so you have your work cut out for you.

I looked at your code.

You need to use "Option Explicit" at the second line after "Option Compare Database"

You also need to introduce some Error Trapping.

Check Google for more info.