Results 1 to 10 of 10
  1. #1
    ayatsweid is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    18

    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
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why do you need to copy records? This should not be necessary in a relational database. Duplication of data is contrary to basic database principle to not duplicate data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ayatsweid is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    18
    Hi June7, 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.
    However, I've found out that I can do this with an append query which i great.
    I'm having problems with the Quickfilter function in the spilt form now for a few fields - can you help me with that?

    My splitform has two fields that 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!!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I believe that is an issue with 'lookup' values and not correctable with the quickfilter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ayatsweid is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    18
    I see, I was afraid of that. Ok, I've created buttons then with the values I wanted to be able to be Quickfiltered which partly solves the problem.
    See the Field I want to be able to filter by is the Field Worker's name. So I created the buttons with each person's name. But I have the form's on load event filtering to today's date. But when I click the button which is an ApplyFilter, it filters by that person's name for all the records in the database - it doesn't filter by the already filtered records by date. So I end up getting all of that person's records for many dates when I just want it for today's date.
    How can I get my buttons to filter by worker name only through the records that are already filtered by today's date because of the on load event.

    Also, I have a button called btnSndEmails in which I RunCommand CollectDataViaEmail but I get an error message that says that this is not available. I figure this is because I can't collect data from a form but I know I can do it from a query. My issue though is that if I were to run a query, I need the query to copy the same filters that the user has manipulated. The user may have clicked on a name to filter the records by that, then clicked on a date to filter by that so now I have two filters that I need the query to copy so that I can run the collectdataviaemail command. Is that possible??

    Thanks June7!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to apply multiple filter criteria? Check this tutorial http://datapigtechnologies.com/flash...tomfilter.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ayatsweid is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    18
    Hi June7,
    That's not quite what I want. I need the criteria to copy whatever filters the user has selected through the buttons for the employee name and through the Quickfilter for the date field.

    So the user will go into the form where are the records are displayed, they will then click one of the buttons with an employee name and the records will be filtered to just records belonging to that employee. Then they will filter down to only one or two dates in the Date field. Then I need the query to run but to copy the filers the user has selected. So I don't know what the user will pick and they will be filtering many times a day so the query criteria needs to apply the same filter the user applies so it comes up with the same records the user came up with.


    Does that make sense?
    Thank you!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Clear as mud! No, I don't understand why you need the quickfilter instead of using unbound comboboxes to define filter criteria. What do you mean by 'run the query'? Aren't you filtering the form's recordset?

    It is possible through VBA code to modify saved query definition.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ayatsweid is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    18
    Hi June,
    sorry about that.
    I need to have a query based on the form run.

    Basically I want the user to filter down to the records they want in my form. Then send the Collect Data Email through access to collect information about those records. But that button will not work with a form - it will only work with tables and queries and so that's why I need to run a query in the background that would give me the same records the form has after its filters so that the collect data email will be sent for the records the user sees.

    Does that help?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, as shown in the tutorial, build a query and save it. Set up parameters in the query that refer to the unbound criteria controls. Use this query for the CollectData process. Can also use it as the form's RecordSource. That's the easiest way I can see to do this. Otherwise, need VBA code that modifies query definition, something I have resorted to only once.

    However, if you want multiple date criteria (OR logic) that gets more complicated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-18-2011, 01:52 AM
  2. Replies: 3
    Last Post: 10-24-2011, 11:42 AM
  3. Formatting split form datasheet portion
    By SpaceEd in forum Forms
    Replies: 5
    Last Post: 10-19-2011, 11:40 AM
  4. Code to call Split Form View Datasheet
    By ahightower in forum Programming
    Replies: 1
    Last Post: 07-28-2011, 04:57 PM
  5. Replies: 2
    Last Post: 04-02-2010, 07:42 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums