Subform creating orphan records

[brucey54]

Hi folks, how do I display a more meaningful message instead of the cryptic error about having to enter data into blah blah blah. How can I trap that error and provide them a more meaningful message about entering data. I have tried the following;

Main form name frmPatientRecords

Sub form name DentalRecords Subform

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(tblPatientDetails!recordid, 0) = 0 Then
MsgBox ("sorry. Please complete the main record entry")
Parent.SetFocus
End If
End Sub


Thanks in advance

 

[gemma-the-husky]

if your form/subform is correctly linked, you shouldn't be able to get such a message.

 

[brucey54]

You always get a error message if the user fills in the subform first before the main form and you have the foreign key set to required....

 

[spikepl]

Dave - not quite. If no parent record exists then it will give you an error, if the Fk is set as required, or else it will create orphans.

You could disable the subform and enable it again On Insert of parent record. Or you could catch the error - the catching is done in the On Error event of the subform.

 

[CJ_London]

disable or hide the subform until data has been entered in the main form

 

[gemma-the-husky]

Dave - not quite. If no parent record exists then it will give you an error, if the Fk is set as required, or else it will create orphans.

You could disable the subform and enable it again On Insert of parent record. Or you could catch the error - the catching is done in the On Error event of the subform.

true. I hadn't considered that. Users, eh?

 

[spikepl]

Yes. Users are the scourge of any system

 

[brucey54]

Hi folks, I have managed to display my own message by using the On Error event on the subform as suggested by spikepl.

But after my message pops-up a second message pops-up i.e. “You must enter a value in the bla bla field”

how do I stop this message from displaying?

 

[spikepl]

try me.undo

 

[brucey54]

eh I created Macro to display the message, don't really know vba any help much appreciated

 

[gemma-the-husky]

bruce

in your error event, try adding

response = acdataerrcontinue

this will tell access to dismiss the normal response to the error.

 

[brucey54]

Hi folks, this is my code so far, I still have the system error message still popping up after my message, would like to stop this i.e. “You must enter a value in the bla bla field”


'------------------------------------------------------------
' mcrMainformMsgBox
'
'------------------------------------------------------------
Function mcrMainformMsgBox()
On Error GoTo mcrMainformMsgBox_Err
response = acDataErrContinue

Beep
MsgBox "Referral must have patient name entered first. Press the Esc key TWICE on the keyboard then complete the Patient Personal Details section first!", vbCritical, "Warning "


mcrMainformMsgBox_Exit:
Exit Function

mcrMainformMsgBox_Err:
MsgBox Error$
Resume mcrMainformMsgBox_Exit

End Function

 

[gemma-the-husky]

Bruce

if this code is compiling and running, then you don't have "option explicit" set at the top, as it should not run. you need to include this option in every module as an extra code check. you may have other code errors

you said you were handling the error in a "error" event. The code in your last post is not the "error" event, and won't do what you want.

 

[brucey54]

How would you write the code for the "error" event

 

[gemma-the-husky]

there is a specific "error" event on the form. you need to use that to intercept and replace access error messages.

alternatively you can write your app differently to test specific circumstances and prevent the error arising in the first place.


it's a matter of taste which method you prefer in a particular circumstance