Running Query based on drop down menu and textbox (1 Viewer)

[George10988]

Hello everyone,

I am fairly new to Access and have been put on a project at my job to create a database for a local police station. I have good knowledge in C#; however, not much in VBA or SQL. I am having a problem here that I hope someone can help me with and with as much detail as possible (if possible). I created a form to be a main search form for the whole database. I want the officers to be able to select from five combo boxes on that form. Each combo box is linked to a common query that has all the fields (columns?) from every single table that I have. I basically want them to be able to select their own search parameter from the drop down list that is based off a field. I then want them to be able to type into the text box associated with it to get more specific results. So for example:

First Drop Down = FirstName, LastName, GangAffiliation, StreetName, etc.

Say for example they choose "FirstName" obviously I want them to be able to type in the name they want to search for in the text box and get the appropriate results in query view.

I want them to be able to choose any combinations they desire.

I am able to link textboxes themselves to a query by placing the following code under the criteria area for a query:

Like (IIf(IsNull([Forms]![frmDatabaseSearch]![txtSearchByFirstName]),"*",[Forms]![frmDatabaseSearch]![txtSearchByFirstName]))


however I realized that offering the ability for officers to choose which fields they want to search would be better for this type of database. Any help would be appreciated.

 

[Brianwarnock]

You really need a dropdown box per search criteria and they just select from the boxes, no need to transfer that to a textbox, then for each combo the SQL will show in the Where clause

(Fieldname=Forms!formname!comboname or Forms!formname!comboname is Null) and (nextone etc , or doing it in the design grid read this thread


Brian

 

[George10988]

You really need a dropdown box per search criteria and they just select from the boxes, no need to transfer that to a textbox...
Brian

You are right, no need to make it more complicated than it needs to be. Thanks Brian for that link. Since I am not too familiar with VBA it helps to have different pieces of code that are similar so I can better decipher exactly how the syntax for VBA works. If you or anyone else does have the time though I have one more question. I am not too confident In my table relationship building. I have looked up videos and have a book on access but I still can't figure out the solution to this one problem I have.

In the database I am creating I am trying to make a query that pulls together all the info from 5 tables that I have and using that query as the source for a main dataEntry form. The guy before me had put all the info in one table and used that table as a source for the data entry form and it worked great; however, with my multiple table approach the query just pulls the field names but none of the info contained in the field. I also get an error in the data entry form when trying to search using the access provided search box on the bottom of the form saying "You can't use find and replace now."

I suspect the root of this is my table relationships?

 

[Brianwarnock]

I would start a new thread for this new question, mentioning that fact here so that there isn't a double post, that way one of the relationship experts will pick it up whereas they may not look at this thread.

It will probably help if you can post a sample DB or atleast prints of the relationship and the query. The Db is best.

Brian

 

[George10988]

Roger that! thanks again