I'm at my wits end. I think there is something wrong with my code because I'm connecting with the database ok. But when I run this I get an 'Object Required' error. I have option explicit and compile fine... :( Can anybody see where I've gone wrong here?

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strStudent As String
Dim strRel As String
Dim strSQL As String
Dim OldDat(0 To 9)

'Database is SQL, linked to the mdb with password saved.

Set db = New ADODB.Connection
db.Open CurrentProject.Connection

strStudent = Forms!frmstudents.lstStudents.Value
strRel = Forms!frmstudents.lstRelatives.Value

Set rs = New ADODB.Recordset
rs.Open "SELECT * from dbo_ADDRRELA WHERE dbo_ADDRRELA.iid = '" & strStudent & "' AND dbo_ADDRRELA.riid = '" & strRel & "'", db, 1, 3

'load an array with current values from the form so you can tell if one has been updated
'doing this because the dirty property doesn't work on unbound forms

OldDat(0) = rs.Fields.Item("hphone")
OldDat(1) = rs.Fields.Item("hpager")
OldDat(2) = rs.Fields.Item("hemail")
OldDat(3) = rs.Fields.Item("hmobile")
OldDat(4) = rs.Fields.Item("bphone")
OldDat(5) = rs.Fields.Item("bemail")
OldDat(6) = rs.Fields.Item("address")
OldDat(7) = rs.Fields.Item("city")
OldDat(8) = rs.Fields.Item("state")
OldDat(9) = rs.Fields.Item("zip")

rs.MoveFirst
'update primary info
If Me.txthphone <> OldDat(0) Then
Set rs.Fields("hphone").Value = Me.txthphone.Value ---ERROR HERE 'Object Required'
End If
If Me.txthpager <> OldDat(1) Then
Set rs.Fields("hpager").Value = Me.txthpager
End If

close rs and db...

Thank you in advance to anyone who can see the error of my ways. Meantime I'll try DAO..?..?

Try changing this:
rs.Open "SELECT * from dbo_ADDRRELA WHERE dbo_ADDRRELA.iid = '" & strStudent & "' AND dbo_ADDRRELA.riid = '" & strRel & "'", db, 1, 3


to this

Dim strSQL As String

strSQL = "SELECT * from dbo_ADDRRELA WHERE dbo_ADDRRELA.iid = '" & strStudent & "' AND dbo_ADDRRELA.riid = '" & strRel & "'"

rs.Open strSQL, db, 1, 3

Unfortunately, same error.
I gave myself both db_datareader and db_datawriter login rights to the sql database. However I find that I can SOMETIMES make a change directly to the table linked to to Access, mostly I cannot.
When I link the tables I am asked to choose a key, so I choose the key that exists in the database.
This DBA stuff with locking and such is more than just a little bit over my head, If anyone has any ideas about what's up with this, I'd love to hear!

Hi.

You say you "have option explicit and compile fine" - but do you per chance have blanketing error handling? (On Error Resume Next)?
If there's some problem - then you'll be attempting to use an object which didn't successfully open. And it is at such a point that you'd receive the error you cite.

The example that screams at me in your example is your method of connection assignment.
Dim db As ADODB.Connection
Set db = New ADODB.Connection
db.Open CurrentProject.Connection
Apart from the fact that your variable naming convention is a little odd (db very often impies a DAO database object)...
You're opening another connection pointing to the MDB you're already working in.
If you've been editing your code prior to testing this - it's very likely you now have an exclusive lock on the MDB (or at least are preventing one).

By passing the CurrentProject.Connection object as the Open method's connectionstring property - you are supplying exactly that... The connection string property of the CurrentProject.Connection. This is then used to open a new connection to the same location as the running code. This is likely failing (it should raise an error) and your connection object is not created.
I can't see any reason why you shouldn't just use your local connection object.
If you want a direct connection to the server - then that's entirely different (and we can help you with that should you want it) but everything about this (not least of which your table names prefixed with dbo_) implies you're running locally.
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
should be all you need.

Cheers.