By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,688 Members | 1,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,688 IT Pros & Developers. It's quick & easy.

Pass-through query as rowsource for combo box

P: n/a
Is it possible to set (using VBA) the rowsource for a combo box but
have that rowsource come from a pass-through query.

I can obviously manually go into the rowsource property and define the
query and even set up the odbc string, but if i want to set this using
VBA i cannot see how to do this.

I know i can set forms!frm_test!combobox.rowsource to the SQL but that
does not allow me to set the odbc for the passthrough element.

Is it just not possible?

Any thoughts woudl be gratefully received.

Thanks.

Jun 12 '07 #1
Share this Question
Share on Google+
5 Replies

P: n/a
OK, i've discovered that i should be setting the RecordSet property as
I can define a recordset using a query def object and then assign the
resultset to the control.

I think that is the best answer - any other comments or ideas?

Cheers,

On 12 Jun, 10:19, dkintheuk <dkinth...@googlemail.comwrote:
Is it possible to set (using VBA) the rowsource for a combo box but
have that rowsource come from a pass-through query.

I can obviously manually go into the rowsource property and define the
query and even set up the odbc string, but if i want to set this using
VBA i cannot see how to do this.

I know i can set forms!frm_test!combobox.rowsource to the SQL but that
does not allow me to set the odbc for the passthrough element.

Is it just not possible?

Any thoughts woudl be gratefully received.

Thanks.

Jun 12 '07 #2

P: n/a
Okay, that didn't work exactly as i planned - it seems that once you
have usde querydef to define the query you only have to refer to the
query as the rowsource by name rather than try to create a
recordsource and make that the recordsource of the control.

Lesson learned = keep it simple.

On 12 Jun, 12:31, dkintheuk <dkinth...@googlemail.comwrote:
OK, i've discovered that i should be setting the RecordSet property as
I can define a recordset using a query def object and then assign the
resultset to the control.

I think that is the best answer - any other comments or ideas?

Cheers,

On 12 Jun, 10:19, dkintheuk <dkinth...@googlemail.comwrote:
Is it possible to set (using VBA) the rowsource for a combo box but
have that rowsource come from a pass-through query.
I can obviously manually go into the rowsource property and define the
query and even set up the odbc string, but if i want to set this using
VBA i cannot see how to do this.
I know i can set forms!frm_test!combobox.rowsource to the SQL but that
does not allow me to set the odbc for the passthrough element.
Is it just not possible?
Any thoughts woudl be gratefully received.
Thanks.- Hide quoted text -

- Show quoted text -

Jun 12 '07 #3

P: n/a
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
Jun 12 '07 #4

P: n/a
On 12 Jun, 18:29, Tim Marshall <TIM...@PurplePandaChasers.Moertherium>
wrote:
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
That's nice pal, cheers for the input - i have found some interesting
things about the handling of ODBC that make it a little awkward to do
what i want - specifically because the destination databse is Sybase
(urk!).

That has helped me a great deal in refining what i had created.

Cheers pal.

Jun 13 '07 #5

P: n/a
dkintheuk wrote:
That's nice pal, cheers for the input - i have found some interesting
things about the handling of ODBC that make it a little awkward to do
what i want - specifically because the destination databse is Sybase
(urk!).
Glad it helped. I find that with apps based on pass through queries I
end up with no table links in the table tab of the mdb database window,
but tons and tons of PTQs in the query window.

As I mentioned, I usually store the connect string as a constant, but
this makes it difficult to hide the database password - even an mde can
be opened in notepad or some text reader and you can find the connect
string in plain text. Nasty, if you include the user password in the
string so that your users don't need to enter it. In my case, since
most of my apps are reporting on existing data, I just use a DSN that
has no writing privileges.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Jun 13 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.