Filter for Inactive Documents (1 Viewer)

[glabbe]

This is actually a 3 part question. First, I created a search form that returns the correct records based on the criteria in a text box but now want to filter out the "Inactive" ones. I only want to Include Inactive when a checkbox is selected. Second is how do I replace the Machine code shown with the Machine Description from another table. The list was manually typed under the combo box for the time being. And third is how do I get a record count from a list box. I'd like to know how many records were returned. I'm very new to all of this so I have no idea how to pull it off or what more you might need to help me through this. Thanks in advance for your help.

 

[theDBguy]

Hi. For the first question, you could modify your filter criteria to have two conditions, one for the text field and the other for the yes/no field.

 

[glabbe]

Sounds like you understand where I'm going with this, I'm just too ignorant about how to get there. This is my search query. Is this where I add filtering?

 

[theDBguy]

Sounds like you understand where I'm going with this, I'm just too ignorant about how to get there. This is my search query. Is this where I add filtering?

Hi. Can you post the SQL statement for your query? It looks like you have an OR where condition because you're looking into two different columns. So, if the intent is to show all records (both active and inactive) when the checkbox is not checked and only the inactive ones when it's checked, you could try putting something like this for the criteria row of the Inactive column.

(True OR Forms!FormName.CheckboxName)

You'll have to put that in both the two rows where you already have a criteria entered for the search box.

Hope that helps...

 

[June7]

Methods for retrieving related machine info:

1. joining tables in query

2. multi-column RowSource in combobox - use an SQL statement and pull data from table, not build a ValueList

3. DLookup() domain aggregate function

This is basic Access functionality. Wouldn't hurt to spend a solid week studying an introductory tutorial book.

Getting count of listbox records is a bit more advanced, an unusual requirement. Show the listbox RowSource query.

 

[glabbe]

SELECT PRODDTL.ID, PRODDTL.PARTNUM, PRODDTL.PARTNAME, PRODDTL.PARTREV, PRODDTL.MACHINE, PRODDTL.INACTIVE
FROM PRODDTL
WHERE (((PRODDTL.PARTNUM) Like "*" & [Forms]![SEARCH]![SearchBox] & "*")) OR (((PRODDTL.PARTNAME) Like "*" & [Forms]![SEARCH]![SearchBox] & "*"))
ORDER BY PRODDTL.PARTNUM;

The reason for the OR is when I search I might want to find the form based on a partial part number or I might only know the part description.

I only want to show active records when the checkbox is unchecked and all records when checked. A little different than your statement.

Thanks for your understanding.

 

[theDBguy]

SELECT PRODDTL.ID, PRODDTL.PARTNUM, PRODDTL.PARTNAME, PRODDTL.PARTREV, PRODDTL.MACHINE, PRODDTL.INACTIVE
FROM PRODDTL
WHERE (((PRODDTL.PARTNUM) Like "*" & [Forms]![SEARCH]![SearchBox] & "*")) OR (((PRODDTL.PARTNAME) Like "*" & [Forms]![SEARCH]![SearchBox] & "*"))
ORDER BY PRODDTL.PARTNUM;

The reason for the OR is when I search I might want to find the form based on a partial part number or I might only know the part description.

I only want to show active records when the checkbox is unchecked and all records when checked. A little different than your statement.

Thanks for your understanding.

In that case, I will probably try it this way:

(True AND Forms!FormName.Combobox=False) OR Forms!FormName.Combobox=True

 

[glabbe]

Sorry, I appreciate you're trying to help but I don't know what to do with this. I added the statements as criteria under the Inactive with the correct form name but it returned no records, checked or not. Is there any way you can tell me exactly where I should enter this?

 

[theDBguy]

Sorry, I appreciate you're trying to help but I don't know what to do with this. I added the statements as criteria under the Inactive with the correct form name but it returned no records, checked or not. Is there any way you can tell me exactly where I should enter this?

Well, earlier, I asked you to post the SQL statement for your query. If you can do that, I could try modifying it to include my suggestion. So, could you post the SQL statement please? Thanks.

 

[glabbe]

I did or maybe this is less than what you were looking for.

SELECT PRODDTL.ID, PRODDTL.PARTNUM, PRODDTL.PARTNAME, PRODDTL.PARTREV, PRODDTL.MACHINE, PRODDTL.INACTIVE
FROM PRODDTL
WHERE (((PRODDTL.PARTNUM) Like "*" & [Forms]![SEARCH]![SearchBox] & "*")) OR (((PRODDTL.PARTNAME) Like "*" & [Forms]![SEARCH]![SearchBox] & "*"))
ORDER BY PRODDTL.PARTNUM;

 

[theDBguy]

I did or maybe this is less than what you were looking for.

SELECT PRODDTL.ID, PRODDTL.PARTNUM, PRODDTL.PARTNAME, PRODDTL.PARTREV, PRODDTL.MACHINE, PRODDTL.INACTIVE
FROM PRODDTL
WHERE (((PRODDTL.PARTNUM) Like "*" & [Forms]![SEARCH]![SearchBox] & "*")) OR (((PRODDTL.PARTNAME) Like "*" & [Forms]![SEARCH]![SearchBox] & "*"))
ORDER BY PRODDTL.PARTNUM;

Hi. I guess I missed it, sorry about that. So, try the following:

SELECT ID, PARTNUM, PARTNAME, PARTREV, MACHINE, INACTIVE
FROM PRODDTL
WHERE (PARTNUM Like "*" & Forms!SEARCH!SearchBox & "*"
OR PARTNAME Like "*" & Forms!SEARCH!SearchBox & "*")
AND (INACTIVE=True AND Forms!SEARCH!CheckboxName=False
OR Forms!SEARCH!CheckboxName=True)
ORDER BY PARTNUM

Make sure to replace "CheckboxName" with the name of the Checkbox control on your form.

 

[glabbe]

You're awesome! This worked perfectly. The only thing I had to do for ease of use was add a button to my form to rerun the query. Without it I had to actually edit the search criteria in order to rerun the search.

With this bit of help I was able to figure out what to do with part 2 of the question myself. For now I'm going to forget about part 3, the record count, and concentrate on getting this rolled out to the floor.

Thanks again for everything.

 

[theDBguy]

You're awesome! This worked perfectly. The only thing I had to do for ease of use was add a button to my form to rerun the query. Without it I had to actually edit the search criteria in order to rerun the search.

With this bit of help I was able to figure out what to do with part 2 of the question myself. For now I'm going to forget about part 3, the record count, and concentrate on getting this rolled out to the floor.

Thanks again for everything.

Hi. Congratulations! Glad to hear you're making good progress. Good luck with your project.

 

[glabbe]

Sorry to come back to this but I find myself double clicking on a line to open the form rather than use the Open Selected button i have. I've tried adding code to the double click event but either get a blank form or nothing. I've also tried adding a macro but was getting the exact same record every time so I was missing something. Any knowledge of this area?

 

[theDBguy]

Sorry to come back to this but I find myself double clicking on a line to open the form rather than use the Open Selected button i have. I've tried adding code to the double click event but either get a blank form or nothing. I've also tried adding a macro but was getting the exact same record every time so I was missing something. Any knowledge of this area?

Hi. If you have a button that works, and you want to have the same functionality by simply double-clicking on the record, then you should be able to simply "call" the button's Click event in the DblClick event of the Form. Hope that makes sense...

 

[glabbe]

Well that was easy enough. Guess that's why they pay you the big bucks. Thanks again..

 

[theDBguy]

Well that was easy enough. Guess that's why they pay you the big bucks. Thanks again..

Hi. Glad it hear you got it sorted out. Cheers!

 

[glabbe]

There's probably a better way to do this but I'll ask using what we've been working on. If I have a list like the attached, you can see that I have a number of different machines that similar parts are run on. I'l like to further filter the list by machine so my thought was to add a check and type the new criteria in a text box. Keeping in mind the Include Inactive check, does this make sense or how do I go about it.

 

[theDBguy]

There's probably a better way to do this but I'll ask using what we've been working on. If I have a list like the attached, you can see that I have a number of different machines that similar parts are run on. I'l like to further filter the list by machine so my thought was to add a check and type the new criteria in a text box. Keeping in mind the Include Inactive check, does this make sense or how do I go about it.

Hi. You basically just need to build the criteria the way you want them applied. For example, after using the criteria we built to show or hide the inactives, we get a result set displayed on the form. Now, if you want to further limit the resulting list down to a specific machine, you should be able to add that criteria as a separate group, like so:

...WHERE (all the previous criteria within these opening and closing brackets) AND (the new criteria for the machine inside this pair of brackets)

Hope that makes sense...

 

[glabbe]

It makes sense but I decided to simplify it a little and just add MACHTYPE to the search box that you helped me with. Keeping it simple until I can get a few more hours under my belt and understand a little better. Thanks again.