Did you add back this line?
Code:Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.accdb")
Did you add back this line?
Code:Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.accdb")
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
Now you've taken out the line setting the recordset.
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.
Seriously?
Set RS = DAO.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
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
When I reran the script, it doesn't like the new addition.
You're still getting the 91 error on the AddNew? In case it's that variable name, try
Code: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.
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.
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.
If ID_D is a number type then eliminate the apostrophe delimiters which are for text type. Date type criteria would use # delimiter.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Try this:
strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = " & Me.ID_D
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
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?
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.