Check my code and stop form loading 1

[NeilT123]

Hi, I am not a programmer, just self taught in Access and I have a form which has the following code behind it. The code is a mix and match of bits that I have put together over the years.

3 questions:
1. Is it OK to have intstore and intstore2?

2. I have added Cancel = 1 to stop the form loading but this does not always work. Is there a better way to stop the form loading if the form frmIfTSoilAnalRslts is triggered?

3. Are there any errors in the code or improvements that I should make?

Private Sub Report_Open(Cancel As Integer)
Dim intStore As Integer
Dim intStore2 As Integer

intStore = Nz(DSum("[SumofFertRecStatus1]", "[qryFertRecStatus]"), 0)
intStore2 = Nz(DMax("[CountofAnalysisNumber]", "[QryCountOfSoilAnalRsltsForNutriPlanP2]"), 0)

'If count of soil analysis results > 1 then display message box
If intStore2 > 1 Then
                    MsgBox "There is a field with more than one soil analysis result for this report. Use Soil Analysis Input form to deselect one field"
       On Error Resume Next
DoCmd.OpenForm "frmIfTSoilAnalRslts"
'close this report
Cancel = 1
If Err = 2501 Then Err.Clear

       Else
End If

DoCmd.SetWarnings False 'switch off warning messages re adding & deleting records
'run sql to populate temporary tables
DoCmd.RunSQL ("INSERT INTO TblTEMPFieldSelect4NutriPlan SELECT * FROM qryrpt10Nutriplan")
DoCmd.RunSQL ("INSERT INTO TblTEMPNMaxCalcs4NutriPlan SELECT * FROM qryNMaxrpt10DSummary")
DoCmd.RunSQL ("INSERT INTO TblTEMPManures4NutriPlan SELECT * FROM qryOMApplnRPTA6P2")

DoCmd.SetWarnings True 'reset warnings
   
If intStore <> 0 Then
                    MsgBox "This farm has provisional recs..."
       Else
End If
End Sub

As always, thank you in advance for any help or suggestions provided

Neil

 

[jedraw]

What happens when you run the code? Do you get compile error? error message?
Do you get syntax errors?
Does the code do what you want it to do?

 

[NeilT123]

There are no errors and the code works the only issue is that when intStore2 > 1 and the form frmIfTSoilAnalRslts is opened the temporary tables are still populated so cancelling the loading of the original form is not working correctly.

Should I move the code to the onload rather than onopen?

 

[PHV]

...
DoCmd.OpenForm "frmIfTSoilAnalRslts"
'close this report
Cancel = True
Exit Sub
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

That seems to have resolved the issue, thank you.

 

[dhookom]

NeilT123,
If PH's answer "seems to have resolved the issue" then you should thank him by clicking the link to star the post.

Duane
Hook'D on Access
MS Access MVP