Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23

    Copy Records


    This sounds very similar to my situation https://www.accessforums.net/forms/c...ous-35615.html, if I am following correctly. I have a main form that uses a date as the value along with an autonumber primary key and is stored in a table called TestDate. The table just below that is the TestBuildings table with an autonumber primary key. That table just below TestBuildings is ChamberDays with an autonumber primary key. I have FKs on each table to create a one to many relationship at each table. Essentially, there's one date to many buildings, one building to many chambers. I have a subform tied to the table TestBuilding on the form that allows a user to select the day. Bottom line, can I use code similarly to what has been mentioned in link to copy both the building data and the chamber data located in two separate tables and do it from an action on the form where the user selects the date the test takes place? I have looked at the Allen Browne solutions and it hasn't clicked yet. I am hoping my brain will engage to get this into a solution. Thanks for any help.
    Last edited by June7; 06-21-2013 at 01:58 PM. Reason: mod edit to split thread

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The code to set the DefaultValue property as described in the link might be what you need. It doesn't really matter what level a form is, the code can be used. What happens is when user moves onto new record row, the DefaultValues will be applied as soon as record is initiated by entering into any control of the record. If that is not what you want, give more info about your situation.
    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
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23
    Thanks June,
    I am not wanting to provide a default value. I am wanting to take from the previous day's work and carry it to the next day since much of that work will take place on the following day (with minor changes). I have a very simple design and don't want to complicate it too much. The end goal; this data will populate a daily report sent to many people and provide utilization rates on equipment. The building count will remain unchanged from day to day.


    Attachment 12861

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Your attachment link isn't working.

    Are you saying each day you want to create a new record from the previous day's entry? Maybe try something like:

    INSERT INTO tablename(fieldname1, fieldname2, fieldname3) SELECT(fieldname1, fieldname2, fieldname3 FROM tablename WHERE some criteria here);

    http://www.w3schools.com/sql/sql_insert_into_select.asp

    The criteria part could be tricky. Might need to use DMax to pull the latest record to copy.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23

    uploaded database

    ClimWorkBlank.zip

    I have left information in the tables which shows my intent. Basically, trying to duplicate all records below the date record (building and chamber data).

    If I was to set a default value, I guess I would have to do that in VBA, correct? Since my subform is derived from the table and the parent/child relationship, I am having a hard time understanding the default values. I look forward to learning more however.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If I understand, you want to start a new TestDate record and create a set of Test-Building records for this new test date?

    In the AfterUpdate event of TestDate textbox or Click event of Duplicate button:

    If Me.NewRecord Then
    DoCmd.RunCommand acCmdSaveRecord
    CurrentDb.Execute "INSERT INTO [Test-Buildings](TestDateID, BuildingNumber) SELECT " & Me.ID & " AS TDID, BuildingNum FROM ClimaticBldgNumbers;"
    Me.Test_Buildings_subform.Requery
    End If

    BTW, the hyphen in table name was an issue without the []. Advise no spaces or special characters in names (underscore is exception).
    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
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23
    Thanks June for your very quick reply. I am basically trying to start a new test date, create the same building and further, create the same chambers. I may try to set up this database differently since I do want the building and the chambers in those buildings to carry over from day to day. The purpose of setting the database up in this manner is to keep metrics on utilization and create a daily use report "TIPS". I used the code above and nothing happened. I am aware of the hyphen, but just trying to get something working quickly and didn't follow standard use rules. Thanks again for you assistance.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It works for me. What event did you put the code in? You went to a new record on the main form? Entered a date into textbox?
    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
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23
    Ok, I was not entering the date in the new form. Would I keep this same line of thinking to copy chamber data from the previous day - just changing the query in the code above? I am definitely on the right track. Thank you for the help!

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, if you want 42 chamber records for each date.
    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.

  11. #11
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23

    Just will base everything off the ID

    The only difference I would be entering the ten or so chambers per building 1 and the 6 chambers per building 2 etc.
    Last edited by hawkdriver; 07-01-2013 at 03:29 PM. Reason: I understand the "42"

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The Chambers table does not associate chambers with buildings. How do you know which chambers go with building 1, etc?
    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.

  13. #13
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23
    I deleted some columns when I sent the database to remove data that would show where I work. Really just a precaution, but all chambers that start with EN, belong to bldg 1 and TTF, belong to bldg 2. I forgot those columns were needed to show how those chambers are linked.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You would still want all 42 chamber records associated with each date? Some of them don't have prefixes, 2 don't have name.

    Each chamber will associate with only one building?
    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.

  15. #15
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    23
    Yes, each chamber is only related
    to one building. When you said 42 chambers, that made me think of the solution based on how you have helped. I am not at my computer right now (using the mobile version), but will work on it tonight. You are a great mentor to everyone with the solutions you help provide. Thank you. I will mark this solved when I get this working shortly.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Any way to copy paste records from
    By super12 in forum Access
    Replies: 5
    Last Post: 03-05-2013, 11:16 PM
  2. Replies: 6
    Last Post: 10-01-2012, 02:00 PM
  3. copy records within the same table
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 01-02-2012, 10:33 PM
  4. COPY records from SUBFORM and PASTE to EXCEL?
    By taimysho0 in forum Programming
    Replies: 9
    Last Post: 11-28-2011, 02:45 PM
  5. Creating a copy of records between databases
    By andrewosborne in forum Import/Export Data
    Replies: 4
    Last Post: 09-22-2011, 12:56 AM

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