How to dynamicaly reference a control on a subform 3

[1DMF]

Hi,

I seem to be unable to get the syntax right to reference a control on a subform from another form dynamically.

If I put this code directly on the pop-up form

msgbox (Forms!Contacts.subVisitsNotes.Form.Actions)

I get the content of the control Actions on the subform.

However, I can't get it right when using Forms() or Controls()

I have tried

Forms(vArgs(5)).Controls(vArgs(3)).Refresh

AND

Controls(vArgs(5) & "." & vArgs(3)).Refresh

But I just get an error "Can't find field Forms!Contacts.subVisitsNotes.Form.Actions"

in the fist attempt...

vArgs(5) = "subVisitsNotes"
vArgs(3) = "Actions"

the second attempt....

vArgs(5) = "Forms!Contacts.subVisitsNotes.Form"
vArgs(3) = "Actions"

What am i doing wrong?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

To refer to a control on a subform it has to be

from the form
me.subformControlName.form.controlonsubform
from external
forms("formName").subformControlName.form.controlonsubform

This has no reference to the form inside the subformcontrol
Forms(vArgs(5)).Controls(vArgs(3)).Refresh
If controls(vArgs(3)) is the subform control you would need
Forms(vArgs(5)).Controls(vArgs(3)).form.Refresh

 

[1DMF]

Hi MajP,

I still cant get it to work, I've tried

Forms(vArgs(5)).Controls(vArgs(3)).form.Refresh

Where vArgs(5) = 'subVistsNotes' , ie the name of the subform

and vArgs(3) = 'Actions' the field on the subform, but I get an error

Cant find the form 'subVisitsNotes'

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

Assuming:
vArgs(5) = "subVisitsNotes"
vArgs(3) = "Actions"
Then:
Forms!Contacts.Controls(vArgs(5)).Form.Controls(vArgs(3)).Refresh

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Ah, then I have a problem, because that isn't dynamic as Forms!Contacts is hard coded!

Looks like my OpenArgs needs another parameter as the popup form for 'enlarging' input boxes won't always be invoked from a subform field!

Never as simple as you first think is it!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

Have you tried to play with the Application.Eval method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

nope? in what context, I tried to Eval the vArgs to a strin g for the Forms() part but it didn't work, but probably had that syntax wrong as well.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

I still don't seem to be able to get this to work even with the additional argument.

Forms(vArgs(5)).Controls(vArgs(6)).Form.Controls(vArgs(3)).Refresh

I now get an error

Object doesn't support this property or method

(6) = 'subVisitsNotes'
(5) = 'Contacts'
(3) = 'Actions'

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

Thanks PHV, like usual I was being an idiot.

I don't want Refresh , I want Requery, D'oh!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Out of curiosity can you explain what you are doing? The whole construct looks complicated and strange. What is the array of that holds names of controls and forms? There may be a lot cleaner way to do this.

 

[1DMF]

The array is of the OpenArgs built in variable of the open form command

The idea is, many textboxes are small with scroll bars to see all the inputted data, i wanted a kind of 'ZOOM' facility where the user can double click the field/textbox and a massive form opens with a single textbox which contains the content of the field they clicked in. I actualy use the OpenArgs to dynamically bind the unbound textbox to the data field like so...

    DoCmd.OpenForm "inputPopUp", acNormal, , , , , Me.VisitID & ";VisitID;Compliance_Audit;Actions;Compliance Audit Actions Required;Contacts;subVisitsNotes"

I then use this code on the form open event handler

    ' Split out OpenArgs
    vArgs = Split(OpenArgs, ";")
        
    ' set Title
    Me.Title = vArgs(4)
    
    'get record source
    Me.RecordSource = "SELECT [" & vArgs(3) & "] FROM [" & vArgs(2) & "] WHERE [" & vArgs(1) & "] = " & vArgs(0)
               
    'bind control
    Me.inputTextBox.ControlSource = "[" & vArgs(3) & "]"
        
    'make focus
    Me.inputTextBox.SetFocus
    
    'edit field
    SendKeys "{F2}"

I have a close form button whith

    If UBound(vArgs) = 6 Then
        Forms(vArgs(5)).Controls(vArgs(6)).Form.Requery
    Else
        Forms(vArgs(5)).Requery
    End If
    
    DoCmd.Close acForm, "inputPopUp"

In the end I decided to requery the form/subform rather than the actual control on the form.

Works great, now I can use this popup form on ANY textbox on ANY form within the whole application to zoom in and allow easy input/edit facility of textboxes which are normally bound to memo type fields in the DB but are placed rather small on the main form GUI.

Cool or stupid?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

oh, I have a form class variable declared at the top like so...

' OpenArgs are in the format :- RecID, Key, Table, Field, Title, Form, Subform
Private vArgs As Variant

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[boblarson]

Make sure you are referring to the CONTROL on the main form which HOUSES the subform. It has to be that name and not the subform name you reference. If the subform and the control share the same name then that is okay, but if they do not you MUST use the subform CONTROL name instead.

So, if your subform control is named subVisitsNotes_subform and the subform is named subVisitsNotes then you would use

subVisitsNotes_subform in your reference and NOT subVisitsNotes.

Does that make sense? Does it help?

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[1DMF]

They are both the same, but I hear what you're saying, thanks.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

this is a little slicker. See what you think
Code on the inputPopUp

Public Sub BindControl(BoundControl As Access.TextBox)
  On Error GoTo errlbl
  
  Dim zoomCtl As Access.TextBox
  Dim ctl As Object
  
  Set zoomCtl = Me.txtZoom
  Set ctl = BoundControl.Parent
  
  Do While Not TypeOf ctl Is Access.Form
    Set ctl = ctl.Parent
  Loop
  Set Me.Recordset = ctl.Recordset
  zoomCtl.ControlSource = BoundControl.ControlSource
  Exit Sub
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Sub

In a standard module put this

Public Function openInput()
  Dim ctl As Access.Control
  Set ctl = Screen.ActiveControl
  DoCmd.OpenForm "inputPopUp"
  Forms("inputPopUp").BindControl ctl
End Function

Now in any textbox in the database you can put this in the double click event.
=openInput()

So now any textbox can be a zoom textbox without any additional code.

The code here is a little strange
Do While Not TypeOf ctl Is Access.Form
'Find the parent form
Set ctl = ctl.Parent
Loop

That is because the parent property can return two types of objects either a control such as a tab control or a accessObject such as a form. Need to keep looping until finding parent form.

 

[MajP]

The above provides a lot of flexibility, because you can navigate through the records once zoomed. However, FYI, you can do all of this in one line of code.

Public Sub functionKeyZoom()
DoCmd.RunCommand acCmdZoomBox
End Sub

 

[1DMF]

You've lost me a bit MajP, what's the functionKeyZoom ?

Is it an exisitng zoom feature of access, is that what you are saying?

I don't want navigation through records with the zoom feature, there are too many sub forms with multiple records from variaous tables, it's not that easy and moving zoomed textbox field record without seing the underlying record you are now on would be a nightmare and corrupt data too easily.

where do I put this functionKeyZoom and how do I call it from a double click on a texbox?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

If interested the following demonstrates 4 methods to do this.

http://www.4shared.com/document/6EB4TBg1/MajP_InputPopUp_V1.html

In my opinion they are easier and more robust then your approach. Definitely a lot easier to call. The employee form has double click events on the text field. The first four fields demonstrate four different methods.

1)Using a bound pop up
2)Using an unbound pop up
3)Using the built in zoom
4)Using an unbound popup and capturing the pop up events.

 

[1DMF]

Cool, thoguh the built in zoom is about as big as the input box is currently so isn't really a zoom!

Is there any way to increase the size of the zoombox?

also there seems no way of titling the page different from the name of the control / textbox , is there?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

AFAIK, there is no way to modify the zoom box; hence, that is why people do what you are asking. However, on my pc that zoom box is as large as the employees form. Not sure if that is the same size you are seeing.

If you looked at the home rolled zoom boxes, I demonstrated setting properties such as captions and titles. In the bound version if you look at the second tab of the Employees form I demonstrate that you can capture many of the properties of the original such as validation rules, validation text, tip text, font color, etc. Also the bound form and unbound form are now simpler then calling even the built in zoom. It takes one line, and all the functionality is encapsulated.

The unbound method is more traditional. Just pass the text in from the current control, and pull the text out from the zoom box.