visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Object variable or With block variable not set (Error 91)
By: Squibi U.S.A.  Date: 18/04/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
I've been told that if I convert my Microsoft Access database to SQL it will run quite faster. So, I've convered the database to a SQL but I'm getting the following error "Object variable or With block variable not set (Error 91)" whenever I try and use the search that I created in Access. Here's my search code:
-------------------------------------------------------------------------------------------------
Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
'Dim MyQueryDef As QueryDef
Dim MyQueryDef As Object
Dim QueryDef As Object
Dim Where As Variant
Dim QuestionCount As Variant
Dim strWhere As Variant

Set MyDatabase = CurrentDb()
Set MyQueryDef = QueryDef

If ahtObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
End If

Where = Null
Where = Where & " AND [ProjectTeam]= '" + Me![ProjectTeam] + "'"
Where = Where & " AND [Bidder]= '" + Me![Bidder] + "'"
Where = Where & " AND [Priority]= '" + Me![Priority] + "'"
Where = Where & " AND [Status]= '" + Me![Status] + "'"
Where = Where & " AND [QuestionType]= '" + Me![QuestionType] + "'"
Where = Where & " AND [Discipline]= '" + Me![Discipline] + "'"
Where = Where & " AND [ApplicableBidderDocument]= '" + Me![ApplicableBidderDocument] + "'"
Where = Where & " AND [ITBSectionNumber]= '" + Me![ITBSectionNumber] + "'"
Where = Where & " AND [RecordNumber]= " + Me![RecordNumber] + ""
Where = Where & " AND [Question] like ""*" + Me![Question] + "*"""
Where = Where & " AND [BidReference] like ""*" + Me![BidReference] + "*"""
Where = Where & " AND [ITBReference] like ""*" + Me![ITBReference] + "*"""
Where = Where & " AND [BidderResponse] like ""*" + Me![BidderResponse] + "*"""
Where = Where & " AND [InternalComments] like ""*" + Me![InternalComments] + "*"""

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from tblQuestions " & (" where " + Mid(Where, 6) & ";"))

DoCmd.Close
DoCmd.OpenForm "frmCustomSearch"
End Sub
--------------------------------------------------------------------------------------------
Function ahtObjectExists(ObjectType As String, ObjectName As String) As Integer
On Error Resume Next

Dim Found_Object As Integer, Find_Object As String, ObjectNum As Integer
Dim db As DAO.Database, T As DAO.TableDef
Dim Q As DAO.QueryDef, C As DAO.Container
Dim Msg As String
Found_Object = False
Set db = CurrentDb()

Select Case ObjectType
Case "Tables"
Find_Object = db.TableDefs(ObjectName).Name

Case "Queries"
Find_Object = db.QueryDefs(ObjectName).Name

Case Else
If ObjectType = "Forms" Then
ObjectNum = 1
ElseIf ObjectType = "Modules" Then
ObjectNum = 2
ElseIf ObjectType = "Reports" Then
ObjectNum = 4
ElseIf ObjectType = "Macros" Then
ObjectNum = 5
Else
Msg = "Object Name """ & ObjectType & """ is an invalid"
Msg = Msg & " argument to function ObjectExists!"
MsgBox Msg, 16, "ObjectExists20"
Exit Function

End If

Set C = db.Containers(ObjectNum)
Find_Object = C.Documents(ObjectName).Name

End Select

If Err = 3265 Or Find_Object = "" Then
Found_Object = False
Else
Found_Object = True
End If

ahtObjectExists = Found_Object

End Function
-----------------------------------------------------------------------------------------------------
By: VGR Date: 18/04/2003 01:56:00 English  Type : Comment
I think your '*' wildcard has to become '%'
By: VGR Date: 18/04/2003 01:56:00 English  Type : Answer
but this has perhaps nothing to do with your problem, as you visibly use a front-end script language I don't master at all :D
By: Squibi Date: 26/04/2003 01:48:00 English  Type : Comment
i figured out the problem. it was in my coding. thanks for all the help.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 296.000960 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page