Results 1 to 11 of 11
  1. #1
    rku01 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    5

    Trouble with a form after deleting a field in a table.


    Hello,

    Can please anybody help me.
    I've deleted a field in a table, and when I try to open a sub-laying form, I get an alert that this field is now missing. That's normal of course !
    But I don't know how to proceed to find in that form the field who calls to the deleted field and causing therefor that alert ?

    Any suggestion ?
    Thanks

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Open the form in design view and look at the property sheet for it and see what the form is bound to (its recordsource property). If there is a source then the form is bound so you will have to find the control that is bound to that field and then delete that also (or at least remove the recordsource property for that control). If there are calculated controls that refer to the missing field, you will have to deal with those as well.

    I have to wonder why you deleted the table field in the first place as this could have all sorts of cascading effects that you are not aware of.
    Last edited by Micron; 09-21-2020 at 07:27 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you shouldnt delete fields because they cause a ripple effect thru : forms, queries, & code.
    just ignore them but do remove the box from the form.

    otherwise you must debug all forms & queries & code to remove them. Thats just more work.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a database backup from before the field was deleted from the table?
    Why was the field deleted?
    Is this new/developing database or i it operational/production?

  5. #5
    rku01 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    5
    Thanks, but my question was precisely "how to identify / find in the form the control that create the trouble" !

  6. #6
    rku01 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    5
    It was an old test field I for what I had no more need !

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Some ideas for tracking it down: In form design mode,
    look for a small green checkmark at the upper left corner of a control. It indicates a problem with the control's controlsource.
    If the filter property has a value referring to the field, blank it out.
    Use the dropdown on the property sheet, looking for the name of the control that might match the field name.
    If the form has a code module, search for the field name in the VBA code.

    Also
    If the recordsource for the form is a query, look for the missing field still being invoked in the query.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Well perhaps you did have more need. Can you post a copy of the database so readers may help identify the issue?

    I have used these routines to identify
    -recordsources of all forms
    -controls on all forms.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : PutFormRecordSourcesInTable
    ' Author    : Jack
    ' Date      : 02/03/2014
    ' Purpose   : Review all forms in this database; find recordsource for each.
    ' If no recordsource bypass the form.
    ' if recordsource is a Table/Query identify the table/query and fields.
    ' if recordsource is SQL, identify the SQL string.
    '---------------------------------------------------------------------------------------
    '
    Sub PutFormRecordSourcesInTable()
              Dim afrm As AccessObject
              Dim frm As Access.Form
              Dim db As DAO.Database
              Dim rs As DAO.Recordset
              Dim RecSourceType As String
              Dim strSQL_Drop As String
              Dim strSQL_Create As String
    
    10        On Error Resume Next
    20        Set db = CurrentDb
              'Delete existing copy of this table
    30        strSQL_Drop = "DROP TABLE tblRecordSourceOfForms;"
    
    40        DoCmd.RunSQL strSQL_Drop
    50        On Error GoTo PutFormRecordSourcesInTable_Error
    60        strSQL_Create = "CREATE TABLE tblRecordSourceOfForms" & _
                              " (form_name varchar(250), RecordSourceType varchar(20),RecordSourceText longtext  );"
              
    70        db.Execute strSQL_Create, dbFailOnError
    
    80        DoEvents
    90        Set rs = db.OpenRecordset("tblRecordSourceOfForms")
    
    100       For Each afrm In CurrentProject.AllForms
    
    110           If Not afrm.IsLoaded Then DoCmd.OpenForm afrm.name, acDesign, , , , acHidden
    
    120           If Len(Forms(afrm.name).RecordSource & "") = 0 Then
    130               Debug.Print afrm.name & "  -- " & "**NO ASSIGNED RECORDSOURCE**"
    140               RecSourceType = "NONE"
    150           ElseIf InStr(Trim(Forms(afrm.name).RecordSource), "SELECT ") > 0 Then
    160               Debug.Print afrm.name & "  -- " & "  -    SQL      - " & Forms(afrm.name).RecordSource
    170               RecSourceType = "SQL"
    180           Else
    190               Debug.Print afrm.name & "  -- " & "  - Table/Query - " & Forms(afrm.name).RecordSource
    200               RecSourceType = "Table/Query"
    210           End If
    220           rs.AddNew
    230           rs!form_name = afrm.name
    240           rs!RecordSourceType = RecSourceType
    250           rs!RecordSourceText = Trim(Forms(afrm.name).RecordSource)
    260           rs.Update
    GetNext:
    270           DoCmd.Close acForm, afrm.name
    280       Next afrm
    290       rs.Close
    300       On Error GoTo 0
    310       Exit Sub
    
    PutFormRecordSourcesInTable_Error:
    
    320       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PutFormRecordSourcesInTable of Module AWF_Related"
    
    End Sub
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : GetAllFormsAndControls
    ' Author    : Jack
    ' Date      : 12/01/2013
    ' Purpose   : To iterate all forms and report all controls by form, control name and control type.
    '
    ' posted question:
    'http://www.accessforums.net/showthread.php?t=31409&p=154208#post154208
    '---------------------------------------------------------------------------------------
    '
    Sub GetAllFormsAndControls()
    10    On Error GoTo GetAllFormsAndControls_Error
    
    20    On Error Resume Next
              Dim objAccObj As AccessObject
              Dim objForm As Object
              Dim strForm As String
              Dim ctl As Control
              Dim objActiveForm As Form
              Dim iCtlCount As Integer
              '
              Dim db As DAO.Database
              Dim rs As DAO.Recordset
              Dim strSQL_Drop As String
              Dim strSQL_Create As String
              'Delete existing copy of this table
    30    strSQL_Drop = "DROP TABLE tblControlsOnForms;"
    
    40    DoCmd.RunSQL strSQL_Drop
    
    50    strSQL_Create = "CREATE TABLE tblControlsOnForms" & _
                        "(form_name varchar(250), control_name varchar(40),control_type varchar(25));"
    
    60    DoCmd.RunSQL strSQL_Create
    70    Set db = CurrentDb
    80    Set rs = db.OpenRecordset("tblControlsOnForms")
    90    With rs
    100     Set objForm = Application.CurrentProject
    110     For Each objAccObj In objForm.AllForms
    
    120         iCtlCount = 0
    130         strForm = objAccObj.name
                ' If strForm <> "frmEATBloatV4" Then GoTo XXX 'for debugging
    140         Debug.Print strForm
    
    
    150         DoCmd.OpenForm strForm, acDesign
    160         Set objActiveForm = Application.Screen.ActiveForm
    170         For Each ctl In objActiveForm.Controls
    180             iCtlCount = iCtlCount + 1
    190             .AddNew
    200             !form_name = strForm
    210             Select Case ctl.ControlType
                    Case 119    ' acWebBrowser, Treeview, Calendar
    220                 !control_type = "Custom control"
    230             Case acTabCtl
    240                 !control_type = "TabCtl"
    250             Case acLabel
    260                 !control_type = "Label"
    270             Case acTextBox
    280                 !control_type = "TextBox"
    290             Case acComboBox
    300                 !control_type = "ComboBox"
    310             Case acCheckBox
    320                 !control_type = "CheckBox"
    330             Case acListBox
    340                 !control_type = "ListBox"
    350             Case acOptionButton
    360                 !control_type = "OptionButton"
    370             Case acToggleButton
    380                 !control_type = "ToggleButton"
    390             Case acSubform
    400                 !control_type = "SubForm"
    410             Case acCommandButton
    420                 !control_type = "CommandButton"
    430             Case acObjectFrame
    440                 !control_type = "ObjectFrame"
    450             Case acBoundObjectFrame
    460                 !control_type = "BoundObjectFrame"
    470             Case acRectangle
    480                 !control_type = "Rectangle"
    490             Case acLine
    500                 !control_type = "Line"
    510             Case acImage
    520                 !control_type = "Image"
    530             Case acPage
    540                 !control_type = "Page"
    550             Case acPageBreak
    560                 !control_type = "PageBreak"
    570             Case acOptionGroup    'some sort of frame???
    580                 !control_type = "Option Group"
    590             End Select
    600             Debug.Print " " & ctl.name & "  " & ctl.ControlType
    610             !control_Name = ctl.name
    620             !control_type = !control_type & "  " & ctl.ControlType  '  used to associate the control with the acControl constant
    630             .Update
    
    640         Next ctl
    650         Debug.Print strForm; "  ---Controlcount--: " & iCtlCount
    660         DoCmd.Close acForm, strForm
    XXX:
    670     Next objAccObj
    
    680     On Error GoTo 0
    690     Exit Sub
    700   End With
    GetAllFormsAndControls_Error:
    
    710   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure GetAllFormsAndControls of Module AWF_Related"
    End Sub
    These may help. Good luck.

    Always work on a copy of your database.

  9. #9
    rku01 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    5
    Thnaks Orange for helping, but I'm afraid your solution is definitively too complicated for me !
    I just was looking for a simple way debugging my form.

    Perhaps another time.
    So long.

  10. #10
    rku01 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    5
    I already tried your proposals before coming on this forum.
    But in any case, thanks.

  11. #11
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    24
    This should locate the missing field if it is bound directly to a control...
    ```
    Dim frm As Form, ctl As Control
    On Error Resume Next
    DoCmd.OpenForm "MyForm",acDesign
    Set frm = Forms("MyForm")
    For Each ctl In frm.Controls
    If ctl.ControlSource = "name_of_missing_field" Then
    Debug.Print ctl.Name, ctl.ControlSource
    End If
    Next ctl
    DoCmd.Close acForm, frm.Name, acSaveNo
    ```

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2019, 02:35 PM
  2. Replies: 5
    Last Post: 05-15-2018, 10:11 AM
  3. Replies: 3
    Last Post: 09-12-2015, 10:16 AM
  4. Replies: 1
    Last Post: 03-28-2014, 10:43 AM
  5. Replies: 11
    Last Post: 12-14-2010, 01:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums