Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: Problem with IF condition

    Hi,
    My project is in MS Access 2002.Its on Inventory
    In which I have one form, which I used for Shipping Data entry.

    I am using this code



    Code:
    Private Sub Enter_Click()
    On Error GoTo Err_Enter_Click
        Dim dbLocation
        Dim sSQL
        Dim sSQL1
        dbLocation = "\\Cpfpidc01\FINISHED GOODS\Inventory.mdb"
        Set objADO = CreateObject("ADODB.Connection")
        objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbLocation
     
        sSQL = "UPDATE PRODUCTION SET SHIPPEDDATE = '" & [Forms]![ShippingEntry]![SHIPPEDDATE] & "',ShippedQuantity = '" & [Forms]![ShippingEntry]![ShippedQuantity] & "',QTYONHAND = ((PRODUCTION.QTYONHAND)-('" & [Forms]![ShippingEntry]![ShippedQuantity] & "')),PackgingSlipNo = '" & [Forms]![ShippingEntry]![PackgingSlipNo] & "',Location = '" & [Forms]![ShippingEntry]![Location_Combo] & "',Notes = '" & [Forms]![ShippingEntry]![Notes] & "',Status = '" & [Forms]![ShippingEntry]![Location_Combo] & "' WHERE PalletNo='" & [Forms]![ShippingEntry]![PalletNo_Combo] & "'"
     
        objADO.Execute (sSQL)
        MsgBox "All The Details saved Successfully."
        objADO.Close
        Set objADO = Nothing
    Exit_Enter_Click:
        Exit Sub
    Err_Enter_Click:
        MsgBox Err.Description
        Resume Exit_Enter_Click
    End Sub


    Now in this code I want to put like this



    Code:
    If((Forms!ShippingEntry!ShippedQuantity) = (PRODUCTION.TTlQuantity)) Then
    {
        sSQL = "UPDATE PRODUCTION SET SHIPPEDDATE = '" & [Forms]![ShippingEntry]![SHIPPEDDATE] & "',ShippedQuantity = '" & [Forms]![ShippingEntry]![ShippedQuantity] & "',QTYONHAND = ((PRODUCTION.QTYONHAND)-('" & [Forms]![ShippingEntry]![ShippedQuantity] & "')),PackgingSlipNo = '" & [Forms]![ShippingEntry]![PackgingSlipNo] & "',Location = '" & [Forms]![ShippingEntry]![Location_Combo] & "',Notes = '" & [Forms]![ShippingEntry]![Notes] & "',Status = '" & [Forms]![ShippingEntry]![Location_Combo] & "' WHERE PalletNo='" & [Forms]![ShippingEntry]![PalletNo_Combo] & "'"
     
    objADO.Execute (sSQL)
    }
    else 
    {
        sSQL1 = "UPDATE PRODUCTION SET SHIPPEDDATE = '" & [Forms]![ShippingEntry]![SHIPPEDDATE] & "',ShippedQuantity = '" & [Forms]![ShippingEntry]![ShippedQuantity] & "',QTYONHAND = ((PRODUCTION.QTYONHAND)-('" & [Forms]![ShippingEntry]![ShippedQuantity] & "')),PackgingSlipNo = '" & [Forms]![ShippingEntry]![PackgingSlipNo] & "'',Notes = '" & [Forms]![ShippingEntry]![Notes] & "',Status = 'TOBESHIPPED' WHERE PalletNo='" & [Forms]![ShippingEntry]![PalletNo_Combo] & "'"
     
        objADO.Execute (sSQL1)
    }
    But when i did that it shows error that

    Object Require

    What to do?
    I think when I used Condition in IF, it require some recordset, which I not used in my code.
    So please help me.

  2. #2
    Join Date
    Jan 2008
    Posts
    3
    I believe that
    Forms!ShippingEntry!ShippedQuantity

    should be
    Forms!ShippingEntry.ShippedQuantity
    or
    nz(Forms!ShippingEntry.ShippedQuantity,0)

    You may get the error if the form is not open or there is no current record.

  3. #3
    Join Date
    Dec 2007
    Posts
    49
    NO its not working.
    its still showing

    object require.

    Mine form is already open.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ... = (PRODUCTION.TTlQuantity)) Then
    where is the object PRODUCTION defined and what is it?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2007
    Posts
    49
    That's the problem.
    PRODUCTION is a Table.
    I want to compare field of a table to field of a loaded form.
    I don't know how to do this?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ahhh!
    Access is giving an accurate err message.

    are you doing this in a loop or just the once?

    once:
    browse help for DLOOKUP() and see if that will give you your TTIQuantity

    loop:
    you probably need a recordset: can't suggest more without more info on what you are trying to achieve.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2007
    Posts
    49
    I want to do this things.
    I have one table(PRODUCTION) of Data Entry in which I enter the all details about product.
    now I have one form to fill it(DataEntry).
    but I have another form for Shipping Entry.
    So when I enter data in that form when product shipped then it will run query and update table(PRODUCTION).
    Now in that update process I want to enter the QTYONHAND.Where,
    QTYONHAND=((TTlQuantity)-(ShippedQuantity))

    Now everything is going good when we shipped all Quantity we have and, so QTYONHAND=0 and status=SHIPPED will set in table.
    but when we shipped some quantity, instead of all, then I have to enter whatever remaing quantity in QTYONHAND and status=TOBESHIPPED ,when TTlQuantity>ShippedQuantity.
    So I want to put this Condition using IF statement.

    I am using it in IF statement.

    In that I want to compare two values one from PRODUCTION table and other from Loded Form ShippingEntry.
    when I used
    If((Forms!ShippingEntry!ShippedQuantity) = (PRODUCTION.TTlQuantity)) Then
    at that time it will show error.
    So I think I already define Forms!ShippingEntry!ShippedQuantity But Accees don't understand what PRODUCTION.TTlQuantity bcz i didn't define it.
    So how to define it over here?
    Do u have any solution or I have to use Recordset for that?
    Thanks For ur Reply.
    Last edited by billy_pit; 01-18-08 at 15:14.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    fine.

    but which TTIQuantity in the table
    do have a go with DLOOKUP

    think of DLOOKUP (in fact all Dxxxxx domain functions) as a strange species of SQL that is guaranteed to return a single value (or NULL) so that Access knows it has a reasonable chance of sticking the return value in a variable.

    the basic construction is remarkably simple:

    Dlookup("SELECT one Field without the word SELECT", "FROM one table without the word FROM", "WHERE as usual but without the word WHERE")

    interpretation of the return value can be problematic: if more than one record matches the WHERE criteria, the return is one of the matching values but you must not depend on it always being the same value

    Dxxxx() "Domain functions" are slow, but an isolated Dxxxxx() does no harm when you need it. If you find yourself resorting to Dxxxxx() inside a loop there is almost certainly a better route.

    so you have:
    If Forms!ShippingEntry!ShippedQuantity = Dlookup("TTlQuantity", "Production", "you have not said what criteria you want") Then

    done!

    izy
    Last edited by izyrider; 01-18-08 at 15:41. Reason: changed one Dxxx to Dlookup so that it makes sense!
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2007
    Posts
    49
    If (Nz(Forms!ShippingEntry!ShippedQuantity, 0) < (DLookup("TTlQuantity", "PRODUCTION", PalletNo = " & [Forms]![ShippingEntry]![PalletNo_Combo] & "))) Then

    sSQL1 = "UPDATE PRODUCTION SET SHIPPEDDATE = '" & [Forms]![ShippingEntry]![SHIPPEDDATE] & "',ShippedQuantity = '" & [Forms]![ShippingEntry]![ShippedQuantity] & "',QTYONHAND = ((PRODUCTION.QTYONHAND)-('" & [Forms]![ShippingEntry]![ShippedQuantity] & "')),PackgingSlipNo = '" & [Forms]![ShippingEntry]![PackgingSlipNo] & "',Notes = '" & [Forms]![ShippingEntry]![Notes] & "',Status = 'TOBESHIPPED' WHERE PalletNo='" & [Forms]![ShippingEntry]![PalletNo_Combo] & "'"

    objADO.Execute (sSQL1)

    Else

    sSQL = "UPDATE PRODUCTION SET SHIPPEDDATE = '" & [Forms]![ShippingEntry]![SHIPPEDDATE] & "',ShippedQuantity = '" & [Forms]![ShippingEntry]![ShippedQuantity] & "',QTYONHAND = ((PRODUCTION.QTYONHAND)-('" & [Forms]![ShippingEntry]![ShippedQuantity] & "')),PackgingSlipNo = '" & [Forms]![ShippingEntry]![PackgingSlipNo] & "',Location = '" & [Forms]![ShippingEntry]![Location_Combo] & "',Notes = '" & [Forms]![ShippingEntry]![Notes] & "',Status = '" & [Forms]![ShippingEntry]![Location_Combo] & "' WHERE PalletNo='" & [Forms]![ShippingEntry]![PalletNo_Combo] & "'"

    objADO.Execute (sSQL)
    End If
    MsgBox "All The Details saved Successfully."

    objADO.Close
    Set objADO = Nothing



    Now its not going in If.every time its going in else only.I think I made some mistake in format

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yup!

    the Dxxxx() parameters live in quotes (and bizarrely Dxxxx is more tricky with numerics than date/string).

    have a go with:

    If (Nz(Forms!ShippingEntry!ShippedQuantity, 0) < DLookup("TTlQuantity", "PRODUCTION", "PalletNo = " & [Forms]![ShippingEntry]![PalletNo_Combo]) Then

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oooops - my parenthesis error:. that should have been:
    If Nz(Forms!ShippingEntry!ShippedQuantity, 0) < DLookup("TTlQuantity", "PRODUCTION", "PalletNo = " & [Forms]![ShippingEntry]![PalletNo_Combo]) Then

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2007
    Posts
    49

    Talking

    If (Nz([Forms]![ShippingEntry]![ShippedQuantity], 0) < (DLookup("val(TTlQuantity)", "PRODUCTION", "PalletNo = '" & [Forms]![ShippingEntry]![PalletNo_Combo] & "'"))) Then


    Finaly I got it.
    Thank You very very much buddy.
    God bless u!

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it actually looks more symmetric with string/date - you just know you must close the ' or the #, but with numerics it always looks odd.

    super that it works for you.
    ...and so it is TV time for me.

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2007
    Posts
    49

    Cool

    Enjoy ur time watching TV.
    U deserve it.
    Same here also I am also going home now.and its play time.
    cya

  15. #15
    Join Date
    Dec 2007
    Posts
    49

    Its Party time.

Posting Permissions

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