Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to put a requirement for fields (2003)

    Hello,

    I have a form that I would like to create a VBA in the "BeforeUpdate" event. I have a main form and three tabs with subforms. I want the following fields to have a value before the record is updated:

    CompanyName
    InvoiceDate
    Program
    InvoicePeriod
    ReceivedDate
    CompanyManager
    Invoiceamount

    One of those tabs mentioned above has a subform called "InvoiceSub". In this "InvoiceSub" subform, I have a field where the invoice amount from the main form gets broken down to line items (i.e. personnal cost, operating expenses. etc.) How can I put a command in the main form so that if there is a dollar in the invoiceamount field, the "InvoiceSub" subform gets fill out too. Any help would be great. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to put a requirement for fields (2003)

    Do you mean that you want to make sure that the InvoiceSub subform has been filled in, or do you want the InvoiceSub subform to be filled in automatically?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to put a requirement for fields (2003)

    I want to have the user to put in the breakdown for the invoice in the subform if there is amount showing in the main form field "InvoiceAmount"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to put a requirement for fields (2003)

    Let's say that you have a field InvoiceID that identifies the invoice, and that the table containing the line items is tblInvoiceSub.
    This table contains InvoiceID and a currency field LineAmount.

    You could put code like this in the Before Update event of the main form:

    ...
    Dim curSum As Currency
    curSum = Nz(DSum("LineAmount", "tblInvoiceSub", "InvoiceID=" & Me.InvoiceID), 0)
    If curSum < Me.InvoiceAmount Then
    MsgBox "Not enough line items!", vbCritical
    Cancel = True
    End If
    ...

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to put a requirement for fields (2003)

    Thanks Hans. how can I also make the fields in the main form require too.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to put a requirement for fields (2003)

    You could set the Required property of these fields to Yes in the table. Or use code like this in the Before Update evenrt of the form:

    If IsNull(Me.CompanyName) Then
    MsgBox "Please enter a company name!", vbExclamation
    Me.CompanyName.SetFocus
    Cancel = True
    Exit Sub
    End If

    And similar for InvoiceDate, Program etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •