Results 1 to 9 of 9
  1. #1
    guydols is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    3

    Question Impossible Access error (Out of memory)


    Hello,

    I was building this database for school and everything was working perfect. I had a few lines of code that interacted with forms and it was working good.
    But now all of a sudden when i try to run the code i get this "run time error 7 out of memory" on the form commands.

    I searched a lot on the internet and i tried many fixes, nothing worked so far.
    This error also happens on other computers to with different versions of access.

    This is a link to the complete database: https://docs.google.com/open?id=0B_e...0psWl9JOS05dWc
    Here is one of the functions that fails.
    Code:
    Function s1()Dim x As String
    
    
    'this one works
    x = Forms_TitleScreen.searchbox.Value
    
    
    If IsNumeric(x) Then
    If Len(x) = 4 Then
    Dim numsql As String
    numsql = "SELECT Movies.MovieID, Movies.Title, Movies.Year FROM Movies WHERE Movies.Year = " & CStr(x) & ";"
    DoCmd.OpenForm ("Info")
    Form_Info.Visible = False
    Form_Info.searchbox.Value = x
    Form_Info.Result.RowSource = numsql
    Form_Info.Visible = True
    Call i1
    End If
    End If
    
    
    If IsNumeric(x) = False Then
    If (InStr(1, x, "a") > 0 Or InStr(1, x, "b") > 0 Or InStr(1, x, "c") > 0 Or InStr(1, x, "d") > 0 Or InStr(1, x, "e") > 0 _
    Or InStr(1, x, "f") > 0 Or InStr(1, x, "g") > 0 Or InStr(1, x, "h") > 0 Or InStr(1, x, "i") > 0 Or InStr(1, x, "j") > 0 _
    Or InStr(1, x, "k") > 0 Or InStr(1, x, "l") > 0 Or InStr(1, x, "m") > 0 Or InStr(1, x, "n") > 0 Or InStr(1, x, "o") > 0 _
    Or InStr(1, x, "p") > 0 Or InStr(1, x, "q") > 0 Or InStr(1, x, "r") > 0 Or InStr(1, x, "s") > 0 Or InStr(1, x, "t") > 0 _
    Or InStr(1, x, "u") > 0 Or InStr(1, x, "v") > 0 Or InStr(1, x, "w") > 0 Or InStr(1, x, "x") > 0 Or InStr(1, x, "y") > 0 _
    Or InStr(1, x, "z") > 0 Or InStr(1, x, "A") > 0 Or InStr(1, x, "B") > 0 Or InStr(1, x, "C") > 0 Or InStr(1, x, "D") > 0 _
    Or InStr(1, x, "E") > 0 Or InStr(1, x, "F") > 0 Or InStr(1, x, "G") > 0 Or InStr(1, x, "H") > 0 Or InStr(1, x, "I") > 0 _
    Or InStr(1, x, "J") > 0 Or InStr(1, x, "K") > 0 Or InStr(1, x, "L") > 0 Or InStr(1, x, "M") > 0 Or InStr(1, x, "N") > 0 _
    Or InStr(1, x, "O") > 0 Or InStr(1, x, "P") > 0 Or InStr(1, x, "Q") > 0 Or InStr(1, x, "R") > 0 Or InStr(1, x, "S") > 0 _
    Or InStr(1, x, "T") > 0 Or InStr(1, x, "U") > 0 Or InStr(1, x, "V") > 0 Or InStr(1, x, "W") > 0 Or InStr(1, x, "X") > 0 _
    Or InStr(1, x, "Y") > 0 Or InStr(1, x, "Z") > 0 Or Len(x) <> 4) Then
    Dim txtsql As String
    Dim tmp As String
    tmp = x
    x = "*" & x & "*"
    txtsql = "SELECT Movies.MovieID, Movies.Title, Movies.Year FROM Movies WHERE Movies.Title like  '" & x & "';"
    DoCmd.OpenForm ("Info")
    Form_Info.Visible = False
    Form_Info.searchbox.Value = tmp
    Form_Info.Result.RowSource = txtsql
    Form_Info.Visible = True
    Call i1
    End If
    End If
    
    
    End Function

    Many thanks for any help, i'm really stuck here.
    sorry for any bad English, i'm Dutch.

    Kind regards,
    Guy Dols

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hello Guy,

    It's not very helpful to publish the working procedure and not publish the one causing the error. However I've looked at the complete db and your problem is in the following procedure:

    Code:
    Private Sub searchbtn_Click()
        If IsNull(Form_Info.searchbox.Value) Then
            MsgBox "Please fill out the search box.", vbCritical, "No search value!"
        Else
        Call s2
        End If
    End Sub
    Quite why your getting the 'Out Of Memory' error I don't know but it is caused by incorrect addressing of the form as highlighted in red above. What you should be coding is:

    Code:
    If IsNull(Forms("Info").searchbox.Value) Then
    Make this change and it should work.

    But there is much to improve in your code! I shall spend some time and suggest ways in which you can improve it. Look out for a further post.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Guy,

    You say you were building this for school. Has your teacher/professor seen your current code?
    As Rod says, there is much that could be improved.

    Did you not get a feeling that there might be a better way than writing that long IF statement?

    Just googling techonthenet may help you considerably.

    http://www.techonthenet.com/access/f...phanumeric.php

    Note: I just realized this is your first post so I do understand part of your issue.

  4. #4
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hello again.

    Code:
    Private Sub searchbtn_Click()
        If IsNull(Form_Info.searchbox.Value) Then
            MsgBox "Please fill out the search box.", vbCritical, "No search value!"
        Else
        Call s2
        End If
    End Sub
    I've already described the error in this code. Now there are two improvements:

    1. The object 'Me' refers to the object in which the VBA code is running, in this case the form 'Info.' It is much easier to use the 'Me' object rather than the explicit reference of 'Forms(...)' and has the advantage that Intellisense then presents for selection all members of Me (i.e. form 'Info') after you type the full stop (period). Also 'Value' is the default property of a textbox so the second line may be written as If IsNull(Me.searchbox) Then.
    2. Why not pass an argument to function 's2' rather than have 's2' retrieve the textbox value from this form. I suggest that the fifth line should be Call s2(Trim$(Me.searchbox)). It's always a good idea to trim leading and trailing spaces from a string that the user has entered.


    So now your code should look more like:

    Code:
    Private Sub searchbtn_Click()
        If IsNull(Me.searchbox) Then
            MsgBox "Please fill out the search box.", vbCritical, "No search value!"
        Else
            Call s2(Trim$(Me.searchbox))
        End If
    End Sub
    But we're not finished because there are corresponding changes to make to procedure 's2.'

    1. The function declaration should be written as Function s2(x as string). The line x = Form_Info.searchbox.Value is now not needed.
    2. Again throughout this module you address form 'Info' incorrectly. Here you cannot use the 'Me' object as the code is running in a standard module named 'Module1.' So you must resort to one of the variations of the 'Forms' syntax.
    3. Now for that ugly multi line 'InStr' construct. You are looking for one occurrence of an alphabetic character in the string; why not use Like "*[a-z,A-Z]*"


    So now the code looks like:

    Code:
    Function s2(x as String)
    Dim numsql As String
    Dim txtsql As String
    Dim tmp As String
    If IsNumeric(x) Then
    If Len(x) = 4 Then
    numsql = "SELECT Movies.MovieID, Movies.Title, Movies.Year FROM Movies WHERE Movies.Year = " & CStr(x) & ";"
    Forms("Info").searchbox= x
    Forms("Info").Result.RowSource = numsql
    Call i1
    End If
    End If
    If IsNumeric(x) = False Then
    If x Like "*[a-z,A-Z]*" Or Len(x) <> 4) Then
    tmp = x
    x = "*" & x & "*"
    txtsql = "SELECT Movies.MovieID, Movies.Title, Movies.Year FROM Movies WHERE Movies.Title like  '" & x & "';"
    Forms("Info").searchbox = tmp
    Forms("Info").Result.RowSource = txtsql
    Call i1
    End If
    End If
    End Function
    There are still all kinds of concerns with the code but at least you are now going in the right direction.

  5. #5
    guydols is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    3
    Well thanks for all the comments, first thing you should know, i don't get any lessons on programming, none.
    I learned everything i know about programming on my own, and i'm aware that there are better ways to do this, i will try these when i'm done.

    But you will never guess what the problem was, the form Info, I removed it and rebuild it exactly the same, and same code.
    Everything works again.

    Btw i saw alot of ways to interact with forms but "Form_formname" work for me so I kinda like to stick with it.

    Never the less, many thanks, i will look at your code, when i'm done and ready for compacting and improving the code.

    Regards,
    Guy Dols

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good luck with your project.
    I will make a comment for you to consider.

    Based on your statement, "I'm aware that there are better ways to do this, I will try these when I'm done.", indicates to me that you won't ever be ready to try new approaches. I suggest you take the time with a small project and experiment with some of the techniques Rod has provided, and those which you know are better.

    Again good luck with your project, and investigating those "better ways".

  7. #7
    guydols is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    3
    Thanks, but perfecting my code is not a priority right now.
    And i prefer python, ruby and simple bash.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I wasn't suggesting - perfecting your code. I was thinking more along the lines of using the tools that are available to you, and using them as appropriate.

    The analogy that comes to mind is hammering a nail with a screwdriver -- it works, but a hammer is designed for the job.

    Good luck with your projects.

  9. #9
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Well, I live and learn! I have never before encountered anyone using the class name for addressing form members. I was sure it would cause an error and it was certainly the cause of the 'out of memory' error. Yet I have just conducted a small test. No error: if the form is not loaded then VBA loads an instance of the form - visible; otherwise VBA simply addresses the already opened form. I don't know if this is useful or not; to my mind it is at least uncontrolled if not non standard.

    Now 'out of memory' errors usually indicate recursion. I'm not prepared to spend the time proving this one way or the other but I suspect there was some form of recursion in Guy's code, perhaps between the On Load events.

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

Similar Threads

  1. Replies: 0
    Last Post: 11-29-2011, 01:06 PM
  2. Replies: 9
    Last Post: 11-09-2011, 11:34 AM
  3. Questions Regarding MS Access Memory
    By JeffG3209 in forum Access
    Replies: 5
    Last Post: 08-10-2011, 05:26 PM
  4. disk space or memory error
    By Cmitchell in forum Database Design
    Replies: 1
    Last Post: 04-04-2011, 03:36 PM
  5. Replies: 0
    Last Post: 01-09-2009, 03:10 PM

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