Results 1 to 10 of 10
  1. #1
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291

    I created an infinite loop somehow, anyone find the problem?

    The first picture shows the characteristics of the text boxes I have so far.
    The second picture is when I go to Form View

    Here is the code that is being called:
    Function MissingInfo(ID As Integer)


    Dim str As String
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Inspections WHERE ID = " & ID)

    If IsNull(rs!MHType) Then
    str = str + "Type "
    End If

    If IsNull(rs![Elevation (45)]) Then
    str = str + "Elev "
    End If
    txtMissing = str

    Set rs = Nothing

    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Personally I wouldn't bother with the unbound textbox. The textbox with the function will return the desired data. Is "ID" a field in your data? It looks like it would be "manhole number".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    I tried setting the textbox with the function in it to a value and it gave me an error, do I need to use a return statement or anything?>

  4. #4
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Ok, after running a few watches and break points I have realized that every time I set txtMissing it changes the value for the textbox next to each record.

    I also tried naming the text box with the function and making it visible and it just displays #error

    So I need to figure out how to set the textbox for each individual record or come up with something like that?

  5. #5
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Fixed with
    MissingInfo = str

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you sorted it out. The sample function I posted before did that; I didn't notice that yours didn't.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Not crucial but why does the text box after the last record always show #error ?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presumably that's a new record. It would probably show error because you're passing a Null to the function. The fix would be to tweak the function to handle a Null and pass back a ZLS or "unknown" or whatever you want.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    How would you tweak the function to handle a Null value?

    Now I put a break point and went through each record and every time it updated the appropriate box except the last time it updated the appropriate box and the #error box, maybe because trying to pass a Null didnt actually call the function and catch the break point?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, Integer can't accept a Null so this:

    Function MissingInfo(ID As Integer)

    would have to be this:

    Function MissingInfo(ID As Variant)

    Then before opening the recordset test for it:

    Code:
    If IsNull(ID) Then
      MissingInfo = "Whatever"
      Exit Function  'or if you have error handling, GoTo ExitHandler
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. How to loop code to run through entire table
    By kmajors in forum Reports
    Replies: 9
    Last Post: 04-23-2010, 09:27 AM
  2. Loop through Records and Make ID
    By rob4465 in forum Programming
    Replies: 3
    Last Post: 01-14-2010, 10:46 AM
  3. For Each LOOP statement
    By zambam737 in forum Programming
    Replies: 3
    Last Post: 10-26-2009, 09:59 PM
  4. concatenate string using loop
    By nengster in forum Programming
    Replies: 0
    Last Post: 02-23-2009, 08:05 PM
  5. Loop a table to use field in query
    By jdubp in forum Programming
    Replies: 0
    Last Post: 03-04-2008, 11:48 AM

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