Record Tracking Function Not Working After Conversion to Access 2007

[maytime]

Can anyone help me out try to debug this code? I have a SubFunction that tracks changes made in some data entry forms by recording what was changed, the user that made the change, dates, etc. It all worked fine in the ".mdb" version of my database but when I converted to the new ".aacdb" version (Access 2007) I am running into an error message around the bolded/red line below. My gut says that something with the 'strSQL' defining statement no longer works right with Access 2007. FYI, the table that gets the data tracking information inputted to it is named "Audit", and the control fields in that table are all that come after the "rs!" statements. Any ideas as to how to fix this? The error I get is "Run Time error 13" and the description is "Type Mismatch". Thanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
'Dim strReason As String
'strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!EncKey = [Enc Key]
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Sub

 

[pbaldy]

For starters, try disambiguating these to:

Dim db As DAO.Database
Dim rs As DAO.Recordset

 

[maytime]

Wow, that did the trick...no error messages and my Audit table updated correctly. Thanks! Are there many little things like this that pop up when you convert from .mdb to .aacdb? Also, what does the "DAO" distinction mean?

 

[pbaldy]

No problem. That one is due to the VBA references. In your previous version, you may only have had the DAO reference, or you may have had both ADO and DAO but had DAO higher on the priority. In this case, you probably had both checked, but ADO was higher and thus the one Access "saw" first. Of course your statement wasn't in ADO format, hence the error. People actually had the same problem going from 97 to 2000, so this really isn't a 2007 issue.