Using Set Control = Nested Subform

MS Office Access


  • 1. =?UTF-8?B?UmU6IEJhc2luZyBhIGZvcm0gb24gU1FMIGFzIG9wcG9zZWQgdG8gYSA=?= =?UTF-8?B?dGFibGUgb3IgcXVlcnkg4oCcZm9ybXMuUmVjb3JkUw==?=
    Mr B wrote: > CES, > > If the SQL statement that you are passing to the forms record source is not > updatable then the data in your form will also not be updatable. If the sql > statement you use as the record source is updatable the you will be able to > update the data in your form. > > This is not the same as a Pass through query. Per the Access help file: > > A pass-through query sends commands directly to ODBC databases, such as > Microsoft FoxPro, using commands that are accepted by the server. For > example, you can use a pass-through query to retrieve records or change data. > With pass-through queries, you work directly with the tables on the server > instead of linking to them. Pass-through queries are also useful for running > stored procedures on an ODBC server. > > To test the update capabilities of your sql statement, try creating your sql > statement as a query, using the QBE. Run the query and then try updating or > change the values while in the query results. If you can update the data > there then you would be able to update the records in your form. > All, I'm sorry for wasting your time, I had a required field in the table that was causing the problem... Thank You
  • 2. Format in an unbound Text Box
    Hi all, I have a form that I have put an unbound text box in, which displays the previous entries from my table. One of my fields is Current Time. Which on my form I set to Short Date (military time). How can I have my unbound text display Short time as well. here is my code (control source of my unbound text box). SELECT DISTINCTROW tblSITLog.[current time], tblSITLog.MONTH, tblSITLog.DAY, tblSITLog.SIT, tblSITLog.[NATURE OF iNCIDENT], tblSITLog.[Case Description], tblSITLog.Incident_Number FROM tblSITLog WHERE (((tblSITLog.[NATURE OF iNCIDENT])="CHRONO ENTRY")) ORDER BY tblSITLog.Incident_Number DESC; Thanks in advance, Steph
  • 3. vbOkCancel Msg will not cancel
    How do I get the cancel button to cancel the action in error 2169 below? I know I need to specify what action to take when cancel is selected, but how do I phrase it? Currently pressing OK or Cancel both respond as though you pressed OK. Thanks! Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 3314 Then Response = acDataErrContinue 'Don't display the default message MsgBox "Enter data in all required fields." ElseIf DataErr = 2169 Then Response = acDataErrContinue 'Don't display the default message MsgBox "Close form without saving?", vbExclaimation + vbOKCancel ElseIf DataErr = 2113 Then Response = acDataErrContinue 'Don't display the default message MsgBox "Enter valid start date." Else MsgBox "Error#: " & DataErr 'Display the error number Response = acDataErrDisplay 'Display default message End If End Sub
  • 4. Where Clause Formulation
    Hi all - Application: Complaint Ticketing System based on a Table/Form named 'CList' with: 1. Y/N field 'TClosed' (checked=Complaint resolved, unchecked=Complaint Open) 2. Date field 'CDate' (date that complaint was initially entered) I'm struggling with the formulation of a where clause in the following procedure. The procedure is a Click event procedure for a button on the "CList" form that should list unresolved complaints (TClosed=False) that are less than 3 days old (System Date minus CDate<3). Private Sub btnListRecentOpenComplaints_Click() DoCmd.OpenForm "CList", acFormDS, , "TClosed = False And Date-CDate<3", acFormEdit, acWindowNormal End Sub "TClosed = False And Date-CDate<3" is incorrect because the Date function is inside of the quotes and it's not recognized as a function. There may be other problems with this clause, too. Any advice is appreciated. -- Thank you, Jay

Using Set Control = Nested Subform

Postby wrldruler » Thu, 01 Feb 2007 02:27:37 GMT

I've got a series of nested sub-forms:

(1a) Main Parent
(1b) Sub-form: Header of calendar -- Months, Days of Week, etc.
(1c) Continuous Sub-form: Displays employee vacations

control to Me and to a subform:

            Set ctrl_Weekday = Me("WeekDay_Text_" & strNum)
            Set ctrl_List = subfrm_Vacation_Details("List" & strNum)

But what is the syntax to Set the control to a NESTED subform.

I want to do something like:

            Set ctrl_List = 
subfrm_Vacations.subfrm_Vacation_Details("List" & strNum)



Re: Using Set Control = Nested Subform

Postby wrldruler » Thu, 01 Feb 2007 02:33:43 GMT

Never mind. This syntax did the trick:

                Set ctrl_List = subfrm_Vacations!
subfrm_Vacation_Details("List" & strNum)



Re: Using Set Control = Nested Subform

Postby Marshall Barton » Thu, 01 Feb 2007 03:01:15 GMT

Newer versions of Access are more strict about the subform
object references.  You should use the Form property:

Set ctrl_List =
subfrm_Vacations.FORM!subfrm_Vacation_Details.FORM("List" &
MVP [MS Access]

Re: Using Set Control = Nested Subform

Postby wrldruler » Fri, 02 Feb 2007 05:28:17 GMT

Follow-up question:

I am moving my code to a Module so that it can be called from a
several forms, including a Popup.

I have 3 forms:

(1A) Main_Form
(1B) Sub_Form1
(1c) Nested_Subform2

I can go down to the Sub_Form1 level with this code.

    Dim frm As Form
    Set frm = Forms("Main_Form")

    Dim sfr As SubForm
    Set sfr = frm.Controls("Sub_Form1")

    Dim ctrl_Combo_Units As Control
    Set ctrl_Combo_Units = sfr("Field_Name")

But I can't figure out the syntax for the Nested.

I want to do something like:

    Dim nested_sfr As SubForm
    Set nested_sfr ="Nested_Subform2")

    Dim ctrl_Combo_Units As Control
    Set ctrl_Combo_Units = nested_sfr("Field_Name")

Thanks again,


Re: Using Set Control = Nested Subform

Postby Perry » Fri, 02 Feb 2007 05:34:23 GMT

Set nested_sfr ="Nested_Subform2").Form


"wrldruler" < XXXX@XXXXX.COM > schreef in bericht 

Re: Using Set Control = Nested Subform

Postby Marshall Barton » Fri, 02 Feb 2007 14:48:10 GMT

I think you might be confusing the subform control and the
form object it is displaying.

Try using some thing more like:

Dim frm As Form
Dim sfr As Form
Dim nested_sfr As Form
Set frm = Forms("Main_Form")
Set sfr = frm.Controls("Sub_Form1").Form
Set nested_sfr = sfr.Controls("Nested_Subform2").Form

Or, to get to nested directly from the main form:

Dim nested_sfr As Form
Set nested_sfr =

MVP [MS Access]

Similar Threads:

1.Tab controls nested 3 levels deep, using forms/subforms nested 3 levels

This works fine for me, and is very useful.

You can't nest tab controls on each other, so:

* start with main form
* tab control 1 is placed on main form
* subform 1 is placed on tab control 1
* tab control 2 is placed on subform 1
* subform 2 is placed on tab control 2
* tab control 3 is placed on subform 2
* subform 3 is placed on tab control 3
* etc.

A reference to the lowest-level subform can be done this way:

Forms.mainForm.Form.subForm1.Form.subForm2.Form.subForm3.Form.RecordSource =
"sql statement"

Nothing too groundbreaking.  Just wondering if others have tried it, and
have gone any levels deeper?

2.Setting "As Control" for Nested Subforms

I've got a series of nested sub-forms:

(1a) Main Parent
(1b) Sub-form: Header of calendar -- Months, Days of Week, etc.
(1c) Continuous Sub-form: Displays employee vacations

I need to iterate through the sub-form name ("subfrm_Vacations_January,
subfrm_Vacations_February, ...", reference a nested subform with a
constant name ("subfrm_Vacation_Details"), and then iterate through a
bunch of Listboxes on the nested subform (List_01, List_02....)

I can reach the sub-form via:
Set cntrl_Month = Me("subfrm_Vacations_" & Month_Name)

I can reach the Listboxes via:
Set cntrl_Listbox = subfrm_Vacation_Details("List" & strNum)

But I can't figure out how to put the two together. I want to do
something like:

cntrl_Listbox = cntrl_Month.subfrm_Vacation_Details("List" & strNum)


3.Set focus in mult-nested controls

I have the following:

Main form with 1 Tab Control with 8 pages.
On Tab_Page1, I have 4 combo boxes and a subform.
The subform contains a Tab Control with 4 pages; each TabPage corresponds to 
each of the 4 combo boxes .

What I am trying to accomplish:

When I select one of the combo boxes, I would like the corresponding TabPage 
in the subform to be "displayed" and the focus returned back to the combo box.

(i.e. show Tab_Page1 when combobox1 is selected, show Tab_Page2 when 
combobox2 is selected, show Tab_Page3 when combobox3 is selected; show 
Tab_Page4 when combobox4 is selected)

I assume this will required code in the "On Got Focus" of each combo box?

4.setting the focus to a nested subform

This thing is going to make me rip what little hair I have right out...

Okay, I have a main form.  On the main form, I have a subform and, on that 
subform, an additional nested subform.  I'm trying to set the focus to a 
control on the nested subform, but can't get it to work.  I've added the 
following lines as researched on-line:


Here's the rub...upon code execution, the expected control does not appear 
to have the focus; there's no cursor or highlight at all on the control.  
But, when I press the delete key (discovered this behavior by mistake) the 
data in the control that I intended to have the focus gets deleted.  Of 
course, at that point the control really does have the focus...but I really 
need my data.

My intent is to set the focus prior to making a search feature available 
thereby saving my users some keystrokes...just can't seem to get that control 
fully exposed. 

Anyone have any ideas?  Sure appreciate it.

5.Repost: Listening for events on subform nested in a tab control

In Access 2002, how do I code a reference to a sub-form in 
a main form from an independent third form.  The syntax to 
reference a sub-form.control does not work and is not 
appropriate for my need.  I need the sub form iteself, not 
a control on the subform.  Here is the full story:

I wish to synchronize a "MyPopUpForm" with custom events 
generated in "MyMainForm" and "MySubForm". 

MySubForm is nested on a page in a tab control 
on "MyMainForm".  

"MyPopUpForm" is opened via a control button on 
MyMainForm, so I'm sure both MyMainForm and MySubForm are 
loaded when MyPopUpForm fires its OnLoad event. 

MyMainForm raises event "NewEmp(EmpID)" that MySubForm and 
MyPopUpForm successfully handle.  

So far, so good with my code.  Now for what's not working..

MySubForm raises event CurRec(EffectiveDate).
MyPopUpForm should catch and handle the event by updating 
a text box control with the EffectiveDate.

Problem:  I keep getting messages at runtime that 
MySubForm cannot be found.

Here's my code:
On "MySubForm",  I have declared a custom event in the 
global area:

   Public Event CurRec(EffectiveDate)

also I have:

   Private Sub Form_Current()
      RaiseEvent CurRec(EffectiveDate)
   End Sub
In the global area of "myPopUp" form I have:

   Dim WithEvents subComp As Form_MySubForm 

Then I coded:

   Private Sub Form_Load()
     Set subComp = Forms!frmEmpMaint!MySubForm
   End Sub

My problem is coding the proper syntax for the "Set 
subComp" statement above.  Various coding attempts have  
all lead to runtime messages that generally say the 
subform can't be found.

Does anyone you know know how to code this?

6. Referencing a control in a nested subform

7. Listening for events on subform nested in a tab control

8. use a value in a text control to goto a record on a nested subform

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 91 guest