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.
6 2219
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 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"?
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?
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
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"
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")
Sign in to post your reply or Sign up for a free account.
Similar topics
by: mike420 |
last post by:
I think everyone who used Python will agree that its syntax is
the best thing going for it. It is very readable and easy
for everyone to learn. But, Python does not a have very good
macro...
|
by: Tom |
last post by:
Wta is the code structure to put Rst.FindNext in a loop? I'm using what I show
below. It works fine but my instinct says it should be in some standard loop
rather than using the GoTo structure.
...
|
by: Karl Irvin |
last post by:
My program looks like this:
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Invoice'")
Do some...
|
by: Paul T. Rong |
last post by:
Sorry I just lost tracks of a series of posts, so I post my question again,
hoping someone can help.
Here is codes provided by Jeff Smith
> Private Sub Form_Load()
> Dim db As...
|
by: Tim Marshall |
last post by:
In A2003, I have a bound continuous form (OK, actually a sub form of an
unbound main form, but I don't think that matters here). The record
source consists of an Jet SQL statement that returns one...
|
by: Tim Marshall |
last post by:
Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:
A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several...
|
by: MLH |
last post by:
The SQL string returns 4 records if pasted into a QBE grid and run
manually. But in this DAO setting, I can't seem to get it to retrun
any records. When line #340 executes, error tells me No...
|
by: thosecars82 |
last post by:
I have a module like this:
Option Compare Database
Sub main()
Dim db As Database
Dim rst As Recordset
Dim sSQL As String
Dim sBID As String
Dim sOutputString(1 To 26) As String
|
by: Constantine AI |
last post by:
Hi i have some details below and i want to update the table by inputting the values where i have detailed 'Blank'.
OrderNo StkID StkShortDesc Qty Price
28 Blank 300 DL 1 Blank
28 Blank 600 SA...
|
by: mh |
last post by:
I am really loving the output, and have started using RST for some
of my own docs as well.
It's wonderful and I know it was a lot of work on somebody's part
to think it through and make the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |