Results 1 to 14 of 14
  1. #1
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13

    Post Auto-populate date/time based on the population of a different specific fie

    I have a form (QUOTE_MASTER) that is derived from a table (also QUOTE_MASTER). I have a field (QTL_ASSGN_QS_DATE) that I want to auto-populate with Date/Time when I populate a text field (QTL_QS_EMP_NAME). The text field (QTL_QS_EMP_NAME) is a drop-down box - the list is based on a table (QS_EMPS). I have tried the following:

    • From the form (QUOTE_MASTER), I have entered the following formula:


    1. In the field box for (QTL_QS_EMP_NAME), for the event, "On Change", =IIF (Not IsNull([QUOTE_MASTER]![QS_EMP_NAME]), ([QUOTE_MASTER]![QTL_ASSGN_QS_DATE]=Now()), ([QUOTE_MASTER]![QTL_ASSGN_QS_DATE] is Null))
    2. In the field box for (QTL_ASSGN_QS_DATE), as Default, =IIF([QUOTE_MASTER]![QS_EMP_NAME]<>Null, [QUOTE_MASTER]![QTL_ASSGN_QS_DATE]=Now(), [QUOTE_MASTER]![QTL_ASSGN_QS_DATE=Null)


    • I have tried the same directly in the table (QUOTE_MASTER).



    And others that I can't remember - am I having this problem because the field (QS_EMP_NAME) is a drop-down box based on a table? If not, can someone please help me with the syntax for this function?

    Thanks!
    Vanessa

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the AfterUpdate event of QTL_QS_EMP_NAME
    If Not IsNull(Me!QTL_QS_EMP_NAME) Then Me!QTL_ASSGN_QS_DATE=Now()

    (Note: not good practice to have objects in the nav pane with the same name. Add suffixes: QUOTE_MASTER_tbl, QUOTE_MASTER_frm)

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Better yet, read this
    https://access-programmers.co.uk/for...d.php?t=225837

    (hope you like typing underscores )
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    Didn't work:

    If Not IsNull(QUOTE_MASTER_frm!QTL_QS_EMP_NAME) Then QUOTE_MASTER_frm!QTL_ASSGN_QS_DATE=Now()
    = If Not IsNull(QUOTE_MASTER_frm!QTL_QS_EMP_NAME) Then QUOTE_MASTER_frm!QTL_ASSGN_QS_DATE=Now()
    = If Not IsNull([QUOTE_MASTER_frm]![QTL_QS_EMP_NAME]) Then [QUOTE_MASTER_frm]![QTL_ASSGN_QS_DATE]=Now()

    Click image for larger version. 

Name:	error.PNG 
Views:	15 
Size:	29.7 KB 
ID:	28827

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    QUOTE_MASTER_FRM is not something that Access recognizes. If you are in the form's VBA, use Me instead, everywhere else you have to tell Access what it is - it is a FORM. So it will be Forms!formname!fieldname

  6. #6
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    I have my Masters in IT - one of the most prominent profs recommended this format. Thank you for the info though!

  7. #7
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    It is still not populating - what am I doing wrong? It can't be this hard!

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't know! What did you change?

  9. #9
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    Does it matter that I am using a drop-down box to populate the name field?

  10. #10
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    If Not IsNull(Me!QSNAME) Then Me!DateAssigned=Now()

    Do I put this exact code and syntax in the "After Update Event", or do I need to open the Code Builder, then enter it? Does it need an equal sign before the "If"?

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No, this is the correct syntax.

    I have a field (QTL_ASSGN_QS_DATE) that I want to auto-populate with Date/Time when I populate a text field (QTL_QS_EMP_NAME)
    Post #2 answered this question but you are using different field names?

  12. #12
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    Hi aytee111,

    Thank you for your responses.

    Yes, I did change some names. So I tried coding other Events, and non of the custom events that I am creating are working. I tried to upload the database so you could take a look at it to see what is happening, but I received an error. I have tried to create a link so that you can access it if you want. The next thing that I tried to do is create an After Update Event for certain field objects to become visible on a form based on the selections "Other Hours" or "Other Days" selected from the TurnTime drop down box. This is the code I used. I tried setting the .Visible in the settings to "yes" and "no" for the fields (ModTurnTimeH) and (ModTurnTimeD) before testing, but it did not make a difference. I included a link to my dropbox for this file - hope you can access it. I really need to figure out what I am doing wrong so I can finish this.

    Private Sub TurnTime_AfterUpdate()
    Select Case Me.TurnTime
    Case "Other Hours"
    Me.ModTurnTimeH.Visible = True
    Me.ModTurnTimeD.Visible = False
    Case "Other Days"
    Me.ModTurnTimeD.Visible = True
    Me.ModTurnTimeH.Visible = False
    End Select
    End Sub

    https://www.dropbox.com/s/i8iifhe2rn...EST.accdb?dl=0

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't think I get all of your issues?

    To attach a file to your post, Go Advanced, click on Attach, browse and upload. Most people will not follow links.

    Your code above is not working because you are looking at the incorrect column of the combobox. When you don't specify which column it defaults to the first one (column 0) which in this case is the ID. Change the code to Select Case Me.TurnTime.Column(1)

  14. #14
    vbagwell1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    13
    UGH! After all of that frustration of not getting my events to work, it turns out that my laptop was corrupt and creating a lot of strange anomalies. Mu computer updated Microsoft updates - after they were finished, they all worked fine. THANK YOU ALL FOR YOUR HELP AND PATIENCE!

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

Similar Threads

  1. Replies: 1
    Last Post: 08-05-2014, 12:40 PM
  2. Auto Populate SQL with Run-Time Error 3464
    By excellenthelp in forum Programming
    Replies: 4
    Last Post: 08-04-2014, 12:03 PM
  3. Replies: 2
    Last Post: 07-07-2014, 09:19 AM
  4. Replies: 7
    Last Post: 11-28-2012, 01:41 PM
  5. Auto Populate based on Combo box
    By Mpike926 in forum Forms
    Replies: 16
    Last Post: 09-07-2012, 03:16 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