Forum Discussion

Debbie3103's avatar
Debbie3103
Copper Contributor
Sep 18, 2023

Data entry form help!

Hi

I have a spreadsheet with 2 sheets.....Sheet A (data) and Sheet B (input). I have created a table in A and managed to create a data entry form.  In sheet B I created a button and attached a macro which opens A and the data entry form.  

The first problem I have is that whenever I try to enter data I get "Cannot extend table or database".

Now this is my wishlist...please note that I am not fluent in VBA etc.

1. Can I create a data entry form that is always displayed in B to enable creation of new data and searching?

2. How do I get rid of the error I am currently getting?

I was hoping to attach a sample but can't see where to do that.

Many thanks

Debbie

 

 

 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Debbie3103 

    It seems like you are trying to create a data entry form in Excel 365 that allows you to input data into a table and perform searches. To address your questions:

    1. Create a Data Entry Form in Sheet B: To create a data entry form in Sheet B, you can use Excel UserForms, which allow you to design custom data input forms. Here is a simplified guide to creating a basic data entry form:

    Step 1: Create a UserForm

    1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
    2. In the VBA editor, go to "Insert" > "UserForm" to create a new UserForm.
    3. Add controls (e.g., text boxes, labels, buttons) to the UserForm to design your data entry form.

    Step 2: Code the UserForm

    Next, you will need to write VBA code to handle interactions with the UserForm. For example, you will write code to add data to your table when the user clicks a "Submit" button.

    Here is a simple example of VBA code to add data to a table:

    vba code:

     

    Private Sub SubmitButton_Click()
        Dim ws As Worksheet
        Dim tbl As ListObject
        Dim newRow As ListRow
        
        ' Set the worksheet and table
        Set ws = ThisWorkbook.Sheets("SheetA")
        Set tbl = ws.ListObjects("TableName") ' Replace "TableName" with your table name
        
        ' Add a new row to the table
        Set newRow = tbl.ListRows.Add(AlwaysInsert:=True)
        
        ' Fill in data from UserForm controls
        newRow.Range(1, 1).Value = TextBox1.Value ' Replace with your control names and values
        
        ' Close the UserForm
        Unload Me
    End Sub

     

    Step 3: Show the UserForm

    You can add a button to Sheet B and attach code to display the UserForm when clicked. Here's a simplified example:

    vba code:

     

    Private Sub ShowFormButton_Click()
        UserForm1.Show ' Replace "UserForm1" with your UserForm's name
    End Sub

     

    1. Resolve the "Cannot Extend Table or Database" Error:

    The "Cannot extend table or database" error typically occurs when you are trying to add data to a table, and Excel is unable to determine the table's size correctly. You can try the following steps to resolve this issue:

    • Ensure that your table has headers.
    • Check if there are any merged cells, blank rows, or columns within your table. These can interfere with the table's structure.
    • Verify that there are no empty rows below the table in Sheet A.
    • If you are using a table formula for column calculations, ensure that it is correctly configured.
    • If the issue persists, try recreating the table in Sheet A to ensure its properly defined.

    By creating a UserForm for data entry in Sheet B and handling data submission through VBA code, you can achieve your goal of entering and managing data more effectively. The text, steps and codes were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

    • Debbie3103's avatar
      Debbie3103
      Copper Contributor

      NikolinoDE 

      Thanks so much....I am amazed that I have got this far so far....still a way to go.

      I have created the user form: 

      I tried applying the code to 'submit' and 'new entry' as you can see i have a problem with the latter.

        

      I appreciate your patience (this is all new to me) but what have i done wrong to get the error and how do i test the user form.

      I am not sure how to open the excel spreadsheet with the user form displayed. 

       

      Code for the 'search' would be great :smile: 

       



      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        Debbie3103 

        VBA Data Entry Form and Search Functionality in Excel

        1. Resolving the "Cannot extend table or database" Error: This error typically occurs when Excel cannot expand the table due to existing data, merged cells, or blank rows/columns surrounding the table. Here’s how to address this issue:

        • Check Surrounding Cells: Ensure that there are no merged cells or data in adjacent rows/columns around the table. Excel needs space to expand the table.
        • Table Boundaries: Ensure the table doesn’t have any blank rows or columns within it. Blank rows or columns can interrupt the table’s structure.
        • Formulas: If your table columns have formulas, ensure they are correctly set up and not causing issues with the table’s auto-expansion.

        If the problem persists, consider recreating the table to ensure it's correctly defined.

         

        2. Making the UserForm Always Visible on Sheet B: To have a UserForm always available on Sheet B for data entry and searching, follow these steps:

        • Create a UserForm:
          • Open the VBA editor with Alt + F11.
          • Go to Insert > UserForm to create a new form. Add the necessary controls like TextBoxes, Labels, and Buttons.
        • Add Code to Show the UserForm:
          • In Sheet B, add a button and assign it a macro that shows the UserForm:

        Vba Code is untested backup your file first

        Private Sub ShowFormButton_Click()
            UserForm1.Show ' Replace "UserForm1" with your UserForm's name
        End Sub

        Submit Data from the UserForm:

          • Add a button (e.g., "Submit") on the UserForm and attach the following code to submit data to your table:

        Vba Code is untested backup your file first.

         

        Private Sub SubmitButton_Click()
            Dim ws As Worksheet
            Dim tbl As ListObject
            Dim newRow As ListRow
        
            ' Set the worksheet and table
            Set ws = ThisWorkbook.Sheets("SheetA")
            Set tbl = ws.ListObjects("YourTableName") ' Replace with your table name
        
            ' Add a new row to the table
            Set newRow = tbl.ListRows.Add(AlwaysInsert:=True)
        
            ' Fill in data from UserForm controls
            newRow.Range(1, 1).Value = TextBox1.Value ' Adjust control names accordingly
        
            ' Close the UserForm
            Unload Me
        End Sub

        3. Adding a Search Functionality: You can also add a search function to the UserForm that allows users to search for existing data. Here’s a simple way to implement it:

        • Add a Search Button and TextBox:
          • Add a TextBox (e.g., "SearchBox") and a Button (e.g., "SearchButton") on your UserForm.
        • Search Code:

        Vba Code is untested backup your file first

        Private Sub SearchButton_Click()
            Dim ws As Worksheet
            Dim tbl As ListObject
            Dim foundCell As Range
            Dim searchValue As String
        
            ' Set the worksheet and table
            Set ws = ThisWorkbook.Sheets("SheetA")
            Set tbl = ws.ListObjects("YourTableName") ' Replace with your table name
        
            ' Get the search value from the TextBox
            searchValue = SearchBox.Value
        
            ' Search in the first column of the table
            Set foundCell = tbl.ListColumns(1).DataBodyRange.Find(What:=searchValue, LookIn:=xlValues)
        
            ' Display search results
            If Not foundCell Is Nothing Then
                MsgBox "Found: " & foundCell.Value, vbInformation
            Else
                MsgBox "No match found.", vbExclamation
            End If
        End Sub

        This simple search will look for a value in the first column of the table and notify the user if a match is found.

    • johanan42's avatar
      johanan42
      Copper Contributor

      NikolinoDE 

      can you look through this code i am having issues with the form section

      Option Compare Database Option Explicit ' Main entry point for setting up the Special Education Database Public Sub SetupSpecialEdDatabase() On Error GoTo ErrorHandler Dim db As DAO.Database Set db = CurrentDb ' Step 1: Create database structure CreateDatabaseStructure db AddControlToForm MsgBox "Special Education Database setup completed successfully!", vbInformation Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbCritical End Sub ' Function to check if a table exists Private Function TableExists(db As DAO.Database, tableName As String) As Boolean Dim td As DAO.TableDef For Each td In db.TableDefs If td.Name = tableName Then TableExists = True Exit Function End If Next td TableExists = False End Function ' Subroutine to create the database structure Private Sub CreateDatabaseStructure(db As DAO.Database) CreateStudentsTable db CreateStaffTable db CreateServicesTable db CreateStudentServicesTable db CreateProgressReportsTable db CreateRelationships db End Sub ' Subroutines to create individual tables Private Sub CreateStudentsTable(db As DAO.Database) If Not TableExists(db, "Students") Then Dim tdf As DAO.TableDef Set tdf = db.CreateTableDef("Students") With tdf .Fields.Append .CreateField("StudentID", dbLong) .Fields("StudentID").Attributes = dbAutoIncrField .Fields.Append .CreateField("FirstName", dbText, 50) .Fields.Append .CreateField("LastName", dbText, 50) .Fields.Append .CreateField("DateOfBirth", dbDate) .Fields.Append .CreateField("Gender", dbText, 1) .Fields.Append .CreateField("GuardianName", dbText, 100) .Fields.Append .CreateField("GuardianContact", dbText, 20) .Fields.Append .CreateField("Address", dbText, 200) .Fields.Append .CreateField("EnrollmentDate", dbDate) .Fields.Append .CreateField("Grade", dbInteger) .Fields.Append .CreateField("PrimaryDiagnosis", dbText, 100) .Fields.Append .CreateField("SecondaryDiagnosis", dbText, 100) .Fields.Append .CreateField("IEPDate", dbDate) End With db.TableDefs.Append tdf Dim idx As DAO.Index Set idx = tdf.CreateIndex("PrimaryKey") idx.Fields.Append idx.CreateField("StudentID") idx.Primary = True tdf.Indexes.Append idx End If End Sub Private Sub CreateStaffTable(db As DAO.Database) If Not TableExists(db, "Staff") Then Dim tdf As DAO.TableDef Set tdf = db.CreateTableDef("Staff") With tdf .Fields.Append .CreateField("StaffID", dbLong) .Fields("StaffID").Attributes = dbAutoIncrField .Fields.Append .CreateField("FirstName", dbText, 50) .Fields.Append .CreateField("LastName", dbText, 50) .Fields.Append .CreateField("Role", dbText, 50) .Fields.Append .CreateField("Specialization", dbText, 100) .Fields.Append .CreateField("Email", dbText, 100) .Fields.Append .CreateField("Phone", dbText, 20) End With db.TableDefs.Append tdf Dim idx As DAO.Index Set idx = tdf.CreateIndex("PrimaryKey") idx.Fields.Append idx.CreateField("StaffID") idx.Primary = True tdf.Indexes.Append idx End If End Sub Private Sub CreateServicesTable(db As DAO.Database) If Not TableExists(db, "Services") Then Dim tdf As DAO.TableDef Set tdf = db.CreateTableDef("Services") With tdf .Fields.Append .CreateField("ServiceID", dbLong) .Fields("ServiceID").Attributes = dbAutoIncrField .Fields.Append .CreateField("ServiceName", dbText, 100) .Fields.Append .CreateField("Description", dbMemo) End With db.TableDefs.Append tdf Dim idx As DAO.Index Set idx = tdf.CreateIndex("PrimaryKey") idx.Fields.Append idx.CreateField("ServiceID") idx.Primary = True tdf.Indexes.Append idx End If End Sub Private Sub CreateStudentServicesTable(db As DAO.Database) If Not TableExists(db, "StudentServices") Then Dim tdf As DAO.TableDef Set tdf = db.CreateTableDef("StudentServices") With tdf .Fields.Append .CreateField("StudentServiceID", dbLong) .Fields("StudentServiceID").Attributes = dbAutoIncrField .Fields.Append .CreateField("StudentID", dbLong) .Fields.Append .CreateField("ServiceID", dbLong) .Fields.Append .CreateField("StaffID", dbLong) .Fields.Append .CreateField("StartDate", dbDate) .Fields.Append .CreateField("EndDate", dbDate) .Fields.Append .CreateField("Frequency", dbText, 50) .Fields.Append .CreateField("Goals", dbMemo) End With db.TableDefs.Append tdf Dim idx As DAO.Index Set idx = tdf.CreateIndex("PrimaryKey") idx.Fields.Append idx.CreateField("StudentServiceID") idx.Primary = True tdf.Indexes.Append idx End If End Sub Private Sub CreateProgressReportsTable(db As DAO.Database) If Not TableExists(db, "ProgressReports") Then Dim tdf As DAO.TableDef Set tdf = db.CreateTableDef("ProgressReports") With tdf .Fields.Append .CreateField("ReportID", dbLong) .Fields("ReportID").Attributes = dbAutoIncrField .Fields.Append .CreateField("StudentID", dbLong) .Fields.Append .CreateField("ReportDate", dbDate) .Fields.Append .CreateField("AcademicProgress", dbMemo) .Fields.Append .CreateField("BehavioralProgress", dbMemo) .Fields.Append .CreateField("SocialProgress", dbMemo) .Fields.Append .CreateField("NextSteps", dbMemo) End With db.TableDefs.Append tdf Dim idx As DAO.Index Set idx = tdf.CreateIndex("PrimaryKey") idx.Fields.Append idx.CreateField("ReportID") idx.Primary = True tdf.Indexes.Append idx End If End Sub Private Sub CreateRelationships(db As DAO.Database) On Error Resume Next ' In case relationships already exist Dim rel As DAO.Relation Set rel = db.CreateRelation("StudentsStudentServices", "Students", "StudentServices", dbRelationUpdateCascade) rel.Fields.Append rel.CreateField("StudentID") rel.Fields("StudentID").ForeignName = "StudentID" db.Relations.Append rel Set rel = db.CreateRelation("ServicesStudentServices", "Services", "StudentServices", dbRelationUpdateCascade) rel.Fields.Append rel.CreateField("ServiceID") rel.Fields("ServiceID").ForeignName = "ServiceID" db.Relations.Append rel Set rel = db.CreateRelation("StaffStudentServices", "Staff", "StudentServices", dbRelationUpdateCascade) rel.Fields.Append rel.CreateField("StaffID") rel.Fields("StaffID").ForeignName = "StaffID" db.Relations.Append rel Set rel = db.CreateRelation("StudentsProgressReports", "Students", "ProgressReports", dbRelationUpdateCascade) rel.Fields.Append rel.CreateField("StudentID") rel.Fields("StudentID").ForeignName = "StudentID" db.Relations.Append rel On Error GoTo 0 ' Reset error handling End Sub Private Sub CreateStudentEntryForm() On Error GoTo ErrorHandler Dim db As DAO.Database Dim strFormName As String Dim frm As Form Set db = CurrentDb strFormName = "frmStudentEntry" ' Check if the form already exists If DCount("*", "MSysObjects", "Type=(-32768) AND Name='" & strFormName & "'") > 0 Then MsgBox "The Student Entry form already exists.", vbInformation Exit Sub End If ' Create a new form Set frm = Application.CreateForm ' Set form properties With frm .Name = strFormName .RecordSource = "Students" .Caption = "Student Entry Form" ' Add controls to the form AddControlToForm .Name, acTextBox, "StudentID", "Student ID:", 1000, 400 AddControlToForm .Name, acTextBox, "FirstName", "First Name:", 1000, 800 AddControlToForm .Name, acTextBox, "LastName", "Last Name:", 1000, 1200 AddControlToForm .Name, acTextBox, "DateOfBirth", "Date of Birth:", 1000, 1600 AddControlToForm .Name, acTextBox, "Gender", "Gender:", 1000, 2000 AddControlToForm .Name, acTextBox, "GuardianName", "Guardian Name:", 1000, 2400 AddControlToForm .Name, acTextBox, "GuardianContact", "Guardian Contact:", 1000, 2800 AddControlToForm .Name, acTextBox, "Address", "Address:", 1000, 3200 AddControlToForm .Name, acTextBox, "EnrollmentDate", "Enrollment Date:", 1000, 3600 AddControlToForm .Name, acTextBox, "Grade", "Grade:", 1000, 4000 AddControlToForm .Name, acTextBox, "PrimaryDiagnosis", "Primary Diagnosis:", 1000, 4400 AddControlToForm .Name, acTextBox, "SecondaryDiagnosis", "Secondary Diagnosis:", 1000, 4800 AddControlToForm .Name, acTextBox, "IEPDate", "IEP Date:", 1000, 5200 ' Add navigation buttons AddControlToForm .Name, acCommandButton, "cmdFirst", "First", 1000, 5600, 1000, 400, "=DoCmd.GoToRecord , , acFirst" AddControlToForm .Name, acCommandButton, "cmdPrevious", "Previous", 2100, 5600, 1000, 400, "=DoCmd.GoToRecord , , acPrevious" AddControlToForm .Name, acCommandButton, "cmdNext", "Next", 3200, 5600, 1000, 400, "=DoCmd.GoToRecord , , acNext" AddControlToForm .Name, acCommandButton, "cmdLast", "Last", 4300, 5600, 1000, 400, "=DoCmd.GoToRecord , , acLast" End With ' Save and close the form DoCmd.Close acForm, frm.Name, acSaveYes MsgBox "Student Entry form created successfully!", vbInformation Exit Sub ErrorHandler: MsgBox "An error occurred while creating the form: " & Err.Description, vbCritical End Sub

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        johanan42 

        Troubleshooting the Special Education Database Setup in Access VBA

        1. Error Handling and Debugging Tips: To troubleshoot and ensure your Access VBA code runs smoothly, you should implement robust error handling and ensure each part of your code works as expected.
        2. Issue with the Form Creation Section: The code to create the Student Entry Form appears to be generally correct. However, if you're facing issues, here are some tips:
        • Check for Existing Forms: Ensure that the form doesn't already exist, as your code checks for this and might exit early if it does:

        Vba Code is untested backup your file.

         

        If DCount("*", "MSysObjects", "Type=(-32768) AND Name='" & strFormName & "'") > 0 Then
            MsgBox "The Student Entry form already exists.", vbInformation
            Exit Sub
        End If

         

        If you want to overwrite the form or create it anew, you might need to delete the existing form first.

        • Form Control Creation: The function AddControlToForm is called but not defined in the provided code. Make sure this function is defined properly elsewhere in your code. Here’s a simplified example of what this function might look like:

        Vba Code is untested backup your file first.

         

        Private Sub AddControlToForm(ByVal formName As String, controlType As AcControlType, controlName As String, controlCaption As String, leftPos As Integer, topPos As Integer, Optional width As Integer = 0, Optional height As Integer = 0, Optional controlSource As String = "")
            Dim ctrl As Control
            Set ctrl = Forms(formName).Controls.Add(controlType, controlName)
            With ctrl
                .Caption = controlCaption
                .Left = leftPos
                .Top = topPos
                If width > 0 Then .Width = width
                If height > 0 Then .Height = height
                If controlSource <> "" Then .ControlSource = controlSource
            End With
        End Sub

         

        • Review Error Handling: Your code has a general error handler at the end of the CreateStudentEntryForm subroutine. Make sure to identify the specific line causing the error by stepping through the code with F8 in the VBA editor. This allows you to see where exactly the error occurs.

        3. General Advice:

        If the form creation part is causing issues, test the code in smaller chunks. Create a simple form first, adding one control at a time, and see where it fails. This will help you isolate the problem.

         

        I hope these explanations help you with your VBA projects. :smile:

Resources