Re: [AccessDevelopers] Error w/ dynamic Form using Datasheet View

[A.D.Tejpal]

Patty,       Sample db named SubFormDynamicFields_2K.zip, uploaded to files section of this group, should also be able to give you some working ideas. It needs reference to DAO 3.6.       On opening the main form, it grabs the names of all available tables and queries, and loads them in the row-source for the combo box. At this stage the main subform has five columns on display, all unbound, with generic headings. There is no record-source yet, for the subform.       As soon as a source table or query is selected in the combo box, the subform at right (serving as field list), gets populated with  names of fields in the selected table/query. Out of these, the user can click the check boxes for selecting the fields required to be loaded in the main subform.       On clicking the update button, the subform gets loaded with appropriate record-source and the controls get bound to  respective fields. The columns are displayed in proper order, with headings matching the field names. If the number of fields selected in the field list is less than the number of controls available, the unused columns remain unbound and get hidden. Widths of displayed columns get adjusted automatically, so as to match the overall width of subform control.       Some interesting peculiarities of datasheets are observed to be -     (a) Column Headings -             So long as captive labels are available, the column headings are governed by captions of these labels, over-ruling the names of respective controls. If the labels are eliminated (as done in the sample db), the column headings are governed by names of relevant controls.     (b) Column Order -             For ensuring desired relative positioning of columns subjected to repeated manipulation, TabIndex property alone is not found adequate. ColumnOrder property also needs to be explicitly assigned. While TabIndex values are zero based (0 for first column), ColumnOrder values commence at 2 (2 for first column).   Best wishes, A.D.Tejpal    ----- Original Message ----- From: Patty To: AccessDevelopers@yahoogroups.com Sent: Saturday, June 11, 2005 05:53 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 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 the Yahoo! Terms of Service.