Need records to show in a subform

[ssmith001]

I have a form where users select from different listboxes which I then use to build a SQL statement to return the matching records. On the main form I have a subform that is based on the query that was just created.

The problem is that I can't seem to get the returned records to display in the subform. They end up in a pop-up datasheet. What am I doing wrong?

Private Sub cmdRunQuery_Click()

Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set Db = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0

where = Null
where = "WHERE (((tblRouting.UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM tblRouting where"
where = where & " tblRouting.Location_ID= '" + Me!Text35 + "'))"
where = where & " AND tblRouting.Final_Dest= '" + Me!List29 + "'"
where = where & " AND tblRouting.Ship_Day= '" + Me!cboShipDay + "')"

Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select tblRouting.LOCATION_ID, tblLocation.NAME, tblLocation.CITY, tblLocation.STATE, " & _
" tblLocation.REGION, tblRouting.UNIQUE_LANE_ID, tblRouting.CARRIER_ID, tblRouting.SHIP_DAY, " & _
" tblRouting.DELIVERY_DAY, tblRouting.TIME_AT_LOCATION, tblRouting.STOP_NUM, " & _
" tblRouting.NO_STOPS FROM tblLocation INNER JOIN tblRouting ON tblLocation.LOCATION_ID = " & _
" tblRouting.LOCATION_ID " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenForm ("subfrmDynamicQuery")

On Error Resume Next
Db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

End Sub

 

[threinen]

have you set the Default View of your subform to Single Form?

 

[pbaldy]

This is your problem:

DoCmd.OpenForm ("subfrmDynamicQuery")

As I noted in your other thread: I see nothing "dynamic" about that query that requires it to be built in code. I'd have a saved query that looked at those form controls for their values. Requery the subform when the values are input.

 

[ssmith001]

I agree, this is not very dynamic, and I can work on that later. I still don't understand why the subform pops up? It has a default view of datasheet. I guess that baits the question...what is the proper way to create the subform? I had created it from the query.

 

[pbaldy]

You don't open the subform. It's already open as part of the main form. You requery it, set its source, etc, depending on what you're doing. You've told it to open another instance of it, so that's what it's done.

 

[ssmith001]

Aha...I think I'm getting it now. Once I run the query, do I requery the main form then to fill the subform with the results?

 

[pbaldy]

You don't "run the query". You'd requery the subform, but I'm thinking that won't work. Since you delete the querydef in your code, I assume the subform isn't based on that query. I'd either use the saved query as the subform's source and requery it, or junk the whole querydef bit and create a string variable instead. Then at the end of the code, set the subform's source to the string:

strSQL = "SELECT..."
Me.SubformControlName.Form.RecordSource = strSQL

 

[ssmith001]

OK, I went with the string variable option and it works. Thanks a lot for the help!

Now, I have a "Clear All" button that I need use to wipe the listboxes and the data in the subform clean so the user can start over again. I can wipe the listboxes clean but I don't seem to know how to empty the subform.

Ideas?

 

[pbaldy]

Try

Me.SubformControlName.Form.RecordSource = ""