Index = "PrimaryKey" error with SQL BE linked

[pbaldy]

Did you add back this line?

Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.accdb")

[Brian62]

Thanks, I got that part of the script to work along with the refresh command button. Now on my "Add Record", in the VBA script, it is highlighting .AddNew as indicated in the jpg. It is with Run-Time error 91: Object variable or With block variable not set.

Private Sub CmdAdd_Click()
Dim strsql As String
Dim DAO As DAO.Database
Dim RS As DAO.Recordset
Dim LngCnt As Long

LngCnt = 0

strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = '" & Me.ID_D & "'"
Debug.Print strsql
Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.ac cdb")
LngCnt = 0

With RS
If LngCnt > 0 Then
.MoveFirst
.Index = "PK_TblRDStudyAuditDocument"
.MoveLast
ID_D = .Fields(0) + 1
.AddNew
.Fields(0) = ID_D
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
Else
.AddNew (Highlighted in Yellow)
.Fields(0) = 1
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
ID_D = 1

End If
.Close

End With

Set DAO = Nothing

End Sub

[pbaldy]

Now you've taken out the line setting the recordset.

[Brian62]

I'm not sure what recoprdset I am missing. I compared it to the one we fixed earlier and I can't find anything missing.

[pbaldy]

Seriously?

Set RS = DAO.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)

[Brian62]

I added the script and it still gives me the same error. Yes seriously..... I'm not a VBA or SQL expert, that's why I come to you guys. You guys have been life savers.

Private Sub CmdAdd_Click()
Dim strsql As String
Dim DAO As DAO.Database
Dim RS As DAO.Recordset
Dim LngCnt As Long

LngCnt = 0

strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = '" & Me.ID_D & "'"
Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.ac cdb")
Set RS = DAO.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)

LngCnt = 0

With RS
If LngCnt > 0 Then
.MoveFirst
.Index = "PK_TblRDStudyAuditDocument"
.MoveLast
ID_D = .Fields(0) + 1
.AddNew
.Fields(0) = ID_D
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
Else
.AddNew
.Fields(0) = 1
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
ID_D = 1

End If
.Close

End With

Set DAO = Nothing

End Sub

[Brian62]

When I reran the script, it doesn't like the new addition.

[pbaldy]

You're still getting the 91 error on the AddNew? In case it's that variable name, try

Dim strsql As String
    Dim db As DAO.Database
    Dim RS As DAO.Recordset
    Dim LngCnt As Long
    
    LngCnt = 0
    
    strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument  WHERE ID_D = '" & Me.ID_D & "'"
    Set db = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.accdb")
    Set RS = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)

It would be really helpful if you could post a sample of the db.

[Brian62]

I made the change as you requested but still have an error on the script you sent me..... Set RS = db.openrecordset....
at the end of the script it doesn't like Set DAO = Nothing either. I changed it to Set db= Nothing giving me the same error.

[Brian62]

I wish I could give you a copy of the db but it's connected to SQL. The new error code is-time error 3464. Data type mismatch in criteria expression.

[June7]

If ID_D is a number type then eliminate the apostrophe delimiters which are for text type. Date type criteria would use # delimiter.

[pbaldy]

Try this:

strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = " & Me.ID_D

[Brian62]

Still doesn't like it. Still has error 91.

Private Sub CmdAdd_Click()
Dim strsql As String
Dim DAO As DAO.Database
Dim RS As DAO.Recordset
Dim LngCnt As Long

LngCnt = 0

strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = " & Me.ID_D
Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.ac cdb")
Set RS = DAO.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)

LngCnt = 0

With RS
If LngCnt > 0 Then
.MoveFirst
.Index = "PK_TblRDStudyAuditDocument"
.MoveLast
ID_D = .Fields(0) + 1
.AddNew
.Fields(0) = ID_D
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
Else
.AddNew
.Fields(0) = 1
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
ID_D = 1

End If
.Close

End With

Set DAO = Nothing

End Sub

[pbaldy]

And we're back to using DAO as a variable?

Can you import the SQL Server table into the Access db, get rid of any personal info, and post that?

[Brian62]

If I post the db then it will work since the VB worked then. The conversion to SQL as the BE is what is giving me trouble with VB.