Coding Error

[KellyJo]

Can anyone tell me what is wrong with this code? When I try to run it, I get an error saying that I tried to assign a null value to a variable that is not a variant data type. Thanks!

Private Sub Form_Current()
Dim s As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set db = CurrentProject.Connection

s = "SELECT Last(ERName) AS ERName FROM Staffing Other"

Set rs = New ADODB.Recordset
rs.Open s, db, adOpenDynamic, adLockOptimistic
'add to the table

With rs
Me![ERName] = .Fields("ERName")
End With
Set rs = Nothing
Set db = Nothing
End Sub

 

[RickSpr]

If this is correctly typed, you should be getting a different error of some kind. "Staffing Other", if it's the name of a table, needs to be in [brackets] because it has an embedded space. I would think that would be giving you an SQL syntax error.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[willir]

Are you sure you have found a record with the SELECT statement?

You can try this

Me![ERName] = Nz(.Fields("ERName"), "Not Found")