Reading in table

[pbaldy]

You have to bracket the field name because of the inadvisable space.

[davegri]

If DCount("Case_Number", "Individual", "[Last Name] = " & "'" & var & "'") > 0 Then

Maybe since DCount returns a number, compare it to a number...

[pbaldy]

Good catch. I was focused on the DCount() and didn't notice the Null bit.

[DevState]

Thank you guys. I'm completely stuck now. I'm still trying to get this thing to start at the first record of "Terminations" table (using the 'Last Name') column and compare each last name to a large table called Individual. Individual has the same 'Last Name' column and if a match occurs, I need it to change the respective "Status" column to "Terminated". Any other ideas? Here is what I have so far.... and it's sloppy and doesnt work because I've been throwing different ideas at it so it looks like spaghetti now.

Private Sub Command79_Click()Dim filepath As String
Dim rs As DAO.Recordset
Dim ID As String
Dim i As Integer
Dim strSQL As String
Dim db As Database
Dim var As String


Set db = CurrentDb


Dim strEmp() As String


filepath = "C:\Users\Desktop\Terminations.xlsx"
DoCmd.TransferSpreadsheet acImport, , "Terminations", filepath, True
 
  Set rs = db.OpenRecordset("Terminations")
                For i = 0 To rs.RecordCount - 1
                        var = rs.Fields("Last")
                                               
                        If DCount("Case_Number", "Individual", "[Last Name] = " & "'" & var & "'") > 0 Then
                            MsgBox ("It Exists!")
                        Else
                    rs.MoveNext
                Next i
                    
            rs.Close


End Sub

Once I get it working, I will clean up the names and coding. I took this project over from someone else.

[davegri]

Try this:

Option Compare Database
Option Explicit


Private Sub cmdTerminations_Click()
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim sSQL As String
    Dim filepath As String
    Set db = CurrentDb
    
    filepath = "C:\Users\Desktop\Terminations.xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Terminations", filepath, True
 
    Set rs = db.OpenRecordset("Terminations")
    rs.MoveLast
    rs.MoveFirst
    Do While Not rs.EOF
        sSQL = "UPDATE Individual set STATUS = " & """Terminated""" & " WHERE Last = " & "'" & rs!Last & "'"
        Debug.Print sSQL
        db.Execute sSQL, dbFailOnError
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

[pbaldy]

Davegri's code looks like it will work (though in my experience the rs.MoveLast and rs.MoveFirst are not required), but as you've already been told, an update query with a join between the tables would be the most efficient method. In small tables you may not see the difference, but in large ones you would.

[DevState]

The code threw a "Too few parameters. Expected 2" on the db.execute sSQL, dbFailOnError.

I would use the join but couldn't figure it out on how to actually update the table and not just give me a list of the ones that were found. I'm not a pro at this (obviously) lol but willing to learn. Maybe that'll be my side project then.

[DevState]

That was my fault, I copied the code incorrectly. It works but says

Syntax Error (missing operator) in query expression 'Last = 'Hansen".

[davegri]

This should fix it.

sSQL = "UPDATE Individual set STATUS = " & """Terminated""" & " WHERE [Last Name]= " & "'" & rs!Last & "'"

[pbaldy]

I would use the join but couldn't figure it out on how to actually update the table and not just give me a list of the ones that were found. I'm not a pro at this (obviously) lol but willing to learn. Maybe that'll be my side project then.

https://support.office.com/en-us/art...__toc336583663

[DevState]

This should fix it.

sSQL = "UPDATE Individual set STATUS = " & """Terminated""" & " WHERE [Last Name]= " & "'" & rs!Last & "'"

Now it's throwing the Too few parameters again.

[davegri]

Is the table being updated named "Individual"?
Is the field in that table named "Status"?
Does table "Individual" have a field named "Last Name"?

If not, adjust SQL accordingly.

[DevState]

Is the table being updated named "Individual"?
Is the field in that table named "Status"?
Does table "Individual" have a field named "Last Name"?

If not, adjust SQL accordingly.

Verified that it's all correct and still throwing it?

[davegri]

Terminations-davegri-v015.zip

Take a look at this. It works here...

You'll have to adjust the filepath setting for your computer.

[DevState]

EDIT:
I got the query to work like was suggested. Sorry for the hassle.