Form's new SQL recordsource returns nothing

[kagard]

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

 

[kagard]

Forgot to mention that I'm using Access 2007.

K

 

[Duane Hookom]

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

 

[kagard]

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

 

[kagard]

Didn't help.

 

[kagard]

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

Keith