+ Reply to Thread
Results 1 to 7 of 7

VBA Error on multiple record entry

  1. #1
    Registered User
    Join Date
    12-06-2017
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    28

    VBA Error on multiple record entry

    Hi,

    I have been developing a "Lessons Learned" database to record incidents, accidents, and near misses in a program I am working with. Data entry is performed via a form I have created. It has, over time, become more complex as I try to meet requests for more functionality. I suspect I've tried to cram way too much into this form. And now, I am experiencing an issue I think may be related to memory. I hoping for some advice to help me perhaps eliminate whatever may be causing the issue. The issue doesn't occur when I enter one or two records, then close the form. But when I enter three or more records, it crashes. So here are some of the details, and thanks in advance for any help!

    Screen shots of the error msg, debugger, and form:

    error msg 1.png

    error msg.png

    form.png

    VBA Code:

    Private Sub UserForm_Initialize()

    'Safety Record form

    Dim Cloc As Range
    Dim CCat As Range
    Dim CPhase As Range
    Dim CSubC As Range

    Dim dte As Date


    Dim ws As Worksheet
    Set ws = Worksheets("Lookups")




    For Each Cloc In ws.Range("Site_Name")
    With Me.Site
    .AddItem Cloc.Value
    End With
    Next Cloc

    For Each CPhase In ws.Range("Phase")
    With Me.cboPhase
    .AddItem CPhase.Value

    End With
    Next CPhase

    For Each CCat In ws.Range("safety")
    With Me.cbo_cat
    .AddItem CCat.Value

    End With
    Next CCat

    For Each CSubC In ws.Range("subcatS")
    With Me.cbo_subcatS
    .AddItem CSubC.Value

    End With
    Next CSubC

    End Sub

    Private Sub cmdAdd_Click()
    Dim lrow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Master")

    'find first empty row in database
    lrow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


    'check for a date
    If Trim(Me.txtDate.Value) = "" Then
    Me.txtDate.SetFocus
    MsgBox "Please enter date"
    Exit Sub
    End If

    'check for a site
    If Trim(Me.Site.Value) = "" Then
    MsgBox "Please selecta a site"
    Exit Sub
    End If

    'check for Reporting Contractor
    If Trim(Me.txtReptC.Value) = "" Then
    MsgBox "Please provide Reporting Contractor"
    Exit Sub
    End If

    'Copy the data to the database
    ws.Cells(lrow, 1).Value = Me.txtDate.Value
    ws.Cells(lrow, 2).Value = Me.Site.Value
    ws.Cells(lrow, 3).Value = Me.txtReptC.Value
    ws.Cells(lrow, 4).Value = Me.TxtSubC.Value
    ws.Cells(lrow, 5).Value = Me.cboPhase.Value
    ws.Cells(lrow, 6).Value = Me.cbo_cat.Value
    ws.Cells(lrow, 7).Value = Me.but_OSHA.Value
    ws.Cells(lrow, 8).Value = Me.But_FirstAid.Value
    ws.Cells(lrow, 9).Value = Me.But_Lost.Value
    ws.Cells(lrow, 10).Value = Me.txt_daysLost
    ws.Cells(lrow, 11).Value = Me.But_Restricted.Value
    ws.Cells(lrow, 12).Value = Me.Txt_Restrict.Value
    ws.Cells(lrow, 13).Value = Me.But_Utility.Value
    ws.Cells(lrow, 14).Value = Me.But_PropDam.Value
    ws.Cells(lrow, 15).Value = Me.Txt_value.Value
    ws.Cells(lrow, 16).Value = Me.txt_Describe.Value
    ws.Cells(lrow, 17).Value = Me.Txt_Lesson.Value
    ws.Cells(lrow, 18).Value = Me.cbo_subcatS.Value
    ws.Cells(lrow, 19).Value = Application.UserName
    ws.Cells(lrow, 20).Value = Now
    ws.Cells(lrow, 20).NumberFormat = "mm/dd/yyyy hh:mm:ss"


    'clear the data

    Me.txtDate.Value = ""
    Me.Site.Value = ""
    Me.txtReptC.Value = ""
    Me.TxtSubC.Value = ""
    Me.cboPhase.Value = ""
    Me.cbo_cat.Value = ""
    Me.cbo_subcatS.Value = ""
    Me.but_OSHA.Value = ""
    Me.But_FirstAid.Value = ""
    Me.But_Lost.Value = ""
    Me.txt_daysLost.Value = ""
    Me.But_Restricted.Value = ""
    Me.Txt_Restrict.Value = ""
    Me.But_Utility.Value = ""
    Me.But_PropDam.Value = ""
    Me.Txt_value = ""
    Me.txt_Describe.Value = ""
    Me.Txt_Lesson.Value = ""
    Me.txtDate.SetFocus

    End Sub

    Private Sub ListRecord_Click()
    'dim the variables
    Dim i As Integer

    'find the selected item
    i = Me.ListRecord.ListIndex
    Me.ListRecord.Selected(i) = True

    'add the values to the text boxes

    Me.txtDate.Value = Me.ListRecord.Column(0, i)
    Me.Site.Value = Me.ListRecord.Column(1, i)
    Me.txtReptC.Value = Me.ListRecord.Column(2, i)
    Me.TxtSubC.Value = Me.ListRecord.Column(3, i)
    Me.cboPhase.Value = Me.ListRecord.Column(4, i)
    Me.cbo_cat.Value = Me.ListRecord.Column(5, i)
    Me.cbo_subcatS.Value = Me.ListRecord.Column(6, i)
    Me.but_OSHA.Value = Me.ListRecord.Column(7, i)
    Me.But_FirstAid.Value = Me.ListRecord.Column(8, i)
    Me.But_Lost.Value = Me.ListRecord.Column(9, i)
    Me.txt_daysLost.Value = Me.ListRecord.Column(10, i)
    Me.But_Restricted.Value = Me.ListRecord.Column(11, i)
    Me.Txt_Restrict.Value = Me.ListRecord.Column(12, i)
    Me.But_Utility.Value = Me.ListRecord.Column(13, i)
    Me.But_PropDam.Value = "Me.ListRecord.Column(14, i )"
    Me.Txt_value = Me.ListRecord.Column(15, i)
    Me.txt_Describe.Value = Me.ListRecord.Column(16, i)
    Me.Txt_Lesson.Value = Me.ListRecord.Column(17, i)

    'format date as a date


    End Sub

    Private Sub PrevRec_Click()
    'scroll back through previous records

    Dim i As Integer

    'select item message
    If Me.ListRecord.Value = "" Then
    MsgBox "Please select an item in the list above by clicking on it"
    Exit Sub
    End If

    'find record
    i = Me.ListRecord.ListIndex
    Me.ListRecord.Selected(i) = True
    If i = 0 Then Exit Sub

    'populate form with data from prev record


    Me.txtDate.Value = Me.ListRecord.Column(0, i)
    Me.Site.Value = Me.ListRecord.Column(1, i)
    Me.txtReptC.Value = Me.ListRecord.Column(2, i)
    Me.TxtSubC.Value = Me.ListRecord.Column(3, i)
    Me.cboPhase.Value = Me.ListRecord.Column(4, i)
    Me.cbo_cat.Value = Me.ListRecord.Column(5, i)
    Me.cbo_subcatS.Value = Me.ListRecord.Column(6, i)
    Me.but_OSHA.Value = Me.ListRecord.Column(7, i)
    Me.But_FirstAid.Value = Me.ListRecord.Column(8, i)
    Me.But_Lost.Value = Me.ListRecord.Column(9, i)
    Me.txt_daysLost.Value = Me.ListRecord.Column(10, i)
    Me.But_Restricted.Value = Me.ListRecord.Column(11, i)
    Me.Txt_Restrict.Value = Me.ListRecord.Column(12, i)
    Me.But_Utility.Value = Me.ListRecord.Column(13, i)
    Me.But_PropDam.Value = "Me.ListRecord.Column(14, i )"
    Me.Txt_value = Me.ListRecord.Column(15, i)
    Me.txt_Describe.Value = Me.ListRecord.Column(16, i)
    Me.Txt_Lesson.Value = Me.ListRecord.Column(17, i)

    'select the new row
    Me.ListRecord.Selected(i) = True


    End Sub

    Private Sub NextRec_Click()

    'select item message
    If Me.ListRecord.Value = "" Then
    MsgBox "Please select an item in the list above by clicking on it"
    Exit Sub
    End If

    'find record
    i = Me.ListRecord.ListIndex
    Me.ListRecord.Selected(i) = True
    If i = 0 Then Exit Sub

    'populate form with data from prev record


    Me.txtDate.Value = Me.ListRecord.Column(0, i + 1)
    Me.Site.Value = Me.ListRecord.Column(1, i + 1)
    Me.txtReptC.Value = Me.ListRecord.Column(2, i + 1)
    Me.TxtSubC.Value = Me.ListRecord.Column(3, i + 1)
    Me.cboPhase.Value = Me.ListRecord.Column(4, i + 1)
    Me.cbo_cat.Value = Me.ListRecord.Column(5, i + 1)
    Me.cbo_subcatS.Value = Me.ListRecord.Column(6, i + 1)
    Me.but_OSHA.Value = Me.ListRecord.Column(7, i + 1)
    Me.But_FirstAid.Value = Me.ListRecord.Column(8, i + 1)
    Me.But_Lost.Value = Me.ListRecord.Column(9, i + 1)
    Me.txt_daysLost.Value = Me.ListRecord.Column(10, i + 1)
    Me.But_Restricted.Value = Me.ListRecord.Column(11, i + 1)
    Me.Txt_Restrict.Value = Me.ListRecord.Column(12, i + 1)
    Me.But_Utility.Value = Me.ListRecord.Column(13, i + 1)
    Me.But_PropDam.Value = "Me.ListRecord.Column(14, i + 1)"
    Me.Txt_value = Me.ListRecord.Column(15, i + 1)
    Me.txt_Describe.Value = Me.ListRecord.Column(16, i + 1)
    Me.Txt_Lesson.Value = Me.ListRecord.Column(17, i + 1)
    'select the new row
    Me.ListRecord.Selected(i + 1) = True


    End Sub
    Private Sub cmdClose_Click()
    Unload Me

    End Sub

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: VBA Error on multiple record entry

    Use code markers to insert it (i.e. code), it's hard to read such a "text message".
    Maybe this (in three procedures):
    Please Login or Register  to view this content.
    What are these double quotes for ?

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: VBA Error on multiple record entry

    Just curious why you would do this in Excel and not Access. Seems to me this would be something that Access is designed to do fairly easily vs programming it in Excel. Access could bring many benefits to it; faster as the data builds up, easier to alter, possible for multiple users to make entries at the same time, ability to "separate" the front end and back end so that users cannot access the whole database and alter it.

    Just an idea
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  4. #4
    Registered User
    Join Date
    12-06-2017
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    28

    Re: VBA Error on multiple record entry

    Thanks for your responses. I hadn't even noticed the quotation marks in the code, and not sure how that happened. But I'm not sure that is what is causing the error. The error happens when I enter 3 or more records, and then click "close." The "close" command (unload) is what is popping up as erroring out.

    To be honest, I hadn't even thought of using Access, mainly because I am more familiar and comfortable with Excel. I'm not sure all my end users have or use Access, either. I'm working with a bunch of Engineers, and I have yet to run across a single Access file at this company. But maybe I'll look into your suggestion a little deeper.

  5. #5
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: VBA Error on multiple record entry

    Quote Originally Posted by chicagolarsons View Post
    ...The error happens when I enter 3 or more records, and then click "close." The "close" command (unload) is what is popping up as erroring out..
    But "FrmSafe.Show" on a screenshot is not the "Unload" procedure, but the "Load" procedure.
    Can you check the code carefully with the "F8" key and exactly determine the place of error generation ?
    Change 'Load' and 'Unload' procedures:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: VBA Error on multiple record entry

    Quote Originally Posted by chicagolarsons View Post
    To be honest, I hadn't even thought of using Access, mainly because I am more familiar and comfortable with Excel. I'm not sure all my end users have or use Access, either. I'm working with a bunch of Engineers, and I have yet to run across a single Access file at this company. But maybe I'll look into your suggestion a little deeper.
    I only bring it up as it seems to be the right tool for the job. Just because I am more comfortable with a hammer doesn't mean I try and use it to screw in a screw.

    As soon as you say "database" Excel is no longer the right tool and it should instead be Access or another database software. Excel is for manipulating, summarizing and processing tabular data. In more recent years the additions of features like PowerPivot and data models have let it fake being a database, but even those tools are still meant to end with a summary or break down of the data, not as a means to store the data and relate it to other data sets long term.

    Its very unlikely they got licenced Excel but not Access. It may not be installed sure, but most IT dept's dont bother excluding certain parts of Office without a reason. Certainly check if Access is an option for everyone before moving to it but if you can use it, in the long run its likely a much better option. It should be easier to maintain, easier for end users, easier to fix and harder if not impossible for users to mess up.

  7. #7
    Registered User
    Join Date
    12-06-2017
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    28

    Re: VBA Error on multiple record entry

    Thanks, I agree with your suggestions and will look into it further. I appreciate your feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Prevent duplicate entry in a userform by matching record to multiple columns.
    By rsbuslon in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-05-2023, 07:44 PM
  2. [SOLVED] [B]Macro for updation of record/ Data Entry[/B]
    By tameen42 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2014, 09:48 AM
  3. Replies: 14
    Last Post: 10-07-2013, 04:04 PM
  4. Record date for data entry
    By wombat71 in forum Excel General
    Replies: 5
    Last Post: 05-26-2013, 05:08 PM
  5. Replies: 1
    Last Post: 01-03-2013, 01:13 AM
  6. [SOLVED] Creating a Record Entry Bar - Possible?
    By diggers16 in forum Excel General
    Replies: 15
    Last Post: 12-27-2012, 03:04 PM
  7. VBA for copy record to sellected 2 sheet & clear entry sheet for new record
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2012, 10:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1