Thanks again Toby, you're awesome! I'll set this up now and
hopefully it will resolve this issue so I can move on with my
project. You've been such a great help!! : < )
--- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]>
wrote:
> 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/
