Runtime Issue (2 Viewers)

P

Pete490h

Member
Local time
Today, 16:32
Joined
Jan 18, 2021
Messages
59
Hi,
I have a new event procedure (below) which works absolutely fine for me but fails on my two Runtime users
Has anyone had similar issues with Runtime as its the first time its happenened to me

Just wonder if anyone can see any issues thanks


Private Sub InTest_Click()


'STATUS "Deleting Previous Import Table" - STAGE1

On Error Resume Next

DoCmd.DeleteObject acTable, "NISFeatureData2"

On Error GoTo 0



'IMPORTING SHEET - STAGE2

'DoCmd.TransferSpreadsheet acImport, , "NISFeatureData2",

"C:\Users\accounts_2\OneDrive - PM\NIS-IRN\" & Me.CustomerIRN & ".xlsx", False



'DELETING HEADER ROWS - STAGE3

Dim rs As Recordset, done As Boolean, S As String

Set rs = CurrentDb.OpenRecordset("NISFeatureData2")

done = False

While Not rs.EOF And Not done

S = Nz(rs!F1, "")

If S = "Dim" Then done = True

rs.Delete

rs.MoveNext

Wend

rs.Close



'LOOP THRU VALID Dim

Dim FoundEnd As Boolean

Set rs = CurrentDb.OpenRecordset("NISFeatureData2")

FoundEnd = False

While Not rs.EOF

S = Nz(rs!F1, "")

If S = "Comments & Photos:" Then FoundEnd = True

If FoundEnd Then rs.Delete

rs.MoveNext

Wend

rs.Close

Set rs = Nothing



'UPDATE FEATURE DATA - STAGE4

DoCmd.OpenQuery "NISFeatureDataAQ", acViewNormal, acReadOnly

DoCmd.RunCommand acCmdRun



DoCmd.OpenQuery "NISFeatureData1UQ", acViewNormal, acReadOnly

DoCmd.RunCommand acCmdRun



DoCmd.RunMacro "NISNextRecordBack1"



DoCmd.RunMacro "NISFeatureTextRemove1"



DoCmd.RunMacro "NISNextRecordBack1"





'UPDATE COMPONENTS - STAGE5

Dim db As DAO.Database

Set db = CurrentDb()



DoCmd.RunMacro "NISNextRecordBack1"

Wait 1

DoCmd.RunMacro "NISAppComponentNumber1"

Wait 1



Me.CompQty.SetFocus

If Me.CompQty & "" > 0 And Me.CompQty <= DMax("TallyNum", "tblTallyNumbers") Then

Else

MsgBox " Component Qty is required and must be <= the Max value in tblTallyNumbers", vbOKOnly

Me.CompQty.SetFocus

Exit Sub

End If



'UPDATE INSPECTION - STAGE6

DoCmd.RunMacro "NISAppItemComp1"

Wait 1

DoCmd.RunMacro "NextRecordNISComp1"

Wait 1



'HIDE INTEST BUTTON - STAGE7

OrderNumber.SetFocus

InTest.Enabled = False

InTest.Transparent = True



End Sub
 
That's quite a wall of text.
I've removed the white space and indented it :
Rich (BB code): 
'STATUS "Deleting Previous Import Table" - STAGE1
    On Error Resume Next
    DoCmd.DeleteObject acTable, "NISFeatureData2"
    On Error GoTo 0
    'IMPORTING SHEET - STAGE2
    'DoCmd.TransferSpreadsheet acImport, , "NISFeatureData2",    "C:\Users\accounts_2\OneDrive - PM\NIS-IRN\" & Me.CustomerIRN & ".xlsx", False
    'DELETING HEADER ROWS - STAGE3
    Dim rs As Recordset, done As Boolean, S As String
    Set rs = CurrentDb.OpenRecordset("NISFeatureData2")
    done = False
    While Not rs.EOF And Not done
        S = Nz(rs!F1, "")
        If S = "Dim" Then done = True
        rs.Delete
        rs.MoveNext
    Wend
    rs.Close
    'LOOP THRU VALID Dim
    Dim FoundEnd As Boolean
    Set rs = CurrentDb.OpenRecordset("NISFeatureData2")
    FoundEnd = False
    While Not rs.EOF
        S = Nz(rs!F1, "")
        If S = "Comments & Photos:" Then FoundEnd = True
        If FoundEnd Then rs.Delete
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    'UPDATE FEATURE DATA - STAGE4
    DoCmd.OpenQuery "NISFeatureDataAQ", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdRun
    DoCmd.OpenQuery "NISFeatureData1UQ", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdRun
    DoCmd.RunMacro "NISNextRecordBack1"
    DoCmd.RunMacro "NISFeatureTextRemove1"
    DoCmd.RunMacro "NISNextRecordBack1"
    'UPDATE COMPONENTS - STAGE5
    Dim db As DAO.Database
    Set db = CurrentDb()
    DoCmd.RunMacro "NISNextRecordBack1"
    Wait 1
    DoCmd.RunMacro "NISAppComponentNumber1"
    Wait 1
    Me.CompQty.SetFocus
    If Me.CompQty & "" > 0 And Me.CompQty <= DMax("TallyNum", "tblTallyNumbers") Then
    Else
        MsgBox " Component Qty is required and must be <= the Max value in tblTallyNumbers", vbOKOnly
        Me.CompQty.SetFocus
        Exit Sub
    End If
    'UPDATE INSPECTION - STAGE6
    DoCmd.RunMacro "NISAppItemComp1"
    Wait 1
    DoCmd.RunMacro "NextRecordNISComp1"
    Wait 1
    'HIDE INTEST BUTTON - STAGE7
    OrderNumber.SetFocus
    InTest.Enabled = False
    InTest.Transparent = True
End Sub

Instead of the On Error Goto 0, I would suggest you use the error handling to pop up a message box with the error code, error description and error line, so you can see what it's failing on when run in the runtime?

I assume it compiles on your non runtime machines?
 
Minty said:
That's quite a wall of text.
I've removed the white space and indented it :
Rich (BB code): 
'STATUS "Deleting Previous Import Table" - STAGE1
    On Error Resume Next
    DoCmd.DeleteObject acTable, "NISFeatureData2"
    On Error GoTo 0
    'IMPORTING SHEET - STAGE2
    'DoCmd.TransferSpreadsheet acImport, , "NISFeatureData2",    "C:\Users\accounts_2\OneDrive - PM\NIS-IRN\" & Me.CustomerIRN & ".xlsx", False
    'DELETING HEADER ROWS - STAGE3
    Dim rs As Recordset, done As Boolean, S As String
    Set rs = CurrentDb.OpenRecordset("NISFeatureData2")
    done = False
    While Not rs.EOF And Not done
        S = Nz(rs!F1, "")
        If S = "Dim" Then done = True
        rs.Delete
        rs.MoveNext
    Wend
    rs.Close
    'LOOP THRU VALID Dim
    Dim FoundEnd As Boolean
    Set rs = CurrentDb.OpenRecordset("NISFeatureData2")
    FoundEnd = False
    While Not rs.EOF
        S = Nz(rs!F1, "")
        If S = "Comments & Photos:" Then FoundEnd = True
        If FoundEnd Then rs.Delete
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    'UPDATE FEATURE DATA - STAGE4
    DoCmd.OpenQuery "NISFeatureDataAQ", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdRun
    DoCmd.OpenQuery "NISFeatureData1UQ", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdRun
    DoCmd.RunMacro "NISNextRecordBack1"
    DoCmd.RunMacro "NISFeatureTextRemove1"
    DoCmd.RunMacro "NISNextRecordBack1"
    'UPDATE COMPONENTS - STAGE5
    Dim db As DAO.Database
    Set db = CurrentDb()
    DoCmd.RunMacro "NISNextRecordBack1"
    Wait 1
    DoCmd.RunMacro "NISAppComponentNumber1"
    Wait 1
    Me.CompQty.SetFocus
    If Me.CompQty & "" > 0 And Me.CompQty <= DMax("TallyNum", "tblTallyNumbers") Then
    Else
        MsgBox " Component Qty is required and must be <= the Max value in tblTallyNumbers", vbOKOnly
        Me.CompQty.SetFocus
        Exit Sub
    End If
    'UPDATE INSPECTION - STAGE6
    DoCmd.RunMacro "NISAppItemComp1"
    Wait 1
    DoCmd.RunMacro "NextRecordNISComp1"
    Wait 1
    'HIDE INTEST BUTTON - STAGE7
    OrderNumber.SetFocus
    InTest.Enabled = False
    InTest.Transparent = True
End Sub

Instead of the On Error Goto 0, I would suggest you use the error handling to pop up a message box with the error code, error description and error line, so you can see what it's failing on when run in the runtime?

I assume it compiles on your non runtime machines?
Click to expand...
Thanks for that, I’ll change and try asap and advise accordingly
Yes I compile the database on my PC
 
Does the Access version of the runtime machines differ to your development PC? If so, be sure to decompile the accdb before deploying it to them.
 
AHeyne said:
Does the Access version of the runtime machines differ to your development PC? If so, be sure to decompile the accdb before deploying it to them.
Click to expand...
No they’re both Access 2021and I regularly decompile and compact
 
Minty said:
That's quite a wall of text.
I've removed the white space and indented it :
Rich (BB code): 
'STATUS "Deleting Previous Import Table" - STAGE1
    On Error Resume Next
    DoCmd.DeleteObject acTable, "NISFeatureData2"
    On Error GoTo 0
    'IMPORTING SHEET - STAGE2
    'DoCmd.TransferSpreadsheet acImport, , "NISFeatureData2",    "C:\Users\accounts_2\OneDrive - PM\NIS-IRN\" & Me.CustomerIRN & ".xlsx", False
    'DELETING HEADER ROWS - STAGE3
    Dim rs As Recordset, done As Boolean, S As String
    Set rs = CurrentDb.OpenRecordset("NISFeatureData2")
    done = False
    While Not rs.EOF And Not done
        S = Nz(rs!F1, "")
        If S = "Dim" Then done = True
        rs.Delete
        rs.MoveNext
    Wend
    rs.Close
    'LOOP THRU VALID Dim
    Dim FoundEnd As Boolean
    Set rs = CurrentDb.OpenRecordset("NISFeatureData2")
    FoundEnd = False
    While Not rs.EOF
        S = Nz(rs!F1, "")
        If S = "Comments & Photos:" Then FoundEnd = True
        If FoundEnd Then rs.Delete
        rs.MoveNext
    Wend
Click to expand...
Rich (BB code): 
At the start of your code you delete "NISFeatureData2".
Then turn off error trapping in the function
Then open the now missing "NISFeatureData2" 
You may be able to to get away with it in Full Access but the un-handled error will crash runtime.

The the delete in the loop can also cause problems. You should replace it with a delete query.






	
		

			
				Minty said:
			
		

	
	

		
		

			rs.Close
    Set rs = Nothing
    'UPDATE FEATURE DATA - STAGE4
    DoCmd.OpenQuery "NISFeatureDataAQ", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdRun
    DoCmd.OpenQuery "NISFeatureData1UQ", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdRun
    DoCmd.RunMacro "NISNextRecordBack1"
    DoCmd.RunMacro "NISFeatureTextRemove1"
    DoCmd.RunMacro "NISNextRecordBack1"
    'UPDATE COMPONENTS - STAGE5
    Dim db As DAO.Database
    Set db = CurrentDb()
    DoCmd.RunMacro "NISNextRecordBack1"
    Wait 1
    DoCmd.RunMacro "NISAppComponentNumber1"
    Wait 1
    Me.CompQty.SetFocus
    If Me.CompQty & "" > 0 And Me.CompQty <= DMax("TallyNum", "tblTallyNumbers") Then
    Else
        MsgBox " Component Qty is required and must be <= the Max value in tblTallyNumbers", vbOKOnly
        Me.CompQty.SetFocus
        Exit Sub
    End If
    'UPDATE INSPECTION - STAGE6
    DoCmd.RunMacro "NISAppItemComp1"
    Wait 1
    DoCmd.RunMacro "NextRecordNISComp1"
    Wait 1
    'HIDE INTEST BUTTON - STAGE7
    OrderNumber.SetFocus
    InTest.Enabled = False
    InTest.Transparent = True
End Sub
		

		
Click to expand...
Minty said:
Instead of the On Error Goto 0, I would suggest you use the error handling to pop up a message box with the error code, error description and error line, so you can see what it's failing on when run in the runtime?

I assume it compiles on your non runtime machines?
Click to expand...
 
You must log in or register to reply here.

Similar threads

R
VBA not working for saved query parameters
2
Replies
24
Views
1,371
RonPaii
RonPaii
N
How to send filtered transactions in the MS Access Combo box with one Click
Replies
15
Views
1,266
Mark_
M
A
Match two numbers and then update
Replies
9
Views
1,211
Gasman
Gasman
A
Restrictions Update Message
Replies
15
Views
1,835
Gasman
Gasman
N
Solved Cleaning Up the recordset after use in MS Access
Replies
6
Views
1,712
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

  • ... and 1 more.
    • Total: 3 (members: 2, guests: 1)
    Back
    Top Bottom