CODEWITHSUNDEEP
sqlvbams-access
cm2115
cm2115

Reputation: 61

Access Filter By Field

Just curious if filtering fields is a possibility in Access? Or if there is VBA code that allows the "Show" Box in the query to be checked or unchecked based on an if statement?

Basically I have about 80 fields in my database and I want to create a table with only 3 of them, based on a value in a combo box. 2 are always the same, and the 3rd would be based on a combo box.

Can I do that?

Upvotes: 0

Views: 482

Answers (1)

ArcherBird
ArcherBird

Reputation: 2134

You could do this by altering the SQL of the query object you want to modify. So if your query object is named "qryMyQ", and your combo box object is named "cboDropDown", then the VBA would be the combo box's AfterUpdate event and would look like this:

Private Sub cboDropDown_AfterUpdate()
    Dim qryDef As QueryDef
    Dim sql As String

    Set qryDef = CurrentDb.QueryDefs("qryMyQ")

    sql = "SELECT [Column1], [Column2], [" & cboDropDown.Value & _ 
          "] FROM yourTableName " & _
          " WHERE [" & cboDropDown.Value & "] = ""applicable"""

    Debug.Print sql 'This line will allow you to troubleshoot the SQL that is to be executed.'
    qryDef.sql = sql

    Set qryDef = Nothing
End Sub

Just a note here; but you'd want to make sure that users cannot add or edit the combo box options to avoid sql injections. Not sure how friendly your userbase is.

Upvotes: 1

Related Questions