Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 7th, 2005, 09:07 PM
Authorized User
Join Date: Apr 2005
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old June 12th, 2005, 11:33 AM
Friend of Wrox
Join Date: Nov 2004
Location: Seattle, WA, .
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
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 10:11 AM.

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