Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: dcount criteria based on values in textbox

    I am constructing a DB that collects data about my IT infrastructure. I have a form with three sections, the first is combo boxes which allows me to select a specific group in my IT environment, the second section are the details associated with that group (i.e. site location, POC information, Active Directory OU name, etc...), the third is tabular data from Active Directory which counts the number of systems of each type.

    The DB has two tables POC and ADdump. The first has the details displayed by the combo box and the second the list of system which I extract with dsquery.

    I am trying to use dcount as shown below to get system counts based on OSType and AD value. For those of you that know AD dsquery the output format of the distinguish name includes the AD OU (which I associate to the group in the POC table) along with several other values that I need to keep in the table. So what I want to say is in the control source method for the "count" text box is

    =DCount("[OSType]","ADDump", "[OSType]="Windows XP Professional" AND "[Syspath] <contains the value of text box ADOU's current value>")

    How do you reference the value of a textbox that is set by a combo box's afterupdate event such that value of the textbox is considered a string which is only part of the string field being evaluated?

    Any assistance is appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Me.<TextBox>.Value & "'")
    Where <TextBox> is the name of the textbox containing the ADOU's current value.

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    5

    Feels closer

    That gives me a #Name? error. It also brackets ([]) "Me.<textbox>.Value" but I don't think that is an issue. Any other ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    would you not be better looking at a SNMP tool that could diagnose whaqt hardware and what software you have insatlled?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2009
    Posts
    5
    In this case I am not looking for management data but comparing AD data against other data to ensure that AD is being cleaned up correctly (people love to add stuff and hate to delete it!). In this environment automatic deletion of system that have not contacted AD in X days is not an option. By being able to parse this table with other tables I end up with cross validation of records (i.e. if it is in all my tables with the correct status I am more sure of its status than if it was just in one). I also am using this data so that I can identify all of the systems by type.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As I wrote <TextBox> is a placeholder for the name of the textbox containing the ADOU's current value.
    Let's suppose that the name of your form is MyForm and that the name of the textbox that contains the the ADOU's current value is Text_ADOUValue.
    then you code would be:
    Code:
    = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Me.Text_ADOUValue.Value & "'")
    (if the ADOU value is of type Text) or:
    Code:
    = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = " & Me.Text_ADOUValue.Value )
    (if the ADOU value if of type Numeric).

    If you use this code ouside the form's module, then the code becomes:
    Code:
    = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Forms!MyForm.Text_ADOUValue.Value & "'")
    Have a nice day!

  7. #7
    Join Date
    Apr 2009
    Posts
    5

    getting closer...I think

    All those options give the #Name? error. The field ADOU is of type text (ex. "TX-San Antonio-Program1"), SysPath is text too (ex. "CN=XXXXXX,laptops,TX-San Antonio,Org1,Bus1,Comp1").

    The system also keeps autoupdating the field with brackets, so Me.tboxADOU.Value becomes [Me].[tboxADOU].[Value] which I assume is ok, but just thought I would check.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is because you probably try to use DLookUp in the Query Builder, which makes no sense. DLookUp is supposed to be used in VBA code and nothing in VBA will automatically put brackets arround parts of your code.

    Ex:
    Code:
    Dim lngCount as Long
    lngCount = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Me.Text_ADOUValue.Value & "'")
    or better (in case DCount returns Null)
    Code:
    lngCount = Nz(DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Me.Text_ADOUValue.Value & "'"), 0)
    But you should also be able to set the ControlSource property of the textbox using the same code:
    Code:
    Me.Text_Count.ControlSource = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Me.Text_ADOUValue.Value & "'" )
    You can use it in the AfterUpdate code of the combo or anywhere else depending on what you want to do.

    Have a nice day!

  9. #9
    Join Date
    Apr 2009
    Posts
    5
    I am not trying to use DLookUp. The syntax in this thread is what I have in the control source property for the count text box.

    My combo box "AfterEvent" box already has [embedded macro] which I use to populate record details in other text boxes.

    It seems to me that the value we are calling & Me.tboxADOU.Value is either not picking up the value in the text box or is not compared against the [SysPath] in a manner that is getting the desired result.

    Is there a way to step through code to see what responses are actually getting populated?

    Sinndho, I appreciate you sticking with my noob-ness on this one.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem: you're welcome.

    Do not use a reference to a control of the form in the Properties window of this form. Use VBA code to assign the ControlSource value of the TextBox dynamically:

    1) Leave the ControlSource property of the textbox empty or assign an initial value to it that does not reference another control of the form.

    2) If the AfterUpdate of the combo uses a macro, note the name of this macro then delete "[embedded macro]" and replace it with "[Event Procedure]". It will create a Sub Combo_XXX_AfterUpdate procedure.

    3) In the Sub AfterUpdate event procedure of the combo add code to execute the macro that was originally used then add code to modify the ControlSource property of the textbox using:
    Code:
    Private Sub Combo_XXX_AfterUpdate()
    '
    ' Run the macro that was formerly used (see step 2)
    '
        DoCmd.RunMacro <MacroName> 
    '
    ' Update the ControlSource property of the textbox
    '
        Me.Text_Count.ControlSource = DCount("[OSType]","ADDump", "[OSType]='Windows XP Professional' AND [Syspath] = '" & Me.Text_ADOUValue.Value & "'" ) 
    End Sub
    I hope this helps.

    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
  •