changing subform recordsource from main form

[svdoerga]

I am creating a form to view certain data summaries of my database. I have started out with the form having controls where the user specifies 4 values. These 4 values are then used to create a query when the user presses a button. So far the data is only retrieved from a query called "Item_Query", but I want to be able to retrieve data from any table or query. The query that is generated with the user input should be made the recordsource for the subform and the data should be displayed in the subform. The subform starts out without a recordsource. I have the part where I create the query:

Private Sub cmdShow_Click()

Dim strQuery, strTime, strReFoRe, strWarranty As String

strTime = "((Item_Query.ReceivedDate) Between #" & txtDateFrom & "# And #" & txtDateTo & "#)"

Select Case frmReFoRe
    Case 1
        strReFoRe = ""
    Case 2
        strReFoRe = " AND ((Item_Query.ReFoReName)='" & cboReFoReName.Column(1) & "')"
End Select

Select Case frmWarranty
    Case 1
        strWarranty = ""
    Case 2
        strWarranty = " AND ((Item_Query.Warranty)= False)"
    Case 3
        strWarranty = " AND ((Item_Query.Warranty)= True)"
End Select
    
strQuery = "SELECT Item_Query.ManufacturerName, Count(Item_Query.ItemID) AS Items FROM Item_Query " & _
"WHERE (" & strTime & strReFoRe & strWarranty & ") " & _
"GROUP BY Item_Query.ManufacturerName;"
    
Me.RepQ_items_per_manufacturer_subform.Form.RecordSource = strQuery
Me.RepQ_items_per_manufacturer_subform.Requery

End Sub

Now I'm not sure how I should handle the controls on the subform. I want to view the subform as a datasheet. The data summaries I want to display have 2 or 3 columns and usually have a calculated field.

How should I generate the text boxes to display the data from code? And how do I get each of them the proper control source?

 

[MajP]

Pick a max number of fields that you would want. Lets say 15 fields. put 15 unbound controls on the subform and set to invisible. It will be in data sheet view so does not matter, you could put a hundred. I would give them a simple name convention like
txtBX1, txtBx2


I am not sure how you are building your query, but I assume you have a way to select the fields you want. I would store the fields in an array called fieldsNames

The subform should be unbounded.

When you load the subform
1)Set the recordsource to your query string
2) 'clear the controls
for I = 1 to 15
with me.subformname.form.controls("txtBx" & I)
.visible = false
.recordsource = ""
end with
next I
'now reset the controls
for I = 1 to ubound(fieldNames)
with me.subformName.form.controls("txtBx" & I)
.recordsource = fieldsNames(I)
.visible = true
end with
next I

 

[svdoerga]

Hi MajP,

I am trying to hide the controls, but I get runtime error 2165 "You can't hide the control while it has focus". I have this code:

Private Sub Form_Load()
Dim intI As Integer

cmdShow.SetFocus
For intI = 0 To 3
    With Me.RepQ_items_per_manufacturer_subform.Form.Controls("txtField" & intI)
       .Visible = False
       .ControlSource = ""
    End With
Next intI

End Sub

I thought I could work around this code by setting the "visible" property to "no" for all the controls in design view, but then all the columns are visible anyway in the datasheet when I open the form. Is there another property to modify to hide the columns in the datasheet?

Another problem I have is that the query returns less records than it should. I copied the same query into query design and it retrieves a lot more records...any idea how that could happen?

 

[MajP]

'leave txtField0 always visible
Me.RepQ_items_per_manufacturer_subform.Form.Controls("txtField0").setfocus
For intI = 1 To 3
With Me.RepQ_items_per_manufacturer_subform.Form.Controls("txtField" & intI)
.Visible = False
.ControlSource = ""
End With
Next intI

 

[MajP]

BTW, another way to do this would be to simply make the source object a query. Then modify the SQL of the querydef. I have not tried it but assume it will work.

 

[MajP]

here is an easy way.
Build a subform, "subFrmQry"
Build a query, "tempQuery"
Make this your source object of the subform.

To change this dynamically to another sql string pass the sql string to the following.

Private sub changeSubFrmSql(sqlString)
CurrentDb.QueryDefs("tempQuery").SQL = strSql
Me.subFrmQry.SourceObject = "query.tempQuery"
End Sub

 

[svdoerga]

Hi MajP,

I have it working now. I found that to hide columns you need to set the .ColumnHidden property to false.

Thanks for your help!