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]

You could try setting the criteria in the AfterUpdate of the CheckBoxes.
You would have one common procedure, called by all the events, which would formulate the criteria based upon which CheckBoxes were set to True.
A .ReQuery of the object would be required after every change of the criteria.

[ChaseCox]

How do I go about setting the value associated with the check box?

[NeoPa]

1. Create CheckBoxes called chkHappy; chkSad; etc.
2. Add Event procedures for the AfterUpdate event of each CeckBox of this form :

Private Sub chkHappy_AfterUpdate()
    Call MakeFilter
End Sub

3. Create the procedure :

Private Sub MakeFilter()
    strFilter As String
 
    strFilter = ""
    If chkHappy then
        strFilter = strFilter & " & (" & {your selection code} & ")"
    End If
    If chkSad then
        strFilter = strFilter & " & (" & {your selection code} & ")"
    End If
    '... etc for all CheckBoxes
    If strFilter > "" Then
        strFilter = "(" & Mid(strFilter,4) & ")"
        Me.Filter = strFilter
        Call Me.Requery
    end If
End Sub

[ChaseCox]

can you explain where to put the numbers in the code of the second box? I tried to edit it, and I messed it up some how.

[ChaseCox]

1. Create CheckBoxes called chkHappy; chkSad; etc.
2. Add Event procedures for the AfterUpdate event of each CeckBox of this form :

Private Sub chkHappy_AfterUpdate()
    Call MakeFilter
End Sub

3. Create the procedure :

Private Sub MakeFilter()
    strFilter As String
 
    strFilter = " DOES ANYTHING GO HERE"
    If chkHappy then
        strFilter = strFilter & " & (" & {your selection code} & ")"
    End If
    If chkSad then
        strFilter = strFilter & " & (" & {your selection code} & ")"
    End If
    '... etc for all CheckBoxes
    If strFilter > " Does anything go here?" Then
        strFilter = "(" & Mid(strFilter,4) & ")"
        Me.Filter = strFilter
        Call Me.Requery
    end If
End Sub

What goes where the & symbol is?

[ChaseCox]

1. Create CheckBoxes called chkHappy; chkSad; etc.
2. Add Event procedures for the AfterUpdate event of each CeckBox of this form :

Private Sub chkHappy_AfterUpdate()
    Call MakeFilter
End Sub

3. Create the procedure :

Private Sub MakeFilter()
 
    strFilter As String
 
    strFilter = ""
    If chkvoy Then
        strFilter = strFilter & " & (" & 463 & ")" & " & (" & 465 & ")" & " & (" & 467 & ")"
    End If
    'If chkSad Then
      '  strFilter = strFilter & " & (" & {your selection code} & ")"
   ' End If
    '... etc for all CheckBoxes
    If strFilter > "" Then
        strFilter = "(" & Mid(strFilter, 4) & ")"
        Me.Filter = strFilter
        Call Me.Requery
    End If
End Sub
 

This is what I tried. WHat is wrong?

[ChaseCox]

Also how would I reference this into the query, or is this already doing it? If it is already doing it, it needs to wait to requery until the person decides to run the form.

[NeoPa]

Sorry man, I had a v busy evening - I will try to look at this tomorrow for you.
I must try to get to bed before the clock strikes 04:00 or I'll turn into a pumpkin ;)

[ChaseCox]

Sorry man, I had a v busy evening - I will try to look at this tomorrow for you.
I must try to get to bed before the clock strikes 04:00 or I'll turn into a pumpkin ;)

Ok Thanks for letting me know.

[NeoPa]

I've redone post #4 with a little more detail.
If you need more help you'll need to post the names of the fields; tables; queries; etc you are using (as well as the SQL of the query(s) of course).

1. Create CheckBoxes called chkHappy; chkSad; etc.
2. Add Event procedures for the AfterUpdate event of each CeckBox of this form :

Private Sub chkHappy_AfterUpdate()
    Call MakeFilter
End Sub
 
Private Sub chkSad_AfterUpdate()
    Call MakeFilter
End Sub
... etc for all CheckBoxes

3. Create the procedure :

Private Sub MakeFilter()
    Dim strFilter As String
 
    'Reset strFilter to empty string
    strFilter = ""
    If chkHappy then
        strFilter = strFilter & ",'0412','0413','0456','0458'"
    End If
    If chkSad then
        strFilter = strFilter & ",'0525','0544','0569','0588'"
    End If
    '... etc for all CheckBoxes
    If strFilter > "" Then _
        strFilter = "([YourField] In(" & Mid(strFilter,2) & "))"
    Me.Filter = strFilter
    Call Me.Requery
End Sub

[ChaseCox]

Tables: Generic Material, Generic Labor
Queries: Material Query, Labor Query

 
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 chkvoy2_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 
 
Private Sub chkvoy3_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 

Private Sub MakeFilter()
 
 
 
    Dim strFilter As String
 
    strFilter = ""
    If chkvoy2 Then
        strFilter = strFilter & ",'0463','0465','0467'"
    End If
    If chkSad Then
        strFilter = strFilter & ",'0382'"
    End If
 
    If strFilter > "" Then _
       strFilter = "([Material Query]![PROD_CODE]  In(" & Mid(strFilter, 2) & "))"
       Me.Filter = strFilter
       Call Me.Requery
 
End Sub
 

Some Questions and Points:

1. When I select a check box, is this code passing the selection into the query? Or how do you do that?

2. Does this string of code allow multiple check boxes to be selected?

3. Do I need to place some code in my query to let it know to look at these boxes?

4. Does this code cause the query to run? I need it to wait unti l the user clicks the appropriate run query button.



This is the code for the query mentioned in my string of code above. it is for material. The field name is Prod_Code

SELECT [Generic Material].CLAIM_NBR, [Generic Material].DETAIL_NBR, [Generic Material].CLAIM_TYPE_DESCR, [Generic Material].[CLAIM DATE], [Generic Material].[SHIP DATE], [Generic Material].PROD_CODE, [Generic Material].PROD_CODE_DESCR, [Generic Material].RFC_CODE, [Generic Material].PROD_GRP, [Generic Material].RFC_FAILURE_DESCR, [Generic Material].RFC_FAILURE_MAT_IND, [Generic Material].RFC_FAILURE_LBR_IND, [Generic Material].RFC_LEVEL_ONE_DESCR, [Generic Material].RFC_LEVEL_TWO_DESCR, [Generic Material].RFC_LEVEL_THREE_DESCR, [Generic Material].RFC_LEVEL_FOUR_DESCR, [Generic Material].RFC_SHORT_DESCR, [Generic Material].BU_ID, [Generic Material].PART_PART_NBR, [Generic Material].PART_DESCR, [Generic Material].SUBMIT_OFFICE_NAME, [Generic Material].PARENT_SERIAL_NBR, Mid([ORDERED_MODEL],1,38) AS [Model Number], [Generic Material].COST_CENTER, [Generic Material].ACCOUNT, [Generic Material].TASK_CODE, [Generic Material].TASK_DESCR, [Generic Material].SALES_ORDER_PRIME, [Generic Material].[DATE ENTERED], [Generic Material].[DATE APPROVED], [Generic Material].[DATE STARTED], [Generic Material].[DATE FAILED], [Generic Material].DETAIL_QTY, [Generic Material].SumOfEXP_TYPE_AMOUNT, [Generic Material].CLAIM_JOB_NAME
FROM [Generic Material]
WHERE ((([Generic Material].[CLAIM DATE]) Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde]) AND (([Generic Material].[SHIP DATE]) Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed]) AND (([Generic Material].RFC_LEVEL_ONE_DESCR) Like ("*" & [Forms]![Form1]![cmbrfcl1dscr] & "*")) AND (([Generic Material].RFC_SHORT_DESCR) Like ("*" & [Forms]![Form1]![cmbrfcsd] & "*")) AND (([Generic Material].PART_PART_NBR) Like ("*" & [Forms]![Form1]![cmbpn] & "*")) AND ((Mid([ORDERED_MODEL],1,38)) Like ("*" & [Forms]![Form1]![cmbm1] & [Forms]![Form1]![cmbm2] & [Forms]![Form1]![cmbm3] & [Forms]![Form1]![cmbm4] & [Forms]![Form1]![cmbm5] & [Forms]![Form1]![cmbm6] & [Forms]![Form1]![cmbm7] & [Forms]![Form1]![cmbm8] & [Forms]![Form1]![cmbm9] & [Forms]![Form1]![cmbm10] & [Forms]![Form1]![cmbm11] & [Forms]![Form1]![cmbm12] & [Forms]![Form1]![cmbm13] & [Forms]![Form1]![cmbm14] & [Forms]![Form1]![cmbm15] & [Forms]![Form1]![cmbm16] & [Forms]![Form1]![cmbm17] & [Forms]![Form1]![cmbm18] & [Forms]![Form1]![cmbm19] & [Forms]![Form1]![cmbm20] & [Forms]![Form1]![cmbm21] & [Forms]![Form1]![cmbm22] & [Forms]![Form1]![cmbm23] & [Forms]![Form1]![cmbm24] & [Forms]![Form1]![cmbm25] & [Forms]![Form1]![cmbm26] & [Forms]![Form1]![cmbm27] & [Forms]![Form1]![cmbm28] & [Forms]![Form1]![cmbm29] & [Forms]![Form1]![cmbm30] & [Forms]![Form1]![31] & [Forms]![Form1]![32] & [Forms]![Form1]![33] & [Forms]![Form1]![34] & [Forms]![Form1]![35] & [Forms]![Form1]![36] & [Forms]![Form1]![37] & [Forms]![Form1]![38] & "*")) AND (([Generic Material].TASK_CODE) Like ("*" & [Forms]![Form1]![cmbtc] & "*")) AND (([Generic Material].TASK_DESCR) Like ("*" & [Forms]![Form1]![cmbtd] & "*")) AND (([Generic Material].[DATE STARTED]) Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2]) AND (([Generic Material].[DATE FAILED]) Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2]))
 

[ChaseCox]

updated code:

Private Sub MakeFilter()
 
 
 
    Dim strFilter As String
 
    strFilter = ""
 
    If chkvoy2 Then
        strFilter = strFilter & ",'0463','0465','0467'"
    End If
 
    If chkvoy3 Then
        strFilter = strFilter & ",'0382'"
    End If
 
    If chkipk Then
        strFilter = strFilter & ",'0383','0393','0422'"
    End If
 
    If chkody Then
        strFilter = strFilter & ",'0419','0411','0416','0418'"
    End If
 
    If chkpre Then
        strFilter = strFilter & ",'0281','0282','0279','0280','0284','0287','0513','0514','0515','0516','0517','0518'"
    End If
 
    If chkwsp Then
        strFilter = strFilter & ",'0328','0331','0176','0075','0326','0332','0042','0142'"
    End If
 
    If chkchil Then
        strFilter = strFilter & ",'0361','0362','0385','0386'"
    End If
 
    If strFilter > "" Then _
       strFilter = "([Material Query]![PROD_CODE]  In(" & Mid(strFilter, 2) & "))"
       Me.Filter = strFilter
       Call Me.Requery
 
End Sub
 

[NeoPa]

Tables: Generic Material, Generic Labor
Queries: Material Query, Labor Query

 
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 chkvoy2_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 
 
Private Sub chkvoy3_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 

Private Sub MakeFilter()
 
 
 
    Dim strFilter As String
 
    strFilter = ""
    If chkvoy2 Then
        strFilter = strFilter & ",'0463','0465','0467'"
    End If
    If chkSad Then
        strFilter = strFilter & ",'0382'"
    End If
 
    If strFilter > "" Then _
       strFilter = "([Material Query]![PROD_CODE]  In(" & Mid(strFilter, 2) & "))"
       Me.Filter = strFilter
       Call Me.Requery
 
End Sub
 

Some Questions and Points:

1. When I select a check box, is this code passing the selection into the query? Or how do you do that?

2. Does this string of code allow multiple check boxes to be selected?

3. Do I need to place some code in my query to let it know to look at these boxes?

4. Does this code cause the query to run? I need it to wait unti l the user clicks the appropriate run query button.



This is the code for the query mentioned in my string of code above. it is for material. The field name is Prod_Code

SELECT [Generic Material].CLAIM_NBR, [Generic Material].DETAIL_NBR, [Generic Material].CLAIM_TYPE_DESCR, [Generic Material].[CLAIM DATE], [Generic Material].[SHIP DATE], [Generic Material].PROD_CODE, [Generic Material].PROD_CODE_DESCR, [Generic Material].RFC_CODE, [Generic Material].PROD_GRP, [Generic Material].RFC_FAILURE_DESCR, [Generic Material].RFC_FAILURE_MAT_IND, [Generic Material].RFC_FAILURE_LBR_IND, [Generic Material].RFC_LEVEL_ONE_DESCR, [Generic Material].RFC_LEVEL_TWO_DESCR, [Generic Material].RFC_LEVEL_THREE_DESCR, [Generic Material].RFC_LEVEL_FOUR_DESCR, [Generic Material].RFC_SHORT_DESCR, [Generic Material].BU_ID, [Generic Material].PART_PART_NBR, [Generic Material].PART_DESCR, [Generic Material].SUBMIT_OFFICE_NAME, [Generic Material].PARENT_SERIAL_NBR, Mid([ORDERED_MODEL],1,38) AS [Model Number], [Generic Material].COST_CENTER, [Generic Material].ACCOUNT, [Generic Material].TASK_CODE, [Generic Material].TASK_DESCR, [Generic Material].SALES_ORDER_PRIME, [Generic Material].[DATE ENTERED], [Generic Material].[DATE APPROVED], [Generic Material].[DATE STARTED], [Generic Material].[DATE FAILED], [Generic Material].DETAIL_QTY, [Generic Material].SumOfEXP_TYPE_AMOUNT, [Generic Material].CLAIM_JOB_NAME
FROM [Generic Material]
WHERE ((([Generic Material].[CLAIM DATE]) Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde]) AND (([Generic Material].[SHIP DATE]) Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed]) AND (([Generic Material].RFC_LEVEL_ONE_DESCR) Like ("*" & [Forms]![Form1]![cmbrfcl1dscr] & "*")) AND (([Generic Material].RFC_SHORT_DESCR) Like ("*" & [Forms]![Form1]![cmbrfcsd] & "*")) AND (([Generic Material].PART_PART_NBR) Like ("*" & [Forms]![Form1]![cmbpn] & "*")) AND ((Mid([ORDERED_MODEL],1,38)) Like ("*" & [Forms]![Form1]![cmbm1] & [Forms]![Form1]![cmbm2] & [Forms]![Form1]![cmbm3] & [Forms]![Form1]![cmbm4] & [Forms]![Form1]![cmbm5] & [Forms]![Form1]![cmbm6] & [Forms]![Form1]![cmbm7] & [Forms]![Form1]![cmbm8] & [Forms]![Form1]![cmbm9] & [Forms]![Form1]![cmbm10] & [Forms]![Form1]![cmbm11] & [Forms]![Form1]![cmbm12] & [Forms]![Form1]![cmbm13] & [Forms]![Form1]![cmbm14] & [Forms]![Form1]![cmbm15] & [Forms]![Form1]![cmbm16] & [Forms]![Form1]![cmbm17] & [Forms]![Form1]![cmbm18] & [Forms]![Form1]![cmbm19] & [Forms]![Form1]![cmbm20] & [Forms]![Form1]![cmbm21] & [Forms]![Form1]![cmbm22] & [Forms]![Form1]![cmbm23] & [Forms]![Form1]![cmbm24] & [Forms]![Form1]![cmbm25] & [Forms]![Form1]![cmbm26] & [Forms]![Form1]![cmbm27] & [Forms]![Form1]![cmbm28] & [Forms]![Form1]![cmbm29] & [Forms]![Form1]![cmbm30] & [Forms]![Form1]![31] & [Forms]![Form1]![32] & [Forms]![Form1]![33] & [Forms]![Form1]![34] & [Forms]![Form1]![35] & [Forms]![Form1]![36] & [Forms]![Form1]![37] & [Forms]![Form1]![38] & "*")) AND (([Generic Material].TASK_CODE) Like ("*" & [Forms]![Form1]![cmbtc] & "*")) AND (([Generic Material].TASK_DESCR) Like ("*" & [Forms]![Form1]![cmbtd] & "*")) AND (([Generic Material].[DATE STARTED]) Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2]) AND (([Generic Material].[DATE FAILED]) Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2]))
 

Firstly, comments on your code :
1. You have mentioned 5 CheckBoxes (chkipk; chkody; chkpre; chkvoy2 & chkvoy3). Each of these needs to have an 'If' statement associated (In the MakeFilter procedure) to ensure it is included in the filter.
2. In your version of the following code you have indented the last two lines which are NOT associated with the If. This is not good. Also, your reference to [Prod_Code] should not specify a query name as it is actually referencing the form's Record Source rather than running any query explicitly. I have changed this version.

    If strFilter > "" Then _
       strFilter = "([PROD_CODE] In(" & Mid(strFilter, 2) & "))"
   Me.Filter = strFilter
   Call Me.Requery

Your questions :
Q1. When I select a check box, is this code passing the selection into the query? Or how do you do that?
A1. Not exactly. It is telling the form that, when it runs the query that it (the form) is bound to, it should filter the records returned as specified in your string. The active part of this (as opposed to the setting up of the string etc), is done in the last two lines of the code a few lines above (Me.Filter = ...etc).

Q2. Does this string of code allow multiple check boxes to be selected?
A2. Yes.

Q3. Do I need to place some code in my query to let it know to look at these boxes?
A3. No, this is handled by the filter which is an attribute or property of the form. This means that the query will still run exactly as expected when simply opened, but can be modified for when the form is used (exclusively for the form).

Q4. Does this code cause the query to run? I need it to wait unti l the user clicks the appropriate run query button.
A4. Yes it does. The 'Call Me.Requery' line is the one that does that so if you need to disable that until later simply remove that line.

Last point.
I'm very suspicious of your [ORDERED_MODEL] selection code in the WHERE clause of your query. It looks very kludgy - but may be ok for all I know as it refers to things I know nothing of.

[ChaseCox]

updated code:

Private Sub MakeFilter()
 
 
 
    Dim strFilter As String
 
    strFilter = ""
 
    If chkvoy2 Then
        strFilter = strFilter & ",'0463','0465','0467'"
    End If
 
    If chkvoy3 Then
        strFilter = strFilter & ",'0382'"
    End If
 
    If chkipk Then
        strFilter = strFilter & ",'0383','0393','0422'"
    End If
 
    If chkody Then
        strFilter = strFilter & ",'0419','0411','0416','0418'"
    End If
 
    If chkpre Then
        strFilter = strFilter & ",'0281','0282','0279','0280','0284','0287','0513','0514','0515','0516','0517','0518'"
    End If
 
    If chkwsp Then
        strFilter = strFilter & ",'0328','0331','0176','0075','0326','0332','0042','0142'"
    End If
 
    If chkchil Then
        strFilter = strFilter & ",'0361','0362','0385','0386'"
    End If
 
    If strFilter > "" Then _
       strFilter = "([Material Query]![PROD_CODE]  In(" & Mid(strFilter, 2) & "))"
       Me.Filter = strFilter
       Call Me.Requery
 
End Sub
 

This will not run properly, any ideas?

[ChaseCox]

Firstly, comments on your code :
1. You have mentioned 5 CheckBoxes (chkipk; chkody; chkpre; chkvoy2 & chkvoy3). Each of these needs to have an 'If' statement associated (In the MakeFilter procedure) to ensure it is included in the filter.

I am not sure what you mean, could you please expound on this statement.

2. In your version of the following code you have indented the last two lines which are NOT associated with the If. This is not good.

Can you clarify more, please?

Last point.
I'm very suspicious of your [ORDERED_MODEL] selection code in the WHERE clause of your query. It looks very kludgy - but may be ok for all I know as it refers to things I know nothing of.

This is becuase there is a 30 digit model #. Is some instancea the user may want to only search on the 5,7,6 and 19,21, and 30 digits, since each digit means something.

[ChaseCox]

I am having an error code thrown when I click the check box. run Time error 94. It says invalid use of null.

[ChaseCox]

chkvoy3 is where the error takes me when I tell it to debug, I have placed that line in bold.

Private Sub MakeFilter()
 
 
 
    Dim strFilter As String
 
    strFilter = ""
 
    If chkvoy2 Then
        strFilter = strFilter & ",'0463','0465','0467'"
    End If
 
    If chkvoy3 Then   <this is the error code it takes me to
        strFilter = strFilter & ",'0382'"
    End If
 
    If chkipk Then
        strFilter = strFilter & ",'0383','0393','0422'"
    End If
 
    If chkody Then
        strFilter = strFilter & ",'0419','0411','0416','0418'"
    End If
 
    If chkpre Then
        strFilter = strFilter & ",'0281','0282','0279','0280','0284','0287','0513','0514','0515','0516','0517','0518'"
    End If
 
    If chkwsp Then
        strFilter = strFilter & ",'0328','0331','0176','0075','0326','0332','0042','0142'"
    End If
 
    If chkchil Then
        strFilter = strFilter & ",'0361','0362','0385','0386'"
    End If
 
     If strFilter > "" Then _
       strFilter = "([PROD_CODE] In(" & Mid(strFilter, 2) & "))"
   Me.Filter = strFilter
 
 
End Sub
 

[NeoPa]

You'll find answers in my previous post.
Try to include everything in there before reposting a question.

If you can't figure it out by tomorrow then post to say that and I'll explain in more detail (but please go through my previous post carefully before you take that step).

[NeoPa]

If you keep posting in small snippets then there will be communication problems. I was responding to your first response to my post. I will have to look through all of these later. In future, please try to organise your thoughts and your comments before posting. That really is your responsibility and you shouldn't leave it to others to do for you. You'll guess I'm not overly impressed atm. ...but I will look at this again later - when I get a chance.

[ChaseCox]

I am relatively new to the message boards and was not aware of certain etiquettes, so I apologize. I just got over zealous and got ahead of myself.

I have reviewed your posts, and after sorting through what I posted, and what you have posted, I can see why you were less than impressed.

Here is an example of what I am having trouble with now; I have reduced it to two check boxes:

Private Sub chkvoy2_AfterUpdate()
 
 Call MakeFilter
 
End Sub
 
Private Sub chkvoy3_AfterUpdate()
 
   Call MakeFilter
 
End Sub
 
Private Sub MakeFilter()
    Dim strFilter As String
 
    strFilter = ""
 
 If chkvoy2 Then
    strFilter = strFilter & ",'0463' , '0465' , '0467'"
 End If
 
 If chkvoy3 Then
        strFilter = strFilter & ",'0382'"
 End If
 
 If strFilter > "" Then _
       strFilter = "([PROD_CODE] In(" & Mid(strFilter, 2) & "))"
   Me.Filter = strFilter
   Call Me.Requery
 
End Sub
 

When I go to the form and select the box that would reference chkvoy2, I get an error that says "Run-Time Error 94, Invalid Use of Null" I tried to figure this out with the Help file, but got nowhere.

When I eliminate the second checkbox via removing the VB code associated with it, I can get the check box to check, and I am positive it is generating the appropriate string of text, but when I run the query from the form, it does not filter based on the selection I have made.

If you have any suggestions on how to fix this, I would be most appreciative. I again apologize for my monstrous post, but I am learning both the VB, Access, and how to use this wonderful recourse the right way. Thanks

[NeoPa]

I am relatively new to the message boards and was not aware of certain etiquettes, so I apologize. I just got over zealous and got ahead of myself.

I have reviewed your posts, and after sorting through what I posted, and what you have posted, I can see why you were less than impressed.

I am actually so impressed with this latest post :)

• Organised.
• Addresses the salient points directly.
• I can really work with this.

When I go to the form and select the box that would reference chkvoy2, I get an error that says "Run-Time Error 94, Invalid Use of Null" I tried to figure this out with the Help file, but got nowhere.

Are you talking about opening the form normally, then clicking in the CheckBox called chkvoy2?
Which line is highlighted in yellow in the VBA window when this happens?

When I eliminate the second checkbox via removing the VB code associated with it, I can get the check box to check, and I am positive it is generating the appropriate string of text, but when I run the query from the form, it does not filter based on the selection I have made.

The code eliminated - is this the AfterUpdate event code or the code within the MakeFilter() function?

Lastly, as I can't see anything obviously wrong with your posted code, could you add in the line

Debug.Print strFilter

before the line

Me.Filter = strFilter

then post me (in this thread) the results in the Immediate window.

[ChaseCox]

Are you talking about opening the form normally, then clicking in the CheckBox called chkvoy2?

Yes.

Which line is highlighted in yellow in the VBA window when this happens?

 If chkvoy3 Then
 

The code eliminated - is this the AfterUpdate event code or the code within the MakeFilter() function?

Code within the MakeFilter() Function.

Lastly, as I can't see anything obviously wrong with your posted code, could you add in the line

Debug.Print strFilter

before the line

Me.Filter = strFilter

then post me (in this thread) the results in the Immediate window.

I pasted the code in, nothing happened.

[NeoPa]

Thanks for your detailed reply :)
Your last answer was that nothing was seen after the Debug.Print. Is this because the code stopped before it got to it?
Otherwise, to see the results of a Debug.Print you need to :

1. Go to the VBA window (Alt-F11 from the Access window).
2. Go to and show the Immediate window (Ctrl-G).

There you will find the results if / when they are produced.

[ChaseCox]

I am not sure where the code is stopping. I think it is at the line in yello,

 If chkvoy3 Then

This is in the top of the window when I pressed ctrl-G

+ : Me : : Form_Form1/Form_Form1
: strFilter : ",'0463' , '0465' , '0467'" : String

This is in the bottom of the window.

,'0463','0465','0467'

[NeoPa]

That top bit looks like Watch or Local window contents.
The strFilter data shown is not what should be there and without access to your database it's hard for me to see what's gone wrong.
Would it be possible to upload your database as an attachment?
How big is it (Your .MDB file)?
Can you also provide, at some stage, a list of the CheckBoxes to be used with the codes that are associated with each.

Sorry this is taking so long to resolve.
You're probably starting to understand some of the problems trying to work on a database when you have to rely on what you're told by someone else as well as having to rely on them following your instructions correctly. Either of these would cause problems, even for more experienced pairs of people.

[ChaseCox]

Posting the database would be quite lengthy, as it is 534 MB.

I have possibly resolved the Run Error by altering the code you submitted slightly, this code runs, and allows me to select one or both check boxes without having a run error. When I run the query however, nothing is returned, just one line of blank fields.

Private Sub MakeFilter()
    Dim strFilter As String
 
    strFilter = ""
 
 If chkvoy2 = True Then
    strFilter = strFilter & ",'0463' , '0465' , '0467'"
 Else
 End If
 
 If chkvoy3 = True Then
        strFilter = strFilter & ",'0382'"
 Else
 End If
 
 If strFilter > "" Then _
       strFilter = "([PROD_CODE] In(" & Mid(strFilter, 2) & "))"
   Debug.Print strFilter
   Me.Filter = strFilter
   Call Me.Requery
 

I will give you a brief overview of what I have coded so far, and you can maybe get a better feel for what I am trying to do.

I have two tables, that contain information specific to several product line's material warranty claims and product warranty claims. The field of the table (same in both tables, but only looking at Materials right now) is the PROD_CODE, short for Product Code. In this field there are aprox. 220 unique codes. However, I only need these check boxes to filter 6 product lines, that account for about 30 Product Codes. I have a form that is full of combo boxes. Each combo box is then referenced by two queries, one for material, one for labor.
Depending on which claims the user wants to look at, will determine which query is run. There are two buttons on the form that are to run the query (either material or labor)

I need to limit the table (either by using the queries I have now) or by running this filter, which is what I think you are suggesting, to the product codes associated with the box checked.

This may be confusing, but I am willing to go into more detail about anything you may find helpful to accomplish this goal. Thanks

[NeoPa]

Chase,
We don't need to get distracted now with broader concepts. Let's keep our focus on this immediate problem and why it's not working. I've done some testing and found out why the Null so will get back to you shortly.
In the mean-time, please post the CheckBox info as in post #26 (unless all listed in your Post #18 - then just tell me that).

[NeoPa]

Try this :

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.Filter = strFilter
End Sub

I got all the information from Post #18 so I hope that's all correct.

[ChaseCox]

Try this :

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.Filter = strFilter
End Sub

I got all the information from Post #18 so I hope that's all correct.

This allows the check boxes to be selected, but this is still not filtering the table. Any thoughts?

[NeoPa]

This allows the check boxes to be selected, but this is still not filtering the table. Any thoughts?

Yes, From your Post #12, you say :

4. Does this code cause the query to run? I need it to wait unti l the user clicks the appropriate run query button.

so I took it out of the MakeFilter() function.
I don't know what you have in your run query buttons. I suggested

Call Me.Requery

(which was at the end of the MakeFilter() function) but that assumes the query is driving the form.

[ChaseCox]

Yes, From your Post #12, you say :

so I took it out of the MakeFilter() function.
I don't know what you have in your run query buttons. I suggested

Call Me.Requery

(which was at the end of the MakeFilter() function) but that assumes the query is driving the form.

I can post my entire VB code for the form if you would like? I placed the

Call Me.Requery

back in the MakeFilter() function, also.

[NeoPa]

That would be good.
Also identify which command buttons do what.

[ChaseCox]

Here is the complete VB Code for my Form.

Option Compare Database
Option Explicit
 
Private Sub PRINT_Click()
On Error GoTo Err_PRINT_Click
 
    Dim stDocName As String
    Dim MyForm As Form
 
    stDocName = "Form1"
    Set MyForm = Screen.ActiveForm
    DoCmd.SelectObject acForm, stDocName, True
    DoCmd.PrintOut
    DoCmd.SelectObject acForm, MyForm.Name, False
 
Exit_PRINT_Click:
    Exit Sub
 
Err_PRINT_Click:
    MsgBox Err.Description
    Resume Exit_PRINT_Click
 
End Sub
 
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()
    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.Filter = strFilter
    Call Me.Requery
 
End Sub
 
Private Sub Command78_Click()
On Error GoTo Err_Command78_Click
 
    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
 
   Forms![Form1]![Practice1].Form.RecordSource = "Labor Query"
 
Exit_Lbrbutton_Click:
    Exit Sub
 
Err_Lbrbutton_Click:
    MsgBox Err.Description
    Resume Exit_Lbrbutton_Click
 
End Sub
 
Private Sub Command308_Click()
On Error GoTo Err_Command308_Click
 
    DoCmd.OutputTo acOutputQuery, "Material Query", acFormatXLS, "", False
 
Exit_Command308_Click:
    Exit Sub
 
Err_Command308_Click:
 
    Resume Exit_Command308_Click
 
End Sub
 
Private Sub Command311_Click()
On Error GoTo Err_Command311_Click
 
    DoCmd.OutputTo acOutputQuery, "Labor Query", acFormatXLS, "", False
 
Exit_Command311_Click:
    Exit Sub
 
Err_Command311_Click:
 
    Resume Exit_Command311_Click
 
End Sub

The command below (pulled from the code above) runs my Material Query. Which displays the results into a subform on my main form.

Private Sub Command78_Click()
On Error GoTo Err_Command78_Click
 
    Forms![Form1]![Practice].Form.RecordSource = "Material       Query"
 
Exit_Command78_Click:
    Exit Sub
 
Err_Command78_Click:
    MsgBox Err.Description
    Resume Exit_Command78_Click
 
End Sub
 

The command below (pulled from the code above) runs my Labor Query. Which displays the results into a subform on my main form.

Private Sub Lbrbutton_Click()
On Error GoTo Err_Lbrbutton_Click
 
   Forms![Form1]![Practice1].Form.RecordSource = "Labor Query"
 
Exit_Lbrbutton_Click:
    Exit Sub
 
Err_Lbrbutton_Click:
    MsgBox Err.Description
    Resume Exit_Lbrbutton_Click
 
End Sub
 

The command below (pulled from the code above) saves my Material Query as an excell file.

Private Sub Command308_Click()
On Error GoTo Err_Command308_Click
 
    DoCmd.OutputTo acOutputQuery, "Material Query", acFormatXLS, "", False
 
Exit_Command308_Click:
    Exit Sub
 
Err_Command308_Click:
 
    Resume Exit_Command308_Click
 
End Sub
 

The command below (pulled from the code above) saves my Labor Query as an excell file.

Private Sub Command311_Click()
On Error GoTo Err_Command311_Click
 
    DoCmd.OutputTo acOutputQuery, "Labor Query", acFormatXLS, "", False
 
Exit_Command311_Click:
    Exit Sub
 
Err_Command311_Click:
 
    Resume Exit_Command311_Click
 
End Sub
 

[NeoPa]

I'll have to get to this later, but already I can see that you are using subforms (I don't recall this being mentioned before) which would explain why you never saw any results.
Do me a favour (As I won't get to this again for a number of hours now) - can you create a cut down version of your db with all tables emptied (but included) and all forms, querydefs etc included too. When created (copy .MDB file then delete data from tables rather than anything more complicated.) do a Compact & Repair on the database. When that's done Zip it up if you can, then attach it to this thread. It should be plenty small enough and will answer most, if not all, of my questions.

[ChaseCox]

I am not sure how to put a file on here.

[NeoPa]

I'll find out for you.
I know I've found instructions before.

[NeoPa]

1. Post a new post.
2. Edit (quickly) the new post.
3. In the screen for editing there should be the option to add or upload an attachment.
4. If it is just uploaded then add in a link to the file into your edited post.

[ChaseCox]

OK hope this works.

[ChaseCox]

This is taking to long to load. Can I email you the file. My email is ChaseDCox at gmail dot com if you would like to email me first.

[NeoPa]

How big is the file Chase?
Is it Zipped?

[ChaseCox]

How big is the file Chase?
Is it Zipped?

120,000 KB it is zipped

[NeoPa]

?!?
With no data in any of the tables; Compacted and Zipped it's 120 MB. That's weird.

[ChaseCox]

?!?
With no data in any of the tables; Compacted and Zipped it's 120 MB. That's weird.

Any thoughts.

[NeoPa]

Any thoughts.

I'll PM you an address to send it to.
I was hoping you'd come back and say something like - "Oh sorry - I forgot the Compact & Repair bit."

[ChaseCox]

I'll PM you an address to send it to.
I was hoping you'd come back and say something like - "Oh sorry - I forgot the Compact & Repair bit."

oh sorry I forgot the Compact and Repair bit! lol file is still too large to upload 51.7 KB

[NeoPa]

ROFLMAO.
You didn't forget to delete all the data first by any chance did you? That could make it work. You'd have to Compact & Repair after that again though, and Zip it up again.

[ChaseCox]

ROFLMAO.
You didn't forget to delete all the data first by any chance did you? That could make it work. You'd have to Compact & Repair after that again though, and Zip it up again.

Yeah all the data is gone, just the "shell" i suppose. I sent it to you though.

[NeoPa]

Yeah all the data is gone, just the "shell" i suppose. I sent it to you though.

Yes - the 'Shell'.
But in there is pretty well all the important logic etc.
This should help greatly.
L8rs -Adrian.

[ChaseCox]

Yes - the 'Shell'.
But in there is pretty well all the important logic etc.
This should help greatly.
L8rs -Adrian.

I will be here at my Comp for about an hour and 1/2, then I have to go on a trip. I may have access to access (ha) during the weekend, but I am not sure. I will be back in the office Tuesday Morning US Central Time 13:36 here now. Thanks again.