Select All not working

[JimLes]

I have a combobox called SelectDate on a form that dictates which date should be updated in a multi select listbox. I am using the select case to use the values in the combo box as you can see in the code below. This code works perfectly if I manually select the items in the list box but I want a select all button to process all items selected. I have code below for a select all button that will select all the items in the list box but my code to process the dates states that noting is selected. Is it my SelectAll button code or my DateProcessed code??? It worked fine before I used the Select Case method.

SelectAll button code:
Private Sub cmdSelectAll_Click()
If cmdSelectAll.Caption = "Select All" Then
Call ListBoxSelect(True)
cmdSelectAll.Caption = "UnSelect All"
Else
Call ListBoxSelect(False)
cmdSelectAll.Caption = "Select All"
End If
End Sub

Private Sub ListBoxSelect(boo As Boolean)
Dim intCounter As Integer
For intCounter = 0 To Me.EEList.ListCount - 1
Me.EEList.Selected(intCounter) = boo
Next intCounter
End Sub


DateProcess code that processes dates in listbox based on combobox selection.

Private Sub MarkAsSent_Click()
Dim db As DAO.Database
Dim var As Variant
Set db = CurrentDb

On Error GoTo Err_MarkAsSent_Click

If IsNull(Me.txtDateHolder) Then
MsgBox "No Date selected...", vbExclamation
End
End If


Select Case SelectDate.Value

Case "Date Sent to HR"
If IsNull(EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Sent HR]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next

Case "Date HR Verified"
If IsNull(EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date HR Verified]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next

Case "Date Sent to Accounting"
If IsNull(EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Sent ACCT]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next

Case "Date Processed"
If IsNull(EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Processed]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next

Case "Date Letter was Signed"
If IsNull(EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Letter Signed]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next

Case "Date Sent for IC"
If IsNull(ICList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.ICList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Sent IC]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.ICList.Column(0, var) & "'")
Next

Case Else


End Select

Call ListBoxSelect(False)
Call ListBoxSelect2(False)
cmdSelectAll.Caption = "Select All"
cmdSelectAll2.Caption = "Select All"
MsgBox "Dates updated successfully...", vbInformation


Me.EEList.Requery
Me.ICList.Requery
Forms!frmSeverance.Refresh


db.Close

Set db = Nothing

Exit_MarkAsSent_Click:
Exit Sub

Err_MarkAsSent_Click:
MsgBox Err.Description
Resume Exit_MarkAsSent_Click

End Sub

 

[kjv1611]

Just to see, have you tried looking at the SelectDate without referencing "Value" or else look at SelectDate.Text just to see if that gives you any difference?

Also, have you tried using the Immediate Window and some Debug.Print statements to see what's going on throughout the code?

 

[JimLes]

Well, I did find out it was because of my nested "if's" to test whether an item was selected in the listboxe. When I removed them, the select all worked fine and the code updated the date properly. I have tried this variation, but no luck......With the If statements at the beginning of the code before the Select Case, it still keeps telling me that no record is selected. Any ideas???



Private Sub MarkAsSent_Click()
Dim db As DAO.Database
Dim var As Variant
Set db = CurrentDb

On Error GoTo Err_MarkAsSent_Click

'Test to make sure date is populated and records are selected in list boxes
If IsNull(Me.txtDateHolder) Then
MsgBox "No Date selected...", vbExclamation
End
End If


If Me.SelectDate.Value = "Date Sent to HR" Then
If IsNull(Me.EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
If Me.SelectDate.Value = "Date HR Verified" Then
If IsNull(Me.EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
ElseIf Me.SelectDate.Value = "Date Sent to Accounting" Then
If IsNull(Me.EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
ElseIf Me.SelectDate.Value = "Date Processed" Then
If IsNull(Me.EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
ElseIf Me.SelectDate.Value = "Date Letter was Signed" Then
If IsNull(Me.EEList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
ElseIf Me.TextBox2.Text = "Date Sent for IC" Then
If IsNull(Me.ICList.Column(0)) Then
MsgBox ("No record selected...")
End
End If
End If

'Process date in list tables based on selection in SelectDate combobox

Select Case SelectDate.Value

Case "Date Sent to HR"
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Sent HR]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next

 

[JimLes]

Okay, so I figured out what was happening, my if statement was wrong to test the list box items. It should have been:

If Me.EEList.ItemsSelected.Count = 0 Then
MsgBox ("No record selected...")

NOW, the code runs but I am getting an error:
"Another user edited this record and saved the changes before you attempted to save your changes"

Any ideas to why this is happening. I thought requering the listboxes at the end of the code ane refreshing the form would avoid this.

Here is my current code. I did not list all of the cases.


Private Sub MarkAsSent_Click()
Dim db As DAO.Database
Dim var As Variant
Set db = CurrentDb

On Error GoTo Err_MarkAsSent_Click

'Test to make sure date is populated and records are selected in list boxes
If IsNull(Me.txtDateHolder) Then
MsgBox "No Date selected...", vbExclamation
End
End If


'Process date in list tables based on selection in SelectDate combobox

Select Case SelectDate.Value

Case "Date Sent to HR"
If Me.EEList.ItemsSelected.Count = 0 Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.EEList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Sent HR]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.EEList.Column(0, var) & "'")
Next


Case "Date Sent for IC"
If Me.ICList.ItemsSelected.Count = 0 Then
MsgBox ("No record selected...")
End
End If
For Each var In Me.ICList.ItemsSelected
db.Execute ("UPDATE tblSeveranceData SET [Date Sent IC]=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.ICList.Column(0, var) & "'")
Next

Case Else
End Select

Me.EEList.Requery
Me.ICList.Requery
Forms!frmSeverance.Refresh

Call ListBoxSelect(False)
Call ListBoxSelect2(False)
cmdSelectAll.Caption = "Select All"
cmdSelectAll2.Caption = "Select All"
MsgBox "Dates updated successfully...", vbInformation

db.Close

Set db = Nothing

Exit_MarkAsSent_Click:
Exit Sub

Err_MarkAsSent_Click:
MsgBox Err.Description
Resume Exit_MarkAsSent_Click

End Sub

 

[JimLes]

As long as I am not on the current record of my form that matches anyone in the list box, the code works but if I am on one of those records, then I get the message:

"Another user edited this record and saved the changes before you attempted to save your changes"

The only I found was to requery my form but that takes me back to the first record.

Any ideas? I am stuck!!

 

[JimLes]

Well, after trying very different things, I think I have found a solution to this error. It seems like a crapy way to do it but it works. I noticed that if I run the code and then click on a different tab of my form and then go back, I would not get this error so I simply used the DoCmd.GoToControl in VBA to toggle to the previous tab of my form and then back.

Good thing is that the user can't see it. Oh well, if it works..............