CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Sinister Beard
Sinister Beard

Reputation: 3680

Specify a default value when a value gained from a form is not available

I have a query in Access 2010 which uses a value from a form as one of its criteria:

WHERE (Contactnames.[Email status])=[Forms]![Reports]![Email status] OR [Forms]![Reports]![Email status])="All statuses"

If an option is chosen from the drop down box Email status, which contains "Valid", "Invalid", "Unsubscribed" etc, the query matches records with that value; if "All statuses" is selected, you get all the records.

This works as long as the form is open. However, if the form isn't open, the query of course can't find the value and asks for user input.

Is there any way of specifying a default value if the form that would normally provide the value isn't open? I'm happy to work with VBA if this goes beyond the normal capabilities of Access' SQL engine.

EDIT: As a workaround, I've created a duplicate query but without the filter condition that I can call when that Form isn't in use, but I'd still value a more elegant answer than that.

Upvotes: 0

Views: 54

Answers (1)

kismert
kismert

Reputation: 1692

I would use a global function. In a standard module, enter code:

Public Function GetReportsEmailStatus() As Variant
    Dim oControl As Access.Control
    On Error Resume Next        
    Set oControl = Application.Forms("Reports").Controls("Email status")
    If Err = 0 Then
        GetReportsEmailStatus = oControl.Value
    Else
        GetReportsEmailStatus = "All statuses"
    End If
End Function

Then, in your query: 

WHERE (Contactnames.[Email status]) = GetReportsEmailStatus() 
OR GetReportsEmailStatus() = "All statuses"

This is efficient because, in Access queries, functions with no parameters get called only once, at the start of query execution.

Upvotes: 2

Related Questions