Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2007
    Posts
    40

    Question Unanswered: Form Help - Button "lights up" if Table has data

    Hi all

    I have a simple form which I fill in and then use one of several command buttons to general a report using parameters I've specified in that form. One of these parameters is "Customer Name" which is chosen from a dropdown box.

    I've started gathering email address for customers. What I'd like to have is something, an circle say, beside this "customer name" field which indicates if I have an email address for that customer in my Customer table.

    So basically I choose the Name from my dropdown list and if the Table "Customer" has an email address entry for that Name, then a button/image/object reacts to tell me so. I'm pictureing in my head a simple circle which is normally grey but turns green through condition formating or something.

    Does anyone have suggestions on the best way to go about this "indicator light"?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In this example I use a rectangle box named "Box_EmailIndicator" with a transparent backstyle and which is located around a text box on the form.
    Code:
    Private Sub Form_Current()
    
        Const vbDarkGrey As Long = 8421504
        
        If Nz(DLookup("emailaddress", "Customer", "CustomerID=" & Me.CustomerID.Value), "") <> "" Then
            Me.Box_EmailIndicator.BorderColor = vbRed
        Else
            Me.Box_EmailIndicator.BorderColor = vbDarkGrey
        End If
    
    End Sub
    You'll have to adapt the names "emailaddress", "Customer", "CustomerID" to match those used in your database.
    Have a nice day!

  3. #3
    Join Date
    Dec 2007
    Posts
    40
    Thanks! That looks fantastic but a little out of my depth! I'm assuming this goes in the VBA editor side of things but not sure how to imput it or how to then put the resulting object in the right place

    I'm very interested in learning more thou so if you have time I'd like to know how to use this sort of code works:

    The names I use from CUSTOMER table are
    NAME - Customer name chosen from a dropdown combi box
    {CODE - the Customer account code used in a query->report and linked to the NAME in the Combi box
    EMAIL - email address

    The Dropdown/Combi box in my form in which I select the name/code is called ACCCODE

    Based on this I assume the adapted code would look like this:
    Code:
    Private Sub Form_Current()
    
        Const vbDarkGrey As Long = 8421504
        
        If Nz(DLookup("EMAIL", "CUSTOMER", "NAME=" & Me.NAME.Value), "") <> "" Then
            Me.Box_EmailIndicator.BorderColor = vbRed
        Else
            Me.Box_EmailIndicator.BorderColor = vbDarkGrey
        End If
    
    End Sub
    Am I right in thinking this will produce an object of the size "8421504" which looks to see if the EMAIL in CUSTOMER that matches the NAME specified in the form has a value? If it does it then colours that object Red, otherwise it leaves it Grey?

    I don't understand the Me.NAME.Value or the Nz parts though.

    Eitherway I appreciate the help so far.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Amused View Post
    Based on this I assume the adapted code would look like this:
    Code:
    Private Sub Form_Current()
    
        Const vbDarkGrey As Long = 8421504
        
        If Nz(DLookup("EMAIL", "CUSTOMER", "NAME=" & Me.NAME.Value), "") <> "" Then
            Me.Box_EmailIndicator.BorderColor = vbRed
        Else
            Me.Box_EmailIndicator.BorderColor = vbDarkGrey
        End If
    
    End Sub
    Not exactly, but you're not far. If, as I supposse, NAME is not a numeric value, you must enclose it between single quotes ('), like this:
    Code:
        If Nz(DLookup("EMAIL", "CUSTOMER", "NAME='" & Me.NAME.Value & "'"), "") <> "" Then
    Quote Originally Posted by Amused View Post
    Am I right in thinking this will produce an object of the size "8421504" which looks to see if the EMAIL in CUSTOMER that matches the NAME specified in the form has a value? If it does it then colours that object Red, otherwise it leaves it Grey?
    Not exactly: "8421504" represents a colour (dark grey), not a size, but you're right, the code examines the contents of "EMAIL" into the table "CUSTOMER" for a row where "NAME" matches the current value for "NAME" in the form.

    Quote Originally Posted by Amused View Post
    I don't understand the Me.NAME.Value or the Nz parts though.
    When used inside the module of a form (or of a report), Me is a shortcut to reference the form (or the report) itself. If the form name is "Form1", you can use Me. instead of using Forms("Form1").

    The Nz function (NullZero) returns the value of its first argument (a variant variable or the value of a field or of a control) if this value is not Null, it returns the value provided as its second argument otherwise. In this case, it is equivalent to:
    Code:
    If IsNull(DLookup("EMAIL", "CUSTOMER", "NAME='" & Me.NAME.Value & "'")) Then
        ReturnedValue = ""
    Else
        ReturnedValue = DLookup("EMAIL", "CUSTOMER", "NAME='" & Me.NAME.Value & "'")
    End If
    If it did not exist, you would probably use (you still can):
    Code:
    Dim VarRetVal As Variant
    VarRetVal = DLookup("EMAIL", "CUSTOMER", "NAME='" & Me.NAME.Value & "'")
    If IsNull(VarRetVal) Then    ' Null returned.
           Me.Box_EmailIndicator.BorderColor = vbDarkGrey
    ElseIf Len(VarRetVal) = 0 Then    ' Zero-length string returned.
           Me.Box_EmailIndicator.BorderColor = vbDarkGrey
    Else
           Me.Box_EmailIndicator.BorderColor = vbRed
    End If
    You cannot test for both Null and zero-length string on the same line because Len(Null) yields Null, not zero, hence the ElseIf... line.
    Have a nice day!

  5. #5
    Join Date
    Dec 2007
    Posts
    40
    Brilliant explaination. I'm following and learning

    At present when I open the form I get a Compile Error - Invalid Qualifier. Debug points to the ".NAME" part of Me.NAME.Value. In case it was a conflict I changed this to ACCNAME (also changed the Name of the Combo Box in the form) but same Error.

    I'm also not getting any resulting object/regtangle in Design View to move about?

    I'm also trying to follow the order of the DLookup: EMAIL and NAME are both Columns within the Table CUSTOMER. Where NAME=Me.ACCNAME then we want to look at the value in EMAIL but I don't see that logically from the code.
    "EMAIL", "CUSTOMER" I get - 'I want you to lookup Email from the Table Customer'
    But then "NAME"=etc follows right after. Why is no WHERE used?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. NAME is indeed a reserved word and it's also a property of the Form object, hence the error. Sorry, I should have thought of that. This does not explain why it still does not work when you use ACCNAME, though. Set a breakpoint on the line "If Nz(DLookup("EMAIL",..." and when the code stops open the immediate window and type (if ACCNAME is now the name of the combo):
    Code:
    Debug.Print Me.ACCNAME.Value
    2. The rectangle box named "Box_EmailIndicator" must exist on the form prior to using this procedure. The procedure won't draw a rectangle, just change its colour.

    3. From MSDN(http://office.microsoft.com/en-us/ac...1228825.aspx):
    The DLookup function syntax has these arguments:

    DLookup(expr, domain [, criteria] )
    ...
    criteria: Optional. A string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE.
    Have a nice day!

  7. #7
    Join Date
    Dec 2007
    Posts
    40
    Thanks Sinndho - still having problems thou.

    1. The breakpoint won't work - true I've never used one before but:

    I set a breakpoint - the whole line turned a dark red colour BUT when I run the code it doesn't stop and I still get the "Invalid Qualifier" alert. When this happens the "Private Sub form_current () is highlighted in yellow and the ".ACCNAME" part of the code is selected. Dunno what I'm doing wrong.

    2. I now understand the concept - have drawn an rectangle and named it "Box_EmailIndicator" for the code to target to.

    3. Great, never used DLookup before. I'm learning loads! Now if it just would work :-\

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you at least compile the code in your database without error ?
    Have a nice day!

  9. #9
    Join Date
    Dec 2007
    Posts
    40
    Sorry, I don't know what that involves. I've tryed googling it and F1 Help but I don't know what compiling is supposed to achive to know if I'm looking at the right info!

    How do I compile the code in the database?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Open the VBA Editor (Alt+F11)
    2. In the Debug menu, select Compile.
    Have a nice day!

  11. #11
    Join Date
    Dec 2007
    Posts
    40
    The only option there is "Compile DBCNov06" which is the whole Database. If I choose that I get Alert

    "Compile Error: Expected End Function" and see the following:

    Code:
    Option Compare Database
    
    Function IsLoaded(ByVal strFormName As String) As Boolean
    
    Dim oAccessObject As AccessObject
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    
    If oAccessObject.IsLoaded Then
      If oAccessObject.CurrentView <> acCurViewDesign Then
        IsLoaded = True
      End If
    End If
    which doesn't mean a whole lot to me

  12. #12
    Join Date
    Apr 2011
    Posts
    34
    You are missing the statement End Function at the end of the function. That indicates the interpreter where the function finishes.

    After the second End If, on a new line, just insert End function.
    Try to compile again.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is the source of the problem: the code is incomplete. It should be:
    Code:
    Option Compare Database
    Option Explicit
    
    Function IsLoaded(ByVal strFormName As String) As Boolean
    
    Dim oAccessObject As AccessObject
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    
    If oAccessObject.IsLoaded Then
      If oAccessObject.CurrentView <> acCurViewDesign Then
        IsLoaded = True
      End If
    End If
    End Function
    Option Explicit is useful because it forces the declaration of a variable before using it. This eliminates many potential problems.

    Every procedure (Sub or Function) must be terminated with the instruction End Sub or End Function, respectively.
    Have a nice day!

  14. #14
    Join Date
    Dec 2007
    Posts
    40
    Ah - well now the compile gets a bit further: still gets stuck at "Me.ACCNAME" etc with

    "Compile Error: Invalid Qualifier"

    Adding a breakpoint to that line makes no difference.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This means that ACCNAME is not the name of a control or a field in the form where the code is run. What's the actual name of the control (or field) you try to reference using ACCNAME?
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •