Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Adding new record from data in unbound form textboxes

Discussion in 'Information Technology' started by StarfishJoe, Jul 28, 2006.

  1. StarfishJoe

    StarfishJoe
    Expand Collapse
    Guest

    I have a main form with 5 textboxes bound to a small table.
    In the center of the main form I also have 5 Text boxes the user will
    manually enter data at runtime.

    in the bottom half of the form is a subform in datasheet view. This is
    bound to another table containing records .


    On the form is a command button to
    1. Calculate a previous balance, this payment, and a new balance and update
    the totals from the first table bound to the fields at the top of the form.
    This does not add a record to this table but merely changes the lone
    record's data (Summary totals)

    2. copy the data from the text fields in the center of the form and add a
    new record to the subform ( PaymentHistory)

    3. refresh the subform so that the new record is now visible.

    4.Clear the unbound fields in the center of the form to be used again.

    The first part of this works like a charm. The 4th part to clear the form
    textboxes works like a charm. but I can not add a record to the subform
    based on the unbound controls on the main form, and then have the subform
    automatically display the added record in datasheet view.
    ---------------------------------------------------------
    Here is a little more detail:
    Summary Table Fields (bound to fields at the top of main form--no problems
    here)
    FieldName Type
    -------------------------------------
    BeginningLoanAmt Currency
    PayoffAmt (balanceDue) Currency
    LoanDate Date
    NumberPaymentsMade number
    TotalPaid Currency

    ------------------------------------------------------------------------
    PaymentHistory table Fields

    Fieldname Type
    Index (autonumber--hidden from view--not on form)
    PaymentNumber Number (a unique number incremented by 1 each time a
    payment entry is made) This one is not an autonumber because I need to keep
    reseting to 1 while testing form)
    PaymentDate Date
    CheckNumber text
    ThisPayment Currency
    NewBalance Currency
    ---------------------------------------------------------------------------
    Top of form includes a text box for each field in the Summary Table These 5
    are bound to the summary table
    Middle of form includes the following UNBAOUND Text boxes

    PaymentNumber (Calculated Field:=[Summary Table]![NumberPaymentsMade]+1)
    BeginningBalance (=PayOffAmount)
    PaymentDate (Current date of this payment--entered manually by user)
    CheckNumber (entered manually by user)
    ThisPayment (Entered Manually by user)
    NewBalance (Calculated Field: =BeginningBalance - ThisPayment)

    Command Button "OK" (see above)

    The subform is a PaymentHistory subform and apears in datasheet view.
    There is afield for every field in the PaymentHistoryTable except the Index
    field. All fields in subform are bound to this table.

    Please help me update the payment history table.
    StarfishJoe
     
  2. Loading...


  3. StarfishJoe

    StarfishJoe
    Expand Collapse
    Guest

    This is an Access2000 database running on a Windows98SE platform (at least
    for now)

    "StarfishJoe" <starfishdex2@hotmail.com> wrote in message
    news:ul5SeKXpGHA.524@TK2MSFTNGP05.phx.gbl...
    > I have a main form with 5 textboxes bound to a small table.
    > In the center of the main form I also have 5 Text boxes the user will
    > manually enter data at runtime.
    >
    > in the bottom half of the form is a subform in datasheet view. This is
    > bound to another table containing records .
    >
    >
    > On the form is a command button to
    > 1. Calculate a previous balance, this payment, and a new balance and

    update
    > the totals from the first table bound to the fields at the top of the

    form.
    > This does not add a record to this table but merely changes the lone
    > record's data (Summary totals)
    >
    > 2. copy the data from the text fields in the center of the form and add a
    > new record to the subform ( PaymentHistory)
    >
    > 3. refresh the subform so that the new record is now visible.
    >
    > 4.Clear the unbound fields in the center of the form to be used again.
    >
    > The first part of this works like a charm. The 4th part to clear the form
    > textboxes works like a charm. but I can not add a record to the subform
    > based on the unbound controls on the main form, and then have the subform
    > automatically display the added record in datasheet view.
    > ---------------------------------------------------------
    > Here is a little more detail:
    > Summary Table Fields (bound to fields at the top of main form--no problems
    > here)
    > FieldName Type
    > -------------------------------------
    > BeginningLoanAmt Currency
    > PayoffAmt (balanceDue) Currency
    > LoanDate Date
    > NumberPaymentsMade number
    > TotalPaid Currency
    >
    > ------------------------------------------------------------------------
    > PaymentHistory table Fields
    >
    > Fieldname Type
    > Index (autonumber--hidden from view--not on form)
    > PaymentNumber Number (a unique number incremented by 1 each time a
    > payment entry is made) This one is not an autonumber because I need to

    keep
    > reseting to 1 while testing form)
    > PaymentDate Date
    > CheckNumber text
    > ThisPayment Currency
    > NewBalance Currency
    > --------------------------------------------------------------------------

    -
    > Top of form includes a text box for each field in the Summary Table These

    5
    > are bound to the summary table
    > Middle of form includes the following UNBAOUND Text boxes
    >
    > PaymentNumber (Calculated Field:=[Summary

    Table]![NumberPaymentsMade]+1)
    > BeginningBalance (=PayOffAmount)
    > PaymentDate (Current date of this payment--entered manually by

    user)
    > CheckNumber (entered manually by user)
    > ThisPayment (Entered Manually by user)
    > NewBalance (Calculated Field: =BeginningBalance - ThisPayment)
    >
    > Command Button "OK" (see above)
    >
    > The subform is a PaymentHistory subform and apears in datasheet view.
    > There is afield for every field in the PaymentHistoryTable except the

    Index
    > field. All fields in subform are bound to this table.
    >
    > Please help me update the payment history table.
    > StarfishJoe
    >
    >
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You might try this methodology


    Dim RsSF AS DAO.Recordset
    Dim SF AS Access.Form
    Set SF = Me!SubFormControlName.Form
    Set RsSF = SF.RecordsetClone

    RsSF.AddNew
    RsSF.Fields("A").Value = Me!A.Value
    ....
    RsSF.Update
    SF.BookMark = RsSF.BookMark

    'if that doesn't work you'll have to do a requery on the Form - ie
    Dim SF AS Access.Form
    Set SF = Me!SubFormControlName.Form
    ' Code to Insert Data
    SF.Requery

    HTH

    Pieter




    "StarfishJoe" <starfishdex2@hotmail.com> wrote in message
    news:eCin1NXpGHA.1440@TK2MSFTNGP03.phx.gbl...
    > This is an Access2000 database running on a Windows98SE platform (at least
    > for now)
    >
    > "StarfishJoe" <starfishdex2@hotmail.com> wrote in message
    > news:ul5SeKXpGHA.524@TK2MSFTNGP05.phx.gbl...
    >> I have a main form with 5 textboxes bound to a small table.
    >> In the center of the main form I also have 5 Text boxes the user will
    >> manually enter data at runtime.
    >>
    >> in the bottom half of the form is a subform in datasheet view. This is
    >> bound to another table containing records .
    >>
    >>
    >> On the form is a command button to
    >> 1. Calculate a previous balance, this payment, and a new balance and

    > update
    >> the totals from the first table bound to the fields at the top of the

    > form.
    >> This does not add a record to this table but merely changes the lone
    >> record's data (Summary totals)
    >>
    >> 2. copy the data from the text fields in the center of the form and add a
    >> new record to the subform ( PaymentHistory)
    >>
    >> 3. refresh the subform so that the new record is now visible.
    >>
    >> 4.Clear the unbound fields in the center of the form to be used again.
    >>
    >> The first part of this works like a charm. The 4th part to clear the
    >> form
    >> textboxes works like a charm. but I can not add a record to the subform
    >> based on the unbound controls on the main form, and then have the subform
    >> automatically display the added record in datasheet view.
    >> ---------------------------------------------------------
    >> Here is a little more detail:
    >> Summary Table Fields (bound to fields at the top of main form--no
    >> problems
    >> here)
    >> FieldName Type
    >> -------------------------------------
    >> BeginningLoanAmt Currency
    >> PayoffAmt (balanceDue) Currency
    >> LoanDate Date
    >> NumberPaymentsMade number
    >> TotalPaid Currency
    >>
    >> ------------------------------------------------------------------------
    >> PaymentHistory table Fields
    >>
    >> Fieldname Type
    >> Index (autonumber--hidden from view--not on form)
    >> PaymentNumber Number (a unique number incremented by 1 each time a
    >> payment entry is made) This one is not an autonumber because I need to

    > keep
    >> reseting to 1 while testing form)
    >> PaymentDate Date
    >> CheckNumber text
    >> ThisPayment Currency
    >> NewBalance Currency
    >> --------------------------------------------------------------------------

    > -
    >> Top of form includes a text box for each field in the Summary Table These

    > 5
    >> are bound to the summary table
    >> Middle of form includes the following UNBAOUND Text boxes
    >>
    >> PaymentNumber (Calculated Field:=[Summary

    > Table]![NumberPaymentsMade]+1)
    >> BeginningBalance (=PayOffAmount)
    >> PaymentDate (Current date of this payment--entered manually by

    > user)
    >> CheckNumber (entered manually by user)
    >> ThisPayment (Entered Manually by user)
    >> NewBalance (Calculated Field: =BeginningBalance - ThisPayment)
    >>
    >> Command Button "OK" (see above)
    >>
    >> The subform is a PaymentHistory subform and apears in datasheet view.
    >> There is afield for every field in the PaymentHistoryTable except the

    > Index
    >> field. All fields in subform are bound to this table.
    >>
    >> Please help me update the payment history table.
    >> StarfishJoe
    >>
    >>
    >>

    >
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You might try this methodology


    Dim RsSF AS DAO.Recordset
    Dim SF AS Access.Form
    Set SF = Me!SubFormControlName.Form
    Set RsSF = SF.RecordsetClone

    RsSF.AddNew
    RsSF.Fields("A").Value = Me!A.Value
    ....
    RsSF.Update
    SF.BookMark = RsSF.BookMark

    'if that doesn't work you'll have to do a requery on the Form - ie
    Dim SF AS Access.Form
    Set SF = Me!SubFormControlName.Form
    ' Code to Insert Data
    SF.Requery

    HTH

    Pieter




    "StarfishJoe" <starfishdex2@hotmail.com> wrote in message
    news:eCin1NXpGHA.1440@TK2MSFTNGP03.phx.gbl...
    > This is an Access2000 database running on a Windows98SE platform (at least
    > for now)
    >
    > "StarfishJoe" <starfishdex2@hotmail.com> wrote in message
    > news:ul5SeKXpGHA.524@TK2MSFTNGP05.phx.gbl...
    >> I have a main form with 5 textboxes bound to a small table.
    >> In the center of the main form I also have 5 Text boxes the user will
    >> manually enter data at runtime.
    >>
    >> in the bottom half of the form is a subform in datasheet view. This is
    >> bound to another table containing records .
    >>
    >>
    >> On the form is a command button to
    >> 1. Calculate a previous balance, this payment, and a new balance and

    > update
    >> the totals from the first table bound to the fields at the top of the

    > form.
    >> This does not add a record to this table but merely changes the lone
    >> record's data (Summary totals)
    >>
    >> 2. copy the data from the text fields in the center of the form and add a
    >> new record to the subform ( PaymentHistory)
    >>
    >> 3. refresh the subform so that the new record is now visible.
    >>
    >> 4.Clear the unbound fields in the center of the form to be used again.
    >>
    >> The first part of this works like a charm. The 4th part to clear the
    >> form
    >> textboxes works like a charm. but I can not add a record to the subform
    >> based on the unbound controls on the main form, and then have the subform
    >> automatically display the added record in datasheet view.
    >> ---------------------------------------------------------
    >> Here is a little more detail:
    >> Summary Table Fields (bound to fields at the top of main form--no
    >> problems
    >> here)
    >> FieldName Type
    >> -------------------------------------
    >> BeginningLoanAmt Currency
    >> PayoffAmt (balanceDue) Currency
    >> LoanDate Date
    >> NumberPaymentsMade number
    >> TotalPaid Currency
    >>
    >> ------------------------------------------------------------------------
    >> PaymentHistory table Fields
    >>
    >> Fieldname Type
    >> Index (autonumber--hidden from view--not on form)
    >> PaymentNumber Number (a unique number incremented by 1 each time a
    >> payment entry is made) This one is not an autonumber because I need to

    > keep
    >> reseting to 1 while testing form)
    >> PaymentDate Date
    >> CheckNumber text
    >> ThisPayment Currency
    >> NewBalance Currency
    >> --------------------------------------------------------------------------

    > -
    >> Top of form includes a text box for each field in the Summary Table These

    > 5
    >> are bound to the summary table
    >> Middle of form includes the following UNBAOUND Text boxes
    >>
    >> PaymentNumber (Calculated Field:=[Summary

    > Table]![NumberPaymentsMade]+1)
    >> BeginningBalance (=PayOffAmount)
    >> PaymentDate (Current date of this payment--entered manually by

    > user)
    >> CheckNumber (entered manually by user)
    >> ThisPayment (Entered Manually by user)
    >> NewBalance (Calculated Field: =BeginningBalance - ThisPayment)
    >>
    >> Command Button "OK" (see above)
    >>
    >> The subform is a PaymentHistory subform and apears in datasheet view.
    >> There is afield for every field in the PaymentHistoryTable except the

    > Index
    >> field. All fields in subform are bound to this table.
    >>
    >> Please help me update the payment history table.
    >> StarfishJoe
    >>
    >>
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4197 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page