Can I Update Record with MultiList

[denileigh]

Can I Update Record with MultiList???

I have timecards set up in my database and they work great however, if we are paying holiday pay, etc. I have to create one for each employee just to enter the holiday time.

Is there anyway to have a form with a multi list box on one side the time card fields on the other so I can select multi (or all) employees, enter the date and 8 hours in the holday field and have it update all of them?

My tables are set up like this:

TIMECARD
TimeCardID
EmployeeID
WorkDate

TIMECARDHOURS
TimeCardDetailID
TimeCardID
WorkOrderID
RegHrs
OTHrs
VacHrs
HolidayHrs
BereavementHrs
OtherHrs

and of course I have the employees table?

Can this be done?

THANKS!

 

[WayneRyan]

denileigh,

What's the WorkOrderID for a holiday?

Doesn't EVERYONE get the holiday?

Your tables make it look like there
is only one timecard entry per employee for
the week. If that's the case and everyone
gets the holiday then:

Update TimeCardHours
Set HolidayHrs = 8
Where TimeCardID In (Select TimeCardID
                     From   TimeCard
                     Where WorkDate = #SomeDate#)

If they have multiple entries per week, then
it is an insert:

Insert Into TimeCardHours (TimeCardID, 
       WorkOrderID,   RegHrs,     OTHrs,
       VacHrs,        HolidayHrs, BereavementHrs,
       OtherHrs
Select TimeCardID, 0, 0, 0, 0, 8, 0, 0
From   TimeCard
Where  WorkDate = #SomeDate#

Neither of those are very scary, but if only some
of them get the holiday then you need a listbox.
Along with the listbox comes the code to extract the
employees names and make the entries.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set dbs = CurrentDb

For i = 0 to Me.MyListBox.ItemCount - 1
   If Me.MyListBox.ItemSelected = True Then
      sql = "Select * from TimeCardHours " & _
            "Where TimeCardID = (Select TimeCardID
            "                    From   TimeCard
            "                    Where  WorkDate = #SomeDate# And
                                        EmployeeID = " & Me.MyListBox.ItemData(i) & ")"
      set rst = dbs.OpenRecordSet(sql)
      rst.AddNew
      rst!HolidayHrs = 8
      rst.Update
      Set rst = Nothing
   End If
   Next i

Wayne

 

[dcx693]

This thread might be helpful: Form question

 

[denileigh]

THANKS!!

I downloaded it and am in the process of trying to figure all that out now. Looks like what I might need....here's a sample of the form I need to create.

I just threw some fields in there to give you an example. The TimeCardID and TimeCardHoursID of course are auto generated and I need it to take the date which is in the TimeCards Table and the holiday hours (in the time card hours table) and generate a new time card with tthat time in it for each employee selected from the list.

Be gentle with me....I am still very much a novice at all of this.

 

[dcx693]

I think the database you downloaded has the code to update tables based on selections from the multiselect box. WayneRyan's post also has code in it that does something similar.

What the code needs to do is go through every selected entry in the multiselect listbox, take the Date you entered, the hours you entered, and create a new record based on that info (of course, first checking that the entry doesn't already exist).

 

[denileigh]

Exactly!

Thanks so much, I will play with it. The code needs to go in the onclick of the update button right?

 

[dcx693]

The code needs to go in the onclick of the update button right?

Yes.

 

[Pat Hartman]

first checking that the entry doesn't already exist

The code doesn't check to see if a record already exists because the table keys are defined to prevent duplication. Whenever your keys will prevent duplication, it is best to NOT check but to intercept the error and report it. My code simply ignores the error (you can see the code in the error procedure) because it wasn't important to me to report on it. You may feel differently and so put in a message box.

 

[denileigh]

I am still playing around trying to make this work.

My form needs to have the multi-select box from the employees table and the work date field from the Time Card table right?

The Code goes in the update button and when clicked it will make a new time card and time card hours entry for the selected employees.

Is this right? I'm still having a little trouble.

 

[denileigh]

Does this help?

 

[WayneRyan]

deni,

Your code is looking pretty good.

Where's the screen with the other info (SomeDate)?

You're so close ... Where's the real DB?

Option Compare Database

Private Sub Command4_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set dbs = CurrentDb

For i = 0 To Me.MyListBox.ItemCount - 1
   If Me.MyListBox.ItemSelected = True Then
      SQL = "Select * from TimeCardHours " & _
            "Where TimeCardID = (Select TimeCardID" & _
            "                    From   TimeCard " & _
            "                    Where  WorkDate = #SomeDate# And " & _
            "                           EmployeeID = " & Me.MyListBox.ItemData(i) & ")"
      Set rst = dbs.OpenRecordset(SQL)
      rst.AddNew
      rst!HolidayHrs = 8
      rst.Update
      Set rst = Nothing
   End If
End Sub

Wayne

 

[denileigh]

The attachment with the db snippet is in the previous post.

Somedate should be the workdate from the time card table.

I'm a little confused as to how to pull it together in a form.

Should the #somedate# be workdate? Should the form be based on a query?