Multi-select listbox: de-selected record not being deleted

[Guest]

I am trying to insert multiple records into a table if more than one employee
has attended a training class via a multi-select list box.
Table: tbl_Main_Table
List box: lst_Emp

The code behind the Save button on the form is as follows:

Private Sub Cmd_Save_Click()
On Error GoTo Err_Cmd_Save_Click

Dim iItem As Integer
Dim lngEmpTraining As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If

Set db = CurrentDb

' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbl_Main_Table", dbOpenDynaset)
With Me!lst_Emp
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngEmpTraining = .Column(0, iItem)

' Determine whether this EmployeeID-TrainingID combination is in
the table
rs.FindFirst "[Class_ID] = " & Me.Class_ID & " AND [Employee_ID]
= " & lngEmpTraining & " AND [Date] = " & Me.Date

If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!Class_ID = Me.Class_ID
rs!Comment = Me.Comment
rs!Date = Me.Date
rs!Hours = Me.Hours
rs!Instructor_ID = Me.Instructor_ID
rs!Employee_ID = lngEmpTraining
rs.Update
End If
' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
rs.Delete ' delete this record if it's been deselected
End If
' if it was selected, leave it alone
End If
Next iItem
End With

rs.Close

Set rs = Nothing
Set db = Nothing

Exit_Cmd_Save_Click:
Exit Sub

Err_Cmd_Save_Click:
MsgBox Err.Description
Resume Exit_Cmd_Save_Click

End Sub


After many long hours, I finally got the code to insert records into the
table, but the logic is not deleting a record that has been de-selected (if
the user selected a name accidentally). Can anyone see what is not working?

Thanks,
Melanie

 

[Nikos Yannacopoulos]

Melanie,

I suspect it has to do with the fact that you are using Date as a field
and control name; Date is an Access reserved keyword (a built-in
function that returns the system date) and it may be that this confuses
your code into looking for a record with the current date instead of the
date in the form control, so rs.NoMatch is always true.
Try changing the field and control name to something else (e.g.,
TrainingDate?) and see if it solves the problem. If not, watch the
values in your variables as the code executes, chances are some value is
not what you expect, so no matching record is found.

HTH,
Nikos

 

[Guest]

Nikos,

Thanks for the response. I went ahead and changed the date field to
"Training_Date." That, however, did not fix the problem. I don't think I'm
having a problem with the rs.NoMatch statement. The records that are
selected are added to the table, but the problem occurs when I select a name
and then de-select that same name: The record is added to the table, but
without the employee ID. So the code below that is supposed to delete the
record if it was de-selected isn't running. Any help would be greatly
appreciated!

Thanks,
Melanie

Melanie,

I suspect it has to do with the fact that you are using Date as a field
and control name; Date is an Access reserved keyword (a built-in
function that returns the system date) and it may be that this confuses
your code into looking for a record with the current date instead of the
date in the form control, so rs.NoMatch is always true.
Try changing the field and control name to something else (e.g.,
TrainingDate?) and see if it solves the problem. If not, watch the
values in your variables as the code executes, chances are some value is
not what you expect, so no matching record is found.

HTH,
Nikos

I am trying to insert multiple records into a table if more than one employee
has attended a training class via a multi-select list box.
Table: tbl_Main_Table
List box: lst_Emp

The code behind the Save button on the form is as follows:

Private Sub Cmd_Save_Click()
On Error GoTo Err_Cmd_Save_Click

Dim iItem As Integer
Dim lngEmpTraining As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If

Set db = CurrentDb

' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbl_Main_Table", dbOpenDynaset)
With Me!lst_Emp
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngEmpTraining = .Column(0, iItem)

' Determine whether this EmployeeID-TrainingID combination is in
the table
rs.FindFirst "[Class_ID] = " & Me.Class_ID & " AND [Employee_ID]
= " & lngEmpTraining & " AND [Date] = " & Me.Date

If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!Class_ID = Me.Class_ID
rs!Comment = Me.Comment
rs!Date = Me.Date
rs!Hours = Me.Hours
rs!Instructor_ID = Me.Instructor_ID
rs!Employee_ID = lngEmpTraining
rs.Update
End If
' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
rs.Delete ' delete this record if it's been deselected
End If
' if it was selected, leave it alone
End If
Next iItem
End With

rs.Close

Set rs = Nothing
Set db = Nothing

Exit_Cmd_Save_Click:
Exit Sub

Err_Cmd_Save_Click:
MsgBox Err.Description
Resume Exit_Cmd_Save_Click

End Sub


After many long hours, I finally got the code to insert records into the
table, but the logic is not deleting a record that has been de-selected (if
the user selected a name accidentally). Can anyone see what is not working?

Thanks,
Melanie

 

[Guest]

I don't think the issue (now) is that the de-selected record is not being
deleted. The code adds the selected records to the recordset with the
correct values, but when the code is done running/the form is closed, one
additional record with no employee ID value is also saved. I'm not sure
where in the code this is occurring. If anyone has any ideas, please let me
know!

Melanie

Melanie,

I suspect it has to do with the fact that you are using Date as a field
and control name; Date is an Access reserved keyword (a built-in
function that returns the system date) and it may be that this confuses
your code into looking for a record with the current date instead of the
date in the form control, so rs.NoMatch is always true.
Try changing the field and control name to something else (e.g.,
TrainingDate?) and see if it solves the problem. If not, watch the
values in your variables as the code executes, chances are some value is
not what you expect, so no matching record is found.

HTH,
Nikos

I am trying to insert multiple records into a table if more than one employee
has attended a training class via a multi-select list box.
Table: tbl_Main_Table
List box: lst_Emp

The code behind the Save button on the form is as follows:

Private Sub Cmd_Save_Click()
On Error GoTo Err_Cmd_Save_Click

Dim iItem As Integer
Dim lngEmpTraining As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If

Set db = CurrentDb

' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbl_Main_Table", dbOpenDynaset)
With Me!lst_Emp
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngEmpTraining = .Column(0, iItem)

' Determine whether this EmployeeID-TrainingID combination is in
the table
rs.FindFirst "[Class_ID] = " & Me.Class_ID & " AND [Employee_ID]
= " & lngEmpTraining & " AND [Date] = " & Me.Date

If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!Class_ID = Me.Class_ID
rs!Comment = Me.Comment
rs!Date = Me.Date
rs!Hours = Me.Hours
rs!Instructor_ID = Me.Instructor_ID
rs!Employee_ID = lngEmpTraining
rs.Update
End If
' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
rs.Delete ' delete this record if it's been deselected
End If
' if it was selected, leave it alone
End If
Next iItem
End With

rs.Close

Set rs = Nothing
Set db = Nothing

Exit_Cmd_Save_Click:
Exit Sub

Err_Cmd_Save_Click:
MsgBox Err.Description
Resume Exit_Cmd_Save_Click

End Sub


After many long hours, I finally got the code to insert records into the
table, but the logic is not deleting a record that has been de-selected (if
the user selected a name accidentally). Can anyone see what is not working?

Thanks,
Melanie

 

[Nikos Yannacopoulos]

Melanie,

Any chance your form is bound to the table while it shouldn't?

Nikos

I don't think the issue (now) is that the de-selected record is not being
deleted. The code adds the selected records to the recordset with the
correct values, but when the code is done running/the form is closed, one
additional record with no employee ID value is also saved. I'm not sure
where in the code this is occurring. If anyone has any ideas, please let me
know!

Melanie

:

Melanie,

I suspect it has to do with the fact that you are using Date as a field
and control name; Date is an Access reserved keyword (a built-in
function that returns the system date) and it may be that this confuses
your code into looking for a record with the current date instead of the
date in the form control, so rs.NoMatch is always true.
Try changing the field and control name to something else (e.g.,
TrainingDate?) and see if it solves the problem. If not, watch the
values in your variables as the code executes, chances are some value is
not what you expect, so no matching record is found.

HTH,
Nikos

I am trying to insert multiple records into a table if more than one employee
has attended a training class via a multi-select list box.
Table: tbl_Main_Table
List box: lst_Emp

The code behind the Save button on the form is as follows:

Private Sub Cmd_Save_Click()
On Error GoTo Err_Cmd_Save_Click

Dim iItem As Integer
Dim lngEmpTraining As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If

Set db = CurrentDb

' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbl_Main_Table", dbOpenDynaset)
With Me!lst_Emp
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngEmpTraining = .Column(0, iItem)

' Determine whether this EmployeeID-TrainingID combination is in
the table
rs.FindFirst "[Class_ID] = " & Me.Class_ID & " AND [Employee_ID]
= " & lngEmpTraining & " AND [Date] = " & Me.Date

If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!Class_ID = Me.Class_ID
rs!Comment = Me.Comment
rs!Date = Me.Date
rs!Hours = Me.Hours
rs!Instructor_ID = Me.Instructor_ID
rs!Employee_ID = lngEmpTraining
rs.Update
End If
' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
rs.Delete ' delete this record if it's been deselected
End If
' if it was selected, leave it alone
End If
Next iItem
End With

rs.Close

Set rs = Nothing
Set db = Nothing

Exit_Cmd_Save_Click:
Exit Sub

Err_Cmd_Save_Click:
MsgBox Err.Description
Resume Exit_Cmd_Save_Click

End Sub


After many long hours, I finally got the code to insert records into the
table, but the logic is not deleting a record that has been de-selected (if
the user selected a name accidentally). Can anyone see what is not working?

Thanks,
Melanie