stephenlecomptejr
asked on
After dynamically creating form based on query/table - the _GotFocus VBA coding won't fire?
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:
https://www.experts-exchange.com/questions/27285631/Need-access-vba-to-dynamically-create-a-datasheet-form-based-on-an-everchanging-query.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!
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:
https://www.experts-exchange.com/questions/27285631/Need-access-vba-to-dynamically-create-a-datasheet-form-based-on-an-everchanging-query.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
ASKER
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?
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
Set ctrl = Application.CreateControl(
ctrl.Name = fld.Name
if ctrl.name="whatever"
ctrl.Name.ongotfocus = "[Event Procedure]"
end if
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
I'm sorry I forgot about the post - was thinking about the extra day off to get away from computers for awhile!
np.. btw, where are you going to use this dynamic form creation ?
ASKER
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.
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.
something lke this
Forms(sForm)(ctl.Name).ong