469,330 Members | 1,329 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Check Boxes to Pass into Query

ChaseCox
294 100+
I would like to use a Check Box, or several check boxes, that will allow a user to select differnent product lines. The user should be able to select one or many. I also need each check box to reference numerous product codes, becuase each product line has several 4 digit codes, each unique to its product line. For example:

All of the Happy Lines Codes might be 0412, 0413, 0456, 0458
All of the Sad Lines Codes might be 0524, 0544, 0569, 0588

thanks.
Jan 9 '07
71 5552
NeoPa
32,181 Expert Mod 16PB
In truth, I doubt I'll be able to do much in that time although it's not impossible.
I'll try to leave you something for your return at least though.
Jan 12 '07 #51
ChaseCox
294 100+
In truth, I doubt I'll be able to do much in that time although it's not impossible.
I'll try to leave you something for your return at least though.
Ok thank you very much. Have a good weekend. Quit question, Are you working this late, or are you on your own time helping me? Either way I am Tremendously appreciative.
Jan 12 '07 #52
NeoPa
32,181 Expert Mod 16PB
Not working really.
Just doing TSDN work from my work PC.
Jan 12 '07 #53
NeoPa
32,181 Expert Mod 16PB
Right, I'll try to post in here all the issues I come across while looking at your database so that you get the feedback, but also anyone reading the thread will be able to follow what's happened.
Firstly I notice that this is an Access 97 database.
Jan 12 '07 #54
NeoPa
32,181 Expert Mod 16PB
The page you use with all the CheckBoxes on is essentially a sibling of the two pages (subforms) you want to populate when you click the command buttons.
This means that the strFilter will need to be available globally (available to all procedures for the whole form) I'll have to look into what is available where between different form objects within subform objects of a main form.
I'll repost when I've made some progress.
Jan 12 '07 #55
ChaseCox
294 100+
The page you use with all the CheckBoxes on is essentially a sibling of the two pages (subforms) you want to populate when you click the command buttons.
This means that the strFilter will need to be available globally (available to all procedures for the whole form) I'll have to look into what is available where between different form objects within subform objects of a main form.
I'll repost when I've made some progress.

Ok thanks.
Jan 15 '07 #56
NeoPa
32,181 Expert Mod 16PB
Ok thanks.
You weren't due back till Tuesday! Surprise.
I think I need another review on this. Let me know what's still outstanding when you check out all that's there since you left (I hope there's some actual info there and not just rambles about versions etc). At least I should understand better where you're coming from.
I think accessing Pages on the main form is transparent. I mean referring to a control on one page from another should still be Me.ControlName. Can you check this is right when you get a minute. I can't really run the database as there's no data (and finding time will be difficult too).
Jan 15 '07 #57
ChaseCox
294 100+
I am not sure If I understood what you were asking for in the last post. I am still stuck where I was on Friday.
Jan 15 '07 #58
ChaseCox
294 100+
HOORAY. After spending all day I got it to work. Here is what I did.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub chkvoy2_AfterUpdate()
  3.  
  4.  Call MakeFilter
  5.  
  6. End Sub
  7.  
  8. Private Sub chkvoy3_AfterUpdate()
  9.  
  10.  Call MakeFilter
  11.  
  12. End Sub
  13.  
  14. Private Sub chkchil_AfterUpdate()
  15.  
  16.  Call MakeFilter
  17.  
  18. End Sub
  19.  
  20. Private Sub chkipk_AfterUpdate()
  21.  
  22. Call MakeFilter
  23.  
  24. End Sub
  25.  
  26. Private Sub chkody_AfterUpdate()
  27.  
  28. Call MakeFilter
  29.  
  30. End Sub
  31.  
  32. Private Sub chkpre_AfterUpdate()
  33.  
  34. Call MakeFilter
  35.  
  36. End Sub
  37.  
  38. Private Sub chkwsp_AfterUpdate()
  39.  
  40.  Call MakeFilter
  41.  
  42. End Sub
  43. Private Sub MakeFilter()
  44.  
  45.  
  46.     strFilter = ""
  47.     If Nz(chkvoy2, False) Then _
  48.         strFilter = strFilter & ",'0463','0465','0467'"
  49.     If Nz(chkvoy3, False) Then _
  50.         strFilter = strFilter & ",'0382'"
  51.     If Nz(chkipk, False) Then _
  52.         strFilter = strFilter & ",'0383','0393','0422'"
  53.     If Nz(chkody, False) Then _
  54.         strFilter = strFilter & ",'0419','0411','0416','0418'"
  55.     If Nz(chkpre, False) Then _
  56.         strFilter = strFilter & ",'0281','0282','0279','0280'" & _
  57.                                 ",'0284','0287','0513','0514'" & _
  58.                                 ",'0515','0516','0517','0518'"
  59.     If Nz(chkwsp, False) Then _
  60.         strFilter = strFilter & ",'0328','0331','0176','0075'" & _
  61.                                 ",'0326','0332','0042','0142'"
  62.     If Nz(chkchil, False) Then _
  63.         strFilter = strFilter & ",'0361','0362','0385','0386'"
  64.  
  65.  
  66.     [txtsee] = strFilter
  67. End Sub
  68.  
  69. Private Sub Command78_Click()
  70. On Error GoTo Err_Command78_Click
  71.  
  72. txtsee.SetFocus
  73.  
  74. If Nz(chkmat, False) Then _
  75.  
  76.     DoCmd.OutputTo acOutputQuery, "Material Query", acFormatXLS, "", False
  77.  
  78. End If
  79.  
  80. Forms![Form1]![Practice].Form.RecordSource = "Material Query"
  81.  
  82. Exit_Command78_Click:
  83.     Exit Sub
  84.  
  85. Err_Command78_Click:
  86.     MsgBox Err.Description
  87.     Resume Exit_Command78_Click
  88.  
  89. End Sub
  90.  
  91. Private Sub Lbrbutton_Click()
  92. On Error GoTo Err_Lbrbutton_Click
  93.  
  94. txtsee.SetFocus
  95.  
  96. If Nz(chklab, False) Then _
  97.  
  98.      DoCmd.OutputTo acOutputQuery, "Labor Query", acFormatXLS, "", False
  99.  
  100. End If
  101.  
  102. Forms![Form1]![Practice1].Form.RecordSource = "Labor Query"
  103.  
  104. Exit_Lbrbutton_Click:
  105.     Exit Sub
  106.  
  107. Err_Lbrbutton_Click:
  108.     MsgBox Err.Description
  109.     Resume Exit_Lbrbutton_Click
  110.  
  111. End Sub
  112.  
  113.  
I am passing the product codes into a text box that is then referenced by a query string:

Expand|Select|Wrap|Line Numbers
  1. InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])
  2.  
may not be elegant, but it works!
Jan 15 '07 #59
NeoPa
32,181 Expert Mod 16PB
I am not sure If I understood what you were asking for in the last post. I am still stuck where I was on Friday.
Your code actually answered my main question Chase.
Pages (where you have multiple tabs) are not treated as part of the reference structure.
So, if I want to reference the SubForm from outside of it, I don't need to include a part for the page. That is determined automatically by the interpreter knowing which items are on each page.
That solution may not be elegant, but if it works that should be fine.
What I was thinking of (and I'll go into more detail if (only if) you express an interest) was to leave the record source as it is (without any connection to the form) and simply apply a Filter to the form in the SubForm when required.
This way we avoid messy references to Form.Control's current values within the SQL.
If you've got what you want and are happy with it though, that's cool.
Jan 16 '07 #60
NeoPa
32,181 Expert Mod 16PB
You could have (in the MakeFilter routine) :
Expand|Select|Wrap|Line Numbers
  1. Me![Practice].Form.Filter = strFilter
  2. Me![Practice1].Form.Filter = strFilter
But the Export would not easily be done with OutputTo.
There may be another way it could be done which provided a Filter or WhereClause parameter but not that way :(
Jan 16 '07 #61
ChaseCox
294 100+
Your code actually answered my main question Chase.
Pages (where you have multiple tabs) are not treated as part of the reference structure.
So, if I want to reference the SubForm from outside of it, I don't need to include a part for the page. That is determined automatically by the interpreter knowing which items are on each page.
That solution may not be elegant, but if it works that should be fine.
What I was thinking of (and I'll go into more detail if (only if) you express an interest) was to leave the record source as it is (without any connection to the form) and simply apply a Filter to the form in the SubForm when required.
This way we avoid messy references to Form.Control's current values within the SQL.
If you've got what you want and are happy with it though, that's cool.

I would be interested to see how that works, if you do not mind.
Jan 16 '07 #62
ChaseCox
294 100+
Just a thought, as you were looking through my database and code I have been posting. The material query takes around 8-10 minutes to run. Do you know any way to stream line this process, or am I going to just have to wait?
Jan 16 '07 #63
NeoPa
32,181 Expert Mod 16PB
I would be interested to see how that works, if you do not mind.
I'll have a look at posting something this evening when i have more time available.
Jan 16 '07 #64
NeoPa
32,181 Expert Mod 16PB
Just a thought, as you were looking through my database and code I have been posting. The material query takes around 8-10 minutes to run. Do you know any way to stream line this process, or am I going to just have to wait?
As this is in a public forum, could you post the question as if I didn't have your database. Then other readers can follow better what's going on, and I can look at it this evening with the other stuff.
Jan 16 '07 #65
ChaseCox
294 100+
I will just paste into a new topic.
Jan 16 '07 #66
NeoPa
32,181 Expert Mod 16PB
Good idea - but include a link to this one so that the database can be downloaded by anyone easily.
Jan 16 '07 #67
NeoPa
32,181 Expert Mod 16PB
I would be interested to see how that works, if you do not mind.
Expand|Select|Wrap|Line Numbers
  1. Private Sub MakeFilter()
  2.     Dim strFilter As String
  3.  
  4.     strFilter = ""
  5.     If Nz(chkvoy2, False) Then _
  6.         strFilter = strFilter & ",'0463','0465','0467'"
  7.     If Nz(chkvoy3, False) Then _
  8.         strFilter = strFilter & ",'0382'"
  9.     If Nz(chkipk, False) Then _
  10.         strFilter = strFilter & ",'0383','0393','0422'"
  11.     If Nz(chkody, False) Then _
  12.         strFilter = strFilter & ",'0419','0411','0416','0418'"
  13.     If Nz(chkpre, False) Then _
  14.         strFilter = strFilter & ",'0281','0282','0279','0280'" & _
  15.                                 ",'0284','0287','0513','0514'" & _
  16.                                 ",'0515','0516','0517','0518'"
  17.     If Nz(chkwsp, False) Then _
  18.         strFilter = strFilter & ",'0328','0331','0176','0075'" & _
  19.                                 ",'0326','0332','0042','0142'"
  20.     If Nz(chkchil, False) Then _
  21.         strFilter = strFilter & ",'0361','0362','0385','0386'"
  22.  
  23.     If strFilter > "" Then _
  24.         strFilter = "([PROD_CODE] In(" & Mid(strFilter, 2) & "))"
  25.     Me![Practice].Form.Filter = strFilter
  26.     Me![Practice1].Form.Filter = strFilter
  27.     'Me.Filter = strFilter
  28.     'Call Me.Requery
  29.     [txtsee] = strFilter
  30. End Sub
  31.  
  32. Private Sub Command78_Click()
  33. On Error GoTo Err_Command78_Click
  34.  
  35.     Me![Practice].Form.RecordSource = "Material Query"
  36.     Me![Practice].Form.Requery
  37.  
  38. Exit_Command78_Click:
  39.     Exit Sub
  40.  
  41. Err_Command78_Click:
  42.     MsgBox Err.Description
  43.     Resume Exit_Command78_Click
  44.  
  45. End Sub
  46.  
  47. Private Sub Lbrbutton_Click()
  48. On Error GoTo Err_Lbrbutton_Click
  49.  
  50.     Me![Practice1].Form.RecordSource = "Labor Query"
  51.     Me![Practice1].Form.Requery
  52.  
  53. Exit_Lbrbutton_Click:
  54.     Exit Sub
  55.  
  56. Err_Lbrbutton_Click:
  57.     MsgBox Err.Description
  58.     Resume Exit_Lbrbutton_Click
  59.  
  60. End Sub
Jan 16 '07 #68
ChaseCox
294 100+
That did not get it. I suppose I am happy for now with my solution. Thank you for all of your help, you were a tremendous help to me.
Jan 16 '07 #69
NeoPa
32,181 Expert Mod 16PB
I'm pleased I was able to help :)
Jan 17 '07 #70
Neat solution NeoPa.. Im gonna use this..
Jan 22 '07 #71
NeoPa
32,181 Expert Mod 16PB
Neat solution NeoPa. Im gonna use this.
Thank you - pleased to be able to help.
Another thread that may be of some help is (Example Filtering on a Form).
Jan 22 '07 #72

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jim in Arizona | last post: by
10 posts views Thread by Jim in Arizona | last post: by
5 posts views Thread by Andrew Meador | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.