Question about query definition to filter data

[George]

frmMain has a qryResult query definition as a recordsource that contains two
relevant fields plus additional fields. Another table is linked to one of
the additional fields also contained in qryResult. These display reference
information or record survey results on frmMain.

field1 = Section contains a series of numbers from 1 to 13
field2 = Question contains a series of numbers from 1 to 273. These occur
in pairs generated by the cartesian product = 3,549 records. All pairs are
unique. The form is a survey questionair that asks each of 13 responders an
identical set of 273 questions about the status of various items.

cboSection is an unbound combo box.

field1 = SectionID (Autonumber 1 to 13)
field2 = Section (description)

I want the user to select his section via cboSection which displays
[Section] and should limit qryResult to his subset of 273 questions.
cboSection works but qryResult can't find it.

frmMain displays 3,549 records in the record selector when no section is
selected but should only display 273 records if any section has been
selected. When cboSection is changed the subset displayed should also
change (a different group of 273).

How do I code this to work with cboSection? I assume that something has to
go into the Criteria field that limits qryResult to the value of cboSection.
I haven't figured out the right code to add so this will work. All I get in
the record selector is 1 record with nothing displayed.

 

[Ken Snell \(MVP\)]

Add a "Where:" condition to the field2 field in the design view of qryResult
query:

Forms!frmMain!cboSection Or Forms!frmMain!cboSection Is Null

 

[Ken Sheridan]

I think Ken meant Field1 not Field2. You'll also need to requery the form in
the AfterUpdate event procedure of cboSection with:

Me.Requery

Ken Sheridan
Stafford, England

 

[George]

But if I add a WHERE condition, via the QBE window, I have to click the
Sigma sign (don't I?) which changes all other fields to "Group By" and that
changes the query result. The user is not appending to an existing table.
All responses are in the edit mode which updates a null field to a field
containing text. (two response fields) or from ) 0 to either 1, 2, or 3 for
a three state response (two responses) after selecting a combo box.
Responses go into other fields than field1 or field2. field1 (major sort)
and field2 (minor sort) are both sorted ascending (not relevant to the
SELECT I want to do).

WHERE do I add the WHERE clause?

If I typed any of the numbers 1 to 13 into the criteria for field1 I'd get
the 273 subset result I expect. But I want the user to accomplish this via
a combo box so the query definition is not exposed. The criteria (1 to 13)
is a PK field in the rowsource for the combo box. I just need to make this
substition, somehow ...

Do I need a separate query containing two fields, field1 and field2, add the
WHERE clause, then link that query to the balance of the fields used as a
recordsource for frmMain?

Understand that if the user does change the combo box I also need to
requery. I did that, but don't think it's my problem.

Thanks

 

[George]

I tried simply typing this expression into the criteria field of the QBE
window:

Forms!frmMain!cboSection (as a SELECT but not in a WHERE clause).

The first choice (SectionID = 1) returns 1 record not 273.
Choices (SectionID = 2 to 13) return 273 records, each section, as expected.

But if I add a textbox txtSection to frmMain and make the control source =
forms!frmMain!cboSection.column(0) (bound to SectionID) this displays all
the correct SectionID numbers. Then if I change the criteria field in my
frmMain recordsource query to

Forms!frmMain!txtSection

I then get the expected query results, all the respective sets of 273
records, via all 13 combo box choices. I'll probably leave this and make
txtSection not visible.

That is the same result I get if I just type 1 through 13 into the field1
criteria. (Although I woudn't want to expose the query to a user).

I don't understand why Forms!frmMain!cboSection does not work while
Forms!frmMain!txtSection works correctly

NOTE - This application is for an inexperienced user who will have to
maintain it going forward. It is a very simple ap. So I am using bound
controls not unbound, entirely eliminating all the embedded VBA / SQL she
inherited. The whole thing could almost have been built with a wizard.
There's a "cheat" - some tables are not fully normalized and rows are
created in advance which keeps the user in the edit mode. No new records
need to be created which eliminated prior code put in to
append/edit/navigate. A bunch of it didn't work.

 

[Ken Snell \(MVP\)]

I think Ken meant Field1 not Field2.

Yes, I did. Thanks, Ken.

 

[Ken Snell \(MVP\)]

What is the SQL statement of the query that you're using for the form's
RecordSource?

 

[Ken Sheridan]

When you enter an expression into the criteria row in the query design grid
this creates a WHERE clause. You'll see this if you switch to SQL view.

It sounds like your combo box's BoundColumn property is not 1. Its this
property which determines the value of the control. Note that a BoundColumn
property of 1 refers to the first column, whereas an index of 0 for the
Column property refers to the first column. This is because a BoundColumn
property of 0 would set the value of the control to the ListIndex value of
the selected row, not the first column.

Ken Sheridan
Stafford, England

 

[George]

Thanks to all - I got past this and it's working great.