Full Version: Filling in controls and referential integrity
UtterAccess Forums > Microsoft® Access > Access Forms
Brother_J5
Hello - I have a form with several subforms. Several of the controls on the main form are drop-down lists to control what users put in the underlying table. So far so good. Of course, when I use the form, I have no problem. But other users like to fill in some of the subforms first. I have referential integrity set up for all the joins. Because of this, if a user tries to skip one of the drop-down list controls on the mainform and jump to a control in a subform they get the default message from Access saying "You cannot add or change a record ...". I would like the users to be free to fill in the form how they like (to a point) while maintaining referential integrity, but the only way I know how to avoid the annoying message that forces users to fill in certain controls first is to drop referential integrity rules. I assume fundamentally not having referential integrity is a no no.
Any ideas, suggestions? If there is no way to do what I want, then I assume I can change the default message to read something more friendly and easy for the average user to understand. Is the programing simple to do for such a message?
This forum rocks by the way!
Justin
Jack Cowley
I would suggest that if there is not data in a subform for the selected record in the main form that you hide the subform. This way the user cannot enter data there until they have created the required data in the main form. When they add data in the subform and there is no related record they get the message, but the also have to delete the record they just entered, create a new record in the main form and then go back and reenter the record in the subform.
My 3 cents worth...
Jack
Brother_J5
Thanks Jack - your 3 cents is much appreciated. Do you mean hide the subform in such a way that it becomes unhidden when the required data on the main form is entered? I'm not sure I follow you after your second sentence.
To be sure we are on the same page (I have a feeling its me that is on the wrong page!), the reason the form won't let me move focus to the control in one of the subforms (prior to entering data into certain controls on the main form by selecting from the drop-down list) is becuase if I skip the dropdown list control(s) on the mainform (leave them blank) I would essentially be creating a "dangling reference," the same result as if me or another user trys to type something in the control that doesn't exist in the underlying table.
Jack Cowley
We are on the same page, I think, I just did a poor job of explaining myself in the second sentence... Typical behavior for me later in the afternoon....
think I followed your explanation, but let's see. If you do not enter or select a record in the main form you cannot add a record to the subform. Am I close? If so, you are right in that you cannot enter a value into a related (Many) table (the subform) if there is not a related record (One, the main form) in a One-to-Many relationship.
We may need to go back a bit here... You mention combo boxes. I assume the data in the combo boxes have related records (the subform) and that if you do not make a selection from the combo box you cannot add data to the subform. If that is the case then hide the subform by setting its Visible property to False, and when a selection is made from the combo box then set the Visible property to True.
Am I making sense and do I understand what you are doing?
Jack
vtd
(PMFBI, Jack).
got a feeling that the AutoSave feature of the Form / Subform combination is in action here. Basically, the Mainform and the Subform are 2 distinct data objects and when the user moves the focus from the Mainform to the Subform, the AutoSave feature kicks in and tries to save the current Record on the Mainform into the Table. You can see this easily by coding a MsgBox in the BeforeUpdate Event of the MainForm.
(Conversely, when the user moves the focus from the Subform to the Mainform, Access (/ AutoSave) will also try to save the current Record in the Subform.)
My guess is that (some of) the Fields that bind the ComboBox Controls on the Mainform have the Required Property set to Yes / True which prevents the current Record on the Main Form from being saved / updated into the Table. Since this happens, the user gets the error message and Access prevents the focus being moved to the Subform, i.e. the user cannot get into the Subform.
Unfortunately, you cannot disable this AutoSave feature on bound MainForm / Subform since it is necessary to preserve the R.I. of the One-to-Many relationship which the Form / Subform combination is designed for. There are a number of work-arounds but all are fairly complex. Probably, the easiest way is hide the Subform Control(s) and provide a CommandButton on the Mainform to explicitly save the current Record on the Mainform (after entering data for all required Fields) at which point your code will make the Subforms visible.
Brother_J5
Jack,
No problem. Yes, it sounds like we were on the same page. Not only was it afternoon, it was Friday afternoon! You are making sense.
Perhaps you and Van are both right?? Anyway, I believe you got me on the right track - I will attempt what you suggest today. I understand how to set the visible property to false (I assume I have to do this for each subform). Do you mind explaining to me how I get the subforms to show once the main form is complete? It sounds like from what Van says I have to write some code.
If it makes any difference, I am not sure if the data in my combo boxes have related records. The control simply looks to the table that has the data for the combo box, which is not used anywhere else. For intance, one of the comboboxes list different business groups in my company. As you know, if I leave it to the users to fill in this control, some will type the whole group name, others will type in an acronym, still others will misspell, messing up any attempt to do a meaningful query based on busines group. But maybe I do not totally understand related records? Because certainly each subform is linked to the mainform.?
Justin
Jack Cowley
Justin -
I am confused about the relationship between combo boxes and forms/subforms. I am unclear as to the purpose of the combo boxes so if you can explain their purpose maybe I can help.
HAs for hiding the subform(s) - Yes, you will have to use code. You can use code, similar to this, in the On Current event of the main form IF the main form is the MasterLink between the subforms:
If DCount("*", "NameOfTableThatIsTheRecordSourceForASubform") = 0 Then
Me.SubformControlName.Visible = False
Else
Me.SubformControlName.Visible = True
End If
You will also need to put code in the last control in the main forms tab order to set the subforms visible property to True, as show above. This MAY not be the correct control to put the code in as I do not know the layout of your main form and all that goes on with that form.
hth,
Jack
vtd
Are you using the Form / Subforms combination for view / edit / new or using it for a single specific purpose, e.g. data entry omly?
From your description, I guess the main problem is data entry. Asume that this is a multi-purpose Form, I would leave the Subform(Control)s visible for normal viewing / editing and code the Current Event of the Mainform to check whether the CurrentRecord is a new Record or not. If it is a NewRecord, set the Subform Controls' Visible Property to False by code.
You will need a CommandButton on the Mainform for the user to explicitly save the Record on the MainForm at which time you make the SubformControls visible.
The code should be something like(****untested****):
CODE
Private Sub Form_Current()
With Me
    If (.NewRecord = True) Then
      .sfr1.Visible = False
      .sfr2.Visible = False
      .cmdSaveNew.Enabled = True
    Else
      .sfr1.Visible = True
      .sfr2.Visible = True
      .cmdSaveNew.Enabled = False
    End If
  End With
End Sub
  
  
  
Private Sub cmdSaveNew()
  With Me
    DoCmd.RunCommand acCmdSaveRecord
    .sfr1.Visible = True
    .sfr2Visible = True
    .sfr1.SetFocus
    .cmdSaveNew.Enabled = False
  End With
End Sub

Since you have Required Fields, you should also use the BeforeUpdate Event (of the Mainform) to perform data validation and present a more user-friendly message advising the problem with data entry rather than the inbuilt Access / JET error message. The code I leave it for you but Access VB Help should give you plenty of info.
Brother_J5
Thanks Jack and Van - I haven't had a chance to play around with it yet. Let me ask you this, is it a totally bad thing to not have RI for some links and instead use an event procedure to prompt users to fill in required fields (controls).
Back - Yes, the main form is the MasterLink. Sorry for the confusion. I am simply using the combo boxes to limit what users put in certain fields. In the form there are three combo boxes - one for bus goup, another for request category, and yet another for requestor (we're a corporate service organization). So the control sources for the comboboxes are three seperate tables (the one) that have the one-to-many relationship with the table (the many) that is the control source for the mainform. The control sources for the subforms are different tables that are related to the main table (main form) in a one-to-many relationship where the main table is the one. For instance, one subform is for entering logs of the actions we have taken to complete a request. There may be many logs for one request. I hope I explained that alright.
Last question, and let me know if I should post this as a new thread. I have read that the macro builder in Access should be avoided and code should be used instead. Why? I currently don't have the time to learn code (I'm a chemical engineer!) I would like to some day, but not now. What kind of trouble will I get myself into if I rely heavily on using the macro builder in Access to automate and simplify things?
Thanks again for your time- Justin
vtd
I *think* the main problem in the set-up is related to Entity Integrity plus the behaviour of the AutoSave feature, NOT the Referential Integrity. The AutoSave feature has something to do with R.I. since it is designed to preserve to R.I.
asically, R.I. is a constraint on relationship between 2 Tables, i. e. the "One" Record must exist before the "Many" Records that "belong" to the "One" Record can be created (children must have parents).
E.I. is constraints on the Entity / Record in a particular Table, e.g. some Fields must have valid data and not Null (Required Property).
In your case, AutoSave simply activates the updating of the "One" Record into the Table. However, the updating cannot proceed because some of the required Fields do not have valid values entered, i.e. failing the E.I. constraints. Thus, the main problem relates to E.I. and not R.I.
(Back to your R.I. question) Personally, I prefer to use R.I. but I have seen databases without R.I. enforced that work quite well. In fact, all database applications using VB and JET I have seen to date never had relationships nor R.I. enforcement in the JET back-ends and they still work OK ( I must be following bad VB programmers). Constraints can mostly be handled by codes, just more work to code the data validation steps.
OTOH, even with using the relationship / R.I., we often write code to perform data validation so that we can present a more user-friendly messages to the user. Thus, in a way, we also do data validation by code, even with relationship & R.I established.
Macro Question: The major drawback in using Macros is that does not allow you to trap the errors. A Macro will just stop dead in its track with some (often cryptic) message / dialog if an error occurs. This have serious problems when the Access database application is used in the "runtime-only" environment. In VBA, you can trap the error (most errors are trappable but some are not), present the user with a more friendly message and proceed to the appropriate next step in the process. In addition, VBA is much more powerful than Macros.
Do use Macros if you have to, though.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.