Full Version: Run-Time Error 2427 and Updating Field Problem
UtterAccess Forums > Microsoft® Access > Access Forms
Lagerca
I open my {frmPatient Data}, view the appropriate record and then press the Command Button “Print Notification” to print the report {rptLetter} based on the current displayed Patient’s record (exam results) and the Patient’s [Exam ID]. The data is taken from the primary table {PatientData} – Demographics, and subdatasheet {ExamData} – Exam Data. The linked field is [SSN]. Issue 1: If there is no data in the table {ExamData}, and I press the Command Button for a particular record, I receive a Microsoft Visual Basic error: “Run-time error 2427. You entered an expression that has no value.” Below is the VBA coding that is displayed when I press the Debug button.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Trichomonal = no Then Label50.Visible = False
If Trichomonal = no Then Trichomonal.Visible = False
If Bacterial = no Then Label51.Visible = False
If Bacterial = no Then Bacterial.Visible = False
If Yeast = no Then Label52.Visible = False
If Yeast = no Then Yeast.Visible = False
End Sub
Each of the 3 fields above refer to the subdatasheet table {ExamData} which are Yes/No fields. After the debug is shown and press the End button, Issue 2: it closes the {frmPatient Data} automatically, updates the field [ResultsNotified] automatically in the table {ExamData} thus creating a new [ExamID] record in the table {ExamData} leaving the remaining fields blank. Out of 479 Exam Data records, all 479 [Trichomonal] fields are false, only 4 [Bacterial] are True, and only 2 [Yeast] fields are True.
The Command Button “Print Notification” refers to: Macro Name: UpdatePrintNotice; Condition: True; Action Name: SetValue; Arguments: [Forms]![frmExam Data]![ResultsNotified],Date()
This macro has two actions: 1) is SetValue and the 2nd is OpenReport. The details for SetValue are above and the details for the action OpenReport: Report Name: rptLetter; View: Print Preview; Filter Name: blank; Where Condition: [ExamData].[ExamID]=[Forms]![frmPatient Data]![frmExam Data]![ExamID]
What I would like to be displayed, if the Command Button is pressed and there is no {ExamData} for that individual, is for a popup Msg: “This individual does not have any Exam Data entered.” Then there would be a “Cancel” button to close the error reading message and return to the {frmPatient Data}, without updating the [ResultsNotified] field. I’m not comfortable in using VBA, so an example and the placement of the coding would be very helpful! Thanks.
montezuma
Hi,
You could try a couple of things, put 'Speech marks' around the no to show it is text and also refer to the label using the 'me' function
If Trichomonal = "no" Then me.Label50.Visible = False
Regards
montezuma
Hi,
Second question, For the report problem,
pen the report in design view
Press <alt> <f11> keys simultaneously, this will open the VBA code editor
Copy and paste the following onto the screen
CODE

Private Sub Report_NoData(Cancel As Integer)
'If no data for repor then inform user
    
MsgBox “This individual does not have any Exam Data entered.”  & _
         Chr(13) & "Previewing / Printing is canceled. " & _
         Chr(13) & "Check the data source for the report)", vbOKOnly + vbInformation
    Cancel = True
End Sub

This should give you the message box you are looking for
Lagerca
Mr Montezuma: Thanks for your response. Issue 1: : At the Debug for the 2427 Run-time error, I inserted “speech marks” as shown below: Hopefully this is what you meant.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Trichomonal = "no" Then Me.Label50.Visible = False
If Trichomonal = "no" Then Me.Trichomonal.Visible = False
If Bacterial = "no" Then Me.Label51.Visible = False
If Bacterial = "no" Then Me.Bacterial.Visible = False
If Yeast = "no" Then Me.Label52.Visible = False
If Yeast = "no" Then Me.Yeast.Visible = False
End Sub
I am still having problems in the Run-time error still appearing and automatically creating a new record in the subdatasheet {ExamData}. The bold part of the coding gets highlighted in yellow when I ask to Debug the 2427 errror.
With Issue 2: I made an error someplace, I believe it was in the process of placing the copied code into the {rptLetter}. In the design of {rptLetter}, I pressed <alt><f11> which opened the VBA code editor. I copied the text and placed it at the “Option Compare Database”; it created the code on one long line; I exited and saved. I tried to locate the Coding that I copied and I couldn’t find it. I then pressed <alt><f11> again; all it gave me was a dark gray shaded blank VBA screen which I couldn’t enter anything. This time I went to the design of {rptLetter}, under the menu View/Code, the Debug coding above was displayed. So I recopied your coding just above the Debug coding, which again copied it into one long line, “Line 2, Col322”. I did locate the coding, only through the View/Code menu, and the <alt><f11> still displays a blank gray VBA screen.
montezuma
Sorry,
Seems I have misread the question, the first thing that should happen when you click the button is a check that there is data available for the particular record. If not then exit the sub, giving the user an error message.
I am not clear about the actual structure of your database, however as [SSN] is the link then you would check that the table holding the exam data actually contains the [SSN] value you are looking for.
Use the 'dlookup' function to do this

In addition as you are using a Yes/No data type field it would be better to construct the if-then :-
If Trichomonal = False Then Me.Label50.Visible = False
montezuma
Hi,
Think I will take a couple of days off the forum and get over the jetlag.
Re-considered the initial error message, As there is no data then Access cannot do what you are asking, namely the if statements, in the statements you have not put an alternative for access to follow.
The Trichomonal condition does not exist so what to do next?, the answer would seem to be add an 'else' statement. Have also reversed the logic so that if any other condition exists it will not display
If Trichomonal = true Then
Me.Label50.Visible = true
Me.Trichomonal.visible = True
else
Me.Label50.Visible = false
Me.Trichomonal.visble = false
end if
It would be easier if a sample of your database was made available
Lagerca
I copied your latest coded suggestion, just for the [Trichomonal] and the debug still directs me to the If Trichomonal = true Then in yellow. I had inherited this database with this problem. My question would be where in the database is this [Trichomonal], [Bacterial] and [Yeast] Condition established to set off this Debug coding? As mentioned these three fields are in the subdatabasheet table {ExamData}. Yes, I agree that it would be easier for a sample to be provided. I have scaled down the db, and zipped it; And will try to e-mail it. However from my office I may not be able to e-mail an Access or Access zipped file. I could though from my home if that is appropriate with you. Thanks for your help.
Lagerca
Mr. Montezuma: After trying to relook at the database, I noticed that the 3 mentioned fields are reflected in the detail section of the {rptLetter}, Secondary Findings. To try something different, I opened up the zipped file I sent, recopied the below coding over the old Debug and now it almost works! The {rptLetter} does get printed bypassing the debug. 1). Now, just the shell of the letter prints with #Error in the address field and all other data is blank; (it should be blank since there is no record in the subdatasheet table {ExamData}. 2). However, when I close the {rptLetter}, the [ResultsNotified] field for that individual automatically gets updated, thus creating a new subdatasheet record, which should not be the case. The {rptLetter} should not get displayed if there is no subdatasheet record. I think we are almost there!
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Trichomonal = "no" Then Me.Label50.Visible = False
If Trichomonal = "no" Then Me.Trichomonal.Visible = False
If Bacterial = "no" Then Me.Label51.Visible = False
If Bacterial = "no" Then Me.Bacterial.Visible = False
If Yeast = "no" Then Me.Label52.Visible = False
If Yeast = "no" Then Me.Yeast.Visible = False
End Sub
montezuma
Hi,
Have looked at the sample database and resolved the issue of the report opening when there is no data, the original code will not work because the program actually passes some data to the report.
o get around this I have simply check if there are no results and then used 'sendkeys' to 'escape' from the report. Also added the missing elements
The code now looks like this:-
CODE

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Trichomonal = True Then
Me.Label50.Visible = True
Me.Trichomonal.Visible = True
Else
Me.Label50.Visible = False
Me.Trichomonal.Visible = False
End If
If Bacterial = True Then
Me.Label51.Visible = True
Me.Bacterial.Visible = True
Else
Me.Label51.Visible = False
Me.Bacterial.Visible = False
End If
If Yeast = True Then
Me.Label52.Visible = True
Me.Yeast.Visible = True
Else
Me.Label52.Visible = False
Me.Yeast.Visible = False
End If
'Check if no results in any field, if all missing inform user and exit report
If trichomanal <> True And Bacterial <> True And Yeast <> True Then
MsgBox "This individual does not have any Exam Data entered." & _
         Chr(13) & "Previewing / Printing is canceled. " & _
         Chr(13) & "Check if Exam Data is Available and Re-run", vbOKOnly + vbInformation
SendKeys "{esc}"
End If
End Sub
montezuma
Oops, Typo
Check if no results in any field, if all missing inform user and exit report
If trichomonal <> True And Bacterial <> True And Yeast <> True Then
MsgBox "This individual does not have any Exam Data entered." & _
Chr(13) & "Previewing / Printing is canceled. " & _
Chr(13) & Chr(13) & "Check if Exam Data is Available and Re-run", vbOKOnly + vbInformation
SendKeys "{esc}"
End If
Lagerca
I appreciate you taking the time; a couple of things though. I copied your suggestions and still have an old issue and a new one. 1. If there is no [ExamID] in the subdatasheet, and press the “Print Notification”, I get the 2427 debug. It directs me to If Trichonomal = True Then highlighted in yellow. As it closes, the [ResultsNotified] field gets updated in the subdatasheet table, {ExamData}. And, 2. if there is an [ExamID] record for a particular individual, the Msg box appears: “This individual does not…. and Re-run." with the OK button. After I press the OK button, the [ResultsNotified] field gets updated, which updating the field is correct. And the {rptLetter} does not get printed. The coding is as below.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Trichomonal = True Then
Me.Label50.Visible = True
Me.Trichomonal.Visible = True
Else
Me.Label50.Visible = False
Me.Trichomonal.Visible = False
End If
If Bacterial = True Then
Me.Label51.Visible = True
Me.Bacterial.Visible = True
Else
Me.Label51.Visible = False
Me.Bacterial.Visible = False
End If
If Yeast = True Then
Me.Label52.Visible = True
Me.Yeast.Visible = True
Else
Me.Label52.Visible = False
Me.Yeast.Visible = False
End If
'Check if no results in any field, if all missing inform user and exit report
If Trichomonal <> True And Bacterial <> True And Yeast <> True Then
MsgBox "This individual does not have any Exam Data entered." & _
Chr(13) & "Previewing / Printing is canceled. " & _
Chr(13) & Chr(13) & "Check if Exam Data is Available and Re-run", vbOKOnly + vbInformation
SendKeys "{esc}"
End If
End Sub
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.