Solved

After dynamically creating form based on query/table - the _GotFocus VBA coding won't fire?

Posted on 2011-09-02
8
397 Views
Last Modified: 2016-08-29
Per the following link:  http://msdn.microsoft.com/en-us/library/aa139986(office.10).aspx under sections:  Programmatically Editing Modules - I'm trying to create a dynamic form based on a dynamic query and fill in that form a code module for the GotFocus event.  

The problem I'm having is that all the code in the GotFocus event won't run after the form opens.  

Please review the sample and tell me what syntax or event I need to add/change.

Here's the code sample I have in the database which is a continuation of question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27285631.html

Also here's a direct link to the sample:
https://filedb.experts-exchange.com/incoming/ee-stuff/8142-DBSample.zip

Thanks to all who review the sample!
Option Compare Database
Option Explicit

Public Sub FillSubForm(strSource As String, Optional fDelFind As Boolean)

    Dim frm As Form, ctrl As Control, fld As Field, CtrlType As Long
    Dim i As Long, r As Recordset, db As Database
    Dim strFldLst As String
    Dim dbFld As DAO.Database
    Dim rsFld As DAO.Recordset
    Dim x As Integer
    
    Dim sFldName As String
    
    Call DeletefromForms("sfSubForm")
    
    
    Set dbFld = CurrentDb()
    Set rsFld = dbFld.OpenRecordset("tblFieldFind")
    If Not IsNull(strSource) Then
       ' Put in a filler form while we rework the other one
       
       ' Open the subform for design
       DoCmd.OpenForm "sfSubForm", acDesign, , , , acHidden

       Set frm = Forms("sfSubform")
       frm.RecordSource = strSource
       
       ' Delete all the old controls from the previous recordsource
       For i = frm.Controls.Count - 1 To 0 Step -1
          Application.DeleteControl frm.Name, frm.Controls(i).Name
       Next i
       

    x = 1
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qdelFieldFind"
    If fDelFind Then
        DoCmd.OpenQuery "qdelAdvFind"
    End If
    DoCmd.SetWarnings True
    With rsFld
       ' Use this to find out what our source fields are
       Set r = CurrentDb.OpenRecordset(strSource, DB_OPEN_DYNASET)
        strFldLst = ""
       ' Build the controls.  Yes/No fields turn into checkboxes, all else into textboxes
       For Each fld In r.Fields
          If fld.Type = dbBoolean Then
             CtrlType = acCheckBox
          Else: CtrlType = acTextBox
          End If
          Set ctrl = Application.CreateControl(frm.Name, CtrlType, acDetail, , fld.Name)
          ctrl.Name = fld.Name
          strFldLst = strFldLst & fld.Name & ";"
          .AddNew
          !fieldID = x
          !FieldName = fld.Name
          !FieldDesc = fld.Name
          !FieldTypeID = fld.Type
          .Update
          x = x + 1
       Next fld
    End With
       ' Close the subform and put it back in our main form
       DoCmd.Close acForm, "sfSubform", acSaveYes
       DoCmd.OpenForm "sfSubform", acFormDS
       ' Make all the fields size to best fit
       For i = 0 To r.Fields.Count - 1
           
          
           
          If Forms!sfSubForm.Form.Controls(i).Name <> "Name" And _
            Forms!sfSubForm.Form.Controls(i).Name <> "Cutsheets" Then _
              Forms!sfSubForm.Form.Controls(i).ColumnWidth = -2
              
          If i = 0 Then
          
            sFldName = Forms!sfSubForm.Form.Controls(i).Name
            
          End If
       Next i
    End If
   
    Create_VBA_Code (sFldName)
   
    Set rsFld = Nothing
    dbFld.Close
    Set dbFld = Nothing

End Sub

Private Sub Create_VBA_Code(sFieldName As String)

  DoCmd.Close acForm, "sfSubForm", acSaveYes
  DoCmd.OpenForm "sfSubForm"
  Call InsertIntoForms("sfSubForm", sFieldName)
  DoCmd.Close acForm, "sfSubForm", acSaveYes
  DoCmd.OpenForm "sfSubForm", acFormDS
  
  
End Sub


Private Sub InsertIntoForms(frmname, sField As String)

    Dim mod1 As Module, strType As String

    'If Form has module, set reference to it
    'and insert line into the module.
    'Free reference resource when done.
    If Forms(frmname).HasModule = True Then
        Set mod1 = Forms(frmname).Module
        
        strType = "End Sub"
        mod1.InsertLines 1, strType
        
        strType = vbTab & "DoEvents"
        mod1.InsertLines 1, strType
        
        strType = vbTab & "DoCmd.RunCommand acCmdCopy"
        mod1.InsertLines 1, strType
        
        strType = vbTab & sField & ".SelLength = Len([" & sField & "])"
        mod1.InsertLines 1, strType
        
        strType = vbTab & sField & ".SelStart = 0"
        mod1.InsertLines 1, strType
        
        strType = vbTab & sField & ".SetFocus"
        mod1.InsertLines 1, strType
        
        strType = "Private Sub " & sField & "_GotFocus()"
        mod1.InsertLines 1, strType
        
        strType = ""
        mod1.InsertLines 1, strType
        
        strType = "Option Explicit"
        mod1.InsertLines 1, strType

        strType = "Option Compare Database"
        mod1.InsertLines 1, strType
        
        Set mod1 = Nothing
    End If
    
    
End Sub

Private Sub InsertIntoForms2(frmname, sField As String)

    Dim mod1 As Module, strType As String

    'If Form has module, set reference to it
    'and insert line into the module.
    'Free reference resource when done.
    If Forms(frmname).HasModule = True Then
        Set mod1 = Forms(frmname).Module
        'strType = "Option Compare" & vbEnter & "Option Explicit" & Chr(34) & "Private Sub " & sField & "_GotFocus()" & Chr(34) & "  MsgBox(" & sField & ".Value)" & "End Sub"
        Debug.Print strType
        mod1.InsertLines 1, strType
        Set mod1 = Nothing
    End If
    
    
End Sub


Sub DeletefromForms(frmname)

    Dim mod1 As Module, strType As String

    'If form has module, then check contents of first line
    'for "Module", and delete the first line if it is present.
    'Free module reference resource when done.
    DoCmd.OpenForm frmname
    If Forms(frmname).HasModule = True Then
        Set mod1 = Forms(frmname).Module
        If mod1.Find("Module", 1, 1, 1, 40) = True Then
            mod1.DeleteLines 1, 1
        End If
        Set mod1 = Nothing
    End If
    
End Sub

Open in new window

0
Comment
Question by:stephenlecomptejr
  • 4
  • 4
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36473118
i did not see you add an event for the control, in the property sheet

something lke this
 
                Forms(sForm)(ctl.Name).ongotfocus = "[Event Procedure]"
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 36473356
It makes sense of what you are telling me, capricorn1 but I'm not sure what syntax do I use to add it?  and where in the above subs?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36473417
try doing it after you created the control

      Set ctrl = Application.CreateControl(frm.Name, CtrlType, acDetail, , fld.Name)
           
            ctrl.Name = fld.Name
            if ctrl.name="whatever"
                      ctrl.Name.ongotfocus = "[Event Procedure]"
            end if
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 36488592
capricron1,

This is the error I'm getting when using the code.
Please note all I'm trying to do is make the event procedure for the first field that comes up.

Thus if x = 1 Then....

errror-applying.png
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36488632
try including the reference to the form
 
     Forms(frm.Name)(ctrl.name).ongotfocus="[event Procedure]"

or

      Forms(frm.Name)(ctrl).ongotfocus="[event Procedure]"
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 36489027
Thank you always for your replies.

I'm sorry I forgot about the post - was thinking about the extra day off to get away from computers for awhile!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36489073
np.. btw, where are you going to use this dynamic form creation ?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 36489757
Where?  In a normal access database opened by users that resides as a front-end on their machines.  
I hope I answered your question as regards that...not sure why the question.

The coding above does work for the first got focus item but as you move down - the process does not throw the other items in the clipboard.  So I'm going to post another question as regards that problem.

Thanks.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamically Reorder List Box 4 38
Top 1 of each supplier 55 56
Filter a form 8 15
Linking to an Excel file and determining the field types 3 16
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question