473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Skip List Item in For Loop

I have a sub routine that creates records for the selected list items in a multi-select list box.

I would like to check if the record already exists before adding the record to avpid du[licate records.

Is there a way to skip a record in a For Loop if the record already exists?

I tried the code below but get the compile error "Next without for":
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_Save_Click()
  2.  
  3. If MsgBox("Do You Want To Save Your Changes?", vbDefaultButton1 + vbYesNo) = vbYes Then
  4.  
  5.     Dim MyDB As dao.Database
  6.     Dim varItem As Variant
  7.     Dim lst As ListBox
  8.  
  9.     Set lst = Me![list_Techs]
  10.  
  11.     Dim rst As dao.Recordset
  12.     If lst.ItemsSelected.Count = 0 Or IsNull(Me![txt_Date_Assigned]) Then
  13.         MsgBox "There is a problem with date generation, notify your system administrator", vbExclamation, "Date Generation Error"
  14.     Exit Sub
  15.     End If
  16.  
  17.     Set MyDB = CurrentDb
  18.     Set rst = MyDB.OpenRecordset("tbl_Tech_Assignments", dbOpenDynaset, dbAppendOnly)
  19.  
  20.     With rst
  21.         For Each varItem In lst.ItemsSelected
  22.  
  23.             If DLookup("Tech_ID", "tbl_Tech_Assignments", "Tech_ID = lst.ItemData(varItem)") > 0 Then
  24.                 Next varItem
  25.                 End If
  26.             Else
  27.             End If
  28.  
  29.             .AddNew
  30.                 ![Tech_ID] = lst.ItemData(varItem)
  31.                 ![Date_Assigned] = Me![txt_Date_Assigned]
  32.                 ![WO_ID] = Me![txt_WO_ID]
  33.             .Update
  34.         Next varItem
  35.     End With
  36.  
  37.     rst.Close
  38.     Set rst = Nothing
  39.     Forms!frm_Edit_WO!Date_Assigned = txt_Date_Assigned
  40.     Forms!frm_Edit_WO!lu_Status = "Assigned"
  41.     DoCmd.Close
  42.     Forms![frm_Edit_WO].Requery
  43.  
  44. Else
  45.     DoCmd.Close
  46.  
  47. End If
  48. End Sub
Thanks in advance.
Feb 20 '12 #1
3 2148
NeoPa
32,556 Expert Mod 16PB
You should probably know by now not to post code in that state, and specially without a full error report including error message and line number. See Before Posting (VBA or SQL) Code.

In this case, luckily, this problem is simple enough. You're trying to use the Next statement conditionally, which is syntactically incorrect. VBA is limited there, in that you have no good way of skipping an iteration through a loop. You need to run the record addition code conditionally instead.
Feb 20 '12 #2
NeoPa,

Thanks for your reply amd My appologies for ommitting the Code Tags. I solved the problem using a subquery to filter out the choices already used instead.
Feb 21 '12 #3
NeoPa
32,556 Expert Mod 16PB
ScottBouley:
My apologies for omitting the Code Tags
I was thinking more of the fact that the code wasn't compiled (I should have made that clearer - doh!). If you haven't seen it before, Before Posting (VBA or SQL) Code gives some tips on how to avoid wasting time with code that can easily be fixed before posting. It's worth it for you to look through it, as it includes general advice that is likely to be helpful to you, as well as saving time here dealing with non-issues.
ScottBouley:
I solved the problem using a subquery to filter out the choices already used instead.
That's a better overall solution. My response was directed more at the evident problem with the code.
Feb 21 '12 #4

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

Similar topics

0
by: Peter Otten | last post by:
There is currently a discussion on python-dev about an extract() function which offers fast access to list items and object attributes. It could serve, e. g., as the key argument of the future...
8
by: dp | last post by:
Is there anyway to have the bullet color of a <li> be a different color than the text without using an image? dp
24
by: Robin Cole | last post by:
I'd like a code review if anyone has the time. The code implements a basic skip list library for generic use. I use the following header for debug macros: /* public.h - Public declarations and...
2
by: James | last post by:
How would I skip an item in a foreach loop without breaking out of the loop altogether? foreach(SearchResult resCol1 in resCol) { string myString = resCol1.Properties.ToString(); if...
2
by: Chris | last post by:
I was wondering if there was any way to add a blank list item control to the beginning of the System.Web.UI.WebControls.DropDownList's datasource after the control's datasource has been specified....
1
by: Alex K. | last post by:
Hi all How do I catch an event when list item is deleted from ListBox? Is there such event? Thank you Alex
4
by: SM | last post by:
Hello, I have an unordered list similar to this one: <ul id=list> <li onclick="addParagraph(0)">Item 1</li> <li onclick="addParagraph(1)">Item 2</li> <li onclick="addParagraph(2)">Item...
4
by: thomas.pohl | last post by:
Hi, let's assume you want to nicely print the content of a list except for one (or some) individual item. You could do it like this: t = print("text: %s\nvalues: %i %i %i" % (t, t, t, t)) ...
9
by: hooijdonk | last post by:
Hi, using a form I have been trying to get a list box to display the values of database table and have two options on there. a) the user can select a list item and click a button to delete that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.