Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble with code (log changes), form load order (A2k, 2k3)

    Two questions:

    First error I receive is while modifying data on a tabbed form, the form's focus is set after a listbox selection is made. I can make the changes in the data fields throught the form, as long as I don't change the record. I can't close the form, either. I receive the error: Run Time Error '2427' You entered an expression that has no value.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim db As DAO.Database
    Dim ctl As Control
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblChanges")
    'On Error GoTo Form_BeforeUpdate_Error

    For Each ctl In Me.Controls

    If TypeOf ctl Is Label Then
    'do nothing
    Else

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">If Nz(ctl.OldValue) <> Nz(ctl.Value) Then</span hi>
    With rst
    rst.AddNew
    rst!SSN = SSN
    rst!OBJECT_CHANGED = ctl.Name
    rst!prior_value = ctl.OldValue
    rst!current_value = ctl.Value
    rst!changed_by = GetNetUser()
    rst.Update
    End With
    End If
    End If

    Next ctl
    On Error GoTo 0
    rst.Close
    Exit Sub

    Form_BeforeUpdate_Error:
    If Err.Number = 2427 Then 'this is the error number for something which does not have any data, and therefore does not have an "oldvalue".
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate"
    End If
    End Sub


    I assume it has something to do with the field having nothing in it to begin with (possibly Null?), and then me adding data, however I thought the Nz function would resolve that issue, and I still get the error when I modify the field with pre-existing data.


    Second question:
    When my form loads, there is a tab with a list box on it, and the list box's SQL's text box refers to [forms]![frmS1_IndividualInfo].txtSearch, a text box obviously that I use to filter the list box for a name. This works fine in A2k, however in 2k3 I receive a criteria prompt before the form even loads. What changed, and more importantly, how do I fix it? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    Try testing for Null instead. The alternative would be to provide a 2nd argument for Nz, but that would entail testing the kind of value ctl can contain, not very attractive.

  3. #3
    Star Lounger
    Join Date
    Nov 2005
    Location
    Edmonton, Alberta, Canada
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    I'm suspicious of your...

    If TypeOf ctl Is Label Then
    'do nothing
    Else
    ...
    although I don't have time to experiment. Seems to me you should be checking for several other control types that don't have values as well (for instance, lines).

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    It is a datasheet form, no lines, no buttons.

    Hans, something I forgot to mention (and should have, <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>) is that I use this code on other forms, and it works flawlessly. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    When testing for null, I have tried
    _______________________
    If ctl.OldValue Is Null Then
    'do nothing
    Else
    _______________________


    For Each ctl In Me.Controls
    <font color=red>here</font color=red>
    If TypeOf ctl Is Label Then
    'do nothing
    Else
    <font color=red>and here</font color=red>
    If Nz(ctl.OldValue) <> Nz(ctl.Value) Then
    With rst
    rst.AddNew
    rst!SSN = SSN
    rst!OBJECT_CHANGED = ctl.Name
    rst!prior_value = ctl.OldValue
    rst!current_value = ctl.Value
    rst!changed_by = GetNetUser()
    rst.Update
    End With
    End If
    End If


    Each time, receiving the error 'object required'. Where else would I test?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    It's impossible to say without seeing the database.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    I was afraid you'd say that...oh boy... It's going to take a while, but I'll get it up soon - any ideas about my second question? I know you don't use 2k3, but perhaps any suggestions?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    No, sorry.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    Your form control
    [forms]![frmS1_IndividualInfo].txtSearch
    I notice you use a . between the form name and the control name, should you try using a ! instead? Just a guess.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    About your second question: I have now tried it in Access 2003 SP-1, and the problem didn't occur. So for this one too, I think you'll have to post a sample database.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    Jeremy, did you ever resolve this as I have just thought, as I look at it, has one of your table fields got a not null value in one of the fields?
    Jerry

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    As requested, extremely stripped down, to the point of only showing the error with the applicable forms (btw, it took nearly 40 minutes to do!)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    What am I supposed to do? Most things I try cause messages about things missing from the database to be displayed.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    Hi Jeremy I have had a quick look. Admittedly I have had to convert this to XP to view it.

    The unbound list box was not updating so I changed the SQL string in the data tab of the list box to:

    Like [forms]![frms1]![sfrms1_IndividualInfo]![txtSearch].[text] & "*" (note there is a dot between [txtSearch].[text])

    I also got rid of the after update event on list box (Private Sub lstNames_AfterUpdate())

    This appears to get the search up for Jezza Bear and Sergeant Hans V <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    This has got rid of the horrible error message but am guessing if this is what you want?
    Jerry

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with code (log changes), form load order (A2k, 2k3)

    Jerry,
    Just did the same as you...still get the message <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    What unbound list box?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 2 12 LastLast

Posting Permissions

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