Form Frustration!!!!

[mousemat]

Hi all, not been here for a while....a few years anyway!

Created an un-bound form that does what I want except for one thing. I have a cbo box that needs to be set to either yes or no. The I have a button which processes various things, but crucially dependant upon what the cbo box says, it will either add the record with the PaidDate if cbobox is YES or add the record without the PaidDate if cboBox is NO.

The above wrks, as in, the records are added correctly, however, on both scenarios, its should close the form and refresh the underlying form to display the invoice details.

If the cboBox is set to NO it works exactly as required. But if the cbobox is set to YES, the record is added, but the form doesn't close and the underlying form isnt refreshed.

Im missing something but what??? cboBox is cboPaid

heres the code:
Button to add record

Private Sub cmd_add_Click()

    If IsNull(Description) Or Description = "" Then
            MsgBox ("Please enter a Description for this Invoice!")
            Description.SetFocus
            
            Else

    If Amount = "" Or IsNull(Amount) Then
            MsgBox ("Please enter an Amount for this Invoice!")
            Amount.SetFocus
            
            Else
            
   
If IsNull(cboPaid) Or cboPaid = "" Then
        MsgBox "You must state if this Invoice has been Paid or Not!", vbOKOnly, "Missing Data"
        cboPaid.SetFocus
                           
        Else
            cmd_Preview.Visible = True
            

    Dim strDocName As String 'Sets the report to print the current record on the screen
    Dim strWhere As String
    strDocName = "Invoice" 'Report Title
    strWhere = "[CustomerID]= " & Me!CustomerID
    
    If cboPaid = "Yes" Then
    
    Call AddPaidInvoice
    
    Else
           
    Call AddInvoice
      
    
   'remove this later DoCmd.OpenReport strDocName, acNormal, , strWhere          'Prints the copy of the report
    [Forms]![tbl_Customers1]![lstInvoice].Requery

   DoCmd.Close
  
   End If
   End If
   End If
   End If
    
End Sub

Here is the code for the AddInvoice and AddPaidinvoice

Public Sub AddInvoice()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tbl_Invoices", dbOpenDynaset)
    With rst
    .AddNew
    rst!CustomerID = CustomerID
    rst!ContactName = ContactName
    rst!Date = Date
    rst!Description = Description
    rst!Amount = Amount
    rst!InvoiceNumber = InvoiceNo
    rst!CompanyName = CompanyName
    rst!VValue = VValue
    rst!Paid = cboPaid
        .Update
    End With
    
End Sub


Public Sub AddPaidInvoice()
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tbl_Invoices", dbOpenDynaset)
    With rst
    .AddNew
    rst!CustomerID = CustomerID
    rst!ContactName = ContactName
    rst!Date = Date
    rst!Description = Description
    rst!Amount = Amount
    rst!InvoiceNumber = InvoiceNo
    rst!CompanyName = CompanyName
    rst!VValue = VValue
    rst!Paid = cboPaid
    rst!PaidDate = Date
    .Update
    End With
    
End Sub

 

[Minty]

Your rather long winded If Then Else construct has tripped you up.

If cboPaid = "Yes" Then

                    Call AddPaidInvoice

                Else

                    Call AddInvoice


                    'remove this later DoCmd.OpenReport strDocName, acNormal, , strWhere          'Prints the copy of the report
                  [COLOR="Red"]  [Forms]![tbl_Customers1]![lstInvoice].Requery

                    DoCmd.Close[/COLOR]

                End If

You need to do the bit in red for both I think.

 

[mousemat]

Minty

Excellent.

I knew i was missing something but just couldn't see what.

Many thanks for that, works like a charm now!!!

 

[Minty]

I would consider changing your If Then construct to simply

If Your Validation = False Then 
MsgBox "Fill it in"
Exit Sub
End If

Although somewhat less elegant from a programming point of view it does make following your code a lot simpler. There are a host of other ways of achieving similar results depending on the data requirements.