Results 1 to 15 of 15
  1. #1
    Join Date
    May 2004
    Posts
    133

    Unanswered: newbie:enter an expression that has no value

    I have "limit" control on the mainform and "currentremainedsubform" subform that have a source is "currentremainedquantity" query.
    On current event: I added below codes:

    Private Sub Form_Current()
    If Forms!products!currentremainedSubform.Form!Remaine d > Me.limit Then
    With Me.ALERT
    .Visible = False
    End With
    Else
    With Me.ALERT
    .Visible = True
    End With
    End If
    End Sub
    So as to find out wich product that have the quantity on hand which already came to limit.
    Abobe code works fine except that if there is a product that does not value in the "remained" control, microsoft access returned this message:
    "run time error '2427'
    You entered an expression that has no value".
    could any one improve my code to clear above error message?
    I'm newbie to access so I'm felling hard to work with it.
    many thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your expression will fail if Remained is Null.
    try nz() as in:
    If nz(Forms!products!currentremainedSubform.Form!Rema ined,0) > Me.limit Then
    ...it substitutes 0 if Remained is Null.

    izy


    Later: and don't you just love the space this site inserts after 50 chars without space.
    Last edited by izyrider; 08-27-04 at 03:30.
    currently using SS 2008R2

  3. #3
    Join Date
    May 2004
    Posts
    133
    I'll try it now and reply you about the result

  4. #4
    Join Date
    May 2004
    Posts
    133
    Hi,
    thank you. As you mentioned, it works.
    But new problem occured:
    When I went to the last record and then go to new record (Newly created record that have nothing inside it), another error message occured. (could you see my attached jpg?)
    How can I solve this problem?
    many thanks
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    on which line? (use the debug button)

    nz(anything, 0) ALWAYS has a non-null value, so the error is from something else.

    if it is the same line causing the problem, then maybe Me.limit is the culprit... try nz(Me.limit,0)

    if not, start looking for nulls elsewhere.

    reconsider default values in your tables & forms
    eg default Limit = -99999 (or 0 or 99999 - whichever makes more sense in your application)?

    wherever possible, avoid nulls through default values.
    where not suitable, second choice is fix nulls with nz()... you can also use
    nz(aNumber, -99999)
    nz(someText, "") ...etc
    if neither of these is suitable, do a "manual" check before the form _Activate or _Current or whatever event triggers your error:
    if isnull(whatever) then 'do something useful

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    May 2004
    Posts
    133

    Unhappy expression error code

    No, my mainform works fine until now except above problem.
    Could you see my attached jpg (showing line number error, and it pointed to line that have nz function that I added )
    please help me.
    many thanks
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    where is this code (mother form "product" or subform "currentrema..")?
    where is Limit? (mother form "product" or subform "currentrema..")?

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    May 2004
    Posts
    133
    this code is in "products" motherform.
    limit control is in "products" motherform.
    limit control is bound to "limit" field in "products" table.
    My database is about 1.8 mb so I can not post it here.
    I may mail it to you if you request.
    Please help me. thank you alot.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't download or open attached dbs: too dangerous.



    try:

    If nz(Me!currentremainedSubform.Remained,0) > Me.limit Then
    ...you might not need nz() any more (but it/they do no harm).

    Me! is much faster than forms!this!that, and i worry about the trailing .Form on your orginal.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    May 2004
    Posts
    133

    Unhappy

    Quote Originally Posted by izyrider
    i don't download or open attached dbs: too dangerous.



    try:

    If nz(Me!currentremainedSubform.Remained,0) > Me.limit Then
    ...you might not need nz() any more (but it/they do no harm).

    Me! is much faster than forms!this!that, and i worry about the trailing .Form on your orginal.

    izy
    I tried it, but same error appeared. I'll die now.
    please help me. thank you

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    OK

    maybe your subform control is not called what you think it is called!!!!!

    design-view products
    get the properties window open (if it is not already open, right-click the form, but NOT inside the hole where "currentremai..." lives)

    then, one single left-click in the subform hole for "currentremai...."

    what do you see in properties/other/name...
    ...thats what you need in:
    me!TheRealNameOfYourSubFormControlGoesHere.Remained

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    May 2004
    Posts
    133
    Now I finished my work and left office. I'lltry it later and reply you soon. (About 15 hour later). My home does not have internet connection. thank you
    You are very kind.

  13. #13
    Join Date
    May 2004
    Posts
    133
    Hi, izyrider
    I tried it. But same error appeared.
    What can I do now?

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    out of ideas!
    i tried to duplicate the problem but could not.



    you could try a workaround... not very elegant, but it might help if nobody else here has a fix for the real problem.

    is it still error 2427 ?? try this:

    Private Sub Form_Current()
    on error goto err_form_current
    If nz(Me!currentremainedSubform.Remained,0) > nz(Me.limit,0) Then
    With Me.ALERT
    .Visible = False
    End With
    Else
    With Me.ALERT
    .Visible = True
    End With
    End If
    exit_form_current:
    exit sub
    err_form_current:
    if err.number = 2427 then
    me.alert.visible = false
    resume exit_form_current
    else
    msgbox err.description, vbcritical, "ERROR " & err.number & " in _Current()"
    resume exit_form_current
    endif
    End Sub


    sorry - that's the best i can do for now.

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    May 2004
    Posts
    133
    Thank you
    I'll try it now.
    you are very kind to help me.
    once again, thank you

Posting Permissions

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