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

Skip List Item in For Loop

P: 30
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
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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

P: 30
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
Expert Mod 15k+
P: 31,186
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

Post your reply

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