UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Switching Between Tab Control Pages Gives Error, Access 2016    
 
   
jabm
post Mar 31 2020, 01:38 PM
Post#1



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


(I apologize in advance if this has already been discussed in another thread and if I ramble on a bit)
Note: I'm testing with fake data so any data seen in the screenshots doesn't violate any privacy issues

I've been reworking a form to view/add Health information for residents. (St. Gianna's Place is a recently opened maternity home). The table is large (many columns) and am reworking the form so that I get rid of multi-select combo-boxes and use subforms instead.
In the previous version I had broken up the form into page 1 and page 2
Now I am trying to do this with Tab Controls.

The first tab includes a subform -- I can add/edit the data in the subform fine but it is not saved (unless I save from the form itself), but then trying to swap to the next tab is giving me the error: You Cannot add or change a record because a related record is required in the table 'T_SGP_ScholarshipApplication_Health'. This is the table for the main form and there is already a record for this (fake) resident in this table. I believe I have all the relationships correct and even as was noted in a previous discussion that I could find - using the On_Change for the actual Tab Control is not working.

The main form has buttons for 'Save and Close' and "Cancel Changes' - I guess I can't cancel changes if I have to save between every tab page change... (but that is another story)

What is triggering this exact error and how do I fix it?

Thank you



Attached File(s)
Attached File  SA_Health_GenInfo.png ( 124.81K )Number of downloads: 4
Attached File  SA_Health_TabChange_Error.png ( 49.5K )Number of downloads: 4
Attached File  SA_HealthTable_RecordExists.png ( 14.9K )Number of downloads: 2
Attached File  SecondTabPageProperties.png ( 72.84K )Number of downloads: 1
Attached File  SecondTabPage_OnClick_Macro.png ( 17.99K )Number of downloads: 1
Attached File  SA_HealthPrevPreg_NoRecordSaved.png ( 12.07K )Number of downloads: 0
Attached File  Relationships.png ( 84.39K )Number of downloads: 4
 
Go to the top of the page
 
jabm
post Mar 31 2020, 01:53 PM
Post#2



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Edit - more testing reveals that it has to do with attempting to add data into the subform on the first tab page
If I add data to the first page, but not in the subform there is no error when switching tab pages
Go to the top of the page
 
RJD
post Mar 31 2020, 02:17 PM
Post#3


UtterAccess VIP
Posts: 10,565
Joined: 25-October 10
From: Gulf South USA


Hi: I think your issue will probably be found in the Relationships window, with Referential Integrity. This means a sub-record must have a saved parent-record to be valid. If you remove the required referential integrity the issue should go away, but subject you to orphaned records. So be careful how you handle this.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jabm
post Mar 31 2020, 04:05 PM
Post#4



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thanks -- Will try adjusting this again -- From other things I had read - unless I read it incorrectly/backwards, which is always a strong possibility (see link for experts-exchange dot com), I thought the way to clear this error was to add the referential integrity, not remove it.
https://www.experts-exchange.com/questions/...blProducts.html

Thank you!
Go to the top of the page
 
jabm
post Mar 31 2020, 04:17 PM
Post#5



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thank you -- That seemed to work
Go to the top of the page
 
tina t
post Mar 31 2020, 04:46 PM
Post#6



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


well, in looking at the tables/relationships screenprint you posted, i'd say table T_SGP_ScholarshipApplication_Health is not normalized. it could use considerable rework.

QUOTE
Edit - more testing reveals that it has to do with attempting to add data into the subform on the first tab page

that sounds odd. i'd be interested to see a copy of your db from before you deleted any relationships, if you want to post a compacted and zipped copy.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Mar 31 2020, 06:31 PM
Post#7


UtterAccess VIP
Posts: 10,565
Joined: 25-October 10
From: Gulf South USA


tina looked deeper into this than I did. I only responded to what was causing the message. And I only suggested RI so you could find the cause. As I said, without RI you may be creating orphan records.

tina's suggestion to revisit your design is spot on. If you are in a position to create records with no parents, it would seem you have a fundamental problem

Listen to tina.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jabm
post Apr 1 2020, 12:59 PM
Post#8



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thank you both

I am including 2 zip files
- SGP.zip is the completed prototype that I handed over to the the director.
There is no actual data, just Lookup Table data
This includes all the multi-select columns (no subforms)
- SGPDB_DEV.zip is the one I've been working in lately - this has a few rows of fake data for testing purposes.
-- the only changes from when I posted yesterday is to
A) adjust the relationship between T_SGP_ScholarshipApplication_Health and ST_ResidentPrevPreg
removing the referential integrity - that did seem to work to clear up the error message - but as has been noted this most likely isn't the correct solution
B) removing any embedded macros from both the TabControl proper and from the 2nd Page of the TabControl

My background
I have a BS-MIS from Worcester Polytechnic Institute (Feb 1996 graduation)
I worked as a Software QA Engineer for Sybase (now part of SAP) from Jan 1996-Feb 2010
- mainly focusing on the old OmniConnect which became the Component Integration Services (CIS)
in the flagship product Adaptive Server Enterprise (ASE) and the DirectConnect to Oracle (DCO)
- CIS was also put into Adaptive Server Anywhere (ASA - formerly SQL Anywhere) which if I remember correctly Sybase acquired when it acquired PowerSoft
Starting in 2010 I became a SAHM (daughter was in 4th grade and struggling) and started volunteering in various capacities, which is how I got involved with St. Gianna's Place

So I have a (rusty) database background and since MS Access was part of Office 365, it made sense to start to convert paper forms into a database using MS Access.
I started working on this in Oct 2019 - starting with the Call Interview form. Not bad for 6months of work with an average of 10hours a week (all volunteer hours - some weeks 15-20 hours, others only 1-2)

Thank you!
Attached File(s)
Attached File  SGP.zip ( 399.98K )Number of downloads: 3
Attached File  SGPDB_DEV.zip ( 578.9K )Number of downloads: 7
 
Go to the top of the page
 
tina t
post Apr 1 2020, 07:11 PM
Post#9



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


ok, i just downloaded your development db, and i'll take a look as i have time, over the next few days. i don't have a .accdb version of Access at home, so it will have to be what time i can squeeze in while at work.

fyi, i compacted your db after unzipping it, and it came out about 25% smaller. going forward, make sure you compact any db first, before zipping and uploading to UA, pls.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
tina t
post Apr 1 2020, 07:54 PM
Post#10



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


okay, after the issue you described in your original post, what relationship did you remove? also, what is the name of the mainform that you're working with?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
jabm
post Apr 2 2020, 12:25 PM
Post#11



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


I will remember to compact it first - sorry about that didn't realize that that was something that should be done.
As I noted above the relationship adjusted to remove the referential integrity, but not removed/deleted is be between T_SGP_ScholarshipApplication_Health and ST_ResidentPrevPreg

The form navigation starts with the default Welcome (AutoExec)->Resident Button->New Checklist for Incoming Resident (or Manange Checklist Information; both open the same form - as either add or edit)
From there the Scholarship Application Data button -> Health button brings to the form with the TabControl and subform
Go to the top of the page
 
tina t
post Apr 2 2020, 03:50 PM
Post#12



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


QUOTE
As I noted above the relationship adjusted to remove the referential integrity, but not removed/deleted is be between T_SGP_ScholarshipApplication_Health and ST_ResidentPrevPreg

fyi, when you remove, or don't add in the first place, Referential Integrity between two tables' primary and foreign keys, there is essentially no relationship that matters - you just have a pretty line drawn between the two tables.

i won't be able to look more at your db until tomorrow, probably PM. i did take a look last night, and can make some immediate recommendations:

1. create a new blank database, and turn OFF the Autocorrect option under Options, CurrentDatabase.

2. close and reopen the db, and then import all the objects from your current db. or, considering the amount of work that needs done on the tables/relationships, you might just want to start fresh in this new db, using the old db only as a reference.

3. in every table you create(d), set the SubDatasheet setting in Properties to [None].

4. in every table you create(d), designate a primary key. Access will accept text fields as PK, but i recommend you use a number field instead, in all tables; AutoNumber is easiest.

5. in every table you create(d), set every field with Text data type to not allow zero-length strings.

6. do not use DLookup fields in tables; in any table already created, remove the lookup from every field that has it. to be clear: combobox controls and listbox controls in forms are fine, very useful. in tables, fields set up to display combobox droplists when the table is in Datasheet view (these are what we call Lookup fields), are problematic, and the trouble they cause far outstrip any benefits. and the only benefit is ease of use when you add data directly to a table - but tables should not be used for direct data entry anyway, so that "benefit" has no practical value.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
jabm
post Apr 2 2020, 10:08 PM
Post#13



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thanks -- will be reworking this again over the next week -- Mike Smart (mike60smart on here) sent me a PM and I did a Skype session with him this afternoon and he pointed out a lot of the same things you did.


Go to the top of the page
 
tina t
post Apr 3 2020, 02:02 PM
Post#14



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


you're welcome, hon. since you've established a dialog with Mike, i'll bow out. when you can get "face to face" (so to speak) help, you're much better off than using a discussion board of any kind. good luck with your project and say "Hi!" to Mike for me! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    3rd July 2020 - 09:16 PM