MsgBox

[Guest]

I have a frmReferral with read only data displayed in datasheet view - user
selects one record from datasheet view and clicks a cmdOpenForm which opens
fsubReferral with all the data for that record for editing. ReferralID links
the frmReferral selected datasheet record with the fsubReferral record. The
problem - if there is no records in the frmReferral datasheet and the user
clicks the cmdOpenForm anyway MS Office Access message box appears 'You
entered an expression that has no value". How can I customise this message
in the MsgBox.

noodleBrain

cmdOpenForm 'onclick' event

....stLinkCriteria = "[ReferralID]=" & Me.frmReferralSub![ReferralID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Allan Murphy]

I think error 2427 is the relevant error that is triggered for no value. If
not a message box will be displayed with the correct error number

I have used a similar procedure for trapping duplicate entries and invalid
date formats on a form.

On your form in the form error event add the following.

Private Sub Form_Error(DataErr As Integer, response As Integer)

On Error GoTo Err_Form_error

Dim strmsg As String


Const conErr_no_data = 2427 ' error number 2427 is generated when
' there is no data etc.

If DataErr = conErr_no_data Then
strmsg = "No Data available for the selected record"
MsgBox strmsg
response = acDataErrContinue
End If

Exit_Form_error:
Exit Sub

Err_Form_error:
MsgBox (" Form Error # " & Str(Err.Number) & " was generated by" _
& Err.Source & Chr(13) & Err.Description)
Resume Exit_Form_error
End Sub

 

[Guest]

Thanks Allan however it doesn't seem to work. I still get the message "You
entered an expression that has no value". With my limited programming
knowledge I think it's got to do with the fact that there is no record, hence
no value in the field trying to be match with the next form that is opening.
Although you wouldnt want to open the form if there is not record to see -
someone is bound to click it and get the above message - I wanted to make it
a bit more user friendly with something like "There is no referral data, add
a referral".

nB

I think error 2427 is the relevant error that is triggered for no value. If
not a message box will be displayed with the correct error number

I have used a similar procedure for trapping duplicate entries and invalid
date formats on a form.

On your form in the form error event add the following.

Private Sub Form_Error(DataErr As Integer, response As Integer)

On Error GoTo Err_Form_error

Dim strmsg As String


Const conErr_no_data = 2427 ' error number 2427 is generated when
' there is no data etc.

If DataErr = conErr_no_data Then
strmsg = "No Data available for the selected record"
MsgBox strmsg
response = acDataErrContinue
End If

Exit_Form_error:
Exit Sub

Err_Form_error:
MsgBox (" Form Error # " & Str(Err.Number) & " was generated by" _
& Err.Source & Chr(13) & Err.Description)
Resume Exit_Form_error
End Sub

--
Allan Murphy
Email: (e-mail address removed)

I have a frmReferral with read only data displayed in datasheet view - user
selects one record from datasheet view and clicks a cmdOpenForm which opens
fsubReferral with all the data for that record for editing. ReferralID links
the frmReferral selected datasheet record with the fsubReferral record. The
problem - if there is no records in the frmReferral datasheet and the user
clicks the cmdOpenForm anyway MS Office Access message box appears 'You
entered an expression that has no value". How can I customise this message
in the MsgBox.

noodleBrain

cmdOpenForm 'onclick' event

...stLinkCriteria = "[ReferralID]=" & Me.frmReferralSub![ReferralID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Frederick Wilson]

I have a frmReferral with read only data displayed in datasheet view - user
selects one record from datasheet view and clicks a cmdOpenForm which opens
fsubReferral with all the data for that record for editing. ReferralID links
the frmReferral selected datasheet record with the fsubReferral record. The
problem - if there is no records in the frmReferral datasheet and the user
clicks the cmdOpenForm anyway MS Office Access message box appears 'You
entered an expression that has no value". How can I customise this message
in the MsgBox.

noodleBrain

cmdOpenForm 'onclick' event

...stLinkCriteria = "[ReferralID]=" & Me.frmReferralSub![ReferralID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Brian,

You need to test the ReferralID before issuing the DoCmd.

OnClick
If isnull(ReferralID) then
msgbox "Sorry no editable record selected.", & _
vbokonly + vbinformation, & _
"No Record"
Else
stLinkCriteria = "[ReferralID]=" & _ Me.frmReferralSub![ReferralID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End if

THIS IS AIR CODE, MEANT TO GET YOU STARTED. IT HAS NOT BEEN TESTED.

--
Frederick Wilson

_____________________________________
for multimedia design services visit
http://www.legalanimatics.com