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

 

[1DMF]

Nice on MajP , looks like I got some refactoring to do!

I've got some changes to this part of the app anyway so refactoring the zoom function is no biggie.

At least I was on the right track

On a side note, due to the multi-embeded sub forms on one of the application's tab controls, I think the bound option with requery will be better, I've just had a mare with a separate popup window which did some updating of the underlying sub form data and got that dreaded 'data has changed... dop changes / copy to clipboard' problem.

As the app is a mess and MS knowledge base states you need to issue a me.parent.requery , the refresh can take a little while making the app a tad slow.

Maybe one day I'll be given the time to re-write the app properly instead of trying to bolt additional functionality onto a dead horse!


"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]

I doubt any of those options will give you that error, because no new recordsets are created.

In the bound option you are controlling the recordset of the parent form. It is as if you are still on the parent form.

In the unbound option you are returning text back to the calling control.

In all the provided options you could bury the control in nested subforms with nested tab controls and it will not matter. That was kind of my initial point.

In the bound option you are passing a direct reference to the calling control.

In the unbound option you hold a reference to the calling control and return it a value.

 

[1DMF]

I used the bound option, but had to do a little jiggery pokery with it.

If you typed something in the box then double clicked to zoom, what you typed was not brought over to the zoom textbox.

In the end I had to add

  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

to the inputZoom method.

I also had to add the F2 sendkeys command otherwise the content of the zoom control was selected which causes loads of problems with the user then typing and wiping out the content of the textbox.

I also had to have a class variable control equal to the BoundControl.Parent so I could do a requery when the form closes otherwise once again the content of the box is selected and open to being wiped if the user then pressed any key.

I now have

Public Function inputZoom(sTitle As String)

  Dim ctl As Access.Control
  
  Set ctl = Screen.ActiveControl

  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  
  DoCmd.OpenForm "inputPopUp", acNormal, , , , , sTitle
  Forms("inputPopUp").BindControl ctl
  
End Function

Private oControl As Object

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

    oControl.Requery

    DoCmd.Close acForm, Me.Name
    
Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub
Public Sub BindControl(BoundControl As Access.TextBox)
  
  On Error GoTo errlbl
  
  Dim zoomCtl As Access.TextBox
  Dim ctl As Object
  
  Set zoomCtl = Me.inputTextBox
  Set ctl = BoundControl.Parent
  
  Set oControl = ctl
  
  Do While Not TypeOf ctl Is Access.Form
    Set ctl = ctl.Parent
  Loop
  
  Set Me.Recordset = ctl.Recordset
  
  With zoomCtl
    .ControlSource = BoundControl.ControlSource
    .Locked = BoundControl.Locked
    .ValidationRule = BoundControl.ValidationRule
    .ValidationText = BoundControl.ValidationText
    .Format = BoundControl.Format
    .ControlTipText = BoundControl.ControlTipText
    .ForeColor = BoundControl.ForeColor
  End With
  
  SendKeys "{F2}"
  
  Exit Sub
  
errlbl:
  MsgBox Err.Number & " " & Err.Description
  Exit Sub
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.Title = Me.OpenArgs
End Sub

So with those small tweeks, it's working fine, so again many 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

 

[boblarson]

Don't use this (as it is old code which only works for backwards compatibility):

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Use the CORRECT verson now:

DoCmd.RunCommand acCmdSaveRecord

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[1DMF]

ok, thanks, it was what was taught to me by my trainer @ 6 years ago, guess I now need to hunt the application for all instances and update them.

"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]

Couple of things to improve the code.

I would use
if me.dirty then me.dirty = false

The reason that happens, if the form is dirty the value of the control and the underlying data is not the same as the value of the textbox. The control text has not been committed to the underlying data. So when you open the zoom form you see the underlying recordset and not the uncommitted text.


Do not use send keys that is bad programming and unreliable. Use the selectstart.

Me.txtZoom.SetFocus
Me.txtZoom.SelStart = Len(Me.txtZoom)

However, I could not replicate selecting the whole text. I always had the cursor at the end.

I also had to have a class variable control equal to the BoundControl.Parent so I could do a requery when the form closes otherwise once again the content of the box is selected and open to being wiped if the user then pressed any key

No. You missed my whole discussion on why the parent of a control is not necessarily the form. If your control is on a tab it is the page or could be another control. Hence, I show how to find the parent form.

If you want to ensure you get the form, then it would go here in the code

'loop until finding the form
Do While Not TypeOf ctl Is Access.Form
Set ctl = ctl.Parent
Loop
Set oControl = ctl


Do not use object if you mean a specific type of object. That is poor programming. Should be
private oControl As access.form
Give your controls good names. Command2 could be cmdOK or CmdClose.


I still do not understand the requery though, and I would not requery the form. I cannot replicate what you are saying, but there is probably a better fix. I purposedly did not requery to ensure this would keep me on the current record. How about requery the control not the form?

What happens if
private oControl as access.textBox
...
set oControl = boundControl

 

[1DMF]

Why do you say using a function of Access and built in commands is bad programming?

If you are not meant to use SendKeys why does it exist?

I tried your method but now I get invalid use of null error?

Also if i use the boundcontrol and requery the control not the form the entire contents is still highlighted.

So i did the same with oControl

    oControl.SetFocus
    oControl.SelStart = Len(Me.inputTextBox)

but that causes another invalid use of null error.

So i wrapped both them in a null check

    If Not IsNull(Me.inputTextBox) Then
        Me.inputTextBox.SelStart = Len(Me.inputTextBox)
    End If

Where am I meant to be putting the 'dirty' code?

on the event handler of each input box I want to use the zoom on or in the popup zoom form?



"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]

If you are not meant to use SendKeys why does it exist?"

So, if you are not meant to use METH why does it exist?

Both exist but it doesn't mean that you can't use them. However, using them means you will have to put up with some not-so-nice effects from using. Does that help?

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[1DMF]

So, if you are not meant to use METH why does it exist?

I think you'll find users disagree.


I'm just curious why something exists interms of a built in function, if it shouldn't be used.

It would be no diferent than a tradesman having a tool in their toolbox that no-one is allowed to use, seems odd to me that's all.

Though the day F2 isn't edit in Excel or DB fields is the day I'll eat my hat!

"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]

The main reason for not using SendKeys is that it is a Kludge for use only when nothing else exists to do what you need it to do and especially since it doesn't play nice now with Vista/Win7.

Sometimes Microsoft also includes things which, even though they exist, should not be used (for example - lookup fields at table level).

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[MajP]

Microsoft will leave old rusty broken tools in the toolbox, which they no longer support. Like an old power saw the thing can kick back and hurt you.

One little example, might be the fact that sendkeys will not work in windows vista.

VB has deprecated functions. Something that is no longer supported. Bob showed you the domenuitem. You can use it, but MS does not suggest you do because it may not work and they are not actively supporting it.

There are a lot of things that Access allows you to do, but still are bad programming.
you can put spaces in table and field names
you can use look up fields in a table
you can name your form, control, report, table all the same name
you can use reserved words in field names
you can not declare variables
etc.

All tools that you can use, and guarantee they will bite you in the end.

So you can be stubborn, but just trying to help you help yourself.

 

[MajP]

Here is some updated code to handle a few things such as the selection

This is the code on the form

Option Compare Database
Option Explicit
Private callingCtl As Access.TextBox

Public Sub BindControl(BoundControl As Access.TextBox)
  On Error GoTo errlbl
  
  Dim zoomCtl As Access.TextBox
  Dim ctl As Object
  Dim blnLocked As Boolean
  
  Set callingCtl = BoundControl
  Set zoomCtl = Me.txtZoom
  Set ctl = BoundControl.Parent
  'Get the caption from the textbox label or its name
  If BoundControl.Controls(0).ControlType = acLabel Then
    lblZoom.Caption = BoundControl.Controls(0).Caption
  Else
NoLabel:
    lblZoom.Caption = BoundControl.ControlSource
  End If
  'Find the parent form
  Do While Not TypeOf ctl Is Access.Form
    Set ctl = ctl.Parent
  Loop
  Set Me.Recordset = ctl.Recordset
  'Determine if the control is editable
  If Not ctl.Recordset.Updatable Or Not ctl.AllowEdits Or BoundControl.Locked Then
    blnLocked = True
  End If
  'Set some properties
  With zoomCtl
    .ControlSource = BoundControl.ControlSource
    .Locked = blnLocked
    .ValidationRule = BoundControl.ValidationRule
    .ValidationText = BoundControl.ValidationText
    .Format = BoundControl.Format
    .ControlTipText = BoundControl.ControlTipText
    .ForeColor = BoundControl.ForeColor
  End With
  Me.txtZoom.SetFocus
  'Set the selection position
  If Not IsNull(txtZoom) Then
    Me.txtZoom.SelStart = Len(Me.txtZoom)
  End If
  Exit Sub
errlbl:
  If Err.Number = 2467 Then
    GoTo NoLabel
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

Private Sub cmdOK_Click()
  DoCmd.Close acForm, Me.Name
  'Set the return selection position
  callingCtl.SelStart = Len(Nz(callingCtl, 0))
End Sub

Here is the code in the module

Public Function openBoundZoom()
  On Error GoTo errlbl
  Dim ctl As Access.Control
  Set ctl = Screen.ActiveControl
  DoCmd.OpenForm "frmBoundZoom"
  Forms("frmBoundZoom").BindControl ctl
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Function

and using the following code I changed every single textbox in the Northwind database to a double click zoom control

Public Sub addDoubleClick()
  Dim frm As Access.Form
  Dim frmName As String
  Dim accObj As Access.AccessObject
  Dim ctl As Access.Control
 
  For Each accObj In CurrentProject.AllForms
    frmName = accObj.Name
    DoCmd.OpenForm frmName, acDesign, , , , acHidden
    Set frm = Forms(frmName)
    For Each ctl In frm.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.OnDblClick = "" Then
          ctl.OnDblClick = "=OpenBoundZoom()"
        End If
        'add other things to replace
      End If
    Next ctl
    DoCmd.Close acForm, frmName, acSaveYes
  Next accObj
End Sub

If interested here is the link:

http://www.4shared.com/document/nI7bs-xE/InputPopUp_V2.html

 

[1DMF]

So you can be stubborn, but just trying to help you help yourself.

Huh, what's stubborn got to do with it, I merley asked a question.


I'm trying to do things right, which is why i'm a member of Tek-Tips

I have changed all places where "DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70" exists. I also am not using sendkeys, I also am currently studying "Designing Applications with Visual Basic" in an attempt to obtain a Diploma in Computing (or maybe a degree!) having just obtained a Distinction in my "OO Programing with Java" module, via the open university and have submitted my first TMA yesterday for the VB module, we start unit 4 this weekend.

Explain to me how that equates to your 'stubborn' comment?

In fact I get damn annoyed, that when I get stuck on how to do something and so press F1 or search the web, I find a solution, even sometimes on MS knowledge base, to then be shot down that it's bad practice or the wrong thing to do!

grr, I didn't search the web with my Google query as 'please show me the wrong way to do XYZ'.

I also wouldn't have scrapped my popup zoom code in favour of yours (even with it being a bit buggy) if I wasn't open to suggestions!

I merley asked why!

Only an idiot and a non-professional leaves broken, rusty tools in their toolbox...hmm, hope no-one looks in my shed ;-)

....Anyhow....

I still can't see where the 'dirty' is being taken care of, am I missing something? Also the link to the 4shared.com doesn't seem to work?



"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]

You can try this link.

http://www.4shared.com/document/zyqsJHRs/MajP_InputPopUp_V3.html

I took the dirty=false code out because I could not replicate the proplem. But the code if needed is here.

Public Function openBoundZoom()
On Error GoTo errlbl
Dim ctl As Access.Control
Set ctl = Screen.ActiveControl

'I do not believe the following is
'necessary but it form some reason
'the popup and the control text are not the same
'then uncomment the next line
'Screen.ActiveForm.Dirty = False

DoCmd.OpenForm "frmBoundZoom"
Forms("frmBoundZoom").BindControl ctl
Exit Function
errlbl:
MsgBox Err.Number & " " & Err.Description
End Function


I guess I misinterpreted the persistent questions, as stubborness instead of curiosity.

When my son was little he was real curious. He would try to stick his finger in the wall socket. You would pull his hand back, and he would smile and try it again. You would slap him on the hand, and he would smile and try again. You slap his hand again, and he would try again. Eventually, his hand would be red, he would be holding the tears back, but he would squeeze out a smile and try it again. My bad, I thought we were in one of those situations.

I probably should not have used the term "bad programming". I should have said "unreliable, unsafe, and not recommended.

 

[1DMF]

I guess I misinterpreted the persistent questions, as stubborness instead of curiosity.

It got me in trouble at school and continues to do so, but I can't change my Dyspraxic nature or frustrated curiosity, it's just the way I am

I'm still having a problem , getting this to fully work.

I have a form where I want to use the zoom, but the form has no editable fields on it, I've tried both the save record method or the dirty method I either get

'Save record isn't available now'

or

'you entered an expression that has an invalid reference to the property Dirty'

What boolean expression can I use to check if either is possible before trying to use them?




"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]

I cannot replicate this, or the need for it. You may try.

if Screen.ActiveForm.Dirty then screen.activeForm.dirty = False

If that does not do it then maybe
If Screen.ActiveForm.Recordset.Updatable and Screen.ActiveForm.Dirty Then screen.activeform.dirty = false

 

[1DMF]

The first one cuases the same error the second gives 'object variable with block variable not set'?

the bound control although locked is on subform which is a continuos form

"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]

well I got it to work with

    If ctl.Parent.Recordset.Updatable Then
        DoCmd.RunCommand acCmdSaveRecord
    End If

"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