Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need vba coding to delete all lines shown in a form coding module.

Posted on 2011-09-06
6
Medium Priority
?
449 Views
Last Modified: 2016-08-29
Please note the following VBA code where I attempt to delete any of sfSubForm's coding modules...  For some reason when I do a Call FillSubForm("Table1") twice it fails to delete the coding the second time around and get the following error per image:

Also please note the uploaded sample provided here:
https://filedb.experts-exchange.com/incoming/ee-stuff/8144-DBSample.zip

And also this is a continuation of question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27288674.html
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

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
          
          If x = 1 Then
          
            ' Forms(frm.Name)(ctrl.name).ongotfocus="[event Procedure]"
            Forms(frm.Name)(ctrl.Name).OnGotFocus = "[Event Procedure]"
          End If
          
          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

Open in new window

amb-error.png
0
Comment
Question by:stephenlecomptejr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36490734
the forms property Allow Design Changes is set to "Design View Only"

try changing this line

DoCmd.OpenForm frmname

with

DoCmd.OpenForm frmname,acDesign
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 36491181
I still get the same error.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36493060
try this codes

Sub DeletefromForms(frmname)

    Dim mod1 As Module, strType As String
    Dim varLine As Integer
    '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, acDesign
    If Forms(frmname).HasModule = True Then
        Set mod1 = Forms(frmname).Module
        varLine = mod1.CountOfLines
        If varLine > 0 Then
            mod1.DeleteLines 1, varLine
          End If
        Set mod1 = Nothing
    End If
   
End Sub

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 36493416
It works good.

Thank you - especially since you must've had to do this late tonight.
I appreciate it!
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 36493418
Capricorn1, is the best.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36494897
;-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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