correct syntax to dynamically reference current active control 1

[1DMF]

Hi,

What's the correct syntax to do the following

If Nz(Forms!contacts!subVisits.Form.[Forms!contacts!subVisits.Form.ActiveControl.Name], "") <> "" Then  
  
      Do what ever

End if

I get an error saying can't find field '|' in the expression?

Thanks,
1DMF.

"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

 

[TheAceMan1]

How are ya 1DMF . . .

Have a look at the [blue]Screen[/blue]:

[blue]   If Nz(Screen.ActiveControl,"") <> "" Then
      [green]'Do What Ever![/green]
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[1DMF]

Hi ace, i'm fine and you?

thanks for the reply , but it doesn't help.

I need to know

Forms!contacts!subVisits.Form.[Forms!contacts!subVisits.Form.ActiveControl.Name]

for use later anyhow.

It's the syntax I require I have a whole load of stuff i want to do, once I get the syntax right.

"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 not get that syntax correct because it makes no sense. Can you say what you are trying to do?

 

[MajP]

Maybe you just want to continue with Ace Man's
dim ctl as access.control
If Nz(Screen.ActiveControl,"") <> "" Then
set ctl = Screen.activecontrol
End If

Just so you know there is only one active control throughout the entire project. Each form/subform does not have its own active control

 

[TheAceMan1]

1DMF . . .

Perhaps if you tell us just what your trying to do ... we could help you better! As it is, just arbitrarily checking the value of the [blue]ActiveControl[/blue] can be ambiguous for your needs ... espcially since the [blue]ActiveControl[/blue] can be anywhere ... form or subform. You may want to consider checking that [blue]Screen.ActiveControl.Name[/blue] is actually the name of an intended control.

Also consider ... if you trigger your code by a button, you switch the [blue]ActiveControl[/blue] to the button and need to set the focus back to the previous control or [blue]Screen.PreviousControl[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[1DMF]

my logic is sound, just stupidly complicated, when there was no need.

I have two fields on a subform, I am creating a modal popup multiple select function when either are double clicked, I need the content of the field that is doubleclicked (which is a comma separated string), split out and then use it to compile a query string to populate an unbound listbox.

Two list boxes on the popup modal form will be used to select or remove items and then when the form closes the listboxes are processed and the comma separated string regenerated and placed in the filed originally double clicked.

Using the Law of Parsimony, I concluded that the simplest assumption was to say your suggestion wouldn't work!

I'd checked your original post at home and couldn't test it till this morning, so used Occums Razzor to assess its posibility for use ;-)

upon retrospective analysis, testing and making many more assumptions than allowed, I have found out that actually I was correct in my conclusion as when I use this code...

MsgBox Screen.ActiveControl.Name

I get this error...

The expression you entered requires the control to be in the active window

I assumed that 'screen' was the same as saying 'me' (ie. current active form), which does seem to be the case, or perhaps because I need the popup form to be modal, that causes the conflict, either way it does look like I cannot use 'screen' for what i'm trying to achieve.

However, it made me railise that all I needed was

Forms!contacts!subVisits.Form.ActiveControl

&

Forms!contacts!subVisits.Form.ActiveControl.Name

I assume

Forms!contacts!subVisits.Form.ActiveControl

gets the value of the field double clicked, rather than trying to get the name via

Forms!contacts!subVisits.Form.ActiveControl.Name

and then putting that into a normal field reference which is what this was trying to do

Forms!contacts!subVisits.Form.[Forms!contacts!subVisits.Form.ActiveControl.Name]

Using the simpler version I can now test which field was double clicked as the SQL query string has to exclude or include certain records from the classifications table as well as get the current value to parse and pre-populate the other listbox for the multiple select modal popup function.

Hope that makes things clearer






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

From your extensive rhetoric it is clear that you tend toward the complicated, because I have no idea what you just said. But this may simplify what you are doing.

If you call a pop up form using the acdialog parameter when the form pops up all code stops in the form that just called it. However when the pop up closes or is hidden then the code resumes in the calling form.

So a trick is to have an OK and cancel button on the pop up. The cancel simplies closes the popup the OK makes it invisible.

If it is closed or invisible the code that calls it resumes. That calling code then uses the isloaded method to determine if it is loaded but hidden. If hidden it then reads the values off of the hidden form. The calling form can then grab values off of the hidden pop up. When done it closes the pop up.

 

[traingamer]

I have two fields on a subform, I am creating a modal popup multiple select function when either are double clicked, I need the content of the field that is doubleclicked (which is a comma separated string), split out and then use it to compile a query string to populate an unbound listbox.

Since there is a doubleclick event in one and only one of the fields at a time, get the value from that field and pass it to your query.

You don't need the active control name or any of this extra complexity.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MajP]

Not sure the need for the two listboxes. If I was doing this I think I would pop open a single multiselect listbox with the appropriate records selected. On close either run an update query based on the listbox, or return control to the calling form as described and have it populate the textbox based on the values in the hidden pop up form.

Private Sub Form_Load()
  unSelectAll
  SelectArgs
End Sub

Public Sub unSelectAll()
   Dim i As Integer
   For i = 0 To (Me.lstCodes.ListCount - 1)
      Me.lstCodes.Selected(i) = False
   Next i
 End Sub

Public Sub SelectArgs()
  Dim aCodes() As String
  Dim vCode As Variant
  Dim i As Integer
  If Not Nz(Me.OpenArgs, "") = "" Then
    aCodes = Split(Me.OpenArgs, ",")
    For Each vCode In aCodes
      For i = 0 To (Me.lstCodes.ListCount - 1)
         If Me.lstCodes.Column(1, i) = vCode Then
           Me.lstCodes.Selected(i) = True
         End If
      Next i
    Next vCode
  End If
End Sub

So pass to open args the string
abc, def, ghi

And it selects these values in the list.

 

[1DMF]

get the value from that field and pass it to your query

I don't understand this in context of what i'm doing, but I do now have a working DB feature, here is the final code on the popup form (all methods)

Note: it is based on an idea I saw on the web using tables as the record source for the listboxes

Private Sub Form_Open(Cancel As Integer)

    Dim rs As Recordset
    Dim sSQL, sSQL2 As String
    Dim vClass As Variant
        
    CurrentDb.Execute "DELETE FROM [curclass] WHERE 1=1"
    CurrentDb.Execute "DELETE FROM [avaclass] WHERE 1=1"
              
    If Nz(Forms!contacts!subVisits.Form.ActiveControl, "") <> "" Then
    
        vClass = Split(Forms!contacts!subVisits.Form.ActiveControl, ",", , vbTextCompare)
        sSQL = ""
        sSQL2 = ""
        For i = 0 To UBound(vClass)
            If sSQL <> "" Then
                sSQL = sSQL & " OR "
                sSQL2 = sSQL2 & " AND "
            End If
            sSQL = sSQL & "[Class] = '" & vClass(i) & "'"
            sSQL2 = sSQL2 & "[Class] <> '" & vClass(i) & "'"
        Next i
        
        sSQL2 = sSQL2 & " AND "
          
        Set rs = CurrentDb.OpenRecordset("SELECT [Class],[Description] FROM [Comp_Classes] WHERE " & sSQL)
    
        Do While Not rs.EOF
            CurrentDb.Execute "INSERT INTO [curclass] ([Class],[Description]) VALUES ('" & rs.Fields("Class") & "','" & rs.Fields("Description") & "')"
            rs.MoveNext
        Loop
    
    End If
      
    If (Forms!contacts!subVisits.Form.ActiveControl.Name) = "Case_Class" Then
        sSQL2 = sSQL2 & "[Class] NOT LIKE 'D*' "
    Else
        sSQL2 = sSQL2 & "[Class] LIKE 'D*' "
    End If
          
    Set rs = CurrentDb.OpenRecordset("SELECT [Class],[Description] FROM [Comp_Classes] WHERE " & sSQL2)
           
    Do While Not rs.EOF
        CurrentDb.Execute "INSERT INTO [avaclass] ([Class],[Description]) VALUES ('" & rs.Fields("Class") & "','" & rs.Fields("Description") & "')"
        rs.MoveNext
    Loop
        
    Set rs = Nothing
    Me.Current_Classes.Requery
    Me.Avail_Classes.Requery
    
End Sub
====================================================
Private Sub Avail_Classes_DblClick(Cancel As Integer)

    Dim sDesc As String
      
    sDesc = Nz(DLookup("Description", "avaclass", "Class='" & Me.Avail_Classes.Value & "'"), "")
         
    If sDesc <> "" Then
        CurrentDb.Execute "INSERT INTO [curclass] ([Class],[Description]) VALUES ('" & Me.Avail_Classes.Value & "','" & sDesc & "')"
        CurrentDb.Execute "DELETE FROM [avaclass] WHERE [Class] = '" & Me.Avail_Classes.Value & "'"
    
        Me.Current_Classes.Requery
        Me.Avail_Classes.Requery
    End If
    
End Sub
===========================================================
Private Sub Current_Classes_DblClick(Cancel As Integer)

    Dim sDesc As String
      
    sDesc = Nz(DLookup("Description", "curclass", "Class='" & Me.Current_Classes.Value & "'"), "")
         
    If sDesc <> "" Then
        CurrentDb.Execute "INSERT INTO [avaclass] ([Class],[Description]) VALUES ('" & Me.Current_Classes.Value & "','" & sDesc & "')"
        CurrentDb.Execute "DELETE FROM [curclass] WHERE [Class] = '" & Me.Current_Classes.Value & "'"
    
        Me.Current_Classes.Requery
        Me.Avail_Classes.Requery
    End If
    
End Sub
=================================================
Private Sub Close_Click()

    Dim myVal As String

    myVal = ""

    For X = 0 To Me.Current_Classes.ListCount - 1
        If myVal <> "" Then
            myVal = myVal & ","
        End If
        myVal = myVal & Me.Current_Classes.Column(0, X)
    Next X
    
    Forms!contacts!subVisits.Form.ActiveControl = myVal
    
    Forms!contacts!subVisits.Form.Refresh

    DoCmd.Close acForm, "Sel_Classes"
        
End Sub

So the form opens , evaluates the CSV string from the datasheet subform field being double clicked and generates the relevant records for the two temp tables. (selected & available)

The popup modal form has two list boxes bound to the two temp tables, I move records from one table to the other as options are double clicked

when the exit button is pressed I parse the required listbox generating the CSV string and put it in the desired field.

Is this the wrong way to do this?

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

Maj, in one way I agree, but because most are used to using CTRL for multi-select not shift which Access requires, plus you have to ensure you can see all options in the select box otherwise it gets awkward to work out what has been selected.

Using two list boxes, one with available classes and one with selected classes, with a double click function to add or remove them is much more user friendly, and considering some of the DB users have trouble opening word, copy pasting into emails or even turning their computer on.... the more user friendly the better!

I like the new 2007 option as you get a list with checkboxes, but when i looked into the multi-select function on a standard 2003 listbox I decided two listboxes was a better way to go.

But of cause feel free to disagree ;-)

"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 am not a real big fan of multi select boxes either. The two "to from" list boxes is probably a nicer interface especially if you have a very big list where it is difficult to see all the selection at once. I also use a listview control because this allows you to have a listbox with built in check boxes.