populate combo box based upon another combo box selection tied to subform

[mdewell]

June7,

I tried your expression but it gives me the error:



This Expression is Typed Incorrectly, or it is too Complex to be Evaluated

I am using Access 2003, I don't know if that's anything to do with it, as you are and Access 2010 user. Yet, I think your code may help, for it looks promising. Please let me know if you think it may be an Access 2003 problem.

[June7]

Yes, allowing edit of the OU in HIPAA_single_computer form (or any form bound to the HIPAA query) changes that field in OU table and that change is then reflected in all related records.

If purpose of combobox is to select an OU to associate with the computer, need to bind the combobox to OU_ID field in HIPAA_relational table. To select computer for this record, bind a control to Comptuer_ID field. Need to include those two fields in the HIPAA query.

Left Join:
return rows that have matching data in the left table, even if there's no matching rows in the right table.

Right Join:
return rows that have matching data in the right table, even if there's no matching rows in the left table.

Inner Join:
return records where there is at least one row in both tables that match the join condition.

Source: http://www.quackit.com/sql/tutorial/sql_outer_join.cfm

I tested the HIPAA_datasheet & subform and all seems to work. What issue are you experiencing?

[mdewell]

The error I'm getting is:

This Expression is Typed Incorrectly, or it is too Complex to be Evaluated

Is there a setting I need to set to allow this query to happen?

[June7]

I am using the project you attached. Where is the expression that errors?

[mdewell]

Sorry. Here, try this one. When you go to "Open HIPAA Datasheet" from the "Main Menu", and then click on the combo box for "Find Computer" I get the error.

On another note I have a question about my "HIPAA_single_computer" form in the attached file.

[June7]

Want to try this:

1. Set OUFilter BoundColumn to 1

2. Remove line from Sub SetOUFilter that sets ComputerLookup to ""

3. Change line in ComputerLookup AfterUpdate to
ComputerName = Me.ComputerLookUp.Column(0)

4. ComputerLookup property settings
ColumnCount: 1
ColumnWidth:
BoundColumn: 1
RowSource:
SELECT Computer.Computer FROM (HIPAA_relational LEFT JOIN OU ON HIPAA_relational.OU_ID = OU.ID) LEFT JOIN Computer ON HIPAA_relational.Computer_ID = Computer.ID WHERE IIf([Forms]![HIPAA_datasheet]![OUFilter]="<All>",OU_ID LIKE "*", OU_ID=[Forms]![HIPAA_datasheet]![OUFilter]);

Hope I haven't forgotten to mention everything. Handling the <All> value drove me nuts for a while. Don't know why the default for ComputerLookup is not working. Also don't understand why ComputerLookup clears after selection even though I removed code that sets to "".

[mdewell]

Thanks June7,

I'll have to mess with it tomorrow, but that is a pretty complex looking SQL statement.

I tried a test run of it and I didn't get any thing in that seemed to work as to getting a computer selection in ComputerLookup. Do I have to get rid of all my SetFilter code?

Thanks

[June7]

Here is the modified project that is working for me.

EDIT: Purpose served, file removed.

[mdewell]

Hello June7,

I tried your mdb, and almost nothing worked for me. As soon as I click on the OUFilter combobox, I get the following:

The value you entered isn't valid for this field.

It's not even showing the <SELECT> in the combobox, ComputerLookup.

Are you using Access 2010 to run this mdb?

[June7]

I am at a loss. I just downloaded the file I provided. I open the HIPPA_datasheet form and the search works fine. I don't get the error message.

I did say I don't know why the <SELECT> won't show.

[mdewell]

Strange things indeed. I've been getting it to work with what I programmed with the VBA to filter the subform, so I guess we are good. If I have anything else about this issue, I'll let you know.

Thanks for all your help.

[June7]

I am testing with 2007 and 2010 but I took your mdb and did not convert to accdb. There is nothing special about the code I used and should work as well in 2003. However, glad you got a working model. Good luck.

[June7]

Rats! Just took another look at your project that I modified. I might have uploaded a bad version. I tested code again and wasn't working. So got it working again with this modification to SetOUFilter:

If Not IsNull(Me.OUFilter) Then
    Me.[HIPAA datasheet subform].Form.RecordSource = "SELECT * FROM HIPAA WHERE OU = '" & OUFilter.Column(1) & "';"
End If
Me.ComputerLookUp.Requery

[mdewell]

It seems like it works for the ComputerLookUp combo box now, and that's great. Your code looks a lot cleaner than mine.

Your ComputerLookUp Query is:

 Private Sub OUFilter_AfterUpdate()
 
  'Call subroutine to set filter based on selected OU
  SetOUFilter
 
  If Not IsNull(Me.OUFilter) Then
      Me.[HIPAA datasheet subform].Form.RecordSource = "SELECT * FROM HIPAA WHERE OU = '" & OUFilter.Column(1) & "';"
      End If
  Me.ComputerLookUp.Requery
 
End Sub

Yet, you notice I still need to call the SetOUFilter subroutine. That’s where I’m going to have a difference, I think.

Your SetOUFilter is:

 Sub SetOUFilter()
 
  Dim SubFormSQL As String
 
  SubFormSQL = "SELECT * FROM HIPAA"
  If IsNull(Me.OUFilter) Then
'        Give them all of the computers
  Else
      SubFormSQL = SubFormSQL & " WHERE OU = '" & OUFilter.Column(1) & "'"
  End If
 
  Me.RecordSource = SubFormSQL
  Me.ComputerLookUp.Requery
 
End Sub

It seems I'm having trouble posting my code into this reply. I'll try to put it in the next one.

[mdewell]

Mine, that I’ve renamed SetDataheetFilter, is the following:

 Sub SetDatasheetFilter()

 
  Set db = CurrentDb()
  Dim rs As Recordset
 
  Dim SubFormSQL As String
  Dim ComputerLookupSQL As String
  Dim WhereStatementSQL As String
  Dim WhereUnionStatementSQL As String
  Dim WHERESet As Boolean
  Dim OUFilterSQL As String
  Dim CaseLockedSQL As String
  Dim BootOnlySQL As String
  Dim JavaVersionSQL As String
  Dim AdobeVersionSQL As String
  Dim BIOSVersionSQL As String
 
  SubFormSQL = "SELECT * FROM tblHIPAArelational"
  WHERESet = False
  ComputerLookupSQL = "SELECT tblComputer.ID, tblComputer.Computer FROM tblComputer WHERE tblComputer.ID" & _
            " IN (SELECT tblHIPAArelational.tblComputer_ID FROM tblHIPAArelational"
  WhereStatementSQL = ""
 
  If Me.cboOUFilter.Column(1) = "<ALL>" Then
'        Give them all OUs
  Else ' OU selected
      OUFilterSQL = " WHERE tblOU_ID = " & cboOUFilter.Column(0)
      WHERESet = True
  End If
 
If Me.cboCaseLockedFilter = "Yes and No" Then
'        No Case Locked requested
Else ' Specific Case Locked request
   If Me.cboCaseLockedFilter = "Yes" Then
       If WHERESet Then
           CaseLockedSQL = " AND Case_Locked_Boolean <> 0"
       Else
           CaseLockedSQL = " WHERE Case_Locked_Boolean <> 0"
           WHERESet = True
       End If
   Else ' Me.CaseLockedFilter = "No"
       If WHERESet Then
           CaseLockedSQL = " AND Case_Locked_Boolean = 0"
       Else
           CaseLockedSQL = " WHERE Case_Locked_Boolean = 0"
           WHERESet = True
       End If
   End If
End If
 
If Me.cboBootOnlyFilter = "Yes and No" Then
'        No Boot Only requested
Else ' Specific Boot Only request
    If Me.cboBootOnlyFilter = "Yes" Then
        If WHERESet Then
            BootOnlySQL = " AND Boot_Only_Boolean <> 0"
        Else ' WHERE not set yet
            BootOnlySQL = " WHERE Boot_Only_Boolean <> 0"
            WHERESet = True
        End If
    Else ' Me.cboBootOnlyFilter = "No"
        If WHERESet Then
            BootOnlySQL = " AND Boot_Only_Boolean = 0"
        Else
            BootOnlySQL = " WHERE Boot_Only_Boolean = 0"
            WHERESet = True
        End If
    End If
End If
 
If Me.cboJavaVersionFilter.Column(1) = "<ALL>" Then
'        Give them all Java versions
Else ' Java Version selected
    If WHERESet Then
            JavaVersionSQL = " AND tblJavaVersion_ID = " & Me.cboJavaVersionFilter.Column(0)
    Else
            JavaVersionSQL = " WHERE tblJavaVersion_ID = " & Me.cboJavaVersionFilter.Column(0)
        WHERESet = True
    End If
End If
 
If Me.cboAdobeVersionFilter.Column(1) = "<ALL>" Then
'        Give them all Adobe versions
Else ' Adobe Version selected
    If WHERESet Then
            AdobeVersionSQL = " AND tblAdobeVersion_ID = " & Me.cboAdobeVersionFilter.Column(0)
        End If
    Else
            AdobeVersionSQL = " WHERE tblAdobeVersion_ID = " & Me.cboAdobeVersionFilter.Column(0)
        WHERESet = True
    End If
End If
 
    If Me.cboBIOSVersionFilter.Column(1) = "<ALL>" Then
'        Give them all BIOS versions
    Else ' BIOS Version selected
        If WHERESet Then
                BIOSVersionSQL = " AND tblBIOSVersion_ID = " & Me.cboBIOSVersionFilter.Column(0)
        Else
                BIOSVersionSQL = " WHERE tblBIOSVersion_ID = " & Me.cboBIOSVersionFilter.Column(0)
            WHERESet = True
        End If
    End If

    WhereStatementSQL = OUFilterSQL & _
                        CaseLockedSQL & _
                        BootOnlySQL & _
                        JavaVersionSQL & _
                        AdobeVersionSQL & _
                        BIOSVersionSQL