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

Do Until Loop Help!

100+
P: 129
Hello, i am wanting a Loop procedure to check details of all the multiple rows with the following D-LOOKUP procedures;
Expand|Select|Wrap|Line Numbers
  1. [*]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:
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2. Dim rst As Recordset
  3. Dim Reply As String
  4. Dim strSQL As String
  5.  
  6. Set dbs = CurrentDb
  7. Set rst = dbs.OpenRecordset("SELECT * FROM preordlin")
  8. strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  9.  
  10. Do Until rst.EOF
  11.     StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  12.     Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  13.     rst.MoveNext
  14. Loop
  15.  
  16. If IsNull(StkID) Then
  17.     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!")
  18.     If Reply = vbYes Then
  19.         DoCmd.RunSQL strSQL
  20.     Else
  21.     End If
  22. Else
  23. 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
Aug 10 '08 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,638
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:

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim Reply As String
  4. Dim strSQL As String
  5.  
  6. Reply = "There are Stock Items that present that do not belong to our " & _
  7.         "existing Stock files. Would you like to ADD then?"
  8.  
  9. Set dbs = CurrentDb
  10. Set rst = dbs.OpenRecordset("SELECT * FROM preordlin")
  11. strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT " & _
  12.          "[StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  13.  
  14. Do Until rst.EOF
  15.   StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  16.   Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  17.     rst.MoveNext
  18. Loop
  19.  
  20. If IsNull(StkID) Then
  21.   Reply = MsgBox(Reply, vbYesNo, "None Existent Stock Details!")
  22.   If Reply = vbYes Then
  23.     DoCmd.RunSQL strSQL
  24.   Else
  25.   End If
  26. Else
  27. End If
Aug 10 '08 #2

NeoPa
Expert Mod 15k+
P: 31,492
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.
Aug 11 '08 #3

Post your reply

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