help with the IF clause to update query 2

[davyre]

Hi,
I got this query and IF clause. What I want is first to check OrderID and UnitID from TblOrderUnit. If both OrderID and UnitID are exist, the strSQL should just update the QtyOrdered. If not exist, then strSQL should insert the record into the
TblOrderUnit.
The problem I am facing right now is if the OrderID and UnitID are exist, it DOES update the old QtyOrdered , but ALSO create new record with same OrderID and UnitID in TblOrderUnit . Why is this happening?
Also, because I use recordset to copy all the data from TblOrderUnit and I use tblOrderUnitRS.MoveFirst, the trouble comes when I create new order and there is no records in the TblOrderUnit (new database). Any solution to do this (without using recordset maybe)?
THanks!

qryTblOrderUnit = "SELECT * FROM TblOrderUnit"
Set tblOrderUnitRS = dbs.OpenRecordset(qryTblOrderUnit)
tblOrderUnitRS.MoveFirst

strSQL = "INSERT INTO TblOrderUnit([OrderunitID],[OrderID],[UnitID],[QtyOrdered],[UnitDeliveryDate])" _
           & " VALUES (" & OrderunitID & ", " & OrderID & ", " & UnitID & ", " & QtyOrdered & ", #" & DeliveryDate & "# )"
           
Do While tblOrderUnitRS.EOF = False
    If tblOrderUnitRS!OrderID = OrderID And tblOrderUnitRS!UnitID = UnitID Then
        strSQL = "UPDATE TblOrderUnit SET QtyOrdered=QtyOrdered+" & QtyOrdered & " WHERE OrderID=" & OrderID & " AND UnitID=" & UnitID
        Exit Do
    End If
    tblOrderUnitRS.MoveNext
Loop


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

 

[davyre]

Ok, I got the solution for the DO WHILE clause

Do While tblOrderUnitRS.EOF = False
    If tblOrderUnitRS!OrderID = OrderID And tblOrderUnitRS!UnitID = UnitID Then
        strSQL = "UPDATE TblOrderUnit SET QtyOrdered=QtyOrdered+" & QtyOrdered & " WHERE OrderID=" & OrderID & " AND UnitID=" & UnitID
        Exit Do
    Else
        [highlight #AD7FA8]tblOrderUnitRS.MoveNext[/highlight]
    End If
Loop

But still I get error when I flush all the data (no record on tblOrderUnit) thus showing error No Current Record when the Access executing the line

tblOrderUnitRS.MoveFirst

How should I address this? How can I use OnError GoTo? Thanks!

 

[PHV]

What about this ?

strWhere = "OrderID=" & OrderID & " AND UnitID=" & UnitID
If DLookup("OrderID", "TblOrderUnit", strWhere) Then
    strsql = "UPDATE TblOrderUnit SET QtyOrdered=QtyOrdered+" & QtyOrdered & " WHERE " & strWhere
Else
    strsql = "INSERT INTO TblOrderUnit(OrderunitID,OrderID,UnitID,QtyOrdered,UnitDeliveryDate)" _
           & " VALUES (" & OrderunitID & "," & OrderID & "," & UnitID & "," & QtyOrdered & ",#" & DeliveryDate & "#)"
End If
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Try this

qryTblOrderUnit = "SELECT * FROM TblOrderUnit " & _
                  "WHERE OrderID=" & OrderID & " AND UnitID=" & UnitID

Set tblOrderUnitRS = dbs.OpenRecordset(qryTblOrderUnit)

With tblOrderUnitRS

    If .EOF and .BOF Then

       strSQL = "INSERT INTO TblOrderUnit([OrderunitID],[OrderID],[UnitID],[QtyOrdered],[UnitDeliveryDate])" _
               & " VALUES (" & OrderunitID & ", " & OrderID & ", " & UnitID & ", " & QtyOrdered & ", #" & DeliveryDate & "# )"

    Else
           
       strSQL = "UPDATE TblOrderUnit SET QtyOrdered=QtyOrdered+" & QtyOrdered & " WHERE OrderID=" & OrderID & " AND UnitID=" & UnitID

    End If

End With
Set tblOrderUnitRS = Nothing

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

 

[PHV]

OOps, sorry for the typo:
If DLookup("OrderID", "TblOrderUnit", strWhere)[!] = OrderID[/!] Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

your solution, PHV, that means I do not need to use recordset? So you directly looking to the table using DLookUp?

 

[PHV]

Yes.

 

[davyre]

thanks PHV and Golom, now I got this working.
Just a quick question because this gave me error, how to make autonumber textbox generate number when you press the button?what is the code?
the form has this textbox that linked to the TblOrderUnit.OrderunitID. It is already set to autonumber, so I cannot change the value in the textbox. But when I press the button to execute the
code above, it shows syntax error INSERT INTO statement, and I suspect the OrderunitID is the one behind this error. Any suggestion? Thanks

 

[PHV]

Don't insert the autonumber:

    strsql = "INSERT INTO TblOrderUnit(OrderID,UnitID,QtyOrdered,UnitDeliveryDate)" _
       & " VALUES (" & OrderID & "," & UnitID & "," & QtyOrdered & ",#" & DeliveryDate & "#)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Gee, how easy the solution is! Silly me. THanks a lot PHV!