If rst!PONo > 0 Then Do this Else this Problem

[Constantine AI]

Hi i am trying to add a little piece of code which tells the form which number to look at; either the one from the rst or start from scratch which is 1. I have the following code (you just need to look at the underline and bold bit [moderator edit: lines 14 to 20] as the rest works fine).

    Dim strSQL As String
    Dim strSQL2 As String
    Dim Reply As String
    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim SeqItemNo As Integer
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLineAdd]![OrderNo] & ") AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
 
     If rst2!PONo > 0 Then '<< intended to be underlined and bold
        rst2.MoveLast
        SeqItemNo = rst2!ItemNo + 1
    rst2.Close
    Else
        SeqItemNo = 1
    End If '<< end of underlined and bold
 
    DoCmd.Save
    DoCmd.GoToRecord , , acFirst
 
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        Me.Requery
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        If IsNull(rst![StkID]) Then
            rst![StkID] = 0
        End If
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = rst!ItemNo + SeqItemNo
        rst.update
        SeqItemNo = SeqItemNo + 1
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing

At present the code works with either of them but not when i applied the if statement. Could anyone suggest how to write this properly. At the minutes with the code i receive no error or message. It does nothing! Thanks in advance.

[puppydogbuddy]

Hi Al,
Try this:

If rst2!PONo > 0 Then '<< intended to be underlined and bold 
            rst2.MoveLast
    rst2.Edit
        rst2!ItemNo = rst2!ItemNo + 1
    rst2.Update
           SeqItemNo = rst2!ItemNo  
  Else 
          SeqItemNo = 1 
  End If '<< end of underlined and bold
  rst2.Close

[Constantine AI]

Hi Al,
Try this:

If rst2!PONo > 0 Then '<< intended to be underlined and bold 
            rst2.MoveLast
    rst2.Edit
        rst2!ItemNo = rst2!ItemNo + 1
    rst2.Update
           SeqItemNo = rst2!ItemNo  
  Else 
          SeqItemNo = 1 
  End If '<< end of underlined and bold
  rst2.Close

Hi Puppydogbuddy, thanks for your help so far, i have tried this code and i am pretty sure it will would work however i think i have noticed a glitch with the code.

    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLine]![OrderNo] & ") AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
 
        rst2.MoveLast
    If rst2!PONo > 0 Then
    rst2.Edit
        rst2!ItemNo = rst2!ItemNo + 1
    rst2.Update
        SeqItemNo = rst2!ItemNo
    Else
        SeqItemNo = 1
    End If
    rst2.Close

Within the Line;

Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLine]![OrderNo] & ") AND ((ordlin.ItemNo)>0))  ORDER BY ordlin.ItemNo")

If the PONo is 0 which i tried testing with i received a message stating "No Current Record" and i think it is because of the bold text i have highlighted within the line. As i have told the rst2 statement to pickup PONo > 0. I think this explains the error message i receive. However i have now tried many statements now within the > 0 part but included the OrderBy ItemNo part. I receive another error stating "Too Few Parameters Expected 1". The code i have tried is as follows;

Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLine]![OrderNo]))ORDER BY ordlin.ItemNo")

Can you see what is wrong with this line in response to receiving "Too Few Parameters Error"?

[Constantine AI]

Hi Puppydogbuddy, thanks for your help so far, i have tried this code and i am pretty sure it will would work however i think i have noticed a glitch with the code.

    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLine]![OrderNo] & ") AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
 
        rst2.MoveLast
    If rst2!PONo > 0 Then
    rst2.Edit
        rst2!ItemNo = rst2!ItemNo + 1
    rst2.Update
        SeqItemNo = rst2!ItemNo
    Else
        SeqItemNo = 1
    End If
    rst2.Close

Within the Line;

Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLine]![OrderNo] & ") AND ((ordlin.ItemNo)>0))  ORDER BY ordlin.ItemNo")

If the PONo is 0 which i tried testing with i received a message stating "No Current Record" and i think it is because of the bold text i have highlighted within the line. As i have told the rst2 statement to pickup PONo > 0. I think this explains the error message i receive. However i have now tried many statements now within the > 0 part but included the OrderBy ItemNo part. I receive another error stating "Too Few Parameters Expected 1". The code i have tried is as follows;

Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLine]![OrderNo]))ORDER BY ordlin.ItemNo")

Can you see what is wrong with this line in response to receiving "Too Few Parameters Error"?

I managed to fix that little bit however all i receive is "No Current Record", I know there would be no record if its new but why can't it process the ELSE part of the statement instead?

[puppydogbuddy]

Try this:

Dim db As DAO.Database
Dim rst2 As DAO.Recordset
 
Set db = CurrentDb() 
Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLine]![OrderNo])) ORDER BY ordlin.ItemNo")
 
If  Not (rst2.BOF Or rst2.EOF)  Then
        rst2.MoveLast 
        If rst2!PONo > 0 Then 
           rst2.Edit 
                rst2!ItemNo = rst2!ItemNo + 1 
           rst2.Update 
            SeqItemNo = rst2!ItemNo 
       Else 
            SeqItemNo = 1 
       End If 
Else
      MsgBox "The recordset is empty"
End If
rst2.Close
Set rst2 = Nothing
Set db = Nothing

[Constantine AI]

Try this:

Dim db As DAO.Database
Dim rst2 As DAO.Recordset
 
Set db = CurrentDb() 
Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLine]![OrderNo])) ORDER BY ordlin.ItemNo")
 
If  Not (rst2.BOF Or rst2.EOF)  Then
        rst2.MoveLast 
        If rst2!PONo > 0 Then 
           rst2.Edit 
                rst2!ItemNo = rst2!ItemNo + 1 
           rst2.Update 
            SeqItemNo = rst2!ItemNo 
       Else 
            SeqItemNo = 1 
       End If 
Else
      MsgBox "The recordset is empty"
End If
rst2.Close
Set rst2 = Nothing
Set db = Nothing

I receive a different error this time stating "Too Few Parameters"

[puppydogbuddy]

Try changing line 5 by adding nz function as shown below:

Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=nz([Forms]![frmPreSOLine]![OrderNo],0)) ORDER BY ordlin.ItemNo")