Update Table based on 2 Criteria from List Box

[JimLes]

Hello, I am running into a problem where I need to update an employee's record with a date based on two matches from a list box (EID & TERM DATE).
The first part of my code works fine if I just have one criteria to match where the [EID] equals my first column in the list box but when I add [Term Date] from the list box to match the Term Date from the list box, it fails. Maybe I have my syntax incorrect?

Any help is greatly appreciated!


'Mark date for sent employees
For Each varItem In Me.EEList.ItemsSelected
MyDB.Execute ("UPDATE tblEmployeeData SET [Date Sent Payout]=Date() WHERE [EID]='" & Me.EEList.Column(0, varItem) & "'" And [Term Date] = Me!EEList.Column(9, oItem))
Next

 

[dhookom]

Try add delimiters to your date and fix the placement of quotes. Use debug.print to allow you to view the results of the SQL statement in the debug window.

[COLOR=#4E9A06]'Mark date for sent employees[/color]
Dim strSQL as String
For Each varItem In Me.EEList.ItemsSelected
    strSQL = "UPDATE tblEmployeeData SET [Date Sent Payout]=Date() " & _
        "WHERE [EID]='" & Me.EEList.Column(0, varItem) & "' And [Term Date] = #" &  Me!EEList.Column(9, oItem) & "#"
    debug.Print strSQL
    MyDB.Execute strSQL, dbFailOnError
Next

Duane
Hook'D on Access
MS Access MVP

 

[JimLes]

That got me past any errors but it did not update the table record. Maybe my date needs to be formatted when I pull it out of the list box?

 

[dhookom]

JimLes,

I added the debug.Print strSQL to the code so you could easily determine if your "date needs to be formatted when I pull it out of the list box". What do you see when you press Ctrl+G to view the debug window?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I see the [Date Sent Payout] is a Date, but how did you declare another fields in your tblEmployeeData :
[EID] => Text ?
[Term Date] => Date ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[JimLes]

Correct, [Term Date]= Date

Since the employee may have duplicate records, I am thinking I could use the AutoNumber as a unique identifier to update the tblEmployeeData![Date Pay Out] rather than use two criteria to find the unique record.

I tried this:
MyDB.Execute ("UPDATE tblSeveranceData SET [Date Sent Payout]=Date() WHERE [AutoNumber]= Me!EEList.Column(19, oItem)")

But I keep getting an "Undefined Function in Me!EEList.Column in expression.

 

[Andrzejek]

Try:

Dim strSQL As String

strSQL = "UPDATE tblSeveranceData " _
  & " SET [Date Sent Payout] = Date() " _
  & " WHERE [AutoNumber] = " &  Me!EEList.Column(19, oItem)

MyDB.Execute strSQL

AutoNumber is not the best name for a field, IMHO

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[JimLes]

Umm, this updated all records for the employees. Wonder why it is not recognizing the autonumber?

When I pull the following, column 19 is equal to the autonumber in tblSeveranceData.

'Grab items selected in Listbox
iCount = 0
If Me!EEList.ItemsSelected.Count <> 0 Then
For Each oItem In Me!EEList.ItemsSelected
sTemp = Me!EEList.Column(19, oItem)
iCount = iCount + 1

 

[dhookom]

I'm not sure how we can help when you didn't provide the strSQL or the Debug.Print results?

Duane
Hook'D on Access
MS Access MVP

 

[JimLes]

Okay, this seems to be working: I simply used both the Employee ID and Autonumber for the criteria to find the record in the table.


strSQL = "UPDATE tblSeveranceData SET [Date Sent Payout]=Date() " & _
"WHERE [EID]='" & Me.EEList.Column(0, varItem) & "' And [Autonumber] =" & Me!EEList.Column(19, varItem)

 

[Andrzejek]

Since AutoNumber should be a PK on this table, which means it uniquely identifies a record, the SQL I gave you should update just one record you need.

Do [tt]Debug.Print strSQL[/tt] before executing it and see (show us) what you get.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[JimLes]

Here was what I was getting with just the AutoNumber as the criteria. The AutoNumber was reading blank. I totally agree with you that it should be pulling because it is a primary key:

UPDATE tblSeveranceData SET [Date Sent Payout]=Date() WHERE [EID]='2248808' And [AutoNumber] =


Here is what I got when I added both the EID and AutoNumber as criteria:
UPDATE tblSeveranceData SET [Date Sent Payout]=Date() WHERE [EID]='2248808' And [Autonumber] =2505

 

[JimLes]

Okay, maybe I entered it wrong but your code is working now. It could be my declaration of "VarItem" that did it but not sure.

Anyway, I am very happy that it works!!

strSQL = "UPDATE tblSeveranceData " _
& " SET [Date Sent Payout] = Date() " _
& " WHERE [AutoNumber] = " & Me!EEList.Column(19, varItem)
MyDB.Execute strSQL