Update Table from ListBox

[denileigh]

Please help...

I have been trying and trying and TRYING to get this form to work. I am trying to update employee time & holiday hours using a multi select box and the code ya'll have been so kind to help me with here but I just can't seem to get it...part of the code isn't working.

I've been working on this since before Christmas and I have GOT to get it working. Can anyone help???

Have I managed to mess up the code?

THANKS!

 

[Uncle Gizmo]

I've had an 1/2 an hour at it and modified your code:

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

Set dbs = CurrentDb

For i = 0 To Me.List2.ListCount - 1
If Me.List2.ListIndex >= 0 Then
MsgBox "Item Data " & Me.List2.ItemData(i)
SQL = "Select * from TimeCardHours " & _
"Where TimeCardID = (Select TimeCardID" & _
" From TimeCards " & _
"Where WorkDate = #txtDate# And " & _
"EmployeeID = " & Me.List2.ItemData(i) & ")"
MsgBox "SQL >>>> " & SQL
Set rst = dbs.OpenRecordset(SQL)
rst.AddNew
rst!HolidayHrs = 8
rst.Update
End If
Next i
Set rst = Nothing
End Sub


Its still producing errors in the SQL, I've put a couple of message boxes in to track the error. you should be able to find it yourself now...

 

[WayneRyan]

denileigh,

Not really understanding this, but it should be close.

Private Sub Command12_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim TCid As Long

Set dbs = CurrentDb
'
' Loop through all selected items
'
For i = 1 To Me.List2.ItemsSelected
   '
   ' For each employee selected, get the "base" timecard id
   '
   SQL = "Select TimeCardID " & _
            "From   TimeCard " & _
            "Where  WorkDate = #" & Me.txtDate & "# And " & _
            "           EmployeeID = " & Me.List2.ItemData(i).Column(0) & ")"
   Set rst = dbs.OpenRecordset(SQL)
   If rst.EOF and rst.BOF Then
      MsgBox("Emp " & Me.List2.ItemData(i).Column(1) & " has no timecard.")
   Else
      '
      ' Save the "base" id, then add an entry for TimeCardHours
      '
      TCid = rst!TimeCardID
      Set rst = Nothing
      Set rst = dbs.OpenRecordset("TimeCardHours")
      rst.AddNew
      rst!TimeCardID = TCid
      rst!HolidayHrs = 8
      rst.Update
      Set rst = Nothing
   End If
   Next i
   
End Sub

I hope that gets you closer.

Wayne

 

[denileigh]

Thanks all...tried both, can't get it. I think maybe I'm just gonna give up and have to enter 30+ cards for each holiday.

I'm just about in tears over this. My boss has been on me and I have no access training or a whole lot of knowledge so this is so far above my head it isn't even funny. I've surfed and surfed and read everything I can find. I've been trying to make it work almost daily for months. This is so far above my minimal knowledge. I think I'll just tell him it can't be done & we will just have to deal with, it seems to be the 1 thing just can't get in this whole project so far.

Thanks again.

 

[Pat Hartman]

Back in the middle of January you were pointed to a sample db that does exactly what you need. Why not copy the code from there? Make sure you pick up the latest sample.

Multiselect Listbox

 

[denileigh]

Back in the middle of January you were pointed to a sample db that does exactly what you need. Why not copy the code from there? Make sure you pick up the latest sample.

Multiselect Listbox

Thank you for the reference however, back in January when I was pointed to it, I DID download the latest sample and have been trying like heck all this time to get it to work, it is EXACTLY what I have been using.

The sample takes the members and the date and creates an attendance record.

I need one that takes the employee and the date and creates new records in the time card table (date & employee) as well as the holiday hours in the time card hours table. That sample just creates the one record and I haven't been doing this long enough to figure out how to make it create both...or one for that matter.

I have even tried moving the holiday hours into the timecard table but the ALL my payroll & attendance records don't work.

 

[Pat Hartman]

But the code you posted in this thread is NOT the code from my db. It is the code that WayneRyan gave you in that January thread. That's why I said something.

 

[denileigh]

But the code you posted in this thread is NOT the code from my db. It is the code that WayneRyan gave you in that January thread. That's why I said something.

Cause I've played with both til I could pull my hair out.

 

[theprez]

From a business standpoint, I would pay someone a couple of hours to come resolve this issue. Your boss can weigh your time vs. entering 30 time cards vs. paying an experienced programmer a couple of hours. Just a thought.

 

[Pat Hartman]

This is the code that adds the records. In my db it is called from the form rather than being in the form because I use it in more than one place. The calling procedure passes in the control name as a parameter. The function returns the count of records inserted because my form displays it. qAttendance is a simple select query that selects the columns I need.

Why not just start again from this code.

Public Function CreateAttendanceRecords(ctlRef As ListBox) As Integer
On Error GoTo Err_CreateAttendanceRecords

    Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim RecCount As Integer
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qAttendance
    Set rst = qd.OpenRecordset
    RecCount = 0
    For Each i In ctlRef.ItemsSelected
        rst.AddNew
        rst!MailingListID = ctlRef.ItemData(i)
        rst!AttendanceDate = Me.txtToday
        rst.Update
        RecCount = RecCount + 1
    Next i
    Set rst = Nothing
    Set qd = Nothing
    CreateAttendanceRecords = RecCount
    
Exit_CreateAttendanceRecords:
    Exit Function

Err_CreateAttendanceRecords:
    Select Case Err.Number
        Case 3022     'ignore duplicate keys
            RecCount = RecCount - 1
            Resume Next
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_CreateAttendanceRecords
    End Select
    
End Function

 

[denileigh]

This is the code that adds the records. In my db it is called from the form rather than being in the form because I use it in more than one place. The calling procedure passes in the control name as a parameter. The function returns the count of records inserted because my form displays it. qAttendance is a simple select query that selects the columns I need.

Why not just start again from this code.

Public Function CreateAttendanceRecords(ctlRef As ListBox) As Integer
On Error GoTo Err_CreateAttendanceRecords

    Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim RecCount As Integer
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qAttendance
    Set rst = qd.OpenRecordset
    RecCount = 0
    For Each i In ctlRef.ItemsSelected
        rst.AddNew
        rst!MailingListID = ctlRef.ItemData(i)
        rst!AttendanceDate = Me.txtToday
        rst.Update
        RecCount = RecCount + 1
    Next i
    Set rst = Nothing
    Set qd = Nothing
    CreateAttendanceRecords = RecCount
    
Exit_CreateAttendanceRecords:
    Exit Function

Err_CreateAttendanceRecords:
    Select Case Err.Number
        Case 3022     'ignore duplicate keys
            RecCount = RecCount - 1
            Resume Next
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_CreateAttendanceRecords
    End Select
    
End Function

Thanks! I'll get right on it! Can I ask an unrelated question?

I'm working on a small side DB for my Girl Scout Service Unit to handle membership registration so of course I have a combo box to select Brownie, Junior, Cadette or Senior.

In a query, how do I have it do a total count based on the value of the combo? For example, count thr brownies, juniors, etc?

 

[Mile-O]

I'm working on a small side DB for my Girl Scout Service Unit to handle membership registration so of course I have a combo box to select Brownie, Junior, Cadette or Senior.

In a query, how do I have it do a total count based on the value of the combo? For example, count thr brownies, juniors, etc?

Assuming your structure is like this:

tblScouts
ScoutID - autonumber
Forename - text
Surname - text
GradeID - number

tblGrades
GradeID - autonumber
Grade - text


Then, the SQL would be:

SELECT tblGrades.Grade, Count(tblGrades.Grade) AS Total
FROM tblGrades INNER JOIN tblScouts ON tblGrades.GradeID = tblScouts.GradeID
GROUP BY tblGrades.Grade
ORDER BY Count(tblGrades.Grade) DESC;

 

[denileigh]

Assuming your structure is like this:

tblScouts
ScoutID - autonumber
Forename - text
Surname - text
GradeID - number

tblGrades
GradeID - autonumber
Grade - text


Then, the SQL would be:

Thanks a bunch! They're gonna LOVE me for this one! Major Brownie points (no pun intended) *hugs*

 

[Mile-O]

Saying that, that's the structure you'd want and the SQL gives a summary of all scouts within the database. It would be best placed in a listbox.

If you want to just count for one thing at a time, as stated in your question, with the selection of a combobox then a textbox with this as the ControlSource may be what you want (on a form):

=IIf(IsNull([MyCombo]), "-", DCount("ScoutID", "tblScouts", "GradeID = " & [MyCombo].[Column](0)))


This will only work if the combo's ControlSource is:

SELECT tblGrade.GradeID, tblGrade.Grade
FROM tblGrade
ORDER BY tblGrade.Grade;

P.S. Is there any need to keep quoting everything that someone says?

 

[Pat Hartman]

The problem is which button you use to reply. The little reply button on the right, within the frame of the post quotes everything. The big reply button on the left, below the frame doesn't do that.

I would disable that feature if that is possible. It is better to make people select what they want to quote and saves a lot of disk space too.