Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 7th, 2005, 09:07 PM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dynamic Datasheet Form Field Headings Wrong

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)


strQuery = "select "

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

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

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

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

End Sub
Old June 12th, 2005, 11:33 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Replacing field names in datasheet philiprodley Access VBA 1 August 10th, 2006 12:47 PM
Datasheet view of a Query - Field Format arfa Access 4 March 13th, 2006 12:34 PM
Refresh datasheet-view subform’s column headings f pkaptein1 Access 2 June 1st, 2005 04:27 PM
Field Headings in Crystal Reports Liz Crystal Reports 2 July 20th, 2004 10:01 AM
Exporting to Excel from Access - field headings no chimp Access VBA 1 July 9th, 2004 11:54 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.