Using criteria to use Query as a Filter

[Mountaineer529]

Hi,


Disclaimer: I am an MS Access rookie. I just learned it this week.

I’ve been tasked to create a database for my job, and I have everything set up, but I can’t get the search qualifications to work (or whatever they are called, which I guess depends on which way you set them up). Basically, what I need is for a user to be able to input data on a form, and filter/search a database based off of that data. I’ll try and explain in detail.

I’m using Access 2007, and what I need to do is set it up so the user can put information on a form, hit a search button, and filter the database. For example, you put information in 3 of 12 fields, and then when you click the search command, the query only shows the records that have the entered information in each of the 3 fields. Obviously, the information entered is different for each of the fields.

Now I’ve tried one method, from Microsoft’s website, that seems like it should work, but it doesn’t. Here is a link to the article I followed, https://support.microsoft.com/en-us/...n-us/kb/304428. This was for Access 2003, but I figured it would work for me.

I set everything up like the article says:

I imported the database (table) from Excel, I called it STIG Database.

I created a Form (called it Search) with the fields (that correspond with the columns in the STIG Database), and set up the Command Button for OnClick ‘SearchMacro’ (The name of my macro).

Created a Macro ‘SearchMacro’ to look like this Action-OpenQuery;Argument-STIG Query, Datasheet, Read Only.

Created a Query ‘STIG Query’ with all of the columns of the database (table/workbook) as the Fields. For simplicity sake I’ll just use two of the fields. Vuln ID and 800-53

Now for the ‘Vuln ID’ field I put the criteria in like this:

[Forms]![Search]![Vuln ID] Or [Forms]![Search]![Vuln ID] Is Null

And for the ‘800-53’ field I put:
[Forms]![Search]![800-53] Or [Forms]![Search]![800-53] Is Null

I kept the names of the fields on the Form the same as the Column Headings in the STIG Database.

This is the SQL view of the information:

SELECT [STIG Database].[Vuln ID], [STIG Database].[STIG ID], [STIG Database].[IA Controls], [STIG Database].Mitigations, [STIG Database].Responsibility, [STIG Database].Classification, [STIG Database].STIG, [STIG Database].[VMS Asset Posture], [STIG Database].CCI, [STIG Database].[800-53], [STIG Database].[800-53A], [STIG Database].[800-53 Rev 4]

FROM [STIG Database]

WHERE ((([STIG Database].[Vuln ID])=[Forms]![Search]![Vuln ID] Or [Forms]![Search]![Vuln Id] Is Null) AND (([STIG Database].[800-53])=[Forms]![Search]![800-53] Or [Forms]![Search]![800-53] Is Null));

No matter what information I put in it always shows me the entire database and doesn’t filter anything.

I’ve changed some of the criteria around, such as remove the second (Null) part. I used simpler names for my names of Forms, Macros, Querys. I couldn’t get this to work. And I couldn’t get the test for Northwind database that they give you to work either, but I’m sure that’s because the fields didn’t line up since the instructions are for an older Access.

I looked at SQL stuff for this, but couldn’t find anything. I found something for VBA, but that is above my head. This is what I found, https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

And this is the VBA code, buildcriteria method, it says to use

Sub SetFilter()
Dim frm As Form, strMsg As String
Dim strInput As String, strFilter As String

' Open Products form in Form view.
DoCmd.OpenForm "Products"

' Return Form object variable pointing to Products form.
Set frm = Forms!Products

strMsg = "Enter one or more letters of product name " _
& "followed by an asterisk."

' Prompt user for input.
strInput = InputBox(strMsg)

' Build criteria string.
strFilter = BuildCriteria("ProductName", dbText, strInput)

' Set Filter property to apply filter.
frm.Filter = strFilter

' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True
End Sub

I couldn’t figure out what I needed to change, and how to apply it to the form.

If anyone could give me some guidance on how to do this it would be greatly appreciated.

Thanks in advance.

[June7]

I don't use macros, only VBA.

I also NEVER use dynamic parameterized queries.

I would not use an InputBox for this - too hard to validate user input. Use an UNBOUND control on form for user input as demonstrated in http://www.allenbrowne.com/ser-62.html.

[orange]

Have you read this article by Allen Browne?

Ooops: I see June has been here quicker than me also!!!!!!!!!!!!!

[Mountaineer529]

I'll check that article out, and see if I can figure it out at work tomorrow. Thanks for the quick reply.

[Mountaineer529]

Okay. I downloaded the search database in the article, and feel like I have a good grasp of it, but have a couple questions. Sorry for any confusing wording. I'm just burnt out and don't feel like editing the post.

1. For the continuous form, how does he get the table into the form for the details section?

I tried a couple ways to do it from looking into sources I found. I put the table on a form in tabular view, and put that form as a subform on my Search form. But after looking at his he doesn't do it this way.

I was messing around with his search database download, trying to recreate it, and I just opened a form, clicked subform, and dragged the table a newly created form. But this creates another form. In his there is only one form listed. So what gives? How did he get all the fields from the table in that section like that without creating a second form?

I liked having it in Tabular view because it makes the data look better, but right now I just want to get the code to work. So if someone could tell me the best way to do the continuous form that would be awesome.

2. While trying to recreate his database, and messing with mine, I discovered when I hit the command button it won't apply the filter. I was just making a button. Naming it cmdFilter, and changing on click to Event Procedure; which seems like the way his is set up.

I feel like these are the two problems holding me back. I discovered what the majority of the code is, and from looking into VBA and other online sources feel like I'm getting closer everyday to getting this damn thing to work, but need a little more guidance.

A couple extra questions. M

y database is going to have a lot of columns of data, at least 8. I have all this on one table. Should I separate it into multiple tables, and if so why? I don't understand why the creator of this database article had two separate tables. Why is that better? I looked into creating relationships between tables, but never understood why it's necessary.

I feel like I transposed his code to my database correctly, but have all my fields as text fields; some have numbers in them, but I figured that wouldn't matter even if it's a text field. So what part of his code is superfluous? For example. I got rid of the date and number fields, and didn't put a clear filter button in there. I just want the bare bones to apply the filter.

I would post my code, but it's at my work computer, and I can't access the internet at work very freely, but if I can't get it to work tomorrow, I can e-mail my code home, and post it that way.



Thanks in advance for any responses.

[Mountaineer529]

Nevermind guys. I got my own created search form on his search database to work, and God willing I can get it to work for mine.

Thanks for the link to the article. It was a HUGE help. I'll let you know if it all works out for me.

[nick404]

Should I separate it into multiple tables, and if so why? I don't understand why the creator of this database article had two separate tables. Why is that better? I looked into creating relationships between tables, but never understood why it's necessary.

To help answer this question of yours sometimes it is necessary (or very well encouraged) and other times it isn't. The reason to create multiple tables and relationships between them (also known as 'normalization') is to avoid duplicating data over and over. This is a classic example (much like taking the derivative of x^2 in calculus 1): Let's say you have a database for tracking orders made for your company. You would need information like product number, quantity, price, the customer name, address, zip code, etc. Now if you have returning customers you don't want to be continually typing in their name, address, zip as you will be duplicating data in your 'main table' and effectively sucking up a lot more storage space than necessary. So instead by creating a 'customers' table and assigning each customer (including their address and zip code) a unique identifier you can save space and create a simpler table that avoids having many duplicates of the same data. By normalizing data you make your database run smoothly, efficiently, and helps to prevent some problems down the road. Here is a link using another good example of data normalization. Probably explains better than I did, I suggest you take a look!

[June7]

8 columns (fields) is not a lot in one table. However, as nick404 describes, splitting into separate tables may be appropriate to prevent duplication of data that bloats database as well as make data entry simpler, faster, more accurate. Access has a 2GB size limit.

It is a balancing act between normalization and ease of data entry/output. Normalize until it works and denormalize until it hurts (or vice versa).

[Mountaineer529]

So, I managed to get everything to work, well I didn’t include the clear filter button, but I could add that later. Thanks for all your help, and the link to that article. I really appreciate it.

I do have two more questions. I figured I would post them here first since I already have this going, and it’s still kind of query related.
First, I want to make the bottom half, that displays the results, datasheet view; so you don’t have to click through each individual record. This involves making the database a subform, which I did. However, I can’t figure out how to indicate the control fields for the subform in the VBA code.

I tried a couple of different ways:

If Not IsNull(Me.txtFilterVulnID) Then
strWhere =strWhere & "([Forms]![frmSTIGdb]![VulnID] Like ""*" & Me.txtFilterVulnID &"*"") AND "
strWhere = strWhere & "(Forms![frmSearch].[frmSTIGdb].Form.[Vuln ID]Like ""*" & Me.txtFilterVulnID & "*"") AND "
End If

[frmSearch] is the main form with the search fields and filter buttons, and [frmSTIGdb] is the subform with the Database on it. This is just a couple examples of what I tried.

I found this link to a decent article explaining how to refer to items on a sub-form in VBA. http://bytes.com/topic/access/insights/599404-referring-items-sub-form.If you can expand on this it would be great.

The second question is more Excel related, but also directly relates to queries in Access.

So, in my excel spreadsheet I have it so one row has info in every column (say 8 columns) that pertains to a record # that is listed in column A. Then there are several rows below it that have additional information, but only in 3 of the 8 columns, that pertain to the record in the first row. So the majority of the cells below the main row with the record # are blank; including column A that has the record # in it.
My question is can I have it so those sub rows are linked to or grouped with the main row? So, if I searched the record # it will show all the rows in the datasheet view linked to that record, even though the record# column is blank in the sub rows?

I would fill down the data in excel, but this would make it look like each one is an individual record. I could, also, put all the data in one row, but that isn't the layout I need.

Thanks in advance, again, for any replies. You guys are great, and I really appreciate all the help with getting my sea legs when it comes to Access.

[June7]

Can set form as Continuous View and arrange controls to look like datasheet. This means everything can be on one form and eliminate the complication of referencing subform.

Sounds like the 3 columns should be in a related dependent table. Then use a form/subform arrangement. Main form displays 5 fields and subform displays the other 3. The main and subform records would be synchronized by the Master/Childs Links of the subform container.

Seems you need a better understanding of relational database principles. Maybe this will help: http://www.rogersaccesslibrary.com/

[Mountaineer529]

Thanks for the quick reply. I'm off until Monday, but will probably mess around with this at home over the weekend.

One quick question. When I made the bottom section that displays the results I used the add existing fields button at the top to add the fields direct from the table that I imported from excel. So all I had in the navigation pane is the form and table. This can be changed to look like a datasheet? I messed around with the controls, but I couldn't seem to get it into a datasheet view.

I immediately thought of using two tables and relationships between the 3 columns as a way to do this, but when I went to do it I kept running into problems. I'll review the article you linked and become familiar with relational database principles.

Thanks again for all your help. I, surprisingly, enjoy messing around in access and using VBA. I'm starting a class on edX that is a beginner course in computation thinking and programming next week; it uses Python 2.7. Hopefully, I get a lot out of it. These are skills I'm going to need to learn to not only get ahead at work, but in the future make sure I can find a steady job.

[June7]

A form Default View property can be set as Datasheet or can be set as Continuous View and controls arranged to look like datasheet.

Programming concepts carry over to other languages. I was familiar with ancient BASIC and FORTRAN when I started learning Access and VBA. Have to learn different vocabulary and syntax but they all use same basic logic concepts and structure.