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
- Private Sub cmd_Save_Click()
- If MsgBox("Do You Want To Save Your Changes?", vbDefaultButton1 + vbYesNo) = vbYes Then
- Dim MyDB As dao.Database
- Dim varItem As Variant
- Dim lst As ListBox
- Set lst = Me![list_Techs]
- Dim rst As dao.Recordset
- If lst.ItemsSelected.Count = 0 Or IsNull(Me![txt_Date_Assigned]) Then
- MsgBox "There is a problem with date generation, notify your system administrator", vbExclamation, "Date Generation Error"
- Exit Sub
- End If
- Set MyDB = CurrentDb
- Set rst = MyDB.OpenRecordset("tbl_Tech_Assignments", dbOpenDynaset, dbAppendOnly)
- With rst
- For Each varItem In lst.ItemsSelected
- If DLookup("Tech_ID", "tbl_Tech_Assignments", "Tech_ID = lst.ItemData(varItem)") > 0 Then
- Next varItem
- End If
- Else
- End If
- .AddNew
- ![Tech_ID] = lst.ItemData(varItem)
- ![Date_Assigned] = Me![txt_Date_Assigned]
- ![WO_ID] = Me![txt_WO_ID]
- .Update
- Next varItem
- End With
- rst.Close
- Set rst = Nothing
- Forms!frm_Edit_WO!Date_Assigned = txt_Date_Assigned
- Forms!frm_Edit_WO!lu_Status = "Assigned"
- DoCmd.Close
- Forms![frm_Edit_WO].Requery
- Else
- DoCmd.Close
- End If
- End Sub