VBA - Access 2007 - Referencing Form Functions in a Query

[weasel7711]

I have a small company Database that tracks our repair orders. I have a query that I would like to execute based on criteria that the user inputs. I am new to VBA so most of my code is based on google research. I took a technique of wrapping my criteria variables with get/set functions. Supposedly, according to this site: http://msdn.microsoft.com/en-us/library/dd671279(v=office.12).aspx it is possible to reference the functions "Get______()" through the query if I specify them as in the example on that site (by calling the function in the criteria field of the design mode view of the query).

However I am getting this error when I click on one of the buttons which executes either of the instances of the "Whiteboard" query, I get the error of "Undefined function 'GetCustomer' in expression"

Not sure what I am doing here, it could be something really obvious. I have given a copy of my code below:

Option Compare Database

'declarations
Private argStatus As String
Private argCustomer As String
Private argManufacturer As String
Private argType As String
Private argDateFrom As Date
Private argDateUntil As Date
    
Public Sub SetStatus(Value As String)
    argStatus = Value
End Sub
Public Sub SetCustomer(Value As String)
    argCustomer = Value
End Sub
Public Sub SetManufacturer(Value As String)
    argManufacturer = Value
End Sub
Public Sub SetType(Value As String)
    argType = Value
End Sub
Public Sub SetDateFrom(Value As Date)
    argDateFrom = Value
End Sub
Public Sub SetDateUntil(Value As Date)
    argDateUntil = Value
End Sub
Public Function GetStatus()
    GetStatus = argStatus
End Function
Public Function GetCustomer()
    GetCustomer = argCustomer
End Function
Public Function GetManufacturer()
    GetManufacturer = argManufacturer
End Function
Public Function GetType()
    GetType = argType
End Function
Public Function GetDateFrom()
    GetDateFrom = argDateFrom
End Function
Public Function GetDateUntil()
    GetDateUntil = argDateUntil
End Function

Private Sub Cmd_ClearAll_Click()
    'Clear all fields, except date
    Chk_Status.Value = False
    Chk_Customer.Value = False
    Chk_Manufacturer.Value = False
    Chk_Type.Value = False
    Chk_Date.Value = False
    Cmb_Status.Value = ""
    Cmb_Customer.Value = ""
    Cmb_Manufacturer.Value = ""
    Cmb_Type.Value = ""
End Sub

Private Sub Cmd_SpecView_Click()
    
    'Evaluate Check Boxes and Search Criteria
    'If the checkboxes are checked, set value of Criteria
    
    'Status
    If Chk_Status.Value = True Then
        SetStatus (Cmb_Status.Value)
        End If
    'Customer
    If Chk_Customer.Value = True Then
        SetCustomer (Cmb_Customer.Value)
        End If
    'Manufacturer
    If Chk_Manufacturer.Value = True Then
        SetManufacturer (Cmb_Manufacturer.Value)
        End If
    'Type
    If Chk_Type.Value = True Then
        SetType (Cmb_Type.Value)
        End If
    'Date Range
    If Chk_Date.Value = True Then
        SetDateFrom (Txt_DateFrom.Value)
        SetDateUntil (Txt_DateUntil.Value)
        End If
    
    'Send these values to a query
    DoCmd.OpenQuery ("Whiteboard")
    
End Sub

Private Sub Cmd_ViewStatus_Click()
    'Opens Query
    'Default Values Are Taken
    DoCmd.OpenQuery ("Whiteboard")
End Sub

Private Sub Form_Load()

    'Initializations
    SetStatus ("*")
    SetCustomer ("*")
    SetManufacturer ("*")
    SetType ("*")
    SetDateFrom (#1/1/2001#)
    SetDateUntil (Date)
    
End Sub

[lolafuertes]

Can you be so kind to put here the SQL text of your query?

[weasel7711]

SELECT tblRMA.RMA, tblRMA.Customer, tblRMA.Status, tblRMA.PONum, tblProduct.Serial, tblProduct.Model, tblRMA.DateAssigned
FROM tblRMA INNER JOIN (tblModel INNER JOIN tblProduct ON (tblModel.Model = tblProduct.Model) AND (tblModel.Model = tblProduct.Model)) ON tblRMA.RMA = tblProduct.RMA
WHERE (((tblRMA.Customer)=GetCustomer()) AND ((tblModel.Manufacturer)=GetManufacturer()) AND ((tblModel.Type)=GetType()))
ORDER BY tblRMA.PONum;

I didn't write the code from scratch, it generated from the GUI query builder native to Access. All I did was add the 3 tables (tblRMA, tblProduct, tblModel) and specify as criteria the get function for the specific field. So for Customer field, on the query criteria row under the tblRMA.Customer column I specified GetCustomer().
That's what the website I referenced said was the best way to do it. I normally use C++ and this is my first experience with VBA so I am not sure how to pass variables around.

[lolafuertes]

That is fine.

Just I am guessing that when you reference the functions in the query, you do not specify that they came from a form(Ie: MyForm.GetCustomer()).

If I remember well, the form functions are not in memory if the form is not opened, so are not allways accessible.

To avoid this situation, the 'best' solution is to place these functions in a new Module ( not form depending) and always being defined as public. Also, you'll need to move all the private fields referenced by them to public fields in the module. My suggestion is to pass all the set and al the get functions.

Then you best change the where clause to be like

(IIF(IssNull(GetCustomer()), True, (tblRMA.Customer)=GetCustomer()) AND IIF(IsNull(Getmanufacturer(), True, (tblModel.Manufacturer)=GetManufacturer()) AND ((tblModel.Type)=GetType()))

This way, in case of null answers will not filter by this column (least bad option).

Hope this helps.

[weasel7711]

That makes a lot of sense. Thank you.

Now with modules, can you just create them and as long as the specified variables/functions are declared as public they can be accessed throughout the Access DB in all forms/queries/reports/etc?