Results 1 to 14 of 14
  1. #1
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27

    Before Update Macro for Timestap Depending on Change to Combobox value

    Hi folks.


    Good evening. I am pretty new to Microsoft Access having only been playing wit it for a few weeks. And I am hoping someone would be kind enough to point me the right direction.
    I have a Combobox on a form with 4 options, 'Not Set', 'Not Applicable', 'QUOTED' & 'QUOTED Verbally'. i am wanting to place a timestamp into another field when this combobox is specifically changed to 'QUOTED' or 'QUOTED' & 'QUOTED Verbally'. In a different circumstance I also need to provide the same timestamp to another field if the Combobox Value changes to just one option, 'QUOTED'. So i wil need a solution to both scenarios....

    I have figured out that if if use the Before Update event and use the following... It completes the Timestamp, but I can't see anyway of specifying the values that only prompt the timestamp...
    SetValue
    [QUOTED Last Update]
    Date()

    I would very much appreciate any help a kind hearted person could provide.
    Thank you and regards.

  2. #2
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    226
    Use the AFTER update event on the combo. Do not use macro's. Make it something like:

    Code:
    If me.YourCBO = 'QUOTED' OR me.YourCBO =  'QUOTED Verbally' Then
        me.Last_Update = now()
    endif

  3. #3
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27
    Hi,
    Thank you so much for getting back to me with a moment of your time.
    I have tried that and replaced it with my FieldNames, but it is reporting a Syntax Error...


    Private Sub Sale_Status_QUOTED_AfterUpdate()
    If me.Sale_Status_QUOTED = 'QUOTED' OR me.Sale_Status_QUOTED = 'QUOTED Verbally' Then
    Me.QUOTED_Last_Update = Now()
    End If
    End Sub

    I can't see that I have done something wrong... Is there maybe something else i need to do to get it to work?

  4. #4
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27
    Oh I figured it. It needed to be ", instead of '.
    Thank you very much matey!!!

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    226
    Good to see it work.

  6. #6
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27
    Oh I figured it. It needed to be ", instead of '.<br>Thank you very much matey!!!
    ###### Mistakingly Posted Twice ###########

  7. #7
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27
    HI again, I am trying to sort of nest two of the same types of thing in the same macro, but it doesnt seem to work anyway I try it.. Can you see whats wrong, as I have tried a few methods..

    If Me.Sale_Status_QUOTED= "QUOTED" Then Me.QUOTED_Last_Update= Now()
    End If
    If Me.Sale_Status_QUOTED= "QUOTED Verbally" Then Me. QUOTED_Verbally_Last_Update= Now()
    End If


    OR


    If Me.Sale_Status_QUOTED= "QUOTED" Then Me.QUOTED_Last_Update= Now() Or Me.Sale_Status_QUOTE_ACCEPTED = " QUOTED Verbally " Then Me.QUOTED_Verbally_Last_Update = Now()
    End If


    Neither of these methods had the desired result.

  8. #8
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    226
    I don't know for sure what the "desired result" is.
    In one method you only refer to Sale_Status_QUOTED. In the other one you also use Sale_Status_QUOTE_ACCEPTED.
    It is also important to know how you trigger the code (which event(s)).

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,981
    I would question why you need to be bothered about how they were quoted, simply have one field LatestQuoteDate? maybe with a quote method dropdown - Verbal, Email, Posted.
    That way you only have to query one field to see if they have been quoted, not two (or three etc.)

    Your code syntax is not correct. You either do the whole command on the single line or you don't so

    Code:
    If Me.Sale_Status_QUOTED= "QUOTED" Then Me.QUOTED_Last_Update= Now() 

    Or

    Code:
    If Me.Sale_Status_QUOTED= "QUOTED" Then 
       Me.QUOTED_Last_Update= Now()
    End IF
    Are correct but not the in between version you posted.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,704
    Did you see xps35's " Do not use macro's."
    The code you were shown and are now using is vba code.
    You can make your posts more readable if you highlight the "vba code" then click on the hash # above your message.
    Here is an example

    Code:
    Private Sub Sale_Status_QUOTED_AfterUpdate()
    If me.Sale_Status_QUOTED = "QUOTED" OR me.Sale_Status_QUOTED ="QUOTED Verbally" Then
        Me.QUOTED_Last_Update = Now()
    End If
    End Sub
    Also better if you "indent" your code to delineate If/EndIf

    Sample:

    Code:
    Private Sub Sale_Status_QUOTED_AfterUpdate()
      If me.Sale_Status_QUOTED = "QUOTED" OR _ 
         me.Sale_Status_QUOTED = "QUOTED Verbally" Then
              Me.QUOTED_Last_Update = Now()
      End If
    End Sub

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,615
    Did you see xps35's " Do not use macro's."
    Use of word 'macro' when posting code must be because Excel people call vba procedures macros.
    I have a Combobox on a form with 4 options,
    How many columns does it have and if more than 1, which one is bound (or are any bound)?
    You could put a break point in the procedure and check what the combo value is by mousing over the reference to it. Or you can type in the immediate window
    ?Me.Sales_Status_quoted
    and hit return to get its value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27
    Code:
    Private Sub Sale_Status_QUOTED_AfterUpdate() 
       If me.Sale_Status_QUOTED = "QUOTED" Then
             Me.QUOTED_Last_Update = Now()OR
       If me.Sale_Status_QUOTED = "QUOTED Verbally" Then
             Me.QUOTED_Verbally_Last_Update = Now()
       End If
       End If
    End Sub
    This is what I am trying to do... Depending on what is selected in the Combobox, dictates which field the TimeStamp is placed in.

    Apologies for my poor etiquette with this stuff. I am very new to it, but I will try to learn quickly, and observe best practise.
    Thank you everyone for your help. Its takes me a little time to digest your replies.

  13. #13
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    226
    Code:
    Private Sub Sale_Status_QUOTED_AfterUpdate() 
       If me.Sale_Status_QUOTED = "QUOTED" Then
             Me.QUOTED_Last_Update = Now()
       EndIf
    
       If me.Sale_Status_QUOTED = "QUOTED Verbally" Then
             Me.QUOTED_Verbally_Last_Update = Now()
       End If
    End Sub

  14. #14
    JonoGee is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    27
    Oh you're a legend! Thank you! I was thinking to two had to be linked somehow, but i see you have just treated it like two separate conditions. Thank you! I am slowly getting there!

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

Similar Threads

  1. Replies: 14
    Last Post: 11-03-2020, 01:38 PM
  2. Replies: 6
    Last Post: 03-27-2017, 03:16 AM
  3. Replies: 9
    Last Post: 09-28-2016, 08:24 AM
  4. trying to use after update macro to change field
    By justlearning123 in forum Programming
    Replies: 3
    Last Post: 07-03-2015, 12:32 AM
  5. change a combobox value depending on another
    By emadaldin in forum Access
    Replies: 3
    Last Post: 01-17-2011, 01:06 PM

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