I need help with the following Code which is written in VBA in MS Access 2010. My goal is to get the current revision from A2CPCIChassisPL7447550 table, then use it to access to another table (NFCSRevisionList) to get next revision (for example is current revision is "C", then from second table I need to retrieve the next revision which is "D").
I am getting the following error on the following line code
Set r1 = db.OpenRecordset("SELECT DISTINCTROW ID, Revision FROM NFCSRevisionList Where Revision = ") & TextCurrentRevision
Dim TextCurrentRevision
Dim r As DAO.Recordset, db As DAO.Database
Dim r1 As DAO.Recordset, db1 As DAO.Database
Set db = CurrentDb
Set r = db.OpenRecordset("SELECT DISTINCTROW A2ID, Revision, A2DrawingNumber, CageCode FROM A2CPCIChassisPL7447550 ORDER BY Revision DESC")
TextCurrentRevision = r!Revision
'get next revision from the table
Set db1 = CurrentDb
Set r1 = db.OpenRecordset("SELECT DISTINCTROW ID, Revision FROM NFCSRevisionList Where Revision = ") & TextCurrentRevision
r1.MoveNext
MsgBox r1!Revision
' promt the user for the latest revision
MsgBox "Current Revision :" & TextCurrentRevision & " Next assigned Revision should be: " & r1!Revision
'Cleanup NFCSRevisionList
r.Close
Set r = Nothing
Set r1 = noting
db.Close
Set db = Nothing
Set db1 = Nothing
V/r
Adam