none
Trying to add add a new record with Form / Subform using VBA. New Record is not displayed.

    Question

  • Hello,

    I am trying to create an Access Form with a sub form to create Invoices.  The structure of my database uses the table InvoiceHDR  to contain one row for customer information and InvoiceDET containing multiple rows for line items sold. The tables are linked by the invoice number InvNum (a string variable).

    I built two Forms, Invoice Form (main form) and InvoiceDET subform.  When I display test records in the forms these look fine.  (See Screen Shot). 

    What I need to do is add a new Invoice using code behind a button.  The standard Access Records Operation button will not work because I need to automatically populate the Invoice Number from a concatenated string comprised of the current year and a sequential number that is obtained from a table.  When I execute my code, a new record is inserted into InvoiceHDR but the form and subform are not properly updated.  Below is the code I am using.  This uses a number of Public Variables that are created when the form is loaded.

    What I need to see is all of header information blank except the new invoice number and all of the line items blank.

    Private Sub AddNewInvoiceHeader()

       

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

    '*** New Invoices are Created using the Year + a 5 digit sequential number as the Invoice Number ***

    '*** rsInvHeader is a Public dao.recordset that points to the InvoiceHDR table                   ***

    '*** rsInvNumber is a Public dao.recordset that points to the InvoiceNumber table                ***

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

        Dim strInvNumberSuffix As String

        Dim lngLastInvNumber As Long

        Dim findCriteria As String

        zeroFill = "00000"          '***  Public Form Variable ***

        strInvNumber = ""           '***  Public Form Variable ***

       

        '***Get Last Invoice Number from table ***

        lngLastInvNumber = rsInvNumber.Fields("InvoiceNumber")

       

        '*** Setup Next Invoice Number ***

        strInvNumberSuffix = CStr(lngLastInvNumber + 1)

        strInvNumber = CStr(Year(Now())) + Left(zeroFill, (Len(strInvNumberSuffix) - 1)) + strInvNumberSuffix

       

        '***  Insert new record in invoice header table  ***

        rsInvHeader.AddNew

        rsInvHeader![InvNum] = strInvNumber

        rsInvHeader.Update

       

        '*** Update base Invoice Number table ***

        rsInvNumber.Edit

        rsInvNumber![InvoiceNumber] = CLng(strInvNumberSuffix)

        rsInvNumber.Update

       

        '***  Move to New Invoice Record  ***

        findCriteria = "InvNum LIKE '" + strInvNumber + "'"

        rsInvHeader.FindFirst (findCriteria)

       

        '*** Set focus to Main Form and refresh  ***

        Me.SetFocus

        Me.Refresh

    End Sub

    My suspicion is that the while the Form and the recordset that I have coded use the same table, they are different objects, and this is causing the problem.

    Anything that can be offered to fix this will be greatly appreciated.  Thanks in advance to all.

    Matt Paisley


    Matthew Paisley

    Friday, July 06, 2012 11:29 PM

Answers

  • I don't see the need for anything so complex.  Simply assign a value to the DefaultValue property of the InvNum control in the parent form, which you can do in its Current event procedure with:

        Dim strCriteria As String
        Dim strNextNumber As String
        
        strCriteria = "Left(InvNum,4) = """ & Year(VBA.Date) & """"
        
        If Me.NewRecord Then
            strNextNumber = Format(Nz(DMax("Right(InvNum,5)", _
                "InvoiceHDR", strCriteria), 0) + 1, "00000")
        
            Me.InvNum.DefaultValue = """" & Year(VBA.Date) & strNextNumber & """"
        End If

    You can then move to a new record in the parent form in any way whatsoever and the InvNum value will be inserted.  As this is assigned to the DefaultValue property the record is not Dirtied, so the user can back out if necessary simply by closing the form or navigating back to an existing record..

    In a multi-user environment there is the possibility of a conflict if two or more users are inserting a new record simultaneously.  Assuming that the InvNum column is designated as the primary key, or otherwise uniquely indexed this will raise an error which can be trapped in the form's Error event procedure.  You'll find an example as CustomNumber.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169


    Ken Sheridan, Stafford, England

    • Marked as answer by Matt Paisley Sunday, July 08, 2012 3:49 PM
    Saturday, July 07, 2012 12:13 AM
  • Should I use your solution above with my code or should I implement this with a native Access "Add New Record" button?
    The latter.  By computing and setting the DefaultValue property of the control in the form's Current event procedure, any further code to compute the value for a new record is unnecessary.

    Ken Sheridan, Stafford, England

    • Marked as answer by Matt Paisley Sunday, July 08, 2012 3:49 PM
    Saturday, July 07, 2012 8:20 PM

All replies

  • I don't see the need for anything so complex.  Simply assign a value to the DefaultValue property of the InvNum control in the parent form, which you can do in its Current event procedure with:

        Dim strCriteria As String
        Dim strNextNumber As String
        
        strCriteria = "Left(InvNum,4) = """ & Year(VBA.Date) & """"
        
        If Me.NewRecord Then
            strNextNumber = Format(Nz(DMax("Right(InvNum,5)", _
                "InvoiceHDR", strCriteria), 0) + 1, "00000")
        
            Me.InvNum.DefaultValue = """" & Year(VBA.Date) & strNextNumber & """"
        End If

    You can then move to a new record in the parent form in any way whatsoever and the InvNum value will be inserted.  As this is assigned to the DefaultValue property the record is not Dirtied, so the user can back out if necessary simply by closing the form or navigating back to an existing record..

    In a multi-user environment there is the possibility of a conflict if two or more users are inserting a new record simultaneously.  Assuming that the InvNum column is designated as the primary key, or otherwise uniquely indexed this will raise an error which can be trapped in the form's Error event procedure.  You'll find an example as CustomNumber.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169


    Ken Sheridan, Stafford, England

    • Marked as answer by Matt Paisley Sunday, July 08, 2012 3:49 PM
    Saturday, July 07, 2012 12:13 AM
  • Ken,

    Thanks for yor response.

    Should I use your solution above with my code or should I implement this with a native Access "Add New Record" button?

    Thanks so much for your help.

    Matt


    Matthew Paisley

    Saturday, July 07, 2012 4:20 PM
  • Should I use your solution above with my code or should I implement this with a native Access "Add New Record" button?
    The latter.  By computing and setting the DefaultValue property of the control in the form's Current event procedure, any further code to compute the value for a new record is unnecessary.

    Ken Sheridan, Stafford, England

    • Marked as answer by Matt Paisley Sunday, July 08, 2012 3:49 PM
    Saturday, July 07, 2012 8:20 PM
  • Ken,

    I tried your solution with some necessary tweaking and it works as expected.  However, I don't see the new invoice in the InvoiceHDR table.  I tried adding a "Save Record" button but that also didn't work.  What do I need to do to save the new record?

    Thanks again for your help. 

    Regards,

    Matt


    Matthew Paisley

    Saturday, July 07, 2012 8:28 PM
  • When you set the DefaultValue property of a field or control it does not initiate a new record.  It merely establishes a value which will be automatically inserted into the field in question when a new record is created.  A new record is initiated when other values are inserted into other bound controls in the form.  As an invoice record which contained nothing but an invoice number would be pointless this is not a problem.  In fact, in a well designed database, it should be impossible to save a record which does not have other values in at least some other fields, e.g. the invoice date and customer fields, which is achieved by setting the Required property of these fields to True (Yes) in the table design.

    The record will be saved once the user moves focus to the subform or moves to another invoice record, though the latter without the former would be rather unusual as an invoice with no invoice details would then have been created.

    There can very occasionally be situations where it would be appropriate to save a record with no values in any columns other than the key, for which a default value has been computed.  I can't see that this can legitimately be one, but if we assume for the moment that it is, the trick is to set the set the value of the key to itself before saving the record.  The code for this would be:

        Me.InvNum = Me.InvNum
        Me.Dirty = False

    Ken Sheridan, Stafford, England

    Saturday, July 07, 2012 9:11 PM
  • My thanks to  Ken for his help.

    By using the native "Add Record" button provided by Access and adding some additional code based on the "NewRecord" prperty of the form, I was able to accomplish my objective (see thread above).  All I needed to to was to set default values for other fields on the form.

    Regards,

    Matt Paisley

    Woodbridge, CT


    Matthew Paisley

    Sunday, July 08, 2012 3:56 PM