Results 1 to 15 of 15
  1. #1
    dotcanada is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44

    "Insert into"

    Good day all.

    I'm trying to input data from a calculated bound text box to a table (called tblPlayerContract (primary key is called [PlayerContractID]). This text box is in "date" format and is called [DateExpired] (obviously, this is also the name of the column in the table that I want the result to go into).

    The calculation is adding the number of years to another date (the # of years control is called [ContractLength] and the date it's added to is called [DateStarted]).

    The expression I'm using is =DateAdd("yyyy",[ContractLength],[DateStarted]). All of these controls are on the same subform (subfrmPlayerContract).



    I've been reading about the use of the "Insert into" function but not sure how I can use it with what I'm trying to do.

    Thanks for your help in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dotcanada is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Quote Originally Posted by pbaldy View Post
    Unfortunately, no. It's a matter placing where to place these items into the code. I was also looking at the UPDATE statement.

    Would this also work?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    An update query would work, but if the form is bound to the table it's simpler to use Allen's method to copy the value to a bound textbox. You will often run into problems if you use both a bound form and code to update the same data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    dotcanada is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Quote Originally Posted by pbaldy View Post
    An update query would work, but if the form is bound to the table it's simpler to use Allen's method to copy the value to a bound textbox. You will often run into problems if you use both a bound form and code to update the same data.
    I've been trying this to no avail.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Trying what exactly? Post your code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dotcanada is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Quote Originally Posted by pbaldy View Post
    Trying what exactly? Post your code.
    I've created a query based on the table. So in my query, I have the fields [ContractLength] and [DateStarted]. I want another field called [DateExpired] to equal the [ContractLength] (in # of years), and add it to [DateStarted]. I want the results of [DateExpired] to be entered into the same table that has [ContractLength] and [DateStarted].

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, I wouldn't use a query if the form is bound to the table, but it would look like:

    UPDATE TableName
    SET DateExpired = DateAdd("yyyy",[ContractLength],[DateStarted])

    perhaps with a criteria to restrict it to a specific record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    dotcanada is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Quote Originally Posted by pbaldy View Post
    Well, I wouldn't use a query if the form is bound to the table, but it would look like:

    UPDATE TableName
    SET DateExpired = DateAdd("yyyy",[ContractLength],[DateStarted])

    perhaps with a criteria to restrict it to a specific record.
    That's where I'm stuck. Which control would this go into and which event?

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't know your app. Perhaps the after update event of the other fields. Should I say again I wouldn't go this way? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    I'll try to attach it.

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you get it sorted out? I got an email notification that there was an attachment, but I don't see it now.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    The file is too big to upload. Here's a link to my google drive.


    https://drive.google.com/file/d/0B8G...ew?usp=sharing

  14. #14
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Hi Paul.

    Don't worry about it. I'll stick with the query. Thanks for your help.

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, on the road. Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 9
    Last Post: 02-02-2016, 06:27 AM
  2. Replies: 15
    Last Post: 09-14-2015, 01:31 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 01-27-2015, 12:25 PM
  5. Replies: 2
    Last Post: 12-04-2012, 01:03 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