Access 2003: Form Combo Box/Text Entry - Results in Query

[mcdonger]

Hi,

I’m looking for a little help with creating a form & query combination for searching my database. For starters – I am using Access 2003.

I have a large amount of information stored in a table on my database (the table has around 20 fields, with around 200 rows of data which is added to on a daily basis). I have several different search forms depending on what type of search the user might want to do. The fields vary from records such as ‘Date Received’, ‘Unique ID’, ‘Account Name’ etc.

I have hit a bit of a roadblock on creating what I have named the ‘AllSearch’ and ‘SingleSearchForm’ forms. As the names would indicate, I want the ‘AllSearch’ form to be where the user can search the entire table and generate a query showing all of the entries that match their chosen criteria. The ‘SingleSearchForm’ is where the user can search for a specific record based on their chosen criteria and the results are displayed on the form itself.

The problem that I have with the ‘SingleSearchForm’ is that I have created a Combo box populated with all of the field names (called Combo65 at present) and a Text box where the user can input what they wish to search for (called Text67 at present). I would like the user to be able to choose their chosen field from the Combo box and then type in what they would like to search that field for in the Text box and then press the ‘Search’ button in order for the form to find the first instance of their chosen Text input against the chosen field from the Combo box, and return that on the form itself. For example – The user chooses ‘Account Name’ from the Combo box and then types in ‘Ryan Peck’ into the Text box and presses Search, with the form then finding the first instance of ‘Ryan Peck’ in the ‘Account Name’ field and returning all of the record details associated with this name. I have the form built with the appropriate boxes, but I am stumped on the VBA coding for the ‘Search’ button as to how to get it to find what I want it to.

This leads me in to the issue that I then have with the ‘AllSearch’ form. The form works on the same principal – the user selects which field they wish to search from a Combo box and then types in what they wish to search in a Text box and executes the search by pressing a button. This is obviously the same issue as I have above in that I’m unsure as to how to write this, but is amplified by the fact that I wish the results of this search to be output into a query. Basically – if the user wants to search by ‘Date Received’ and inputs the date ‘03/09/2012’, I want the query to then execute and return all records which have the date as 03/09/2012 in the Date Received column.

Hopefully you’ll be able to offer some help / guidance on this point as this issue has been stumping me for some time and I have been unable to find any information via numerous searches around the internet (for starters I’m not even certain how to word the problem I am facing to search for it accurately, but suffice to say I have found nothing).

If you need any further information / examples / background, please let me know and I’ll happily oblige - many thanks in advance for any help you are able to offer!

 

[rs2k]

Hi mcdonger,

I cant provide you with the exact answer you are looking for, but I use the link below to build my search functions;

599CD Video Player: http://www.amicron2.com/videos/tips/access/multi-field-search.wmv

It basically searches on any field that you choose, the more fields you enter the more it filters the results.

HTH
Colin

 

[mcdonger]

Many thanks for coming back to me.

I found your link very helpful with regards to building a query, and it does almost exactly what I want. However, the problem is that the form (and table) I wish the user to be able to search through has approximately 20 fields on it. Obviously, to make a search form like the one shown in the video would make it rather sizable (in screen space that is), and potentially quite tedious for the user to have go through.

Does anyone happen to know a way of taking the same setup/principal, and rather than having to specify that each query criteria has to be linked to an individual related field on the form, to make it so that the field in the query to apply the criteria to is chosen from the dropdown box on the form?

I.e if the user chooses 'Unique ID' from the drop down field, then the query would put the criteria into the 'Unique ID' field and then search it for the contents of the static text box on the form. If the user chooses 'Customer Name' from the drop down field, the query puts the criteria into the 'Customer Name' field and searches it against the contents of the text box.

I would imagine something along the lines of LIKE "*" & [Forms]![AllSearch]![Text67], but with the query or some VBA code doing the leg work of choosing which 'Field' this would go into on the query?

That said, the results for the SingleSearchForm should be displayed in the boxes listed on the form itself. (i.e my form layout is that there is a dropdown and text entry box at the top with a search button, and then all 20 of the fields are listed underneath and which display the results of the search conducted in the box above).

Hopefully this makes sense, not sure of a clearer way of writing it unfortunately.

Thanks for any help provided!

 

[rs2k]

Hi Mcdonger,

approximately 20 fields on it. Obviously, to make a search form like the one shown in the video would make it rather sizable (in screen space that is), and potentially quite tedious for the user to have go through.

The link I provided does what you are asking and more, you are asking to filter on one criteria using a combobox, why not give the user more options to filter. The user doesn't have to fill out all the fields. The way the criteria is structured allows for empty fields in the search form.

As an example, say you have 20 fields in your table/form, the user can enter say 'USA' in the 'country' field and it will return all records for USA, if they then proceeded to add 'California' to the state field, only 'USA and California' records will be returned, and so on... and so on, so the user can use 1 field or 20 fields if they require.

How many records would come back for 'Smith' as opposed to 'Smith and Long Beach'??

HTH

Cheers
Colin

 

[alansidman]

I envision that you could accomplish this with a combo box listing all the field names in your query. One search text box on your form. In VBA, use the Select Case function
Look here: MS Access: Case Statement

In your results for the Select Case you would create your WHERE statement for the criteria.

Then when you run your query from VBA, you would use the variable WHERE statement in your SQL statement.

Alan

 

[alansidman]

Here is some sample VBA that I tested. You would naturally have to change field names, query names, tables, etc.

Private Sub Run_Query_Click()
Dim Q As DAO.QueryDef
Dim strWHERE As String
Dim strCrit As String


Select Case [myCombo]
    Case 2
    strWHERE = "Date1"
    Case 3
    strWHERE = "Date2"
    Case 4
    strWHERE = "Date3"
End Select


strCrit = Me.mySelection


strWHERE = strWHERE & "=" & "#" & strCrit & "#"


CurrentDb.QueryDefs.Delete ("qrySearch")


Set Q = CurrentDb.CreateQueryDef("qrySearch", "SELECT cedb.twr, cedb.Date1, cedb.Date2, cedb.Date3 FROM cedb WHERE " & strWHERE & ";")
DoCmd.OpenQuery "qrySearch", , acReadOnly




End Sub

My fields are date fields so they require the # signs surround the criteria. Yours will be different.

Alan

 

[mcdonger]

As a bit more of a general background (if anyone happens to be interested and if it might be useful to others), the system is intended to be used to 'load' and track cases handled by a team of 6 people. We currently use an Excel workbook I created a while back to collate & track all of the information. However, due to the level of work (approximately 300-400 pieces per month) and the sheer number of fields that we need to track, the workbook gets to be quite a large size and rather slow. Unfortunately, whether it is due to Excel 2003 or just the nature of the shared drives at work, if we attempt to share this workbook (even if it is a fresh one created for the new year), the size of the file balloons beyond belief and it becomes incredibly slow to use, so we're stuck with an unshared workbook that becomes rather frustrating as it needs to be accessed continually by each team member.

As such, I'm in the process of attempting to create this database to act as, essentially, a 'front-end' to the workbook that all of the team members can use at once to load the cases & update their progress from start to finish, as well as use it as a 'hub' to access the variety of links we use (approximately 15 links to various sites & files). The management can then use the Workbook as and when needed to extract the information from the database in order to obtain the various bits of MI they require (such as volumes of work, what is outstanding etc).

The search functions that I am working on (and a little stuck with, hence this thread) are intended to act as a way for the user to quickly obtain information similar to how it was displayed on the Excel workbook as this is a format familiar to them (unfortunately those who need to use the database/workbook are not particularly computer literate, so I'd prefer to keep things as familiar as possible for them to prevent any issues) i.e the user can easily find how many pieces of work have been received today and that are still outstanding and any associated details of these pieces of work relatively quickly and easily.

 

[mcdonger]

Hi Mcdonger,

The link I provided does what you are asking and more, you are asking to filter on one criteria using a combobox, why not give the user more options to filter. The user doesn't have to fill out all the fields. The way the criteria is structured allows for empty fields in the search form.

As an example, say you have 20 fields in your table/form, the user can enter say 'USA' in the 'country' field and it will return all records for USA, if they then proceeded to add 'California' to the state field, only 'USA and California' records will be returned, and so on... and so on, so the user can use 1 field or 20 fields if they require.

How many records would come back for 'Smith' as opposed to 'Smith and Long Beach'??

HTH

Cheers
Colin

After having a bit of a rethink as to the structuring of the form and the searches a user might need to perform, I think what you have suggested would be good for the 'AllSearch' form. After having a scan through the database (which I can only do at work at the minute), there are a total of 29 fields on the table itself, although the user would only ever need to search for data in approximitely 11 of these (things like the date when an entry was created, name of the customer, what the decision was for the piece of work etc) whilst the rest of the fields, while useful, are not necessary to filter down or search by.</SPAN>


Also, apologies for the length of my posts, I dont realise whilst I'm writing them but they turn into quite the wall of text it seems, so sorry for that, and many thanks for the help provided so far, still experimenting at the minute.

 

[mcdonger]

Hello again!

Following on from the above questions, I have managed to get my Form to Query search working perfectly as per Colin's replies & provided link and a big thank you to both you and Alan for your replies.

However, I am still having a little trouble with the on-form search that I touched on above.

Basically, what I have attempted to do is take the basic code generated by the Combo Box wizard for finding a record based on what was selected, and then modify it to find a record based on the combined values of a Combo Box & Text Box.

Basically, this is the default code generated by the Wizard:

Private Sub Combo35_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Unique ID] = " & Str(Nz(Me![Combo35], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When run, that basically just finds the record that corresponds to the value chosen in the combo box against the Unique ID field and then displays the record information on the form where I already have the 29 fields displayed.


I have modified it to read like this:

 Private Sub Command33_Click()
    Dim rs As Object
    Dim StrFieldName As String
    
    StrFieldName = Me![Combo41].Value
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[StrFieldName] = " & [Text43].Value
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However, I am having significant difficulty in getting the 'StrFieldName' to work. I have reworded the code multiple times but only ever get error messages reading 'The Microsoft Jet Database engine does not recognize 'StrFieldName' as a valid field name or expression' or 'Syntax error (missing operator) in expression'. I have also tried attempts at using 'Combo41' directly in the FindFirst line rather than trying to define it as something seperate.

I'm at a bit of a loss as to how to word it so that it finds the first record for the field name chosen from Combo41 and the value input into Text43.

Any assistance is greatly appreciated!