MS Access Query using parameters from a form

  Gary Wood 15:38 30 May 06
Locked

I am creating a database with MS Access. I want a select query to choose certain records from a table and display them, depending on which if six values features in a specific field.

I want to do this using a form, so that I can have all six values on the form as check boxes, display the form and allow the user to tick which ones to include in the query, and then run the query to select values that match those ticked.

Can anyone advise me how to do this?

Thanks,

Gary

  Crunchy 17:09 30 May 06

If your six values will never change you could create a form with six labels to display your values and six check boxes to tick. If you want the records to display on this form add a subform which is looking at your table. Use the IIF statement to select your records ie

IIF([Forms]![YourFormName]![Check1]= -1,"Value1","") Or IIF([Forms]![YourFormName]![Check2]= -1,"Value2",""

Add 4 more Or IIF statements. Value1 and Value2 are to match your data.

Hope this gets you started.

  Main Access 20:38 30 May 06

you can only do this using vba dynamicaly, you will have to interigate each check box then build your query from that

  Main Access 13:19 31 May 06

PART 1
'Create a form with 7 check boxs and two command button

'Name the check boxs chk1 trough to chk6

'name the 7th check box chkAll

'Name the command button cmdOpenQuery

'with the other in the ON CLICK event enter =BuildTable()

Function BuildTable()

Dim DB As DAO.Database

Dim myTdf As TableDef

On Error GoTo err_Handler

'This will build the table required for the demo

Set DB = CurrentDb

Set myTdf = DB.CreateTableDef("tbl_Data")

With myTdf

.Fields.Append .CreateField("Value1", dbBoolean)

.Fields.Append .CreateField("Value2", dbBoolean)

.Fields.Append .CreateField("Value3", dbBoolean)

.Fields.Append .CreateField("Value4", dbBoolean)

.Fields.Append .CreateField("Value5", dbBoolean)

.Fields.Append .CreateField("Value6", dbBoolean)

End With

DB.TableDefs.Append myTdf

err_Handler_Exit:

Exit Function

err_Handler:

Select Case Err

Case 3010

DB.TableDefs.Delete "tbl_Data"

Resume

Case Else

MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error"

End Select

Resume err_Handler_Exit

End Function

Function BuildWhereStatement() As String

Dim strSQL As String

Dim blnSelected As Boolean

'set the default query for non selected"

strSQL = "Value1 = false AND Value2 = false AND Value3 = false AND Value4 = false AND Value5 = false AND Value6 = false"

'Find the value of the checkbox

If chk1 = True Then

'Check to see if anything has been selected before

'If it hasnt the getrid of the default where

'and buid a new one

If Not blnSelected Then

strSQL = ""

blnSelected = True

End If

'If we already have a value in the return the put an OR into the string

'This is obviously not necessary in the first one, but its good practice

'to put it in

If Trim(" " & strSQL) <> "" Then

strSQL = strSQL & " OR "

End If

strSQL = strSQL & " VALUE1 = true"

End If

If chk2 = True Then

If Not blnSelected Then

strSQL = ""

blnSelected = True

End If

If Trim(" " & strSQL) <> "" Then

strSQL = strSQL & " OR "

End If

strSQL = strSQL & " VALUE2 = true"

End If

If chk3 = True Then

If Not blnSelected Then

strSQL = ""

blnSelected = True

End If

If Trim(" " & strSQL) <> "" Then

strSQL = strSQL & " OR "

End If

strSQL = strSQL & " VALUE3 = true"

End If

If chk4 = True Then

If Not blnSelected Then

strSQL = ""

blnSelected = True

End If

If Trim(" " & strSQL) <> "" Then

strSQL = strSQL & " OR "

End If

strSQL = strSQL & " VALUE4 = true"

End If

If chk5 = True Then

If Not blnSelected Then

strSQL = ""

blnSelected = True

End If

If Trim(" " & strSQL) <> "" Then

strSQL = strSQL & " OR "

End If

strSQL = strSQL & " VALUE5 = true"

End If

If chk6 = True Then

If Not blnSelected Then

strSQL = ""

blnSelected = True

End If

If Trim(" " & strSQL) <> "" Then

strSQL = strSQL & " OR "

End If

strSQL = strSQL & " VALUE6 = true"

End If


If Trim(" " & strSQL) <> "" Then

strSQL = " WHERE " & strSQL

End If

BuildWhereStatement = strSQL

End Function

  Main Access 13:19 31 May 06

Private Sub chkAll_AfterUpdate()

Dim ctrl As Control

'Look at each control on the form

For Each ctrl In Me.Controls

'If the control is a checkbox

If ctrl.ControlType = acCheckBox Then

'Find out that it is not the calling checkbox

If ctrl.Name <> "chkall" Then

ctrl.Value = chkAll.Value

End If

End If

Next

End Sub

Private Sub cmdOpenQuery_Click()

Dim DB As DAO.Database

Dim myqry As DAO.QueryDef

Dim strSQL As String

On Error GoTo err_Handler

DoCmd.Close acQuery, "qry_myQuery", acSaveNo

strSQL = "SELECT tbl_Data.* FROM tbl_Data " & BuildWhereStatement

Set DB = CurrentDb

Set myqry = DB.CreateQueryDef("qry_myQuery", strSQL)

DoCmd.OpenQuery "qry_myQuery"

err_Handler_Exit:

Exit Sub

err_Handler:

Select Case Err

Case 3012 ' Query already exist, so delete it and try again

DB.QueryDefs.Delete "qry_myQuery"

Resume

Case Else

MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error"

End Select

Resume err_Handler_Exit

End Sub

  Gary Wood 15:28 01 Jun 06

Thanks Main Access.
I'll give the code a go.

Gary

This thread is now locked and can not be replied to.

iPhone 7 review: a range of small updates add up to an excellent phone

1995-2015: How technology has changed the world in 20 years

How New York’s Stylin’ Seniors became a golden social media campaign

23 Apple Watch tips & secret features: Master your Apple Watch, Apple Watch Series 1 or Apple…