vba access 2007 open query from function

Question

In order to avoid duplicating code I've tried to use a function in a module instead of a sub related to a form. The sub was already made by somebody else, I just have to make some minor alterations to get it to work properly as a function. Doing so I got stuck on something very simple, and sadly I have not been able to find the solution elsewhere.

In this function I would like to open a query which is in my database as a string, since additional filters will be added to the query before it is executed. How should I open this query?

The code as it was as a sub is shown below.

Private Sub Unhide_Click()
    Dim query_pre As String
    Dim whereStr As String

    whereStr = " (AND ((IIF(IsNull([Actions complete].OTL_man), ([Actions complete].ATL_man) < DateAdd('m', 6, Date()),
                                                               ([Actions complete].OTL_man) < DateAdd('m', 6, Date())))" & _
               " AND (([Actions complete].Finished) Is Null)))"

    owner_id = Nz(DLookup("ID", "Owners", "[Full Name] like '*" & prefilter.Value & "*' "), -1)
    sender_id = Nz(DLookup("ID", "Senders", "[Sender] like '*" & prefilter.Value & "*' "), -1)

    query_pre = " [Actions complete].[Ref_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[owner_man] = " & owner_id & _
                " OR [Actions complete].[action_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[Sender_man] = " & sender_id & _
                " OR [Actions complete].[ATL_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[OTL_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[finished] Like '*" & prefilter.Value & "*' "

    final_query2 = "SELECT [Actions complete].* FROM [Actions complete] WHERE (( " & query_pre & " ) " & whereStr & ") ORDER BY [OTL_man] ASC "

    final_query3 = "SELECT [Actions complete].* FROM [Actions complete] WHERE ( " & query_pre & " ) ORDER BY [OTL_man] ASC"

    If Unhide.Value = True And CurrentDb.OpenRecordset(final_query3).RecordCount <> 0 Then
        Me.RecordSource = final_query3
    ElseIf Unhide.Value = False And CurrentDb.OpenRecordset(final_query2).RecordCount <> 0 Then
        Me.RecordSource = final_query2
    Else
        MsgBox "There are no records matching the filter criteria. The filter was ignored"
    End If
End Sub

Now I want to change this code so that what was the "Actions complete" can be variable depending on the situation. Therefore I transferred to to a function, as said to avoid duplicating code.

Function mail_filter(Past_unhide As Boolean, Inactive_unhide As Boolean)
    Dim query_pre As String
    Dim where As String
    Dim Relevant_Query As String

    If Inactive_unhide = False Then
        Relevant_Query = DoCmd.OpenQuery "[Actions complete]"               'This is where i am stuck right now
    Else
        Relevant_Query = DoCmd.OpenQuery([Actions complete with inactive])  'This is where i am stuck right now
    End If

where = "AND ((  IIF(isnull([Relevant_Query].OTL_man), ([Relevant_Query].ATL_man)<DateAdd('m',6,Date()) ,([Relevant_Query].OTL_man)<DateAdd('m',6,Date()) ) ) AND (([Relevant_Query].Finished) Is Null)) "
query_pre = " "

owner_id = Nz(DLookup("ID", "Owners", "[Full Name] like '*" & prefilter.Value & "*' "), -1)
sender_id = Nz(DLookup("ID", "Senders", "[Sender] like '*" & prefilter.Value & "*' "), -1)

query_pre = " [Relevant_Query].[Ref_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[owner_man] = " & owner_id & " OR [Relevant_Query].[action_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[Sender_man] = " & sender_id & " OR [Relevant_Query].[ATL_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[OTL_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[finished] Like '*" & prefilter.Value & "*' "

final_query2 = "SELECT [Relevant_Query].* FROM [Relevant_Query] WHERE ( " & query_pre & " ) " & where & " ORDER BY [OTL_man] ASC"
final_query3 = "SELECT [Relevant_Query].* FROM [Relevant_Query] WHERE ( " & query_pre & " ) ORDER BY [OTL_man] ASC"
If Forms!mail_v3!Past_unhide.Value = True And CurrentDb.OpenRecordset(final_query3).RecordCount <> 0 Then
    Forms!mail_v3.RecordSource = final_query3
ElseIf Forms!mail_v3!Past_unhide.Value = False And CurrentDb.OpenRecordset(final_query2).RecordCount <> 0 Then
    Forms!mail_v3.RecordSource = final_query2
Else
    MsgBox "There are no records matching the filter criteria. The filter was ignored"
End If

End Function

Somehow it is not clear to Acces what query I am trying to open. How should I do this?

Made some edits to the code, to make it more readable and also added some paraens to make it more legible. — PaulFrancis
– PaulFrancis, Commented Aug 12, 2014 at 10:39

Patrick Honorez · Accepted Answer · 2014-08-12 10:36:29Z

2

Docmd.OpenQuery "Actions complete"

should do the trick. OpenQuery requires a string for the name of the query.

edited Aug 12, 2014 at 10:36

answered Aug 12, 2014 at 9:43

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3932582 Over a year ago

Thank you for your response. Sadly this does not work, I receive the error: compile error - expected function or variable. Maby I should specify to which database I am referring, how should I do that?

Fionnuala Over a year ago

As it is a string, I very much doubt that the square brackets are part of the name.

Patrick Honorez Over a year ago

on what line ? That instruction cannot give a compile error I think

Fionnuala Over a year ago

I also think that Relevant_Query = will never work, is that what you are implying?

user3932582 Over a year ago

@Fionnuala I am trying to choose eighter the "actions complete" query or the "actions complete with inactive" query, depending on the value of unhide inactive.

|

Patrick Honorez · Accepted Answer · 2014-08-12 11:45:09Z

1

Question was a bit unclear. I think what you expect something is this:

If Inactive_unhide = False Then
    Relevant_Query = "Actions complete" 
Else
    Relevant_Query = "Actions complete with inactive"
End If
'more stuff here
'....
final_query2 = "SELECT * FROM [" & Relevant_Query & "] WHERE..."

answered Aug 12, 2014 at 11:45

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

3 Comments

Fionnuala Over a year ago

Hi I think you would be better off adding this to your previous answer.

user3932582 Over a year ago

That's it! Now I finally understand what was going wrong. Combined with changing the other strings as well, I've been able to get it to work. Thank you very much!

Patrick Honorez Over a year ago

@Fionnuala: You're right, but I think it's a bit late now, isn't it ? :-/

PaulFrancis · Accepted Answer · 2014-08-12 09:42:55Z

0

DoCmd.OpenQuery takes in the name of the Query as a String Argument so you have to use "" something like.

DoCmd.OpenQuery "[Actoions Complete]"

On the other hand if you wish to include filters, you might think about using VBa, or QueryDef object to play with the parameters passed to the Query or modify the Query even.

answered Aug 12, 2014 at 9:42

PaulFrancis

5,8191 gold badge23 silver badges38 bronze badges

4 Comments

user3932582 Over a year ago

Thank you for your response. Sadly this does not work, I receive the error: compile error - expected function or variable. Maby I should specify to which database I am referring, how should I do that?

PaulFrancis Over a year ago

@user3932582 Where exactly are you using the code? Can you edit your original post to show the complete code?

user3932582 Over a year ago

Done. Thank you in advance for looking into it.

PaulFrancis Over a year ago

@user3932582, I think the issue is you are trying to assign the DoCmd.OpenQuery to a String. DoCmd is a function and it needs to be called simply as Docmd.OpenQuery "Actions Complete", no need to assign it to something.

Collectives™ on Stack Overflow

vba access 2007 open query from function

3 Answers 3

6 Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related