For Loop not initialised ??

P

PhilipEwen

Registered User.
Local time
Today, 15:37
Joined
Jun 11, 2001
Messages
81
Hi,

When i run the following query on the SQL of the query generator i get the right results.

SELECT contacts.*, contacts.sector, staff_to_contact.staff_pk, staff_to_contact.contact_pk FROM contacts INNER JOIN staff_to_contact ON contacts.pk_contacts = staff_to_contact.contact_pk WHERE ((contacts.sector)="engineering");


When i try to make this query in VBA code, i get "ERROR: For Loop not Initialised"

Set MyQueryDef = MyDatabase.CreateQueryDef(qryname, "SELECT contacts.*, contacts.sector, staff_to_contact.staff_pk, staff_to_contact.contact_pk FROM contacts INNER JOIN staff_to_contact ON contacts.pk_contacts = staff_to_contact.contact_pk WHERE ((contacts.sector)='engineering');")

qryname is defined and i have no idea why it thinks it may be looping !?!? ( I am going loopy though !! )

What could be causing this ?
 
Is there some other code in the sub? Or function?
 
Code: 
Private Sub Command83_Click()
On Error GoTo Err_Command83_Click
'Dim MyDatabase As Database
'Dim MyQueryDef As QueryDef
'Dim where As Variant
Dim sectorfilter As String
Dim myfiltersector As String
Dim qryname As String
Dim secondqryname As String
Dim rannum As Integer
Dim staffid As Integer

Dim obj As AccessObject
Dim dbs As Object
Dim strQryName As String

Set MyDatabase = CurrentDb()
On Error Resume Next
qryname = "qry" & Minute(Now()) & Second(Now())

'see if query exists and, if so,
' delete it if it's not open
    For Each obj In dbs.AllQueries
       If obj.Name = qryname Then
       qryname = qryname & 1
           End If
    Next obj

WHERE = Null

If Not IsNull(Me.[title]) Then
If Left(Me![title], 1) = "*" Or Right(Me![title], 1) = "*" Then WHERE = WHERE & " AND [title] like '" + Me![title] + "'" Else WHERE = WHERE & " AND [title] = '" + Me![title] + "'"
End If

If Not IsNull(Me.[FirstName]) Then
If Left(Me![FirstName], 1) = "*" Or Right(Me![FirstName], 1) = "*" Then WHERE = WHERE & " AND [firstname] like '" + Me![FirstName] + "'" Else WHERE = WHERE & " AND [firstname] = '" + Me![FirstName] + "'"
End If

If Not IsNull(Me.[LastName]) Then
If Left(Me![LastName], 1) = "*" Or Right(Me![LastName], 1) = "*" Then WHERE = WHERE & " AND [lastname] like '" + Me![LastName] + "'" Else WHERE = WHERE & " AND [lastname] = '" + Me![LastName] + "'"
End If

If Not IsNull(Me.[position]) Then
If Left(Me![position], 1) = "*" Or Right(Me![position], 1) = "*" Then WHERE = WHERE & " AND [position] like '" + Me![position] + "'" Else WHERE = WHERE & " AND [position] = '" + Me![position] + "'"
End If

If Not IsNull(Me.[company]) Then
If Left(Me![company], 1) = "*" Or Right(Me![company], 1) = "*" Then WHERE = WHERE & " AND [company] like '" + Me![company] + "'" Else WHERE = WHERE & " AND [company] = '" + Me![company] + "'"
End If

If Not IsNull(Me.[address_1]) Then
If Left(Me![address_1], 1) = "*" Or Right(Me![address_1], 1) = "*" Then WHERE = WHERE & " AND [address_1] like '" + Me![address_1] + "'" Else WHERE = WHERE & " AND [address_1] = '" + Me![address_1] + "'"
End If

If Not IsNull(Me.[address_2]) Then
If Left(Me![address_2], 1) = "*" Or Right(Me![address_2], 1) = "*" Then WHERE = WHERE & " AND [address_2] like '" + Me![address_2] + "'" Else WHERE = WHERE & " AND [address_2] = '" + Me![address_2] + "'"
End If

If Not IsNull(Me.[city]) Then
If Left(Me![city], 1) = "*" Or Right(Me![city], 1) = "*" Then WHERE = WHERE & " AND [city] like '" + Me![city] + "'" Else WHERE = WHERE & " AND [city] = '" + Me![city] + "'"
End If

If Not IsNull(Me.[postcode]) Then
If Left(Me![postcode], 1) = "*" Or Right(Me![postcode], 1) = "*" Then WHERE = WHERE & " AND [postcode] like '" + Me![postcode] + "'" Else WHERE = WHERE & " AND [postcode] = '" + Me![postcode] + "'"
End If

If Not IsNull(Me.[country]) Then
If Left(Me![country], 1) = "*" Or Right(Me![country], 1) = "*" Then WHERE = WHERE & " AND [country] like '" + Me![country] + "'" Else WHERE = WHERE & " AND [country] = '" + Me![country] + "'"
End If

If Not IsNull(Me.[comments]) Then
If Left(Me![comments], 1) = "*" Or Right(Me![comments], 1) = "*" Then WHERE = WHERE & " AND [comments] like '" + Me![comments] + "'" Else WHERE = WHERE & " AND [comments] = '" + Me![comments] + "'"
End If

If Not IsNull(Me.[telephone]) Then
If Left(Me![telephone], 1) = "*" Or Right(Me![telephone], 1) = "*" Then WHERE = WHERE & " AND [telephone] like '" + Me![telephone] + "'" Else WHERE = WHERE & " AND [telephone] = '" + Me![telephone] + "'"
End If

If Not IsNull(Me.[mobile_phone]) Then
If Left(Me![mobile_phone], 1) = "*" Or Right(Me![mobile_phone], 1) = "*" Then WHERE = WHERE & " AND [mobile_phone] like '" + Me![mobile_phone] + "'" Else WHERE = WHERE & " AND [mobile_phone] = '" + Me![mobile_phone] + "'"
End If

If Not IsNull(Me.[main_phone]) Then
If Left(Me![main_phone], 1) = "*" Or Right(Me![main_phone], 1) = "*" Then WHERE = WHERE & " AND [main_phone] like '" + Me![main_phone] + "'" Else WHERE = WHERE & " AND [main_phone] = '" + Me![main_phone] + "'"
End If

If Not IsNull(Me.[email]) Then
If Left(Me![email], 1) = "*" Or Right(Me![email], 1) = "*" Then WHERE = WHERE & " AND [email] like '" + Me![email] + "'" Else WHERE = WHERE & " AND [email] = '" + Me![email] + "'"
End If

If Not IsNull(Me.[email_2]) Then
If Left(Me![email_2], 1) = "*" Or Right(Me![email_2], 1) = "*" Then WHERE = WHERE & " AND [email_2] like '" + Me![email_2] + "'" Else WHERE = WHERE & " AND [email_2] = '" + Me![email_2] + "'"
End If

If Not IsNull(Me.[email_3]) Then
If Left(Me![email_3], 1) = "*" Or Right(Me![email_3], 1) = "*" Then WHERE = WHERE & " AND [email_3] like '" + Me![email_3] + "'" Else WHERE = WHERE & " AND [email_3] = '" + Me![email_3] + "'"
End If

If Not IsNull(Me.[fax]) Then
If Left(Me![fax], 1) = "*" Or Right(Me![fax], 1) = "*" Then WHERE = WHERE & " AND [fax] like '" + Me![fax] + "'" Else WHERE = WHERE & " AND [fax] = '" + Me![fax] + "'"
End If

If Not IsNull(Me.[www_page]) Then
If Left(Me![www_page], 1) = "*" Or Right(Me![www_page], 1) = "*" Then WHERE = WHERE & " AND [www_page] like '" + Me![www_page] + "'" Else WHERE = WHERE & " AND [www_page] = '" + Me![www_page] + "'"
End If

If Not IsNull(Me.[pa_name]) Then
If Left(Me![pa_name], 1) = "*" Or Right(Me![pa_name], 1) = "*" Then WHERE = WHERE & " AND [pa_name] like '" + Me![pa_name] + "'" Else WHERE = WHERE & " AND [pa_name] = '" + Me![pa_name] + "'"
End If

If Not IsNull(Me.[pa_tel]) Then
If Left(Me![pa_tel], 1) = "*" Or Right(Me![pa_tel], 1) = "*" Then WHERE = WHERE & " AND [pa_tel] like '" + Me![pa_tel] + "'" Else WHERE = WHERE & " AND [pa_tel] = '" + Me![pa_tel] + "'"
End If

If Not IsNull(Me.[pa_email]) Then
If Left(Me![pa_email], 1) = "*" Or Right(Me![pa_email], 1) = "*" Then WHERE = WHERE & " AND [pa_email] like '" + Me![pa_email] + "'" Else WHERE = WHERE & " AND [pa_email] = '" + Me![pa_email] + "'"
End If
'If Not IsNull(Me.[relationship]) Then
'If Left(Me![relationship], 1) = "*" Or Right(Me![relationship], 1) = "*" Then where = where & " AND [relationship] like '" + Me![relationship] + "'" Else where = where & " AND [relationship] = '" + Me![relationship] + "'"
'End If
'If Not IsNull(Me.[sector]) Then
'If Left(Me![sector], 1) = "*" Or Right(Me![sector], 1) = "*" Then where = where & " AND [sector] like '" + Me![sector] + "'" Else where = where & " AND [sector] = '" + Me![sector] + "'"
'End If
'Check for message box call
'If (Me.[mailouts_required]) = "Select" Then MsgBox ("Please select whether you wish Mail Out data and Event Data included")
' break from subroutine here to allow for OK
'If (Me.[mailouts_required]) = "Yes" Then where = where & "AND [mailouts] = '1'" Else where = where
'If (Me.[events_required]) = "Yes" Then where = where & "AND [events] = '1'" Else where = where

'COMPILE ADDITIONAL SEARCH OPTIONS
If Me.srch_by_ind_sector = True Then WHERE = WHERE & Me.txtsectorsearchby

If Me.srch_by_relationships = True Then WHERE = WHERE & Me.txtrelationshipssearchby


If Me.srch_by_staff = True Then
WHERE = WHERE & Me.txtstaffsearchby
secondqryname = qryname & "-2nd"
'First Query
Set MyQueryDef = MyDatabase.CreateQueryDef(qryname, "SELECT contacts.*, contacts.sector, staff_to_contact.staff_pk, staff_to_contact.contact_pk FROM contacts INNER JOIN staff_to_contact ON contacts.pk_contacts = staff_to_contact.contact_pk WHERE ((contacts.sector)='engineering');")
'MsgBox "WHERE " & WHERE
End If

[code]
 
I think this is the problem here:
For Each obj In dbs.AllQueries

You've dim'd the dbs variable as an object, but you haven't used the Set command to set it to anything. Try:
Set dbs = Application.CurrentData
before the "For" line.
 
Thank you so much for your answer - really good of you - i was tearing my hair out !!

Thanks again
 
You must log in or register to reply here.

Similar threads

J
Solved Changed relationships has stopped a form working
Replies
18
Views
1,115
John O'Malvern
J
D
Solved Del All FK & PK Constraints
Replies
11
Views
521
kentgorrell
K
N
How to deal with locking system in MS access safely in point of sales environment
Replies
6
Views
487
GustavBrock
GustavBrock
G
Solved Update recordset skipping the last record
2
Replies
21
Views
837
The_Doc_Man
The_Doc_Man
R
VBA not working for saved query parameters
2
Replies
24
Views
772
RonPaii
RonPaii

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom