Access: How do I choose from two SQL commands to fill combo box

[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;

Here is what I have come up with so far. I have no idea if I am anywhere close to getting this right...

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  

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

Any help would be great!!! Thanks!!

 

[PHV]

Simply replace this:
Else If
with this:
Else

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886