In your original email, you were using text boxes named txtData1, txtData2,
txtData3, . . ., txtData25. These text box names were hard coded. If you
dynamically change the text box names so that the Datasheet headers are the
query's field names, your hard coded txtData# names no longer work.
I don't see any way of determining at run time what the text box names are
from the last query. That is, they no longer follow the naming convention
that you were relying on. I believe you need to store these names in a
table.
So in your Form_Load event, you must change the text box names to match your
recordsource query's field names, and then store each of these in a table.
The table should have at least two fields:
Position Number
TextBoxName Text
For initial setup, I would fill the Postion column with the numbers 1-25,
and the TextBoxName column with txtData1 - txtData25.
Then, instead of
=============================================
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
=============================================
you could use
=============================================
intField = 1
For Each fld In Me.Recordset.Fields
textBoxName = DLookup("TextBoxName", "tbl_TextBoxNames", "Position = " &
intField)
Me.Controls(textBoxName).ControlSource = fld.Name
Me.Controls(textBoxName).Visible = True
Me.Controls("lblData" & intField).Caption = fld.Name
Me.Controls("lblData" & intField).Visible = True
' Must Change The Text Box Names, but can it be done from the Load event?
' (since you must open the form in design view to change the names)
CurrentDb.Execute "Update tbl_TextBoxNames Set TextBoxName = '" & fld.Name
& "' Where Position = " & intField
intField = intField + 1
Next
For intField = intField To 25
textBoxName = DLookup("TextBoxName", "tbl_TextBoxNames", "Position = " &
intField)
Me.Controls(textBoxName).Visible = False
Me.Controls("lblData" & intField).Visible = False
Next
=============================================
Now the problem is incorporating changing the the Text Box Names. You can't
do this from the Load Event since you have to open the form separately in
design mode to change the names. I think the solution would be to add
another field to tbl_TextBoxNames called "NewTextBoxName" that you could
store the names to be changed to. Then, at the end of your Load Event, you
could close the form and call a module that changes the Text Box Names and
then reopens the form. When you reopen the form from the module, you would
need to skip the Load event, so we could set the OpenArgs argument. Here is
revised code:
=============================================
Private Sub Form_Load()
Dim fld As DAO.Field
Dim intField As Integer
Dim textBoxName As String
Dim numOfField As Integer
If Me.OpenArgs = "No Load" Then Exit Sub
intField = 1
For Each fld In Me.Recordset.Fields
textBoxName = DLookup("TextBoxName", "tbl_TextBoxNames", "Position = " &
intField)
Me.Controls(textBoxName).ControlSource = fld.Name
Me.Controls(textBoxName).Visible = True
Me.Controls("lblData" & intField).Caption = fld.Name
Me.Controls("lblData" & intField).Visible = True
CurrentDb.Execute "Update tbl_TextBoxNames Set NewTextBoxName = '" &
fld.Name & "' Where Position = " & intField
intField = intField + 1
Next
numOfFields = intField - 1
For intField = intField To 25
textBoxName = DLookup("TextBoxName", "tbl_TextBoxNames", "Position = " &
intField)
Me.Controls(textBoxName).Visible = False
Me.Controls("lblData" & intField).Visible = False
Next
DoCmd.Close acForm, "FormName"
Call ChangeTextBoxNames(numOfFields)
End Sub
=============================================
Then the following would go in a Module:
=============================================
Sub ChangeTextBoxNames(numOfFields As Integer)
Dim OldName, NewName
DoCmd.SetWarnings False
DoCmd.OpenForm "FormName", acDesign
For i = 1 To numOfFields
OldName = DLookup("TextBoxName", "tbl_TextBoxNames", "Position = " & i)
NewName = DLookup("NewTextBoxName", "tbl_TextBoxNames", "Position = " &
i)
Forms("FormName").Controls(OldName).Name = NewName
CurrentDb.Execute "Update tbl_TextBoxNames Set TextBoxName = '" &
NewName & "' Where Position = " & i
Next i
DoCmd.Close acForm, "FormName"
DoCmd.SetWarnings True
DoCmd.OpenForm "FormName",,,,,,"No Load"
End Sub
=============================================
Hopefully the form's role as a subform doesn't complicate things more. I
hope this works for you. Good luck.
HTH,
Toby
----- Original Message -----
From: "Patty" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Tuesday, June 14, 2005 6:59 AM
Subject: [AccessDevelopers] Re: Error w/ dynamic Form using Datasheet View
> 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
>
>
>
>
>
>
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/
