dkintheuk wrote:
Is it possible to set (using VBA) the rowsource for a combo box but
have that rowsource come from a pass-through query.
Here's what I do. All the code here uses David Fenton's dblocal
function which keeps the currentdb database object open throughout the
application. If you don't use this, where you see "dblocal", you'll
need to dim a DAO.database object
The following is the code from a form on open event (or could be used in
the combo box got focus event if the results are goiing to vary a lot
depending on the value of other combos and controls). It populates a
combo box cboAcctCredit with a pass through query. I'm using Oracle.
Sub sPopulateCombo()
dim strS as string
'Populate cboacctcredit - make PTQ of credit accounts
strS = "Select "
strS = strS & "cst_code ""TMA Acct Code"", "
strS = strS & "cst_name ""TMA Acct Name"", cst_active ""Active"" "
strS = strS & "from tma.f_accounts "
strS = strS & "where "
strS = strS & "cst_ay_fk in "
strS = strS & "(select mbc_tma_credit_acct_type_fk "
strS = strS & "from tma.mun_ban_constant) "
strS = strS & "order by 1"
'Use sCreatePT sub (code follows) to create PTQ
sCreatePT strS, "qryRulesTmaCreditAccts"
'Assign the PTQ to the rowsource of the combo box
Me.cboAcctCredit.RowSource = "qryRulesTmaCreditAccts"
End Sub
Sub sCreatePT(strSql As String, strQryName As String, _
Optional strConnect As String)
'Takes an SQL string (strSql) and a query name (strQryName) and either
'creates a new PT query or, if
'it exists already, changes the SQL string
'
'strSql is the Oracle select statement
'strQryName is the name of the PTQ to be saved in the mdb/e
'strConnect is the optional connect string that differs from the
' "default" connect string used here
Dim qdf1 As DAO.QueryDef
Dim booFound As Boolean
On Error GoTo Err_Proc
dbLocal.QueryDefs.Refresh
booFound = False
'dblocal - see email notes
For Each qdf1 In dbLocal.QueryDefs
If qdf1.Name = strQryName Then
booFound = True
Exit For
End If
Next
If booFound = False Then 'querydef does not exist, so create it
Set qdf1 = dbLocal.CreateQueryDef(strQryName)
Else 'already exists
Set qdf1 = dbLocal.QueryDefs(strQryName)
End If
'Now assign characteristics
'The connect string here is a constant declared in a standard
' module as Public Const cTmarConnect = "<connect string>"
If strConnect = "" Then strConnect = cTmarConnect 'default connect
string
With qdf1
.Connect = strConnect
.SQL = strSql
.ReturnsRecords = True
End With
Exit_Proc:
On Error Resume Next 'use brute force to close
qdf1.Close
Set qdf1 = Nothing
On Error GoTo 0
Exit Sub
Err_Proc:
Select Case Err.Number
'Case 3011 '
Case Else
'My own custom error handling routine that
' deals with Oracle errors
fError "Mod_Access_Objects", "sCreatePT"
Resume Exit_Proc
End Select
End Sub
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
