locked
Create a button to copy certain fields in a form RRS feed

  • Question

  • I am creating a form to update data.  The number of records will not change and all records have some data.  Field staff will be entering point data, for which there will be 20 tests per location.  The Lat, Long, Address, Zip, and TestDate fields will be constant for all 20 tests at each location.  I want to give them the option of copying these field values from the previous record - one entry, 20 records.  I have found countless posts for creating new records, but nothing that would update select fields in existing records.
    Thursday, May 26, 2016 9:01 PM

Answers

  • If, as seems to be the case, these data are contained in a single table, then the root of the problem is the design of the table.  The table has multiple columns which are not functionally determined by the key and consequently is not normalized to Second Normal Form (2NF).  Not only does this require repetitive data entry, but more importantly, leaves the table open to the risk of update anomalies.

    The solution is to decompose the table to eliminate the redundancies.  From your description it sounds like you need three tables, Locations, TestEvents and TestResults.  Locations would contain columns such as Lat, Long etc which are attributes functionally determined solely by the whole of the table's key.  Consequently there will be one row per location.  TestEvents would contain, in addition to its primary key, a foreign key referencing the primary key of Locations, and a TestDate column.  This assumes that there can be test events on more than one date at each location.  TestResults would, in addition to its primary key, contain a foreign key referencing the primary key of TestEvents.

    A possible interface would be a locations form, in single form view,  in which there is embedded a test events subform, also in single form view, and within that subform a test results subform in continuous forms view.  Alternatively the test events subform could be in continuous forms view, in which case the test results subform would be embedded directly in the parent locations form and correlated with the test events subform.  The test results subform would thus be requeried when you navigate to a different row in the test events subform, showing the results for that event.  This is achieved by basing the test results subform on a query which references the key of the test events subform's recordset as a parameter.

    A correctly normalized model and an interface which mirrors the model would eliminate the need for what you are currently attempting, but more importantly, would protect the integrity of the data.


    Ken Sheridan, Stafford, England

    Thursday, May 26, 2016 9:59 PM

All replies

  • Hi Robert. Have you considered using a form/subform setup instead? Just curious...
    Thursday, May 26, 2016 9:09 PM
  • I looked at that as an option, but I could not find a way to tie them together.  For example:

    Primary Key = 1S1

    This is site 1, Sprint, call 1.  The data that will be copied is all site 1 values will have the same Lat, Long, Address, Zip, and TestDate 

    How would I tie record 1S1 to 1S2, 1S3, 1S4, 1S5, 1A1..., 1T1..., 1V1... and so on using a sub form.  Scrape the number before the S and match it to all 1 values in the CallNumb field, and populate those selected fields within the selected records?

    Thursday, May 26, 2016 9:18 PM
  • Not sure if this helps, but pressing Ctrl ' (ctrl + the apostrophe key) copies a single field value from the previous record to your current record.  If your data is being presented in datasheet or continuous forms view, this could be a pretty straightforward approach.

    -Bruce

    Thursday, May 26, 2016 9:35 PM
  • I looked at that as an option, but I could not find a way to tie them together.  For example:

    Primary Key = 1S1

    This is site 1, Sprint, call 1.  The data that will be copied is all site 1 values will have the same Lat, Long, Address, Zip, and TestDate 

    How would I tie record 1S1 to 1S2, 1S3, 1S4, 1S5, 1A1..., 1T1..., 1V1... and so on using a sub form.  Scrape the number before the S and match it to all 1 values in the CallNumb field, and populate those selected fields within the selected records?

    Hi. A Lat/Long location is pretty fixed, in my estimation, so if you have a record "1S1" with a Lat/Long similar to 1S2, 1S3, etc., then I would say you need a table of Lat/Long and then use its Primary Key in the other table. For example:

    tblTests
    TestID, pk
    Lat
    Long
    Address
    Zip
    TestDate

    tblTestResults
    ResultID, pk
    TestID, fk
    OtherFields
    etc...

    Hope it helps...

    Thursday, May 26, 2016 9:54 PM
  • Perhaps it is the fact that I have been working on this form all day, but I am not getting Ctrl` to do anyth...

    OH FOR ^%#&*%$(&^@# Mother #*%^(*#^%(*# and )%*(&)*(#&@)%&*

    My left hand was excited to press ` when my right hand should have been pressing '

    Beer time...

    Thursday, May 26, 2016 9:56 PM
  • That is the answer for today.  Still wish there was a POPULATE button to grab the 5 values and paste them in...
    Thursday, May 26, 2016 9:57 PM
  • If, as seems to be the case, these data are contained in a single table, then the root of the problem is the design of the table.  The table has multiple columns which are not functionally determined by the key and consequently is not normalized to Second Normal Form (2NF).  Not only does this require repetitive data entry, but more importantly, leaves the table open to the risk of update anomalies.

    The solution is to decompose the table to eliminate the redundancies.  From your description it sounds like you need three tables, Locations, TestEvents and TestResults.  Locations would contain columns such as Lat, Long etc which are attributes functionally determined solely by the whole of the table's key.  Consequently there will be one row per location.  TestEvents would contain, in addition to its primary key, a foreign key referencing the primary key of Locations, and a TestDate column.  This assumes that there can be test events on more than one date at each location.  TestResults would, in addition to its primary key, contain a foreign key referencing the primary key of TestEvents.

    A possible interface would be a locations form, in single form view,  in which there is embedded a test events subform, also in single form view, and within that subform a test results subform in continuous forms view.  Alternatively the test events subform could be in continuous forms view, in which case the test results subform would be embedded directly in the parent locations form and correlated with the test events subform.  The test results subform would thus be requeried when you navigate to a different row in the test events subform, showing the results for that event.  This is achieved by basing the test results subform on a query which references the key of the test events subform's recordset as a parameter.

    A correctly normalized model and an interface which mirrors the model would eliminate the need for what you are currently attempting, but more importantly, would protect the integrity of the data.


    Ken Sheridan, Stafford, England

    Thursday, May 26, 2016 9:59 PM
  • Hi Robert the Map Guy,

    I think that the suggestion given by the Ken Sheridan is the proper and suitable suggestion to your question. I also agree with  Ken Sheridan and the DB guy that you need to normalize the tables.

    so I hope you understand the whole scenario and how can you solve your issue by the given suggestions.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 27, 2016 12:54 AM