Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78

    Making a label change color when getting to a minimum

    Hi,
    I got a table Stock where i do set a minimum for products.And from the invoice form i got a label. I would like this label to turn to red when the minimum is reached.
    I use the Dlook but i keep getting an error message.
    Those are the codes i have used


    Dim ts As String


    Me.Texte15.Value = Me.Texte15.Value - Me.quantiteSortiO.Value


    ts = DLookup("[minimum]", Stock, "[produitId = " & Me.Modifiable27)


    If Me.quantiteSortiO.Value < ts Then
    Étiquette29 = "Red"
    Étiquette29.BackColor = vbRed




    End If

    It s telling me no valid function as an error message.

    I would like to precise that modifiable27 is bound to productId,productQuantite and name
    Where did i go wrong.
    Pbadly hox to fix it
    Please help

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, the table needs to be in quotes too, and your brackets are incomplete:

    ts = DLookup("[minimum]", "Stock", "[produitId] = " & Me.Modifiable27)

    If minimum is a numeric data type, I'd declare ts as the same data type. You may also want to use the Nz() function in case the DLookup() returns null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    as previous advised, here is a link for dlookup

    https://support.office.com/en-us/art...b-bed10dca5937

  4. #4
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    Using the link did not help,i guess the case is not the same.Pbaldy i have tried coreecting the mistakes but it still gives me an error message.I do i use the Nz() function

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the code now? What exactly is the error message, and on what line does it occur?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    On factureOutsideFormulaire i have added a label and i try but the label does not change color when the quantity is < to the minimum set in the stock table.The error is a execution error saying You enters an invalid argument in a domain function.
    This is the full code


    Private Sub quantiteSortiO_AfterUpdate()
    Dim ts As Integer




    Me.Texte15.Value = Me.Texte15.Value - Me.quantiteSortiO.Value


    ts = DLookup("[minimum]", Stock, "[produitId] = " & Me.Modifiable27)


    If Me.quantiteSortiO.Value < ts Then
    Étiquette29 = "Red"
    Étiquette29.BackColor = vbRed




    End If
    Thanks master

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you fixed the brackets but not the quotes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    yes you were rigth but now it goes through but the label color does not change.Did i forget something

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This line will error if it's a label:

    Étiquette29 = "Red"

    Otherwise, set a breakpoint and see what values are being returned by the different components.

    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    Please i am stuck for days and my research did not give me any issue out.Attached on your email is the new db and could you please advise books for better understanding of vba

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Change the Back Style property of the label from Transparent to Normal.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    I even tried already to change the back color as well as the back style but nothing happens

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Couldn't get the BG to change using the constant vbRed, but declaring a long worked.

    Try this:
    Code:
    Private Sub quantiteSortiO_AfterUpdate()
        Dim ts As Integer
        Dim lngRed As Long, lngWhite As Long
    
        lngRed = RGB(255, 0, 0)
        lngWhite = RGB(255, 255, 255)
    
    
        Me.Texte15 = Me.Texte15 - Me.quantiteSortiO
    
        ts = DLookup("[minimum]", "Stock", "[produitId] = " & Me.Modifiable27)
    
        'is less than
        If Me.Me.quantiteSortiO < ts Then   '<<-- shouldn't this be  Me.Texte15 ???
            Me.Étiquette29.Caption = "Red"
            Me.Étiquette29.BackStyle = 1
            Me.Étiquette29.BackColor = lngRed
            '        Me.Étiquette29.BorderStyle = 1
        Else
            'is greater than or equal to
            Me.Étiquette29.Caption = "White"
            Me.Étiquette29.BackStyle = 0
            Me.Étiquette29.BackColor = lngWhite
            '        Me.Étiquette29.BorderStyle = 0
        End If
    
    End Sub

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I changed the back style in design view and then the code worked.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks, Paul. Good to know. It didn't even occur to me to set the back style in design view first.
    I was stuck in VBA (as usual).......

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

Similar Threads

  1. Replies: 7
    Last Post: 04-25-2016, 02:13 AM
  2. change tab color or header color
    By witz07 in forum Access
    Replies: 6
    Last Post: 03-31-2016, 05:31 PM
  3. Changing font color of just part of a label
    By NGFLNG1 in forum Access
    Replies: 3
    Last Post: 09-11-2015, 11:02 AM
  4. Replies: 5
    Last Post: 09-18-2012, 12:39 PM
  5. Replies: 1
    Last Post: 03-29-2009, 08:27 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