Check Boxes to Pass into Query

[ChaseCox]

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.

[NeoPa]

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.

[ChaseCox]

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.

[NeoPa]

Not working really.
Just doing TSDN work from my work PC.

[NeoPa]

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.

[NeoPa]

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.

[ChaseCox]

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.

[NeoPa]

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).

[ChaseCox]

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.

[ChaseCox]

HOORAY. After spending all day I got it to work. Here is what I did.

 
Private Sub chkvoy2_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 
Private Sub chkvoy3_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 
Private Sub chkchil_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 
Private Sub chkipk_AfterUpdate()
 
Call MakeFilter
 
End Sub
 
Private Sub chkody_AfterUpdate()
 
Call MakeFilter
 
End Sub
 
Private Sub chkpre_AfterUpdate()
 
Call MakeFilter
 
End Sub
 
Private Sub chkwsp_AfterUpdate()
 
 Call MakeFilter
 
End Sub
Private Sub MakeFilter()
 
 
    strFilter = ""
    If Nz(chkvoy2, False) Then _
        strFilter = strFilter & ",'0463','0465','0467'"
    If Nz(chkvoy3, False) Then _
        strFilter = strFilter & ",'0382'"
    If Nz(chkipk, False) Then _
        strFilter = strFilter & ",'0383','0393','0422'"
    If Nz(chkody, False) Then _
        strFilter = strFilter & ",'0419','0411','0416','0418'"
    If Nz(chkpre, False) Then _
        strFilter = strFilter & ",'0281','0282','0279','0280'" & _
                                ",'0284','0287','0513','0514'" & _
                                ",'0515','0516','0517','0518'"
    If Nz(chkwsp, False) Then _
        strFilter = strFilter & ",'0328','0331','0176','0075'" & _
                                ",'0326','0332','0042','0142'"
    If Nz(chkchil, False) Then _
        strFilter = strFilter & ",'0361','0362','0385','0386'"
 
 
    [txtsee] = strFilter
End Sub
 
Private Sub Command78_Click()
On Error GoTo Err_Command78_Click
 
txtsee.SetFocus
 
If Nz(chkmat, False) Then _
 
    DoCmd.OutputTo acOutputQuery, "Material Query", acFormatXLS, "", False
 
End If
 
Forms![Form1]![Practice].Form.RecordSource = "Material Query"
 
Exit_Command78_Click:
    Exit Sub
 
Err_Command78_Click:
    MsgBox Err.Description
    Resume Exit_Command78_Click
 
End Sub
 
Private Sub Lbrbutton_Click()
On Error GoTo Err_Lbrbutton_Click
 
txtsee.SetFocus
 
If Nz(chklab, False) Then _
 
     DoCmd.OutputTo acOutputQuery, "Labor Query", acFormatXLS, "", False
 
End If
 
Forms![Form1]![Practice1].Form.RecordSource = "Labor Query"
 
Exit_Lbrbutton_Click:
    Exit Sub
 
Err_Lbrbutton_Click:
    MsgBox Err.Description
    Resume Exit_Lbrbutton_Click
 
End Sub
 
 

I am passing the product codes into a text box that is then referenced by a query string:

InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])
 

may not be elegant, but it works!

[NeoPa]

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.

[NeoPa]

You could have (in the MakeFilter routine) :

Me![Practice].Form.Filter = strFilter
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 :(

[ChaseCox]

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.

[ChaseCox]

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?

[NeoPa]

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.

[NeoPa]

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.

[ChaseCox]

I will just paste into a new topic.

[NeoPa]

Good idea - but include a link to this one so that the database can be downloaded by anyone easily.

[NeoPa]

I would be interested to see how that works, if you do not mind.

Private Sub MakeFilter()
    Dim strFilter As String
 
    strFilter = ""
    If Nz(chkvoy2, False) Then _
        strFilter = strFilter & ",'0463','0465','0467'"
    If Nz(chkvoy3, False) Then _
        strFilter = strFilter & ",'0382'"
    If Nz(chkipk, False) Then _
        strFilter = strFilter & ",'0383','0393','0422'"
    If Nz(chkody, False) Then _
        strFilter = strFilter & ",'0419','0411','0416','0418'"
    If Nz(chkpre, False) Then _
        strFilter = strFilter & ",'0281','0282','0279','0280'" & _
                                ",'0284','0287','0513','0514'" & _
                                ",'0515','0516','0517','0518'"
    If Nz(chkwsp, False) Then _
        strFilter = strFilter & ",'0328','0331','0176','0075'" & _
                                ",'0326','0332','0042','0142'"
    If Nz(chkchil, False) Then _
        strFilter = strFilter & ",'0361','0362','0385','0386'"
 
    If strFilter > "" Then _
        strFilter = "([PROD_CODE] In(" & Mid(strFilter, 2) & "))"
    Me![Practice].Form.Filter = strFilter
    Me![Practice1].Form.Filter = strFilter
    'Me.Filter = strFilter
    'Call Me.Requery
    [txtsee] = strFilter
End Sub
 
Private Sub Command78_Click()
On Error GoTo Err_Command78_Click
 
    Me![Practice].Form.RecordSource = "Material Query"
    Me![Practice].Form.Requery
 
Exit_Command78_Click:
    Exit Sub
 
Err_Command78_Click:
    MsgBox Err.Description
    Resume Exit_Command78_Click
 
End Sub
 
Private Sub Lbrbutton_Click()
On Error GoTo Err_Lbrbutton_Click
 
    Me![Practice1].Form.RecordSource = "Labor Query"
    Me![Practice1].Form.Requery
 
Exit_Lbrbutton_Click:
    Exit Sub
 
Err_Lbrbutton_Click:
    MsgBox Err.Description
    Resume Exit_Lbrbutton_Click
 
End Sub

[ChaseCox]

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.

[NeoPa]

I'm pleased I was able to help :)

[RookieDan]

Neat solution NeoPa.. Im gonna use this..

[NeoPa]

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).