Form's new SQL recordsource returns nothing

Greetings:

I have a form with two subforms. When users indicates the records they
want to view, I change the form's recordsource to a SQL statement
representing their selection criteria. Then I requery the form. In
this case, the form returns no records and no errors. Here is the
code:

Private Sub cmdGo_Click()
Dim strSQL As String

strSQL = "SELECT * FROM Distribution WHERE DistID IN " _
& "(SELECT DistID FROM qryFindDistributions);"
Me.RecordSource = strSQL
Me.Requery
End Sub

If I leave the form open, copy and paste the SQL into a new query, and
run it, it returns the appropriate records. Any idea why the form
returns nothing?

TIA
Keith

Forgot to mention that I'm using Access 2007.

K

If I leave the form open, copy and paste the SQL into a new query, and
run it, it returns the appropriate records. Any idea why the form
returns nothing?

TIA
Keith

I would check the form's filter. Also, is the record set read-only and the
form set to data entry?

--
Duane Hookom
Microsoft Access MVP


"kagard" wrote:

Forgot to mention that I'm using Access 2007.

K

If I leave the form open, copy and paste the SQL into a new query, and
run it, it returns the appropriate records. Any idea why the form
returns nothing?

TIA
Keith

.

I assume that the qryFindDistributions query is somehow referencing a
parameter or parameters by which the user "indicates the records they want to
view". My guess would be that it's something to do with the way in which the
parameter or parameters are being evaluated. It may be that the act of
requerying the form is affecting the evaluation.

It might help the dog to see the rabbit if you post the SQL for the
qryFindDistributions query, and explain the mechanism by which the user
selects the records to which they want the form restricted.

Ken Sheridan
Stafford, England

kagard wrote:
Greetings:

I have a form with two subforms. When users indicates the records they
want to view, I change the form's recordsource to a SQL statement
representing their selection criteria. Then I requery the form. In
this case, the form returns no records and no errors. Here is the
code:

Private Sub cmdGo_Click()
Dim strSQL As String

strSQL = "SELECT * FROM Distribution WHERE DistID IN " _
& "(SELECT DistID FROM qryFindDistributions);"
Me.RecordSource = strSQL
Me.Requery
End Sub

If I leave the form open, copy and paste the SQL into a new query, and
run it, it returns the appropriate records. Any idea why the form
returns nothing?

TIA
Keith

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Thanks Ken (and Duane) for looking at this.

Here's the SQL my code uses for the Form's RecordSource:

SELECT * FROM Distribution WHERE DistID IN (SELECT DistID FROM
qryFindDistributions);

Here's the SQL for qryFindDistributions:

SELECT DISTINCT Distribution.DistID
FROM Entertainment LEFT JOIN ((Distribution LEFT JOIN AttendDDC ON
Distribution.DistID = AttendDDC.DistID)
LEFT JOIN AttendGuest ON Distribution.DistID = AttendGuest.DistID) ON
Entertainment.TicketID = Distribution.TicketID
WHERE (((AttendDDC.Employee)=[Forms]![frmDistribution].
[cblFindEmployee]) AND ((AttendGuest.Account)=[Forms]!
[frmDistribution].[cboFindAccount]) AND ((Entertainment.Event) Like
"*" & [Forms]![frmDistribution].[cboFindEvent] & "*"))
ORDER BY Distribution.DistID;

The second query references 3 combo boxes on frmDistribution:

cblFindEmployee
cboFindAccount
cboFindEvent

As I said, I can have the form open, the search parameters entered,
run eith query above, and get the right data back. It's just that it
doesn't show up on the form. I'm going to try capturing the values in
global variables and referencing them in the second query.

Keith
On May 21, 12:29*pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
It might help the dog to see the rabbit if you post the SQL for the
qryFindDistributions *query, and explain the mechanism by which the user
selects the records to which they want the form restricted.

Didn't help.

On May 21, 2:05*pm, kagard wrote:
I'm going to try capturing the values in
global variables and referencing them in the second query.

Duh! Form was in DataEntry mode. Sorry for the trouble.

Keith

On May 21, 2:18*pm, kagard wrote:
Didn't help.

On May 21, 2:05*pm, kagard wrote:



I'm going to try capturing the values in
global variables and referencing them in the second query.- Hide quoted text -

- Show quoted text -

There are a couple of points I notice with the qryFindDistributions query.
Firstly you are using LEFT OUTER JOINs unnecessarily. As the query is
restricted on columns on both of the tables on the right side of the joins
this in effect makes them INNER JOINs It shouldn't affect the operation of
the query, however. Secondly, you are using the dot operator when
referencing the controls. It's always been said in the past that the one
place one should use the exclamation mark as the operator is in a parameter
in a query. Whether this has changed in later versions of Access I don't
know. Finally the DISTINCT option and ORDER BY clause serve no purpose here.
So the query could be changed to:

SELECT Distribution.DistID
FROM Entertainment INNER JOIN ((Distribution INNER JOIN AttendDDC ON
Distribution.DistID = AttendDDC.DistID)
INNER JOIN AttendGuest ON Distribution.DistID = AttendGuest.DistID) ON
Entertainment.TicketID = Distribution.TicketID
WHERE AttendDDC.Employee=[Forms]![frmDistribution]![cblFindEmployee]
AND AttendGuest.Account=[Forms]![frmDistribution]![cboFindAccount]
AND Entertainment.Event Like "*" & [Forms]![frmDistribution]![cboFindEvent] &
"*";

You could also change the form's RecordSource to use the EXISTS predicate
rather than the IN operator; the former is usually more efficient:

SELECT *
FROM Distribution AS D1
WHERE EXISTS
(SELECT *
FROM qryFindDistributions AS D2
WHERE D2.DistID = D1.DistID);

Whether these amendments will make any difference I've no real idea. If not
you could try building the string expression for the form's RecordSource
property so that it concatenates the values of the controls in the string
rather than referencing the controls as parameters:

strSQL = "SELECT * " & _
"FROM Distribution AS D1 " & _
"WHERE EXISTS " & _
"(SELECT * " & _
"FROM Entertainment INNER JOIN ((Distribution AS D2 " & _
"INNER JOIN AttendDDC ON " & _
"D2.DistID = AttendDDC.DistID) " & _
"INNER JOIN AttendGuest ON D2.DistID = AttendGuest.DistID) ON " & _
"Entertainment.TicketID = D2.TicketID " & _
"WHERE D2.DistID = D1.DistID " & _
"AND AttendDDC.Employee = """ & _
[Forms]![frmDistribution]![cblFindEmployee] & """ " & _
"AND AttendGuest.Account = """ & _
[Forms]![frmDistribution]![cboFindAccount] & """ " & _
"AND Entertainment.Event LIKE ""*" & _
[Forms]![frmDistribution]![cboFindEvent] & "*"")";

I've assumed in the above that the Employee, Account and Event columns are
all of text data type and therefore enclosed the values in literal quotes
characters, represented by pairs of contiguous quotes characters in the
expression.

Ken Sheridan
Stafford, England

kagard wrote:
Thanks Ken (and Duane) for looking at this.

Here's the SQL my code uses for the Form's RecordSource:

SELECT * FROM Distribution WHERE DistID IN (SELECT DistID FROM
qryFindDistributions);

Here's the SQL for qryFindDistributions:

SELECT DISTINCT Distribution.DistID
FROM Entertainment LEFT JOIN ((Distribution LEFT JOIN AttendDDC ON
Distribution.DistID = AttendDDC.DistID)
LEFT JOIN AttendGuest ON Distribution.DistID = AttendGuest.DistID) ON
Entertainment.TicketID = Distribution.TicketID
WHERE (((AttendDDC.Employee)=[Forms]![frmDistribution].
[cblFindEmployee]) AND ((AttendGuest.Account)=[Forms]!
[frmDistribution].[cboFindAccount]) AND ((Entertainment.Event) Like
"*" & [Forms]![frmDistribution].[cboFindEvent] & "*"))
ORDER BY Distribution.DistID;

The second query references 3 combo boxes on frmDistribution:

cblFindEmployee
cboFindAccount
cboFindEvent

As I said, I can have the form open, the search parameters entered,
run eith query above, and get the right data back. It's just that it
doesn't show up on the form. I'm going to try capturing the values in
global variables and referencing them in the second query.

Keith
On May 21, 12:29 pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
It might help the dog to see the rabbit if you post the SQL for the
qryFindDistributions query, and explain the mechanism by which the user
selects the records to which they want the form restricted.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1