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
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
Public Sub SetCustomer(Value As String)
argCustomer = Value
Public Sub SetManufacturer(Value As String)
argManufacturer = Value
Public Sub SetType(Value As String)
argType = Value
Public Sub SetDateFrom(Value As Date)
argDateFrom = Value
Public Sub SetDateUntil(Value As Date)
argDateUntil = Value
Public Function GetStatus()
GetStatus = argStatus
Public Function GetCustomer()
GetCustomer = argCustomer
Public Function GetManufacturer()
GetManufacturer = argManufacturer
Public Function GetType()
GetType = argType
Public Function GetDateFrom()
GetDateFrom = argDateFrom
Public Function GetDateUntil()
GetDateUntil = argDateUntil
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 = ""
Private Sub Cmd_SpecView_Click()
'Evaluate Check Boxes and Search Criteria
'If the checkboxes are checked, set value of Criteria
If Chk_Status.Value = True Then
If Chk_Customer.Value = True Then
If Chk_Manufacturer.Value = True Then
If Chk_Type.Value = True Then
If Chk_Date.Value = True Then
'Send these values to a query
Private Sub Cmd_ViewStatus_Click()
'Default Values Are Taken
Private Sub Form_Load()
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.
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).