VBA function locks up

[Dudley]

My accounting database in Access 2002 has a function to create a one off
invoice as follows:

Private Sub Create_New_Invoice_Click()
Dim NewIN As Long
Dim ThisDate

On Error GoTo errcust
ThisDate = InputBox("What date should the new invoice be dated with?",
"Date of Invoicing", Date)
If ThisDate = "" Then Exit Sub

DoCmd.SetWarnings False
DoCmd.Hourglass True

NewIN = NewInvoiceNumber()
DoCmd.RunSQL "UPDATE Enquiries " & "SET [Invoice Number] = " & _
NewIN & " WHERE ([Customer Code] = '" & _
Me![Customer Code Field] & "') AND ([Invoice Number] = 0);", False

DoCmd.RunSQL ("INSERT INTO Invoices ([Invoice Number],[Customer
Code],[Creation Date],[One Off?]) " & _
"VALUES (" & NewIN & ",'" & Me![Customer Code Field] & "','" &
ThisDate & "', Yes);")

DoCmd.OpenForm "Invoices", , , "[Invoice Number]=" & NewIN
DoCmd.Hourglass False

errcust:
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

This has worked for years, but now it has started locking up two out of
three times it is run, and when I do Ctrl/Alt/Del it says Access not
responding.

Can anyone help please?

Thanks
Dudley

 

[Dudley]

Thanks very much for your help. I had already compacted and I have run scan
disk but it turned up no problems. I then tried your suggestion about message
boxes and the problem strangely seemed to be with

NewIN = NewInvoiceNumber()

which is still working in another function. I tried a few changes and ended
up changing back to what I started with, and this seems to have solved the
problem for the present!

Thanks
Dudley

I would put a couple of message boxes in the code to pop up and let you know
at which point it hung. If it has been working right along and nothing has
changed, consider the data. Compact the tables. Run SCAN Disk to rule out
disk errors.

Bonnie
http://www.dataplus-svc.com

My accounting database in Access 2002 has a function to create a one off
invoice as follows:

Private Sub Create_New_Invoice_Click()
Dim NewIN As Long
Dim ThisDate

On Error GoTo errcust
ThisDate = InputBox("What date should the new invoice be dated with?",
"Date of Invoicing", Date)
If ThisDate = "" Then Exit Sub

DoCmd.SetWarnings False
DoCmd.Hourglass True

NewIN = NewInvoiceNumber()
DoCmd.RunSQL "UPDATE Enquiries " & "SET [Invoice Number] = " & _
NewIN & " WHERE ([Customer Code] = '" & _
Me![Customer Code Field] & "') AND ([Invoice Number] = 0);", False

DoCmd.RunSQL ("INSERT INTO Invoices ([Invoice Number],[Customer
Code],[Creation Date],[One Off?]) " & _
"VALUES (" & NewIN & ",'" & Me![Customer Code Field] & "','" &
ThisDate & "', Yes);")

DoCmd.OpenForm "Invoices", , , "[Invoice Number]=" & NewIN
DoCmd.Hourglass False

errcust:
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

This has worked for years, but now it has started locking up two out of
three times it is run, and when I do Ctrl/Alt/Del it says Access not
responding.

Can anyone help please?

Thanks
Dudley