By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,072 Members | 1,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,072 IT Pros & Developers. It's quick & easy.

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

100+
P: 129
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).

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.     Dim strSQL2 As String
  3.     Dim Reply As String
  4.     Dim db As Database
  5.     Dim rst As Recordset
  6.     Dim rst2 As Recordset
  7.     Dim SeqItemNo As Integer
  8.  
  9.     DoCmd.SetWarnings False
  10.  
  11.     Set db = CurrentDb()
  12.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLineAdd]![OrderNo] & ") AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
  13.  
  14.     If rst2!PONo > 0 Then '<< intended to be underlined and bold
  15.         rst2.MoveLast
  16.         SeqItemNo = rst2!ItemNo + 1
  17.     rst2.Close
  18.     Else
  19.         SeqItemNo = 1
  20.     End If '<< end of underlined and bold
  21.  
  22.     DoCmd.Save
  23.     DoCmd.GoToRecord , , acFirst
  24.  
  25.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  26.  
  27.     Do
  28.         rst.Edit
  29.         Me.Requery
  30.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  31.         If IsNull(rst![StkID]) Then
  32.             rst![StkID] = 0
  33.         End If
  34.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  35.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  36.         rst!ItemNo = rst!ItemNo + SeqItemNo
  37.         rst.update
  38.         SeqItemNo = SeqItemNo + 1
  39.         rst.MoveNext
  40.     Loop Until rst.EOF
  41.     rst.Close
  42.     Me.Requery
  43.     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.
Oct 3 '08 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hi Al,
Try this:

Expand|Select|Wrap|Line Numbers
  1. If rst2!PONo > 0 Then '<< intended to be underlined and bold 
  2.             rst2.MoveLast
  3.     rst2.Edit
  4.         rst2!ItemNo = rst2!ItemNo + 1
  5.     rst2.Update
  6.            SeqItemNo = rst2!ItemNo  
  7.   Else 
  8.           SeqItemNo = 1 
  9.   End If '<< end of underlined and bold
  10.   rst2.Close
Oct 4 '08 #2

100+
P: 129
Hi Al,
Try this:

Expand|Select|Wrap|Line Numbers
  1. If rst2!PONo > 0 Then '<< intended to be underlined and bold 
  2.             rst2.MoveLast
  3.     rst2.Edit
  4.         rst2!ItemNo = rst2!ItemNo + 1
  5.     rst2.Update
  6.            SeqItemNo = rst2!ItemNo  
  7.   Else 
  8.           SeqItemNo = 1 
  9.   End If '<< end of underlined and bold
  10.   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.

Expand|Select|Wrap|Line Numbers
  1.     Set db = CurrentDb()
  2.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLine]![OrderNo] & ") AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
  3.  
  4.         rst2.MoveLast
  5.     If rst2!PONo > 0 Then
  6.     rst2.Edit
  7.         rst2!ItemNo = rst2!ItemNo + 1
  8.     rst2.Update
  9.         SeqItemNo = rst2!ItemNo
  10.     Else
  11.         SeqItemNo = 1
  12.     End If
  13.     rst2.Close
Within the Line;

Expand|Select|Wrap|Line Numbers
  1. 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;

Expand|Select|Wrap|Line Numbers
  1. 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"?
Oct 6 '08 #3

100+
P: 129
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.

Expand|Select|Wrap|Line Numbers
  1.     Set db = CurrentDb()
  2.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=" & [Forms]![frmPreSOLine]![OrderNo] & ") AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
  3.  
  4.         rst2.MoveLast
  5.     If rst2!PONo > 0 Then
  6.     rst2.Edit
  7.         rst2!ItemNo = rst2!ItemNo + 1
  8.     rst2.Update
  9.         SeqItemNo = rst2!ItemNo
  10.     Else
  11.         SeqItemNo = 1
  12.     End If
  13.     rst2.Close
Within the Line;

Expand|Select|Wrap|Line Numbers
  1. 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;

Expand|Select|Wrap|Line Numbers
  1. 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?
Oct 6 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
Try this:


Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst2 As DAO.Recordset
  3.  
  4. Set db = CurrentDb() 
  5. Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLine]![OrderNo])) ORDER BY ordlin.ItemNo")
  6.  
  7. If  Not (rst2.BOF Or rst2.EOF)  Then
  8.         rst2.MoveLast 
  9.         If rst2!PONo > 0 Then 
  10.            rst2.Edit 
  11.                 rst2!ItemNo = rst2!ItemNo + 1 
  12.            rst2.Update 
  13.             SeqItemNo = rst2!ItemNo 
  14.        Else 
  15.             SeqItemNo = 1 
  16.        End If 
  17. Else
  18.       MsgBox "The recordset is empty"
  19. End If
  20. rst2.Close
  21. Set rst2 = Nothing
  22. Set db = Nothing
Oct 6 '08 #5

100+
P: 129
Try this:


Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst2 As DAO.Recordset
  3.  
  4. Set db = CurrentDb() 
  5. Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLine]![OrderNo])) ORDER BY ordlin.ItemNo")
  6.  
  7. If  Not (rst2.BOF Or rst2.EOF)  Then
  8.         rst2.MoveLast 
  9.         If rst2!PONo > 0 Then 
  10.            rst2.Edit 
  11.                 rst2!ItemNo = rst2!ItemNo + 1 
  12.            rst2.Update 
  13.             SeqItemNo = rst2!ItemNo 
  14.        Else 
  15.             SeqItemNo = 1 
  16.        End If 
  17. Else
  18.       MsgBox "The recordset is empty"
  19. End If
  20. rst2.Close
  21. Set rst2 = Nothing
  22. Set db = Nothing
I receive a different error this time stating "Too Few Parameters"
Oct 6 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
Try changing line 5 by adding nz function as shown below:

Expand|Select|Wrap|Line Numbers
  1. Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=nz([Forms]![frmPreSOLine]![OrderNo],0)) ORDER BY ordlin.ItemNo")
Oct 6 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.