In my parent form, I have two child subforms as follows: 1 is the subform employee header; it defaults to single form view. The second is the subform hours; it defaults to datasheet view and contains hours-worked detail. Using datasheet view allows (1) user to use built-in Access functionality to hide/unhide columns at will, and (2) supposedly: when the dynamic query (qryHeadings) linked to the datasheet subform changes to include new column headings/columns, then those new column headings/columns would refresh the linked subform datasheet the next time that subform was opened. I set up the design to do this as follows: SQL brings in different fields depending on the contents of a table. This table doesn't change often, but when it does, it can pull new fields with new column headings. Here is the VBA code that refreshes my qryHeadings:


Function test()
Dim dbs As Database
Dim qdf As QueryDef

Dim rst As Recordset
Dim strQuery As String
Dim strSQL As String
Dim Frst As Integer
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryHeading")

Frst = 1

strSQL = "SELECT FieldName, FieldLabel FROM tblColOrder " & _
"WHERE (((tblColOrder.Active) = Yes)) ORDER BY tblColOrder.Seq;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

rst.MoveFirst

strQuery = "select "

Do While Not rst.EOF
If Frst <> 1 Then
strQuery = strQuery & ", "
Else
Frst = 0
End If

strQuery = strQuery & rst("FieldName").Value & " as [" & rst("FieldLabel").Value & "] "
rst.MoveNext
Loop

rst.Close
strQuery = strQuery & " from tblHours;"

qdf.SQL = strQuery

End Function

Using the above with the below code (to impact the form itself) works beautifully when I view my subform in 'continous-form' or 'single-form' form view. However, when I use that solution and view my subform in 'datasheet' form view, I see the correct fields in the correct location yet the field headings (aka column headings), oddly enough, display txtData1, txtData2, etc. regardless of what I place in textbox name, or label name, or label caption, or even if I eliminate label name altogether. It appears that datasheet form view picks up what is placed in each textbox controlsource, which in my case is, literally =[txtData1], =[txtData2] etc., and uses the nonbracketed portion as the column/field heading for the appropriate field. This is regardless of what I put in the textbox name, or label name or label caption. Can someone comment on how to resolve the datasheet view problem? Thanks.


Private Sub Form_Load()
Dim fld As DAO.Field
Dim intField As Integer

intField = 1
For Each fld In Me.Recordset.Fields
Me.Controls("txtData" & intField).ControlSource = fld.Name
Me.Controls("txtData" & intField).Visible = True
Me.Controls("lblData" & intField).Caption = fld.Name
Me.Controls("lblData" & intField).Visible = True
intField = intField + 1
Next

For intField = intField To 25
Me.Controls("txtData" & intField).Visible = False
Me.Controls("lblData" & intField).Visible = False
Next

End Sub

You're doing the right thing to build the field names for the query so you will display the name in datasheet view. I.e. the "AS [fldname]" will name the columns. However, your code doesn't look like strQuery ends up with the correct Select statement syntax. It looks like strQuery is going to be:

Select fieldx, fieldy, fieldz, from tblHours;

Notice the extra comma and space after fieldz.

I don't know why your form is showing at all. Maybe the qdf.SQL = strQuery statement simply isn't accepting the new value??? The Form_Load event code works because it's using whatever SQL is in the querydef whether it is the correct statement or not.

Try putting:

Debug.Print Err.Description
Debug.Print strQuery
Debug.Print qdf.SQL

after qdf.SQL = strQuery. Of course the Err.Description will tell you if there is an error. Your test() function doesn't have error handling. But if the procedure that calls test() has error handling, you could be missing the error. Also, if the second and third lines don't print, error handling is taking over before your routine finishes. If the second and third lines are identical, the new value was accepted. At that point I don't know what's going on?

Also try:

debug.print Me.RecordSource

in Form_Load to be sure you have the expected record source.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org