Run-Time error '91': Object variable or With block variable not se

[Jim]

I am attempting to move all my access tables to an sql server backend
and continue to use access 97 as the front end...Everything went fairly
smoothly until I started to receive this error message..


Run-Time error '91':
Object variable or With block variable not set


I dont know much about VBA and just want to fix the problem any way I
can..Heres the code and thanks in advance:


Private Sub SendToCTT_Click()


Dim CurrQuery As String 'Checking for absence of
any records
CurrQuery = Me![AlertsCTT].Form.RecordSour­ce


Dim NumEvents As Integer
NumEvents = DCount("*", CurrQuery)


If NumEvents = 0 Then
MsgBox "There are no Alerts to send."
Exit Sub
End If


Dim Criteria As String, MyDB As Database, MySet As Recordset,
MySourceSet As Recordset
Dim AlertNum As Long
AlertNum = Me![AlertsCTT].Form![AlertID]
Criteria = "[AlertID] = " & AlertNum
Set MyDB = DBEngine.Workspaces(0).Databas­es(0)


If Me.OpenArgs = "Crisis" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCris­is",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "CTT" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCTT"­, DB_OPEN_DYNASET,
dbSeeChanges)
ElseIf Me.OpenArgs = "CaseMgt" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCase­Mgt",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "Substance" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsSubs­tance",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "Clinic" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsClin­ic",
DB_OPEN_DYNASET, dbSeeChanges)
End If


Set MySet = MyDB.OpenRecordset("AlertsCTT"­, DB_OPEN_DYNASET,
dbSeeChanges)


MySourceSet.FindFirst Criteria ' Find first occurrence.


MySet.AddNew
'MySet!AlertID = MySourceSet!AlertID
MySet!StaffID = MySourceSet!StaffID
MySet!ClientID = MySourceSet!ClientID
MySet!Comment = MySourceSet!Comment
MySet!Date = MySourceSet!Date
MySet!Time = MySourceSet!Time
MySet.Update


MySet.Close
MySourceSet.Close


MsgBox "The selected Alert has been forwarded to CTT."


End Sub

 

[Jim]

The debugger moves to this line in the code:

MySourceSet.FindFirst Criteria ' Find first occurrence.

When I hover over Criteria it shows a text tool tip:

Criteria = "[Alert ID] = 591"

 

[Dirk Goldgar]

I am attempting to move all my access tables to an sql server backend
and continue to use access 97 as the front end...Everything went
fairly smoothly until I started to receive this error message..


Run-Time error '91':
Object variable or With block variable not set


I dont know much about VBA and just want to fix the problem any way I
can..Heres the code and thanks in advance:


Private Sub SendToCTT_Click()


Dim CurrQuery As String 'Checking for absence of
any records
CurrQuery = Me![AlertsCTT].Form.RecordSour­ce


Dim NumEvents As Integer
NumEvents = DCount("*", CurrQuery)


If NumEvents = 0 Then
MsgBox "There are no Alerts to send."
Exit Sub
End If


Dim Criteria As String, MyDB As Database, MySet As Recordset,
MySourceSet As Recordset
Dim AlertNum As Long
AlertNum = Me![AlertsCTT].Form![AlertID]
Criteria = "[AlertID] = " & AlertNum
Set MyDB = DBEngine.Workspaces(0).Databas­es(0)


If Me.OpenArgs = "Crisis" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCris­is",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "CTT" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCTT"­,
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "CaseMgt" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCase­Mgt",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "Substance" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsSubs­tance",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "Clinic" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsClin­ic",
DB_OPEN_DYNASET, dbSeeChanges)
End If


Set MySet = MyDB.OpenRecordset("AlertsCTT"­, DB_OPEN_DYNASET,
dbSeeChanges)


MySourceSet.FindFirst Criteria ' Find first occurrence.


MySet.AddNew
'MySet!AlertID = MySourceSet!AlertID
MySet!StaffID = MySourceSet!StaffID
MySet!ClientID = MySourceSet!ClientID
MySet!Comment = MySourceSet!Comment
MySet!Date = MySourceSet!Date
MySet!Time = MySourceSet!Time
MySet.Update


MySet.Close
MySourceSet.Close


MsgBox "The selected Alert has been forwarded to CTT."


End Sub

The error suggests that MySourceSet has not been set. Mybe OpenArgs
isn't equal to any of the strings you're testing for. I suggest you add
an Else clause to that big If ... ElseIf block, or rewrite it as a
Select Case block with a Case Else statement, and logic to handle the
situation when none of the cases is met. You can also set a breakpoint
at the top of that code and step through to see what happens, and
examine the value of Me.OpenArgs in the debugger.

 

[Jim]

Thanks..that helped me debug it..turns out that I was linked to an sql server
table tha needed to have a primary key and identity added to it to make this
work..

I am attempting to move all my access tables to an sql server backend
and continue to use access 97 as the front end...Everything went
fairly smoothly until I started to receive this error message..


Run-Time error '91':
Object variable or With block variable not set


I dont know much about VBA and just want to fix the problem any way I
can..Heres the code and thanks in advance:


Private Sub SendToCTT_Click()


Dim CurrQuery As String 'Checking for absence of
any records
CurrQuery = Me![AlertsCTT].Form.RecordSour­ce


Dim NumEvents As Integer
NumEvents = DCount("*", CurrQuery)


If NumEvents = 0 Then
MsgBox "There are no Alerts to send."
Exit Sub
End If


Dim Criteria As String, MyDB As Database, MySet As Recordset,
MySourceSet As Recordset
Dim AlertNum As Long
AlertNum = Me![AlertsCTT].Form![AlertID]
Criteria = "[AlertID] = " & AlertNum
Set MyDB = DBEngine.Workspaces(0).Databas­es(0)


If Me.OpenArgs = "Crisis" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCris­is",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "CTT" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCTT"­,
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "CaseMgt" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsCase­Mgt",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "Substance" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsSubs­tance",
DB_OPEN_DYNASET, dbSeeChanges)
ElseIf Me.OpenArgs = "Clinic" Then
Set MySourceSet = MyDB.OpenRecordset("AlertsClin­ic",
DB_OPEN_DYNASET, dbSeeChanges)
End If


Set MySet = MyDB.OpenRecordset("AlertsCTT"­, DB_OPEN_DYNASET,
dbSeeChanges)


MySourceSet.FindFirst Criteria ' Find first occurrence.


MySet.AddNew
'MySet!AlertID = MySourceSet!AlertID
MySet!StaffID = MySourceSet!StaffID
MySet!ClientID = MySourceSet!ClientID
MySet!Comment = MySourceSet!Comment
MySet!Date = MySourceSet!Date
MySet!Time = MySourceSet!Time
MySet.Update


MySet.Close
MySourceSet.Close


MsgBox "The selected Alert has been forwarded to CTT."


End Sub

The error suggests that MySourceSet has not been set. Mybe OpenArgs
isn't equal to any of the strings you're testing for. I suggest you add
an Else clause to that big If ... ElseIf block, or rewrite it as a
Select Case block with a Case Else statement, and logic to handle the
situation when none of the cases is met. You can also set a breakpoint
at the top of that code and step through to see what happens, and
examine the value of Me.OpenArgs in the debugger.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)