Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Unanswered: Copy records from split form's datasheet section to another table

    Hello,
    I have a split form (frmPatient Schedules) that shows me my records in the datasheet view at the bottom section of the form. My records have a field with a checkbox (ckbxHOLD). How can I get the records that don't have their checkbox checked to be copied as new records to a different table (tblHome Visits)?

    Thanks so much for your help!!

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    You don't need the same data in the two tables.

  3. #3
    Join Date
    Dec 2011
    Posts
    6
    Hi MStef-ZG, thanks for your response!

    The reason I want to copy the data is because I want frmPatient Schedules to stay static and use it more like a reference. It is a listing of the schedule of home visits required to be made for a list of patients. For example, Patient A must have a field worker visit them every Monday. Patient B must have someone visit them every Thursday. I have this tblPatient Schedules bound to a split form called frmPatient Schedules so that I could put buttons in the form top part, and the records are shown in the datasheet part on the bottom. My buttons apply a macro to filter by day (Monday button filters all records that should be seen Monday). So, supervisor can come in Monday morning, click on Monday, and see the required visits to be made that day.

    I would like my button, btnAddtoVisitLog, when clicked, to copy those filtered records to the other table called tblHome Visits. I need them to go to a different table that logs the actual visits that were made rather than that table with a static schedule that only changes when a patient is completely added or deleted from our case load (tblPatient Schedules ). The second table (tblHome Visits) is what will show the actual visits that were done by the field workers

    Can you think of another way to accomplish this without violating the Access rule of not having same info in two tables?

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    What is the look of your table (tables) ?
    What is the Primary key (keys) ?
    What is the Foreign key (keys) ?
    What is the Relationships ?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you still dont' need to copy data to another table for that, unless there are performance or security requirements
    set a flag which indicates if a record is locked and take appropriate action in your forms. eg if your flag indicates the record is locked then don't allow edits.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2011
    Posts
    6
    Well, I've figured out that I can just do an append query to copy the data from the first table to the second. That's exactly what I wanted. But the append query, which is run when I click the button on frmPatient Schedules, will always copy all the records - I need it to copy only the filtered records. Can you help me with that? I've attached my database for you to take a look at.
    Also, is there a way to only copy the records that have an unchecked checkbox in the HOLD field?

    Thanks so much for your help!

    Quote Originally Posted by MStef-ZG View Post
    What is the look of your table (tables) ?
    What is the Primary key (keys) ?
    What is the Foreign key (keys) ?
    What is the Relationships ?
    Attached Files Attached Files

  7. #7
    Join Date
    Dec 2011
    Posts
    6
    Quote Originally Posted by healdem View Post
    you still dont' need to copy data to another table for that, unless there are performance or security requirements
    set a flag which indicates if a record is locked and take appropriate action in your forms. eg if your flag indicates the record is locked then don't allow edits.
    Hi Healdem, thanks for your help! Please see my reply to MStef-ZA above as I've figured out that an append query is what I was looking for. I'm not sure what you mean by performance or security requirements..?
    Thank you!

  8. #8
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I can't read yor "Patient Visits", because I have got an Acces2000,
    and Access2002-2003.

  9. #9
    Join Date
    Dec 2011
    Posts
    6
    Oh - thanks for trying!
    Maybe you can walk me through this then if I am more specific?
    I just added a checkbox asking the user if they want to copy the record over and put a criteria on my append query to only take over records with a checked checbox.

    I have a different issue now though - I have a splitform with a couple of field in the datasheet portion whose quickfilters are not working. The two fields are combo boxes whose lookup values come from different tables. These are the two whose quickfilters do not show - the other fields are fine.

    Do you know what's causing this issue?

    Thanks so much for your help!!
    Last edited by taya621; 12-16-11 at 17:36.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by taya621 View Post
    So what should I put in the Where Condition of the append query that would tell it to copy over only records that are showing to the user?
    Probably what's in the Filter property of the form, or something that derives from it.
    Have a nice day!

  11. #11
    Join Date
    Dec 2011
    Posts
    6
    Ok, thanks for all your help
    What I've done to get around this is just create buttons with the field workers' names (which was the field in which Quickfilter was not working).

    The whole point of this filtering though was so that the user can filter records by date and worker name and then send the showing records through the Collect Data Email function Access has. I made a button so that the wizard will pop up through Run Command Macro and then CollectDataViaEmail. I've learned, however that you can only run that command with a query or table - not a form.

    So now I need to have a query run in the background, from which I can then run the CollectDataViaEmail command. However, I need the query to copy the filters that the user has put on the form so the same records can be sent to the email addresses. Is there something I can put in the criteria of the query field that tells it to look for the filters applied by the user in the form?

    Thanks so much for all your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •