syntax error with multiple inner join query - Access 2003

P

pgcn

Holiday greetings good people - please help with your expertise:

I have a client search form [frm_FindStakeholder] which has an option
group: 1. First Name, 2. Last Name & 3. Organisation. Also a list box
[cmdWhich] with 3 options: "Contains", "Begins With" & "Equals". And
lastly an unbound text box [FindText].

Upon clicking cmdFind I'm getting: Syntax error (missing operator).
I've tried numerous cobinations of ( )'s and have read all the
relevant posts I could find, but am no wiser.

I've just taken the dbase over & have tried to modify the form from
returning records showing:
First Name & Last Name, & when organisation is searched - organisation
to
First Name, Last Name, Organisation, Job Title and Inactive.

The original form was working with the following query:

strsql = "SELECT tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive

FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID = tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID =
tblProjectOwnerStakeHolders.pos_Stakeholder) ON
tlkpJobTitle.JobTitleID = tblStakeholderDetails.fJobTitleID";

ORDER BY tblStakeholders.SHFName;

I created a new query in design view (which works perfectly on its
own) & pasted it into the sub - at each case, but kept the WHERE
clause - but alas no good. The sub is:

Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Dim strsql As String
Dim rst As DAO.Recordset
Select Case Me.cmdWhich
Case "Contains"
strsql = "LIKE " & quote & "*" & Me.findtext & "*" &
quote '(quote="""")
Case "Begins With"
strsql = "LIKE " & quote & Me.findtext & "*" & quote
Case "Equals"
strsql = "= " & quote & Me.findtext & quote
End Select
Select Case Me.frWhat
Case 1 'first name
strsql = "SELECT tblStakeholders.SHFName,
tblStakeholders.SHLName, tblOrganisation.OrgName,
tlkpJobTitle.JobTitle, tblStakeholderDetails.Inactive
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID=tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID=tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID=tblProjectOwnerStakeHolders.pos_Stakeholder)
ON tlkpJobTitle.JobTitleID=tblStakeholderDetails.fJobTitleID

WHERE (((tblStakeholders.SHFName)" & strsql & _
") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
"))ORDER BY tblStakeholders.SHLName,
tblStakeholders.SHFName;"'

' I'm not sure of the
" " either - is there a good text or link I could study?

Case 2 'last name
same again
WHERE (((tblStakeholders.SHLName)" & strsql & _
") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
"))ORDER BY
tblStakeholders.SHLName,tblStakeholders.SHFName;

Case 3 'org
WHERE (((tblOrganisation.OrgName) " & strsql & _
") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
"))ORDER BY
tblStakeholders.SHLName,tblStakeholders.SHFName;
End Select

Me.sfm_FindStakeholders.Form.RecordSource = strsql
Set rst = Me.sfm_FindStakeholders.Form.RecordsetClone
If rst.RecordCount = 1 Then
rst.MoveFirst
glbStakeholder = Me.sfm_FindStakeholders.Form.StakeHolderID
rst.Close
DoCmd.Close acForm, "frm_FindStakeholder"
Else
rst.Close
Me.sfm_FindStakeholders.SetFocus
End If

Thanks for your time
Peta
 
A

Allen Browne

Peta, I'm not exactly sure what you're up to, but perhaps glbProjectOwner is
Null, so the query statement is incomplete?

This example shows how to build the WHERE clause, which you can then use in
various ways, e.g.:
- as the Filter of this form,
- as the WhereCondition of another OpenForm, or
- as the WHERE clause in a query (saved, or assigned to RecordSource of
form)

Hope this approach helps you to follow through what's going on. The line:
Debug.Print strWhere
is to help you debug it. When if fails, open the Immediate Window (Ctrl+G)
and see what was printed there. Compare it to the WHERE clause in a working
query if you get stuck.

Here's the idea:
------------code starts---------------
Dim strWhere as String

Select Case Me.frWhat.Value
Case Me.optFirstName.OptionValue
strWhere = "(tblStakeholders.SHFName"
Case Me.optSurname.OptionValue
strWhere = "(tblStakeholders.SHLName"
Case Else
strWhere = "(tblOrganisation.OrgName"
End Select


Select Case Me.cmdWhich
Case "Contains"
strWhere = strWhere & " LIKE """*" & Me.findtext & "*"")"
Case "Begins With"
strWhere = strWhere & " LIKE """ & Me.findtext & "*"")"
Case Else
strWhere = strWhere & " = """ & Me.findtext & """)"
End Select

If Not IsNull(glbProjectOwner) Then
strWhere = strWhere & " AND
(tblProjectOwnerStakeHolders.pos_ProjectOwner = " & glbProjectOwner & ")"
End If

Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
------------code ends---------------

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Holiday greetings good people - please help with your expertise:

I have a client search form [frm_FindStakeholder] which has an option
group: 1. First Name, 2. Last Name & 3. Organisation. Also a list box
[cmdWhich] with 3 options: "Contains", "Begins With" & "Equals". And
lastly an unbound text box [FindText].

Upon clicking cmdFind I'm getting: Syntax error (missing operator).
I've tried numerous cobinations of ( )'s and have read all the
relevant posts I could find, but am no wiser.

I've just taken the dbase over & have tried to modify the form from
returning records showing:
First Name & Last Name, & when organisation is searched - organisation
to
First Name, Last Name, Organisation, Job Title and Inactive.

The original form was working with the following query:

strsql = "SELECT tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive

FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID = tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID =
tblProjectOwnerStakeHolders.pos_Stakeholder) ON
tlkpJobTitle.JobTitleID = tblStakeholderDetails.fJobTitleID";

ORDER BY tblStakeholders.SHFName;

I created a new query in design view (which works perfectly on its
own) & pasted it into the sub - at each case, but kept the WHERE
clause - but alas no good. The sub is:

Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Dim strsql As String
Dim rst As DAO.Recordset
Select Case Me.cmdWhich
Case "Contains"
strsql = "LIKE " & quote & "*" & Me.findtext & "*" &
quote '(quote="""")
Case "Begins With"
strsql = "LIKE " & quote & Me.findtext & "*" & quote
Case "Equals"
strsql = "= " & quote & Me.findtext & quote
End Select
Select Case Me.frWhat
Case 1 'first name
strsql = "SELECT tblStakeholders.SHFName,
tblStakeholders.SHLName, tblOrganisation.OrgName,
tlkpJobTitle.JobTitle, tblStakeholderDetails.Inactive
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID=tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID=tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID=tblProjectOwnerStakeHolders.pos_Stakeholder)
ON tlkpJobTitle.JobTitleID=tblStakeholderDetails.fJobTitleID

WHERE (((tblStakeholders.SHFName)" & strsql & _
") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
"))ORDER BY tblStakeholders.SHLName,
tblStakeholders.SHFName;"'

' I'm not sure of the
" " either - is there a good text or link I could study?

Case 2 'last name
same again
WHERE (((tblStakeholders.SHLName)" & strsql & _
") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
"))ORDER BY
tblStakeholders.SHLName,tblStakeholders.SHFName;

Case 3 'org
WHERE (((tblOrganisation.OrgName) " & strsql & _
") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
"))ORDER BY
tblStakeholders.SHLName,tblStakeholders.SHFName;
End Select

Me.sfm_FindStakeholders.Form.RecordSource = strsql
Set rst = Me.sfm_FindStakeholders.Form.RecordsetClone
If rst.RecordCount = 1 Then
rst.MoveFirst
glbStakeholder = Me.sfm_FindStakeholders.Form.StakeHolderID
rst.Close
DoCmd.Close acForm, "frm_FindStakeholder"
Else
rst.Close
Me.sfm_FindStakeholders.SetFocus
End If

Thanks for your time
Peta
Click to expand...
 
P

pgcn

Peta, I'm not exactly sure what you're up to, but perhaps glbProjectOwner is
Null, so the query statement is incomplete?

This example shows how to build the WHERE clause, which you can then use in
various ways, e.g.:
- as the Filter of this form,
- as the WhereCondition of another OpenForm, or
- as the WHERE clause in a query (saved, or assigned to RecordSource of
form)

Hope this approach helps you to follow through what's going on. The line:
    Debug.Print strWhere
is to help you debug it. When if fails, open the Immediate Window (Ctrl+G)
and see what was printed there. Compare it to the WHERE clause in a working
query if you get stuck.

Here's the idea:
------------code starts---------------
Dim strWhere as String

Select Case Me.frWhat.Value
Case Me.optFirstName.OptionValue
    strWhere = "(tblStakeholders.SHFName"
Case Me.optSurname.OptionValue
    strWhere = "(tblStakeholders.SHLName"
Case Else
    strWhere = "(tblOrganisation.OrgName"
End Select

Select Case Me.cmdWhich
Case "Contains"
    strWhere = strWhere & " LIKE """*" & Me.findtext & "*"")"
Case "Begins With"
    strWhere = strWhere & " LIKE """ & Me.findtext & "*"")"
Case Else
    strWhere = strWhere & " = """ & Me.findtext & """)"
End Select

If Not IsNull(glbProjectOwner) Then
    strWhere = strWhere & " AND
(tblProjectOwnerStakeHolders.pos_ProjectOwner = " & glbProjectOwner & ")"
End If

Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
------------code ends---------------

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




Holiday greetings good people - please help with your expertise:
Click to expand...
I have a client search form [frm_FindStakeholder] which has an option
group: 1. First Name, 2. Last Name & 3. Organisation. Also a list box
[cmdWhich] with 3 options: "Contains", "Begins With" & "Equals". And
lastly an unbound text box [FindText].
Click to expand...
Upon clicking cmdFind I'm getting: Syntax error (missing operator).
I've tried numerous cobinations of ( )'s and have read all the
relevant posts I could find, but am no wiser.
Click to expand...
I've just taken the dbase over & have tried to modify the form from
returning records showing:
First Name & Last Name, & when organisation is searched - organisation
to
First Name, Last Name, Organisation, Job Title and Inactive.
Click to expand...
The original form was working with the following query:
Click to expand...
strsql = "SELECT tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive
Click to expand...
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID = tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID =
tblProjectOwnerStakeHolders.pos_Stakeholder) ON
tlkpJobTitle.JobTitleID = tblStakeholderDetails.fJobTitleID";
Click to expand...
ORDER BY tblStakeholders.SHFName;
Click to expand...
I created a new query in design view (which works perfectly on its
own) & pasted it into the sub - at each case, but kept the WHERE
clause - but alas no good. The sub is:
Click to expand...
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Dim strsql As String
Dim rst As DAO.Recordset
   Select Case Me.cmdWhich
       Case "Contains"
           strsql = "LIKE " & quote & "*" & Me.findtext & "*" &
quote             '(quote="""")
       Case "Begins With"
           strsql = "LIKE " & quote & Me.findtext & "*" & quote
       Case "Equals"
           strsql = "= " & quote & Me.findtext & quote
  End Select
Select Case Me.frWhat
       Case 1              'first name
           strsql = "SELECT tblStakeholders.SHFName,
tblStakeholders.SHLName, tblOrganisation.OrgName,
tlkpJobTitle.JobTitle, tblStakeholderDetails.Inactive
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID=tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID=tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID=tblProjectOwnerStakeHolders.pos_Stakeholder)
ON tlkpJobTitle.JobTitleID=tblStakeholderDetails.fJobTitleID
Click to expand...
WHERE (((tblStakeholders.SHFName)" & strsql & _
           ") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
           "))ORDER BY tblStakeholders.SHLName,
tblStakeholders.SHFName;"'
Click to expand...
'                                                  I'm not sure of the
" " either - is there a good text or link I could study?
Click to expand...
Case 2       'last name
same again
WHERE (((tblStakeholders.SHLName)" & strsql & _
           ") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
           "))ORDER BY
tblStakeholders.SHLName,tblStakeholders.SHFName;
Click to expand...
Case 3         'org
WHERE (((tblOrganisation.OrgName) " & strsql & _
           ") And ((tblProjectOwnerStakeHolders.pos_ProjectOwner) = "
& glbProjectOwner & _
           "))ORDER BY
tblStakeholders.SHLName,tblStakeholders.SHFName;
End Select
Click to expand...
Me.sfm_FindStakeholders.Form.RecordSource = strsql
  Set rst = Me.sfm_FindStakeholders.Form.RecordsetClone
  If rst.RecordCount = 1 Then
   rst.MoveFirst
       glbStakeholder = Me.sfm_FindStakeholders.Form.StakeHolderID
       rst.Close
       DoCmd.Close acForm, "frm_FindStakeholder"
  Else
   rst.Close
   Me.sfm_FindStakeholders.SetFocus
  End If
Click to expand...
Thanks for your time
Peta- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Allen but I think I need to clarify some details:

On my search form [frm_FindStakeholder] (details above) you enter the
criteria and by clicking cmdFind a VBA sub runs and the subform
[sfm_FindStakeholders] will hopefully display relevant rows with First
Name, Last Name, Organisation and Inactive fields.

I have used your code in the cmdFind_Click() as below:

Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Dim rst As DAO.Recordset
Dim strWhere As String

SELECT tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID = tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID =
tblProjectOwnerStakeHolders.pos_Stakeholder) ON
tlkpJobTitle.JobTitleID = tblStakeholderDetails.fJobTitleID
GROUP BY tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive, tblStakeholders.StakeHolderID,
tblProjectOwnerStakeHolders.pos_ProjectOwner
WHERE "Select Case Me.frWhat.Value" 'I added the " "
Case Me.optFirstName.OptionValue
strWhere = "(tblStakeholders.SHFName"
Case Me.optLastName.OptionValue
strWhere = "(tblStakeholders.SHLName"
Case Else
strWhere = "(tblOrganisation.OrgName"
End Select

Select Case Me.cmdWhich
Case "Contains"
strWhere = strWhere & " LIKE """*" & Me.findtext & "*"")"
Case "Begins With"
strWhere = strWhere & " LIKE """ & Me.findtext & "*"")"
Case Else
strWhere = strWhere & " = """ & Me.findtext & """)"
End Select
HAVING tblProjectOwnerStakeHolders.pos_ProjectOwner = 1
ORDER BY(tblStakeholders.SHLName)

Debug.Print strWhere
Me.sfm_FindStakeholders.Filter = strWhere
Me.sfm_FindStakeholders.FilterOn = True

Set rst = Me.sfm_FindStakeholders.Form.RecordsetClone
If rst.RecordCount = 1 Then
rst.MoveFirst
glbStakeholder = Me.sfm_FindStakeholders.Form.StakeHolderID
rst.Close
DoCmd.Close acForm, "frm_FindStakeholder"
Else
rst.Close
Me.sfm_FindStakeholders.SetFocus
End If

Exit_cmdFind_Click:
Exit Sub

However I'm getting a compile error "syntax error" on the select
statement & also the line: strWhere = strWhere & " LIKE """*" &
Me.findtext & "*"")" is in red.

I haven't been able to get the debug.print to work.
glbProject is set to 1 in the globals.

Hope this now makes a bit more sense.

Thanks for your help.

regards

Peta
 
D

Douglas J. Steele

You've got one too many quotes after the LIKE. It should be:

strWhere = strWhere & " LIKE ""*" & Me.findtext & "*"")"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)



However I'm getting a compile error "syntax error" on the select
statement & also the line: strWhere = strWhere & " LIKE """*" &
Me.findtext & "*"")" is in red.
 
P

pgcn

You've got one too many quotes after the LIKE. It should be:

strWhere = strWhere & " LIKE ""*" & Me.findtext & "*"")"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)



However I'm getting a compile error "syntax error" on the select
statement & also the line: strWhere = strWhere & " LIKE """*" &
Me.findtext & "*"")" is in red.
Click to expand...

Thank you Douglas, that line is fine now.
Still have the problem with the SELECT.

Peta
 
D

Douglas J. Steele

Your code is very wrong.. You can't just type a SQL statement into a VBA
module: you need to assign it to a string. But then, you don't need that SQL
statement in the code at all: what you're doing is creating a filter on the
form.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


You've got one too many quotes after the LIKE. It should be:

strWhere = strWhere & " LIKE ""*" & Me.findtext & "*"")"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)



However I'm getting a compile error "syntax error" on the select
statement & also the line: strWhere = strWhere & " LIKE """*" &
Me.findtext & "*"")" is in red.
Click to expand...

Thank you Douglas, that line is fine now.
Still have the problem with the SELECT.

Peta
 
P

pgcn

Your code is very wrong.. You can't just type a SQL statement into a VBA
module: you need to assign it to a string. But then, you don't need that SQL
statement in the code at all: what you're doing is creating a filter on the
form.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)








Thank you Douglas, that line is fine now.
Still have the problem with the SELECT.

Peta- Hide quoted text -

- Show quoted text -
Click to expand...

Ok - I've put the WHERE clause in the record source of the
sfm_FindStakeholders and that works fine.
Have a problem with the filtering:
I put Allen's Select in the filter of the same sfm_FindStakeholders,
along with: HAVING tblProjectOwnerStakeHolders.pos_ProjectOwner = 1

I modified the Private Sub cmdFind_Click() 'not really
sure what I'm doing here, but had to have a go!
On Error GoTo Err_cmdFind_Click
Dim rst As DAO.Recordset

DoCmd.ApplyFilter
Me.sfm_FindStakeholders.FilterOn = True 'getting the error Method or
data member not found (with .FilterOn = ) highlighted

Set rst = Me.sfm_FindStakeholders.Form.RecordsetClone 'if only
one matching record - displays it
If rst.RecordCount = 1 Then
rst.MoveFirst
glbStakeholder = Me.sfm_FindStakeholders.Form.StakeHolderID
rst.Close
DoCmd.Close acForm, "frm_FindStakeholder"
Else
rst.Close
Me.sfm_FindStakeholders.SetFocus
End If

Exit_cmdFind_Click:
Exit Sub

I would like to be able to open the form with the last search criteria/
records showing.
Also FYI when a number of records are returned I have a double-click
event on the First and Last Name fields to select and go to that
record's individual form.

Thanks & I appreciate the help
 
P

pgcn

Ok - I've put the WHERE clause in the record source of the
sfm_FindStakeholders and that works fine.
Have a problem with the filtering:
I put Allen's Select in the filter of the same sfm_FindStakeholders,
along with: HAVING tblProjectOwnerStakeHolders.pos_ProjectOwner = 1

I modified the Private Sub cmdFind_Click()             'not really
sure what I'm doing here, but had to have a go!
On Error GoTo Err_cmdFind_Click
Dim rst As DAO.Recordset

DoCmd.ApplyFilter
Me.sfm_FindStakeholders.FilterOn = True 'getting the error Method or
data member not found (with .FilterOn = ) highlighted

Set rst = Me.sfm_FindStakeholders.Form.RecordsetClone         'if only
one matching record - displays it
   If rst.RecordCount = 1 Then
    rst.MoveFirst
        glbStakeholder = Me.sfm_FindStakeholders.Form.StakeHolderID
        rst.Close
        DoCmd.Close acForm, "frm_FindStakeholder"
   Else
    rst.Close
    Me.sfm_FindStakeholders.SetFocus
   End If

Exit_cmdFind_Click:
    Exit Sub

I would like to be able to open the form with the last search criteria/
records showing.
Also FYI when a number of records are returned I have a double-click
event on the First and Last Name fields to select and go to that
record's individual form.

Thanks & I appreciate the help- Hide quoted text -

- Show quoted text -
Click to expand...


I've come back refreshed to holidays to try and tackle this again -
with your help.
Sorry there was an error in my message above.

"Ok - I've put the WHERE clause in the record source of the
sfm_FindStakeholders and that works fine."
- it should have been original SELECT statement. ie

The original SELECT statement:
SELECT tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID = tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID =
tblProjectOwnerStakeHolders.pos_Stakeholder) ON
tlkpJobTitle.JobTitleID = tblStakeholderDetails.fJobTitleID
GROUP BY tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive, tblStakeholders.StakeHolderID,
tblProjectOwnerStakeHolders.pos_ProjectOwner

I have saved it as qflt_FindStakeholders and have put this name in the
record source of the subform sfm_FindStakeholders.

In the sfm_FindStakeholders properties filter I have:
Select Case Me.frWhat.Value Case Me.optFirstName.OptionValue strWhere
= "(tblStakeholders.SHFName" Case Me.optLastName.OptionValue strWhere
= "(tblStakeholders.SHLName" Case Else strWhere =
"(tblOrganisation.OrgName" End Select Select Case Me.cmdWhich Case
"Contains" strWhere = strWhere & " LIKE ""*" & Me.findtext & "*"")"
Case "Begins With" strWhere = strWhere & " LIKE """ & Me.findtext &
"*"")" Case Else strWhere = strWhere & " = """ & Me.findtext & """)"
End Select
HAVING tblProjectOwnerStakeHolders.pos_ProjectOwner = 1

and Order By:
ORDER BY tblStakeholders.SHLName

Is this correct?
I'm now getting - Method or data member not found (with .FilterOn = )
highlighted
in the Sub cmdFind_Click() - see above. I have also tried running
it without DoCmd.ApplyFilter
and it doesn't make a difference.

How do I get the criteria entered in frm_FindStakeholder to be applied
to the subform?
I would like to be able to open frm_FindStakeholder with the last search criteria/
records showing.
Also FYI when a number of records are returned I have a double-click
event on the First and Last Name fields to select and go to that
record's individual form.
Click to expand...

cheers.
 
P

pgcn

I've come back refreshed to holidays to try and tackle this again -
with your help.
Sorry there was an error in my message above.

"Ok - I've put the WHERE clause in the record source of the
 sfm_FindStakeholders and that works fine."
- it should have been original SELECT statement. ie

The original SELECT statement:
SELECT tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive
FROM tlkpJobTitle INNER JOIN ((tblStakeholders INNER JOIN
(tblStakeholderDetails INNER JOIN tblOrganisation ON
tblStakeholderDetails.fOrgID = tblOrganisation.OrganisationID) ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID)
INNER JOIN tblProjectOwnerStakeHolders ON
tblStakeholders.StakeHolderID =
tblProjectOwnerStakeHolders.pos_Stakeholder) ON
tlkpJobTitle.JobTitleID = tblStakeholderDetails.fJobTitleID
GROUP BY tblStakeholders.SHFName, tblStakeholders.SHLName,
tblOrganisation.OrgName, tlkpJobTitle.JobTitle,
tblStakeholderDetails.Inactive, tblStakeholders.StakeHolderID,
tblProjectOwnerStakeHolders.pos_ProjectOwner

I have saved it as qflt_FindStakeholders and have put this name in the
record source of the subform sfm_FindStakeholders.

In the sfm_FindStakeholders properties filter I have:
Select Case Me.frWhat.Value Case Me.optFirstName.OptionValue strWhere
= "(tblStakeholders.SHFName" Case Me.optLastName.OptionValue strWhere
= "(tblStakeholders.SHLName" Case Else strWhere =
"(tblOrganisation.OrgName" End Select Select Case Me.cmdWhich Case
"Contains" strWhere = strWhere & " LIKE ""*" & Me.findtext & "*"")"
Case "Begins With" strWhere = strWhere & " LIKE """ & Me.findtext &
"*"")" Case Else strWhere = strWhere & " = """ & Me.findtext & """)"
End Select
HAVING tblProjectOwnerStakeHolders.pos_ProjectOwner = 1

and Order By:
ORDER BY tblStakeholders.SHLName

Is this correct?
I'm now getting - Method or data member not found (with .FilterOn = )
highlighted
in the Sub cmdFind_Click()    - see above. I have also tried running
it without DoCmd.ApplyFilter
and it doesn't make a difference.

How do I get the criteria entered in frm_FindStakeholder to be applied
to the subform?


cheers.- Hide quoted text -

- Show quoted text -
Click to expand...

FYI - this is an Access 2000 file format running in Access 2003. Would
this make any difference.

Thanks
Peta
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to search & replace chr in multiple fields and records 1

Top