combo box question

T

Tcs

I have only one table that I would like to scroll to the screen. But I figure
that I need 2 combo boxes to filter the records I want.

I've tried using 2 combo boxes in the header section of just one form, with my
records displaying in the the detail section.

While I *have* been able to tie the second cobo box to the first, I can't get my
displayed records to be the ones I want. Do I need a subform instead? My books
and the the other info I've here and elsewhere just don't seem to be clear
enough on how to do it. Any help would be appreciated.

Thanks in advance,

Tom
 
T

Tcs

Sorry about this duplicate post. I'm having a problem
with my reader, it isn't showing me everything.
 
K

Ken Snell [MVP]

You can use two combo boxes in the form's header. The "trick" is to do a
requery of the form based (most likely) on clicking a button. The form's
recordsource query must use the form's combo boxes as the source of values
for filtering the query.
 
K

Ken Snell [MVP]

If it doesn't seem to work, post the details of the form's recordsource
query (SQL statement) and the combo boxes' names, and the code running on
the button that you click.
 
T

Tom

Thanks, here goes...
----------------------------------------

Form:
=====

Name: frmSubModules

Data
----
Record Source: tblSubModules
Order By: tblSubModules.RecCntr

----------------------------------------

Combo Box 1:
============

Name: cbxMetaSchema

Format
------
Column Count: 1
Column Widths: 1"

Data
----
Row Source Type: Table/Query
Row Source: SQL

SELECT DISTINCT tblSubModules.MetaSchema
FROM tblSubModules;

Bound Column: 1
Limit To List: No

Event
-----
After Update:

Private Sub cbxMetaSchema_AfterUpdate()
On Error GoTo Err_cbxMetaSchema_AfterUpdate

Dim strSQL As String

strSQL = "Select distinct CatalogVers from
tblSubModules"
strSQL = strSQL & " where MetaSchema = " & Me!
cbxMetaSchema
Me!cbxCatVersion.RowSourceType = "Table/Query"
Me!cbxCatVersion.RowSource = strSQL

Exit_cbxMetaSchema_AfterUpdate:
Exit Sub

Err_cbxMetaSchema_AfterUpdate:
MsgBox Err.Description
Resume Exit_cbxMetaSchema_AfterUpdate

End Sub

----------------------------------------

Combo Box 2:
============

Name: cbxCatVersion

Format
------
Column Count: 2
Column Widths: 1";1"

Data
----
Row Source Type: Table/Query
Row Source: SQL

SELECT DISTINCT tblSubModules.CatalogVers,
tblSubModules.MetaSchema
FROM tblSubModules
WHERE (((tblSubModules.MetaSchema)=[cbxMetaSchema]));

Bound Column: 2
Limit To List: Yes

Event
-----
After Update:

Private Sub cbxCatVersion_AfterUpdate()
On Error GoTo Err_cbxCatVersion_AfterUpdate

' strSQL = "Select * from tblSubModules "
' strSQL = strSQL & "Where MetaSchema = " & Me!
cbxMetaSchema
' Forms!frmSubModules.RowSourceType = "Table/Query"
' Forms!frmSubModules.RowSource = strSQL

Exit_cbxCatVersion_AfterUpdate:
Exit Sub

Err_cbxCatVersion_AfterUpdate:
MsgBox Err.Description
Resume Exit_cbxCatVersion_AfterUpdate

End Sub

----------------------------------------

Command Button:
===============

Name: cmdRefresh

Event
-----
On Click: (What Access built when I used the wizard.)

Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

----------------------------------------
 
K

Ken Snell [MVP]

A form does not have a RowSourceType nor a RowSource. It has a RecordSource.

To do what you want, change your form's RecordSource to a query that has an
SQL statement similar to this:

SELECT * FROM tblSubModules
WHERE MetaSchema = Forms!frmSubModules!cbxMetaSchema
AND CatVersion = Forms!frmSubModules!cbxCatVersion;

What you've set up is basing your form on a table, which will always show
the same records no matter what you do, and your cbxCatVersion_AfterUpdate
code make no sense because it's trying to set a RowSource for the form,
which doesn't have one.

--

Ken Snell
<MS ACCESS MVP>


Tom said:
Thanks, here goes...
----------------------------------------

Form:
=====

Name: frmSubModules

Data
----
Record Source: tblSubModules
Order By: tblSubModules.RecCntr

----------------------------------------

Combo Box 1:
============

Name: cbxMetaSchema

Format
------
Column Count: 1
Column Widths: 1"

Data
----
Row Source Type: Table/Query
Row Source: SQL

SELECT DISTINCT tblSubModules.MetaSchema
FROM tblSubModules;

Bound Column: 1
Limit To List: No

Event
-----
After Update:

Private Sub cbxMetaSchema_AfterUpdate()
On Error GoTo Err_cbxMetaSchema_AfterUpdate

Dim strSQL As String

strSQL = "Select distinct CatalogVers from
tblSubModules"
strSQL = strSQL & " where MetaSchema = " & Me!
cbxMetaSchema
Me!cbxCatVersion.RowSourceType = "Table/Query"
Me!cbxCatVersion.RowSource = strSQL

Exit_cbxMetaSchema_AfterUpdate:
Exit Sub

Err_cbxMetaSchema_AfterUpdate:
MsgBox Err.Description
Resume Exit_cbxMetaSchema_AfterUpdate

End Sub

----------------------------------------

Combo Box 2:
============

Name: cbxCatVersion

Format
------
Column Count: 2
Column Widths: 1";1"

Data
----
Row Source Type: Table/Query
Row Source: SQL

SELECT DISTINCT tblSubModules.CatalogVers,
tblSubModules.MetaSchema
FROM tblSubModules
WHERE (((tblSubModules.MetaSchema)=[cbxMetaSchema]));

Bound Column: 2
Limit To List: Yes

Event
-----
After Update:

Private Sub cbxCatVersion_AfterUpdate()
On Error GoTo Err_cbxCatVersion_AfterUpdate

' strSQL = "Select * from tblSubModules "
' strSQL = strSQL & "Where MetaSchema = " & Me!
cbxMetaSchema
' Forms!frmSubModules.RowSourceType = "Table/Query"
' Forms!frmSubModules.RowSource = strSQL

Exit_cbxCatVersion_AfterUpdate:
Exit Sub

Err_cbxCatVersion_AfterUpdate:
MsgBox Err.Description
Resume Exit_cbxCatVersion_AfterUpdate

End Sub

----------------------------------------

Command Button:
===============

Name: cmdRefresh

Event
-----
On Click: (What Access built when I used the wizard.)

Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

----------------------------------------
-----Original Message-----
If it doesn't seem to work, post the details of the form's recordsource
query (SQL statement) and the combo boxes' names, and the code running on
the button that you click.

--

Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Top