Choose from two SQL statements to fill Combo Box 1

[efiftythree]

Im working on a database to track information about our phone switch. I have a form for adding a new phone/device and on that form there are currently three combo boxes that are built based on information in the other combo boxes.

They are cboEqlist, cboLenlist, and cboExttrunk.

cboEqlist determines what goes into cboLenlist and I also need it to determine what goes into cboExttrunk. Problem is that extensions and trunks are in two different tables. So I need to have cboExttrunk choose from two different SQL commands depending on whats in cboEqlist.

Options for cboEqlist = ATTCON, DTERM, DID, TRUNK, SLT, OPX

If cboEqlist = TRUNK then cboExttrunk =

SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse FROM tblTrunks WHERE (((tblTrunks.inuse)=0)) ORDER BY tblTrunks.trunk;

ANYTHING ELSE =

SELECT tblExt.ID, tblExt.ext, tblExt.inuse FROM tblExt WHERE (((tblExt.inuse)=0)) ORDER BY tblExt.ext;

Thanks for taking the time to read my post!

 

[efiftythree]

This will show that I really don't have a head for programming!

Dim strSQL As String
Dim strEqtype As String
Dim strEqtrunk As String

strEqtype = Me.cboEqlist
strEqtrunk = "TRUNK"

If strEqtype = strEqtrunk Then
	strSQL = "SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse " & _
		     "FROM tblTrunks " & _
			 "WHERE (((tblTrunks.inuse)=0)) " & _
			 "ORDER BY tblTrunks.trunk;"
		 
Else If
	strSQL = "SELECT tblExt.ID, tblExt.ext, tblExt.inuse " & _
			 "FROM tblExt " & _
			 "WHERE (((tblExt.inuse)=0)) " & _
			 "ORDER BY tblExt.ext;"
End If

Im not sure if this is even close to correct. Even if it is.. I dont know how to assign the strSQL to the RowSource of the Combo Box.

 

[efiftythree]

Maybe like this?

Me!cboExttrunk.RowSourceType = "Table/Query"
Me!cbxExttrunk.RowSource = strSQL

 

[efiftythree]

I hate to keep responding to my own thread but I'm still in need of assistance. If there is anyone that can help me with this problem please respond. Thanks!!!

 

[SgtJarrow]

You look like you are on the right track. Try the following (taken from your previous posts):

Dim strSQL As String
Dim strEqtype As String
Dim strEqtrunk As String

strEqtype = Me.cboEqlist
strEqtrunk = "TRUNK"

If strEqtype = strEqtrunk Then
    strSQL = "SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse " & _
             "FROM tblTrunks " & _
             "WHERE (((tblTrunks.inuse)=0)) " & _
             "ORDER BY tblTrunks.trunk;"
        
Else
    strSQL = "SELECT tblExt.ID, tblExt.ext, tblExt.inuse " & _
             "FROM tblExt " & _
             "WHERE (((tblExt.inuse)=0)) " & _
             "ORDER BY tblExt.ext;"
End If

Me!cbxExttrunk.RowSource = strSQL
me!cboExttrunk.Requery

That should be about right. If this doesn't work, let us know what is happening...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[efiftythree]

Ok! Thanks mstrmage1768!! Im almost there. Here is what I have so far:

Private Sub cboEqlist_AfterUpdate()
    Me.cboLenlist = Null
    Me.cboLenlist.Requery
    Me.cboLenlist = Me.cboLenlist.ItemData(0)
    
    Dim strSQL As String
    Dim strEqtype As String
    Dim strEqtrunk As String
    
    strEqtype = Me.cboEqlist
    strEqtrunk = "TRUNK"
    
    If strEqtype = strEqtrunk Then
        strSQL = "SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse " & _
                 "FROM tblTrunks " & _
                 "WHERE (((tblTrunks.inuse)=0)) " & _
                 "ORDER BY tblTrunks.trunk;"
            
    Else
        strSQL = "SELECT tblExt.ID, tblExt.ext, tblExt.inuse " & _
                 "FROM tblExt " & _
                 "WHERE (((tblExt.inuse)=0)) " & _
                 "ORDER BY tblExt.ext;"

    End If
    
    Me.cboExttrunk.RowSource = strSQL
    Me.cboExttrunk.Requery
   
End Sub

I have a few problems. I need to set the Column Widths to somthing like 0";0";1";0" and I also need to set the Bound Column.

The first SQL query seems to be causing some sort of problem. I get an error that reads: "Data Type Mismatch in criteria expression." Here is the SQL string that ends up in the Row Source property:

SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse FROM tblTrunks WHERE (((tblTrunks.inuse)=0)) ORDER BY tblTrunks.trunk;

I'm going to keep plugging away it this but if anyone has any pointers.. I'd be more than happy to take them! Thanks again!

 

[efiftythree]

Here's what I have come up with so far.

Private Sub cboEqlist_AfterUpdate()
    Me.cboLenlist = Null
    Me.cboLenlist.Requery
    Me.cboLenlist = Me.cboLenlist.ItemData(0)
    
    Dim strSQL As String
    Dim strColCount As String
    Dim strColWidth As String
    Dim strColBound As String
    Dim strEqtype As String
    Dim strEqtrunk As String
    
    strEqtype = Me.cboEqlist
    strEqtrunk = "TRUNK"
    
    If strEqtype = strEqtrunk Then
        strSQL = "SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse FROM tblTrunks WHERE (((tblTrunks.inuse)=0)) ORDER BY tblTrunks.trunk;"
                 
        strColCount = "3"
        strColWidth = ""
        strColBound = "2"
            
    Else
        strSQL = "SELECT tblExt.ID, tblExt.ext, tblExt.inuse " & _
                 "FROM tblExt " & _
                 "WHERE (((tblExt.inuse)=0)) " & _
                 "ORDER BY tblExt.ext;"
                 
        strColCount = "3"
        strColWidth = ""
        strColBound = "2"

    End If
    
    Me.cboExttrunk.RowSource = strSQL
    Me.cboExttrunk.ColumnCount = strColCount
    Me.cboExttrunk.ColumnWidths = strColWidth
    Me.cboExttrunk.BoundColumn = strColBound
    Me.cboExttrunk.Requery
   
End Sub

Now, I'm not sure how to complete the strColWidth because it needs to equal 0";1";0" and I'm not real sure how to make the compiler ignore the extra ";.

Also, I adjusted the first query and put it all on one line and that seems to have removed the error but I still dont get any data in that one which makes me thing its not really working at all. I do get data with the second query however the columns are not set right so I see all three data columns in the drop down. Thanks again.

 

[efiftythree]

OK. All is well. Thanks to mstrmage1768 for putting me on the right track.

Private Sub cboEqlist_AfterUpdate()
    Me.cboLenlist = Null
    Me.cboLenlist.Requery
    Me.cboLenlist = Me.cboLenlist.ItemData(0)
    
    Dim strSQL As String
    Dim strColCount As String
    Dim strColWidth As String
    Dim strColBound As String
    Dim strEqtype As String
    Dim strEqtrunk As String
    
    strEqtype = Me.cboEqlist
    strEqtrunk = "TRUNK"
    
    If strEqtype = strEqtrunk Then
        strSQL = "SELECT tblTrunks.ID, tblTrunks.trunk, tblTrunks.inuse " & _
                 "FROM tblTrunks " & _
                 "WHERE (((tblTrunks.inuse)=0)) " & _
                 "ORDER BY tblTrunks.trunk;"
                 
        strColCount = "2"
        strColWidth = "0;1 in;0"
        strColBound = "2"
            
    Else
        strSQL = "SELECT tblExt.ID, tblExt.ext, tblExt.inuse " & _
                 "FROM tblExt " & _
                 "WHERE (((tblExt.inuse)=0)) " & _
                 "ORDER BY tblExt.ext;"
                 
        strColCount = "3"
        strColWidth = "0;1 in;0"
        strColBound = "2"

    End If
    
    Me.cboExttrunk.RowSource = strSQL
    Me.cboExttrunk.ColumnCount = strColCount
    Me.cboExttrunk.ColumnWidths = strColWidth
    Me.cboExttrunk.BoundColumn = strColBound
    Me.cboExttrunk.Requery
   
End Sub