Hello, i am wanting a Loop procedure to check details of all the multiple rows with the following D-LOOKUP procedures; - [*]StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")[*]Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
For the Loop procedure to work i have used the following code: - Dim dbs As Database
-
Dim rst As Recordset
-
Dim Reply As String
-
Dim strSQL As String
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset("SELECT * FROM preordlin")
-
strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
-
-
Do Until rst.EOF
-
StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
-
Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
-
rst.MoveNext
-
Loop
-
-
If IsNull(StkID) Then
-
Reply = MsgBox("There are Stock Items that present that do not belong to our existing Stock files. Would you like to ADD then?", vbYesNo, "None Existent Stock Details!")
-
If Reply = vbYes Then
-
DoCmd.RunSQL strSQL
-
Else
-
End If
-
Else
-
End If
The only problem is when i click on the button to execute this procedure, it works but only works on 1 row at a time and you have to keep clicking the button on all the rows of data. Is there a way that does it for all of them at once?
Please help!
Thanks in advance.
Chris
2 3141
Just subscribing, I took the liberty of rewriting your code for the sake of clarity. I realize that you are new to this, but in the future, kindly use the Code Tags for obvious reasons. I really don't have the time now to help you with a Reply to this Thread, but I'll check back later and in the meantime, I'm sure someone will provide you with assistance. - StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
- Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
'For the Loop procedure to work i have used the following code: - Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim Reply As String
-
Dim strSQL As String
-
-
Reply = "There are Stock Items that present that do not belong to our " & _
-
"existing Stock files. Would you like to ADD then?"
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset("SELECT * FROM preordlin")
-
strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT " & _
-
"[StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
-
-
Do Until rst.EOF
-
StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
-
Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
-
rst.MoveNext
-
Loop
-
-
If IsNull(StkID) Then
-
Reply = MsgBox(Reply, vbYesNo, "None Existent Stock Details!")
-
If Reply = vbYes Then
-
DoCmd.RunSQL strSQL
-
Else
-
End If
-
Else
-
End If
NeoPa 32,556
Expert Mod 16PB
It's not clear what you're trying to achieve, but if you look at lines #10 through #14 (Do Until ... Loop) you will see that they assign values to a couple of items which are neither explained nor referred to elsewhere.
As the first thing you do after assigning them once is to assign them again with (presumably) different values, it's clear to see that this loop is not doing anything for you. If these objects are form controls then there will be no time to see them before they disappear again. Only the last record will remain visible. If they are intended to reference fields in a recordset somewhere (they don't) then apart from that they are never written away anyway (No rst.Edit & rst.Update are ever called).
Hopefully these pointers will help you find your problems. I'm afraid your question is otherwise too unclear to help further.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Eric |
last post by:
Let me start off by saying I have VB working model which means no help. I
have searched MSDN. It seems that I get "Read the help" instead of answers
which is totally useless to me. With that...
|
by: .Net Sports |
last post by:
I am trying to display records from a recordset after sql statement:
<% sqlstr ="SELECT horsename FROM tblhorseentry WHERE trackname = '" &
request.querystring("trackname") & "' and racedate =...
|
by: MLH |
last post by:
Take a look at the code that follows. Line 110 is the beginning
of Do-Loop. Regarding line #220, I find that I'm getting Error #3021
(No Current Record) during execution of line #230. It puzzles me...
|
by: jcrouse |
last post by:
I am having problems with a Label_Paint event causing a continuous loop.
Here is an explanation of the code.
I right click on a label and a context menu pops up.
I then select a menu...
|
by: shannon |
last post by:
Hello,
I am wondering if a Do until loop can be used in Javascript. I have an
array and want to fill the array 10 times with the users details until
it reaches 10 or if they press cancel. I'm...
|
by: abitlikehomer |
last post by:
Hi i am writing a bit of code so that the date and time of a booking can not be booked twice but i am getting an error message saying "loop without do". Any suggestions?
setflag = False
Do Until...
|
by: Madhavi |
last post by:
Hi
Is there any Do Until Loop in C#
Maadhavi
|
by: SyGC |
last post by:
Hi People,
Im trying to do a simple Loop where by an IP address is pinged (Using My.computer.network.ping) and the results, true or false, are used to invoke another line of code.
Basically if...
|
by: dbrother |
last post by:
Access 2003 Win XP Pro SP3
Using SQL /ADO Recordsets in a Do Loop
Hello,
I'm using a random number generator based on an integer input from a user from a form that will get X number of random...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
| |