Lookup current record with ComboBox, any changes create new record

Discussion in 'Access Forms Coding' started by newbiesupreme@yahoo.com, Aug 26, 2012.

  1. Guest

    In a form, I am trying to:

    1) Have a scrollable multi-row subform that has, let's say, 10 rows total,but shows 5, and the subform can be scrolled down to see the other 5. Thefields that comprise each row are: a Combobox that looks up Social Security Number in Table A, Name, address, and salary. Name, Address, and Salaryare 3 fields from Table A (there are many other fields in Table A; this subform only shows 3)
    2) The combobox in each row of this 10-row subform would, afterupdate, load the current values for a record that matches that combobox value in the rest of the fields (e.g. user chooses social secuity number, and the matching record from Table A populates the Name, Address, and salary fields in therow where user used the combobox)
    3) User would then make a change to any or all of the 3 fields (Name, Address, Salary), and would repeat this process for up to ten records (recall that this subform shows 10 rows)
    4) Upon clicking a button, Access would see if any of the values in each record were changed (comparing their current value to the value in Table A for that record), and if they were, create a NEW record in Table A that is acopy of the record that was looked up, but with the changed values from this subform

    So you see, a bound form won't work, as I don't want to CHANGE any of the current data in the table, I just want to COPY a record if there was a change made to any of those 3 fields, and the new record would have these changed values in their respective fields.

    My idea was to actually use a bound form, but have the bound fields invisible, and their only purp;ose would be to set the initial value of the UNBound fields. But I can't get the combobox to work for all rows (that's a typical problem, and I've seen the solution to it for bound forms, but not thisparticular scenatio of mine).

    I'm familiar with the VBA editor.

    Any help, ideas, or code for this would be greatly appreciated. It's also possible I'm going about this completely the wrong way, so if you think there's a better aproach, please let me know; I'm just trying to make it possible for a user to make changes to specific fields via a multi-row form interface (I don't want to use one combobox in a form header to do one at a time; it would be too tedious), and any change would actually be a new record,with all data the same except for the changes made by the user.

    Again, thanks for any help and for reading.
     
    , Aug 26, 2012
    #1
    1. Advertisements

  2. This seems to me to be a somewhat unusual configuration. It's difficult for
    me to figure out what you are trying to _accomplish_ with what you
    describe -- I can read, and think I understand, how you want to do it, but
    am puzzled as to _why_ you'd want to do that.

    Perhaps, too, I am puzzled why you'd want to add records for the same
    individual with each change of data (and, you don't mention any date/time or
    other idenifier to indicate which is the most recent).

    I suspect I'd find a user interface as you describe _more_ "tedious" than
    using a Combo Box to select one record at a time. But, it's quite possible
    that I just don't understand the needs of your use of the form/subform/data.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    <> wrote in message
    news:...
    In a form, I am trying to:

    1) Have a scrollable multi-row subform that has, let's say, 10 rows total,
    but shows 5, and the subform can be scrolled down to see the other 5. The
    fields that comprise each row are: a Combobox that looks up Social Security
    Number in Table A, Name, address, and salary. Name, Address, and Salary are
    3 fields from Table A (there are many other fields in Table A; this subform
    only shows 3)
    2) The combobox in each row of this 10-row subform would, afterupdate, load
    the current values for a record that matches that combobox value in the rest
    of the fields (e.g. user chooses social secuity number, and the matching
    record from Table A populates the Name, Address, and salary fields in the
    row where user used the combobox)
    3) User would then make a change to any or all of the 3 fields (Name,
    Address, Salary), and would repeat this process for up to ten records
    (recall that this subform shows 10 rows)
    4) Upon clicking a button, Access would see if any of the values in each
    record were changed (comparing their current value to the value in Table A
    for that record), and if they were, create a NEW record in Table A that is a
    copy of the record that was looked up, but with the changed values from this
    subform

    So you see, a bound form won't work, as I don't want to CHANGE any of the
    current data in the table, I just want to COPY a record if there was a
    change made to any of those 3 fields, and the new record would have these
    changed values in their respective fields.

    My idea was to actually use a bound form, but have the bound fields
    invisible, and their only purp;ose would be to set the initial value of the
    UNBound fields. But I can't get the combobox to work for all rows (that's a
    typical problem, and I've seen the solution to it for bound forms, but not
    this particular scenatio of mine).

    I'm familiar with the VBA editor.

    Any help, ideas, or code for this would be greatly appreciated. It's also
    possible I'm going about this completely the wrong way, so if you think
    there's a better aproach, please let me know; I'm just trying to make it
    possible for a user to make changes to specific fields via a multi-row form
    interface (I don't want to use one combobox in a form header to do one at a
    time; it would be too tedious), and any change would actually be a new
    record, with all data the same except for the changes made by the user.

    Again, thanks for any help and for reading.
     
    Access Developer, Aug 26, 2012
    #2
    1. Advertisements

  3. Guest

    Thanks for reading, Larry, and yes, it IS a bit tedious and unusual. The issue is that the records are later exported to other applications, which use the value in every field to perform operations. We only want to use the LATEST record, but we still want past values for the record stores, and we can only use one table. So, every record could potentially have copies where all but three 4 values are different (the 3 that could be changed and the date stamp of the change). Not good design, I know, but normalisation was not the focus (as it rarely is, am I right?).

    with assistance, I have it almost working using DLookup to populate currentvalues on the user's form when they pick the SS number. I only now need to, upon their click of a button to commit changes, make the new record in TableA where all values are the same except for the fields in the form (Name, Address, Salary, and the record datestamp). So basically duplicating therecord, but with those 4 field values coming from the form instead of TableA.

    Would the best way to do that just be to include all 20 fields from Table Ain the form when the user selects a value inthe combobox, but have the 17 unchnaging ones invisible? Then nI'd set all their values with DLookup, and on the click of the button, run the Append query and include all the fields, visible and invisible, from the form?
     
    , Aug 26, 2012
    #3
  4. Computing, like life, "is full of little surprises" -- requirements/needs
    that are not in the "usual" category but justified. I _try_ to politely
    inquire, rather than jumping to conclusions as some do (and making a fool of
    myself in public... I've done that 'way too many times already <GRIN>).

    In the situation you describe, VBA code would be my approach, too. I'm not
    sure I'd use DLookup and an Append Query instead of DAO code to read the
    additional values and to append the new record, but either should work.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    <> wrote in message
    news:...
    Thanks for reading, Larry, and yes, it IS a bit tedious and unusual. The
    issue is that the records are later exported to other applications, which
    use the value in every field to perform operations. We only want to use the
    LATEST record, but we still want past values for the record stores, and we
    can only use one table. So, every record could potentially have copies
    where all but three 4 values are different (the 3 that could be changed and
    the date stamp of the change). Not good design, I know, but normalisation
    was not the focus (as it rarely is, am I right?).

    with assistance, I have it almost working using DLookup to populate current
    values on the user's form when they pick the SS number. I only now need to,
    upon their click of a button to commit changes, make the new record in
    TableA where all values are the same except for the fields in the form
    (Name, Address, Salary, and the record datestamp). So basically duplicating
    the record, but with those 4 field values coming from the form instead of
    TableA.

    Would the best way to do that just be to include all 20 fields from Table A
    in the form when the user selects a value inthe combobox, but have the 17
    unchnaging ones invisible? Then nI'd set all their values with DLookup, and
    on the click of the button, run the Append query and include all the fields,
    visible and invisible, from the form?
     
    Access Developer, Aug 26, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rajesh B. Patel

    Event that fires everytime any value in any control changes...

    Rajesh B. Patel, Apr 9, 2004, in forum: Access Forms Coding
    Replies:
    3
    Views:
    102
  2. schuh.mike

    ComboBox record lookup

    schuh.mike, Jul 21, 2006, in forum: Access Forms Coding
    Replies:
    1
    Views:
    94
    Steve Schapel
    Jul 22, 2006
  3. Elizabeth Swoope

    "Date updated" in "one" record any time a "many record" changes?

    Elizabeth Swoope, Aug 17, 2006, in forum: Access Forms Coding
    Replies:
    8
    Views:
    91
    Elizabeth Swoope
    Aug 19, 2006
  4. Steve S

    ComboBox Current Record Pointer

    Steve S, Oct 24, 2006, in forum: Access Forms Coding
    Replies:
    3
    Views:
    97
    Dirk Goldgar
    Oct 25, 2006
  5. PureRadiantJoy

    Dependant Combobox not showing current value if record viewed

    PureRadiantJoy, Aug 23, 2007, in forum: Access Forms Coding
    Replies:
    1
    Views:
    106
    George Nicholson
    Aug 23, 2007
  6. disneygoof via AccessMonster.com

    ComboBox: Update a value in current record before changing the ComboBox Value

    disneygoof via AccessMonster.com, Mar 7, 2008, in forum: Access Forms Coding
    Replies:
    1
    Views:
    177
    miss031
    Mar 8, 2008
  7. David_Beginner via AccessMonster.com

    combobox lookup works but can't add new record

    David_Beginner via AccessMonster.com, Aug 2, 2008, in forum: Access Forms Coding
    Replies:
    1
    Views:
    104
    Asad Ali
    Aug 2, 2008
  8. Mark Kubicki
    Replies:
    1
    Views:
    347
    strive4peace
    Jan 16, 2009
Loading...