Hi Toby, Thank you for this info; I'll be working with it now; if you have further insights re: info in < > below, I'd very much appreciate it! : )
<.....Another problem here is knowing what ControlName to put in that third line of code, since you are trying to do it all dynamically. I'd have to think about it more, but I'm not sure if there is an easy way to know which order the Controls are in. You may have to create a table to contain the Control Names from 1-25, then when you grab your query's field names, insert these into that table in order and then use the table to reset all your Control Names, and also know which controls to set to Visible = False......> --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]> wrote: > It is not the control source or caption that provides the name that shows up > in the column headers on a datasheet, but rather the Name of the textbox > when you edit the form in Design View. > > Change the NAMES of the text boxes, and you will affect the columns headers. > > The problem then becomes the difficulty of changing the names with VBA code. > You have to be in design view to edit the text box names. Further, you have > to disable the warnings or you will be prompted to confirm the saving of any > changes. The following works for me in a quick little test database: > > DoCmd.SetWarnings False > DoCmd.OpenForm "FormName", acDesign > Forms("FormName").Controls("ControlName").Name = "OtherName" > DoCmd.Close acForm, "FormName" > DoCmd.SetWarnings True > > Another problem here is knowing what ControlName to put in that third line > of code, since you are trying to do it all dynamically. I'd have to think > about it more, but I'm not sure if there is an easy way to know which order > the Controls are in. You may have to create a table to contain the Control > Names from 1-25, then when you grab your query's field names, insert these > into that table in order and then use the table to reset all your Control > Names, and also know which controls to set to Visible = False > > HTH, > Toby > > ----- Original Message ----- > From: "Patty" <[EMAIL PROTECTED]> > To: <[email protected]> > Sent: Friday, June 10, 2005 5:23 PM > Subject: [AccessDevelopers] Error w/ dynamic Form using Datasheet View > > > > 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. Can someone comment on how to resolve the datasheet view > > problem? > > > > 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 > > > > PLEASE NOTE: When I inserted this code as a solution <Me.Controls > > ("txtData" & intField).Name = fld.Name>, I received the following > > Visual Basic dialogue box error message: > > > > <Run-time error '2136': To set this property, open the form or report > > in Design view.> > > > > Thanks for any assistance! Patty > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > Yahoo! Groups Links > > > > > > > > > > > > Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
