Lookup current record with ComboBox, any changes create new record

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.

 

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

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.

 

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?

 

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

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?