Results 1 to 5 of 5
  1. #1
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    Question Set date field on subform to date variable

    (I thought this was solved, and it's not so I'm re-posting)...
    I need to set a date field on a sub form to a date variable.


    - The parent form is called: "f_LCA"
    - Have sub_form called "f_Advertise subform"

    - Field on the sub_form called "PostedDate" (date, short)

    - Have a variable called "NewPostedDate", (dim as date), e.g., 07/19/2010



    I tried the following things:

    1) me.PostedDate = NewPostedDate
    (got error message)

    2) me.PostedDate = (# NewPostedDate #)
    (got error message)

    3) me.PostedDate.DefaultValue = NewPostedDate
    (no error message, but
    PostedDate field did not display the new date)

    4) me.PostedDate.DefaultValue = NewPostedDate
    (no error message, but
    PostedDate field did not display the new date)

    5) tried putting NewPostedDate variable as the default value in the field's property.
    It translated the entry as "NewPostedDate"

    Your help would be greatly appreciated!



  2. #2
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71
    I'm assuming this is a vba event that's being run from the parent form (f_LCA)?

    First off check out this for help referring to subforms and stuff, I use it a ton:
    http://www.mvps.org/access/forms/frm0031.htm

    In your case I would try:

    Me![pf_Advertise subform].Form![Posted Date].Value = NewPostedDate

  3. #3
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    still not working

    Thanks for the idea, but it's still not working.

    Perhaps if I explain what I'm doing it may help?

    Parent form: f_lca
    sub_form: f_Advertise subform

    1st field on sub form called "StartOfPost" (date, short)
    User enters a Start Posted date
    After this date is entered, in the after_update event, I call a function
    that adds working days to this date and passes back a new date in
    a variable called "NewPostedDate"

    2nd field on sub form called "PostedDate" (date, short). I need to display the contents of the variable "NewPostedDate" in this field. The user may wish to change it, but it needs to display the new date.

    I checked the variable "NewPostedDate" and it does have a date value in the right format. (e.g., 7/19/2010)

    I did try formatting the date using format(NewPostedDate, "mm/dd/yy") but it did not change the date.

    I reviewed the info in the link you provided and tried the following

    me![f_lca]!form![PostedDate] = tempnewdate 'error msg can't find field referred to (I DOUBLECHECKED the field name in properties and it IS PostedDate)

    me![f_advertise subform].form![PostedDate].value = tempnewdate 'can't find field referred to.

    forms!f_lca![f_advertise subform].form![PostedDate] = tempdate 'field is too small to accept amount of data entered. (I doublechecked that the format was correct in the variable)

    me![f_lca]![posteddate] = tempnewdate 'can't find field referred to

    Thanks for your help.

  4. #4
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71
    This may seem silly but have you tried:

    Code:
    PostedDate.Value = NewPostedDate
    placing that line right after the variable NewPostedDate gets set?

    Edit: Also, if this doesn't work are you able to post your database? (Remove sensitive data first)

  5. #5
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    solved!

    Your suggestion was not silly at all. This worked!
    THANKS!!!

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

Similar Threads

  1. set date field on form to date variable
    By laavista in forum Access
    Replies: 3
    Last Post: 06-28-2010, 03:03 AM
  2. Replies: 10
    Last Post: 04-21-2010, 01:16 PM
  3. Replies: 9
    Last Post: 03-19-2010, 10:37 AM
  4. Replies: 2
    Last Post: 07-31-2009, 06:56 AM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

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