VBA Update Table Field Based on Multi-Select Listbox

[billk]

I've been struggling with this for a bit now. I'm trying to create a Freeday program for our HR department. I have the code figured out how to use a multi-Select List box to add new earned freedays to the tblFreedays. However I need to also update the earned hours based on the multi-select listbox. I attached the database to this posting. Below the list box is an add button that I would like to have it update the tblFreedays date earned(which is working) and also increment the daysearned and daysavailable field in the tblEmp(this isn't working) I added a cmdbutton for Update Hours to practice writing a code to make this happen. Any help would greatly be accepted.

Thanks in advanced

[pbaldy]

Haven't looked at the sample, but within the standard multiselect loop you can run an update query:

CurrentDb.Execute "UPDATE TableName SET FieldName = 123 WHERE KeyField = " & Me.ListboxName.ItemData(varItem)

Presumably you'd want to concatenate in some value as well.

[billk]

Haven't looked at the sample, but within the standard multiselect loop you can run an update query:

CurrentDb.Execute "UPDATE TableName SET FieldName = 123 WHERE KeyField = " & Me.ListboxName.ItemData(varItem)

Presumably you'd want to concatenate in some value as well.

Thanks, I got it figured out. I attached a new working rev of the database.

Regards,

[pbaldy]

Glad you got it working. Welcome to the site by the way! A lot of people are reluctant to download attachments, and a lot can't due to work restrictions, so it might be helpful to them if you post the relevant code.

[billk]

Glad you got it working. Welcome to the site by the way! A lot of people are reluctant to download attachments, and a lot can't due to work restrictions, so it might be helpful to them if you post the relevant code.

Private Sub cmdAdd_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var As Variant
Dim varAdd As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFreeDay")

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

For Each var In Me.lstEmployees.ItemsSelected
With rs
.AddNew
.Fields("EmpName") = CLng(Me.lstEmployees.Column(0, var))
txtDateHolder.SetFocus
.Fields("DateEarned") = Me.txtDateHolder.Text
.Update

End With
Next

MsgBox "Dates Updated successfully...", vbInformation

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

Set db = CurrentDb
Set rsAdd = db.OpenRecordset("tblEmp")

For Each varAdd In Me.lstEmployees.ItemsSelected
db.Execute ("UPDATE tblEmp SET [DaysEarned] = [DaysEarned]+1 WHERE [tblEmp.EmpName] = '" & Me.lstEmployees.Column(1, varAdd) & "'")
db.Execute ("UPDATE tblEmp SET [DaysAvailable] = [DaysAvailable]+1 WHERE [tblEmp.EmpName] = '" & Me.lstEmployees.Column(1, varAdd) & "'")

Next
Me.lstEmployees.Requery
MsgBox "Hours Updated Successfully...", vbInformation
Forms!frmEmp.Refresh


rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

[pbaldy]

A couple of thoughts.

You shouldn't need to set focus to your form control. Just drop the .Text from the reference (.Value is the default, and it's what you want anyway).

You should be able to combine the two SQL statements:

...SET [DaysEarned] = [DaysEarned]+1, [DaysAvailable] = [DaysAvailable]+1 WHERE...

As a general thought, I would probably not store the earned and available that way. It's sort of like inventory really. I'd have a transaction table where I have a record each time I earned a day, and a record when I used a day (or days). My days available is the net of the two, just as my inventory quantity on hand is the net of my purchases less sales. With your method, sooner or later somebody is going to complain that their balance is off, and you have no way to document it other than trying to go back and manually recalculate it. What if that recalculation doesn't jive with the balance in the table?

[krjoshi]

where I can find the attachment?

[pbaldy]

I believe the forum software automatically deletes attachments after a certain amount of time to save space. This thread being over 4 years old, that's likely what happened.