vba hangs

[FinHard1]

DoCmd.OpenReport hangs in access


Private Sub cmdPrnRep_Click()
On Error GoTo ln55
'On Error Resume Next
If Not IsNull(SaleRef) Then
DoCmd.OpenReport "Docket", acViewNormal, , "Saleref = " & SaleRef
cmdSnP.Default = True
Me.cmdSnP.SetFocus
End Sub

It worked for 20 yrs trouble free

 

[Jim E]

I haven't used MS Access in decades, but I ran the code thru chat GPT, which I have found excellent for debugging Excel formulas and VBA. Here is what AI said:
This VBA code is intended to be used in Microsoft Access, and it is triggered when the cmdPrnRep_Click event is fired, typically by a user clicking a button (possibly named cmdPrnRep).


Here's what each part of the code does:
Code Breakdown:

1. Error Handling (On Error GoTo ln55):
   The line On Error GoTo ln55 directs Access to jump to the line labeled ln55 in case of an error. This is used to handle errors gracefully, preventing the code from crashing and allowing the developer to take action on errors (though ln55 is not defined in your provided code, so an error here would likely cause the process to terminate).
2. Check for Null Value in SaleRef:
   vba
   CopyEdit
   If Not IsNull(SaleRef) Then
   This line checks if the SaleRef field (likely a control or variable on the form) is not null. If it is not null (i.e., there is a valid value in SaleRef), the code inside the If block will be executed.
3. Open the Report:
   vba
   CopyEdit
   DoCmd.OpenReport "Docket", acViewNormal, , "Saleref = " & SaleRef
   This command is what attempts to open the "Docket" report. Here's a breakdown of the parameters:
   • "Docket": The name of the report you want to open.
   • acViewNormal: This means the report will open in normal view, not in design or print preview.
   • The third argument is left blank (i.e., , ,), which means you are not specifying any specific filter or sorting for the report beyond the criteria set in the next parameter.
   • "Saleref = " & SaleRef: This is a filter for the report. It limits the report to records where the SaleRef field matches the value of the SaleRef control on the form. This assumes that SaleRef is a field in the report's underlying data source.
4. Set Default Button:
   vba
   CopyEdit
   cmdSnP.Default = True
   This sets the cmdSnP button (likely another button on the form) to be the default button. This typically means that when the user presses "Enter" on the form, cmdSnP will be activated.
5. Set Focus to Button:
   vba
   CopyEdit
   Me.cmdSnP.SetFocus
   This sets the focus to the cmdSnP button, so if the user starts typing, the form is ready for them to interact with the cmdSnP button.

Potential Issues Leading to Hanging:

• Hanging Issue with DoCmd.OpenReport:
  The issue you're experiencing, where the DoCmd.OpenReport call "hangs", could be due to several reasons:
  • Filter Condition Issue: If SaleRef is empty or contains invalid characters (like non-numeric values where numbers are expected), it may cause an issue in the filtering. Ensure that SaleRef is not empty and that it is a valid value for the query in the report.
  • Corruption in the Report: If the "Docket" report has issues (e.g., a query or control in the report is malfunctioning), it could cause Access to hang when attempting to open it. Check the report's design and any queries it relies on.
  • Locks or Database Issues: Sometimes, database locks or other issues in the Access file can cause delays or freezes when opening reports.

Debugging:​

To debug this further, consider:

• Removing the filter in DoCmd.OpenReport to see if the report opens without any conditions (DoCmd.OpenReport "Docket", acViewNormal).
• Checking the SaleRef field for validity.
• Using Debug.Print SaleRef to print out the value of SaleRef before the DoCmd.OpenReport call, so you can inspect what value it holds.
• Adding additional error handling or breaking into the code (e.g., with MsgBox or Debug.Print) to see where exactly the code is hanging.

If you have more context, like whether SaleRef can sometimes be null or invalid, that could help pinpoint the issue more precisely.