VBA on button click seems to be unexplainably skipping lines

[TazoTazmaniac]

Hi everyone,



I have form to book in a delivery of furniture, and on that form is a button with the code posted below. It has been running fine for a while, but recently it seems to have started skipping some of the lines and I can't work out why. Most of it runs fine, but it just doesn't come up with an input box that should appear every time. The strange thing is that sometimes it appears and sometimes it doesn't and there doesn't seem to be any particular similarities in the deliveries that are being booked when it does go wrong. The input box it is skipping is between the stars. Anybody got any ideas of what I could try?

Private Sub cmdBookDelivery_Click()

Dim iNoDate As Integer
Dim iNumFurn As Integer
Dim strTimeNeeded As String
Dim strFullName As String
Dim strAddress As String
Dim LAncillaries As Integer

On Error GoTo ErrHandler

If IsNull(Me.Invoice) = True Then

        MsgBox "Please select who to invoice."
        Me.Invoice.SetFocus
        Exit Sub
    
    Else
    
        If IsNull(Me.TakenBy) = True Then
            
            MsgBox "Please fill in 'Taken By' field."
            Me.TakenBy.SetFocus
            Exit Sub
            
                Else
        
                If IsNull(Me.DeliveryDate) = True Then
                        
                        iNoDate = MsgBox("There is no delivery date set for this delivery.  Book anyway?", vbOKCancel)
                        
                        If iNoDate = vbOK Then
                                
                                'Do nothing
                            Else
                                Exit Sub
                        End If
                    
                    Else
                        'Do nothing
                
                End If
                
                DoCmd.RunCommand acCmdSaveRecord
                
                        If DCount("*", "qryNewPendingDelFurnSub", "[Free] < [NumberAllocated] AND [NumberAllocated]>0 AND DeliveryID = " & Forms!frmDelivery.DeliveryID) > 0 Then
                        
                                DoCmd.OpenForm "frmOverAllocationWarningBox"
                        
                            Else
                                If DateDiff("d", [DateTaken], [DeliveryDate]) > 5 Then
                        
                                        MsgBox "Have you sent a postcard?", , "Reminder"
                                    Else
                                        'Do nothing
                                End If
                        
                                If Me.Invoice.Value = "Agency" Then
                                
                                        MsgBox "Have you requested a purchase order number?", , "Reminder"
                                    Else
                                        'Do nothing
                                End If
                                
                                iNumFurn = DLookup("TotalNumRequested", "qryTotalNumRequestedAllocatedFurniture", "DeliveryID = " & Me.DeliveryID)
                                
                                If iNumFurn < 4 Then
                                
                                        strTimeNeeded = "1/2 hour"
                                
                                    ElseIf iNumFurn < 11 Then
                                    
                                        strTimeNeeded = "1 hour"
                                    
                                    ElseIf iNumFurn < 16 Then
                                    
                                        strTimeNeeded = "1 & 1/2 hour"
                                    
                                    Else
                                        strTimeNeeded = "2 hour"
                                    
                                End If
                                
                                strFullName = DLookup("ContactFirstName", "tblClientDonorContact", "ContactID = " & Me.ContactID) & " " & _
                                DLookup("ContactLastName", "tblClientDonorContact", "ContactID = " & Me.ContactID)
                                
                                strAddress = DLookup("Area", "tblAddresses", "AddressID = " & Me.AddressID) & ", " & _
                                DLookup("Postcode", "tblAddresses", "AddressID = " & Me.AddressID)
                                
'**************************** HERE *************************************

                                InputBox "Please add an appointment in the calendar." & vbCrLf & vbCrLf & "Appointment length:" & vbCrLf & strTimeNeeded & _
                                vbCrLf & vbCrLf & "Copy and paste the following:", "Reminder", "Delivery - " & strFullName & " - " _
                                & strAddress & " - " & Me.DelBeforeAfter

'**********************************************************************

                        
                                DoCmd.SetWarnings False
                                DoCmd.OpenQuery "qryDeliveryUpdateStock"
                                DoCmd.SetWarnings True
                                        
                                If DCount("*", "tblAllocatedAncillaries", "DeliveryID = " & Forms!frmDelivery.DeliveryID) > 0 Then
                                    
                                        LAncillaries = MsgBox("View/edit ancillary report before printing?", vbYesNoCancel)
                        
                                        If LAncillaries = vbYes Then
                            
                                                DoCmd.OpenReport "rptAncillaryReport", acViewReport, , "DeliveryID = " & Forms!frmDelivery.DeliveryID
                                                DoCmd.Close acForm, "frmDelivery"
                                                MsgBox "Delivery booked."
                                                
                                            ElseIf LAncillaries = vbNo Then
                                    
                                                DoCmd.OpenReport "rptAncillaryReport", acViewReport, , "DeliveryID = " & Forms!frmDelivery.DeliveryID
                                                Application.DoCmd.SelectObject acReport, "rptAncillaryReport"
                                                DoCmd.PrintOut acPages, 2, 2
                                                DoCmd.Close acReport, "rptAncillaryReport"
                                                DoCmd.Close acForm, "frmDelivery"
                                                MsgBox "Delivery booked."
                                                DoCmd.OpenForm "frmPhoneCalls", acNormal, , , acFormAdd
                        
                                            ElseIf LAncillaries = vbCancel Then
                        
                                                MsgBox "Delivery booked.  Ancillary sheet can be printed from Pending Deliveries."
                                                DoCmd.Close acForm, "frmDelivery"
                                                DoCmd.OpenForm "frmPhoneCalls", acNormal, , , acFormAdd
                                         
                                        End If
                                         
                                    Else
                                         DoCmd.Close acForm, "frmDelivery"
                                         MsgBox "Delivery booked."
                                         DoCmd.OpenForm "frmPhoneCalls", acNormal, , , acFormAdd
                                End If
                        
                        End If
            End If
End If

ErrExit:
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbOKOnly, "ERROR"
    LogError "cmdBookDelivery_Click", Err.Number, Err.Description
    Resume ErrExit

End Sub

[pbaldy]

It does everything above and below that? That seems like an odd use of InputBox. I'd expect to set a variable to it or something, like:

VariableName = InputBox(...)

What is it supposed to do? Or could/should a simple MsgBox be used?

[TazoTazmaniac]

Haha I should have guessed that would have been a question :-) I needed something that would bring up a string of text that could just be copied and pasted into another programme. A message box wouldn't work as I couldn't copy and paste the text from it. That's the only reason why an input box was used-I'm open to other suggestions of any better way, but I'm not sure that will solve the current problem.

As for your other question; yes, everything before and after seems to work fine every time, although with the variables making up the string I can't strictly tell about as that would only be shown in the input box if that worked.

[Perceptus]

you could try inserting some debug.print statements above and below the problem line. I would use "On error goto 0" and see if it spits you any good info

[pbaldy]

I've never needed to copy to the clipboard but there are a couple of methods here:

https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx

[ssanfu]

Instead of the Input box, why can't/couldn't you use an unbound text box on the form? Something like:

Me.tbAddAppointment = Null  'clear the text box before adding new message
Me.tbAddAppointment = "Please add an appointment in the calendar." & vbCrLf & vbCrLf & "Appointment length:" & vbCrLf & strTimeNeeded & _
                                vbCrLf & vbCrLf & "Copy and paste the following:", "Reminder", "Delivery - " & strFullName & " - " _
                                & strAddress & " - " & Me.DelBeforeAfter

[TazoTazmaniac]

Thanks for the ideas guys, I shall have a look into it when I get a chance. Both look a whole lot better than what I've got at the moment. I'll also give the debug.print method a go and post back if I find anything. Thanks!