CheckBox Function In Queries 1

[ccepaulb]

I'm looking for someone that knows how to link check boxes on a form to the "show" check-box in a query. Basically, all I'm trying to do is have the end-user tell the query which fields to show in the output. If checkbox "A" is checked on a form prior to kicking off the query, I'd like field "A" to show in the query output...and so on.

Thanks, Paul

 

[jebry]

Hi!

To my knowledge you can't link the show box to a form field. There are a couple of alternatives if you are willing to use code. You can build the query dynamically in VBA using the check boxes to determine which fields to use:

strSQL = "Select"

If chkA = True Then
If Len(strSQL) = 6 Then
strSQL = strSQL & " FieldA"
Else
strSQL = strSQL & ", FieldA"
End If
End If

Etc for each box

strSQL = strSQL & " From YourTable"


Then you can use the SQL to create a new query.

Dim qryDef As DAO.QueryDef
Dim db as DAO.Database

Set db = CurrentDb

Set qryDef = db.CreateQueryDef("YourName", strSQL)

DoCmd.OpenQuery "YourName"

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ccepaulb]

Wow! Seems a lot more complicated than I had hoped!

Thanks, Paul

 

[ccepaulb]

Here is the SQL of my query if anyone can help with how to build in a way to have a check box from a form dictate which field to show in the query output.

Thanks, Paul

SELECT DISTINCTROW [CORE TABLE].CATEGORY, [CORE TABLE].SEGMENT, [CORE TABLE].[SUB SEGMENT], [CORE TABLE].CORE_UPC, [CORE TABLE].CORE_ITEM, Sum(ACNDATA.ACNUNITVOLCUR) AS [Sum Of ACNUNITVOLCUR], Sum(ACNDATA.ACNUNITVOLPRIOR) AS [Sum Of ACNUNITVOLPRIOR], Format([Sum Of ACNUNITVOLCUR]-[Sum Of ACNUNITVOLPRIOR],"#,###") AS UNITACTCHG, Format([UNITACTCHG]/[Sum Of ACNUNITVOLPRIOR],"Percent") AS UNITPERCCHG, Sum(ACNDATA.ACNDOLVOLCURR) AS [Sum Of ACNDOLVOLCURR], Sum(ACNDATA.ACNDOLVOLPRIOR) AS [Sum Of ACNDOLVOLPRIOR], Format([Sum Of ACNDOLVOLCURR]-[Sum Of ACNDOLVOLPRIOR],"$#,###") AS DOLACTCHG, Format([DOLACTCHG]/[Sum Of ACNDOLVOLPRIOR],"Percent") AS DOLPERCCHG, [CORE TABLE].MANUFACTURER, [CORE TABLE].BRAND, [CORE TABLE].FORMAT, [CORE TABLE].TYPE, [CORE TABLE].FLAVOR, [CORE TABLE].UNIT_SIZE
FROM (ACNDATA INNER JOIN [ACN TABLE] ON ACNDATA.ACN_SDESC = [ACN TABLE].ACN_SDESC) INNER JOIN ([CORE TABLE] INNER JOIN [UPC TABLE] ON [CORE TABLE].CORE_UPC = [UPC TABLE].CORE_UPC) ON [ACN TABLE].ACN_UPC = [UPC TABLE].ACN_UPC
GROUP BY [CORE TABLE].CATEGORY, [CORE TABLE].SEGMENT, [CORE TABLE].[SUB SEGMENT], [CORE TABLE].CORE_UPC, [CORE TABLE].CORE_ITEM, [CORE TABLE].MANUFACTURER, [CORE TABLE].BRAND, [CORE TABLE].FORMAT, [CORE TABLE].TYPE, [CORE TABLE].FLAVOR, [CORE TABLE].UNIT_SIZE
HAVING (((Sum(ACNDATA.ACNUNITVOLCUR))>0) AND ((Sum(ACNDATA.ACNUNITVOLPRIOR))>0) AND ((Sum(ACNDATA.ACNDOLVOLCURR))>0) AND ((Sum(ACNDATA.ACNDOLVOLPRIOR))>0))
ORDER BY [CORE TABLE].CATEGORY, [CORE TABLE].CORE_ITEM;

 

[jebry]

Hi Paul!

I forgot to give you the second alternative:

You can build a series of queries with the appropriate combination of fields and then use a series of If statements to find out which query to open. This is not a valid alternative if you have a lot of fields.

Actually, there is a third alternative:

Dim qryDef As DAO.QueryDef

Set qryDef = CurrentDb("YourQuery")

If chkA = False Then
qryDef.Fields("FieldA").Delete
End If

etc.

DoCmd.OpenQuery "YourQuery"

Then you will have to loop back through the check boxes and put the fields back for the next time.

You can compact the code by using and control objects and looping through the controls on the form. Let me know what you are interested in and I can help. I can't give you my email address, but go to this link and you can search for it.

http://www.itap.purdue.edu/directory/

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ccepaulb]

Jeff,

I received your suggestion via e-mail and it works perfectly...Thank you!!!

For anyone that may be interested, here is how Jeff solved my problem.

He wrote this code and associated with "click" on a button on the form next tot he check boxes.


Private Sub cmdRun_Click()


Dim strSQL As String
Dim strFrom As String
Dim strGroupBy As String
Dim strHaving As String
Dim strOrderBy As String
Dim cntl As Control
Dim NewQuery As DAO.QueryDef


DoCmd.DeleteObject acQuery, "ACN Data"

strFrom = " FROM (ACNDATA INNER JOIN [ACN TABLE] ON ACNDATA.ACN_SDESC = [ACN TABLE].ACN_SDESC) INNER JOIN " & _
"([CORE TABLE] INNER JOIN [UPC TABLE] ON [CORE TABLE].CORE_UPC = [UPC TABLE].CORE_UPC) ON [ACN TABLE].ACN_UPC = [UPC TABLE].ACN_UPC"
strGroupBy = " GROUP BY [CORE TABLE].CATEGORY, [CORE TABLE].SEGMENT, [CORE TABLE].[SUB SEGMENT], [CORE TABLE].CORE_UPC, " & _
"[CORE TABLE].CORE_ITEM, [CORE TABLE].MANUFACTURER, [CORE TABLE].BRAND, [CORE TABLE].FORMAT, " & _
"[CORE TABLE].TYPE, [CORE TABLE].FLAVOR, [CORE TABLE].UNIT_SIZE"
strHaving = " HAVING (((Sum(ACNDATA.ACNUNITVOLCUR))>0) AND ((Sum(ACNDATA.ACNUNITVOLPRIOR))>0) AND " & _
"((Sum(ACNDATA.ACNDOLVOLCURR))>0) AND ((Sum(ACNDATA.ACNDOLVOLPRIOR))>0))"
strOrderBy = " ORDER BY [CORE TABLE].CATEGORY, [CORE TABLE].CORE_ITEM;"

strSQL = "SELECT DISTINCTROW [CORE TABLE].CATEGORY, [Core Table].Core_Item"

For Each cntl In Me.Controls
If cntl.ControlType = acCheckBox Then
If cntl.Value = True Then
If cntl.Name = "chkSub Segment" Then
strSQL = strSQL & ", [Core Table].[" & Mid(cntl.Name, 4) & "]"
Else
strSQL = strSQL & ", [Core Table]." & Mid(cntl.Name, 4)
End If
End If
End If
Next cntl


Thanks Again Jeff,

Paul