Create a form to filter records in a table 2

[doobybug]

Hi,

I need to create a form to perform a search in a table, with any fields found in that table. Can anyone help me pls?

 

[MajP]

Can you explain this a little more? What are you really trying to do? You probably have specific information that you are searching, and can probably design a better user interface than searching all fields. What type of information is in the table, how many fields, what type of search? What have you tried? Is it a keyword search, part of a word search, date range, value, etc?

People asks this question a lot, and to me it just seems useless. I have built all kinds of forms and controls to find records, but randomly searching all the fields in a table seems like an unfocused way to find records.

Normally this also seems to suggest that your data base is not normal.

 

[Mayhem9]

Hi MajP,

Can you elaborate on what you use or recommend?

I have a database in which I am logging tools and equipment in to and I often need to search for items. I tried creating a form to search on several varialbles (all with a default of null set and Like * set in the query) as demonstrated in this very helpful tutorial:

http://www.datapigtechnologies.com/flashfiles/searchform.html

The problem is that some of the variables are not actually stored directly into the table being searched (e.g. manufacturer name and type), so the search is not working. I have a main table (Tool_Log) which pulls the manufacturer name from another table (Manufacturer) using a drop-down list and I am pretty sure that this is the problem. If I remove these items, it works. I tried replacing the look-up variables with the actual variable from the relevant table (e.g. manufacturer) but I couldn’t get this to work either.

My problem at the moment is that the majority of the posts I find are either beyond my ability to follow or do not provide any examples (because the people discussing them know what they are talking about!!!)

If a search form isn’t what you would use, is a filter a better alternative? I haven’t got a grasp of filters yet but I am trying to figure them out.

Anyhow – any suggestions you can give would be greatly appreciated.

 

[MajP]

I have a main table (Tool_Log) which pulls the manufacturer name from another table (Manufacturer) using a drop-down list and I am pretty sure that this is the problem.

Yes this is bad. You Should never use drop down lists in a table!!! Even if MS shows many examples such as Northwind that does this. It only leads to pain down the road. Only use the pull downs on a form.

However if you create a query using the manufacturer table you should be able to search as described in the tutorial.

It sounds like you tried this but it still did not work. Can you list the actual table and field names and post the code?

 

[Mayhem9]

You Should never use drop down lists in a table!!!

Now you tell me!

In response to this particular issue, how would you structure your tables? My understanding of the reason behind using a database is to reduce the amount of data and avoid duplication. I hope this doesn’t sound negative – certainly not my intention. Just trying to figure out how to conquer this beast!

There are several fields in the Tool_Log table that are common to a number of tools (manufacturer, type, year of purchase, purchase details and tool location), which are all pulled from their own individual tables.

To be honest, each of these tables contain only two fields – an auto number (PK) and the variable (which is pulled into the tool log).

The tool log (which is the table I am trying to search) is as follows:

Tool_Log
ToolID dbLong PrimaryKey Indexed
ManID dbLong Indexed * #
Serial dbText Indexed *
TypeID dbLong Indexed * #
Size dbText Indexed *
Description dbText
Set dbText Indexed *
YearID dbLong
RRP dbCurrency
PurID dbLong
Date dbDate
LocID dbLong Indexed * #
ImagePath dbText

* = field in the search form / query
# = variable pulled from an external table

I can provide an explanation of the fields if required but I hope this is helpful.

 

[MajP]

No you should definately do what you are saying. The point is that the pull downs should not be in the table but in the forms. Also user should never enter data directly into a table, but do that through forms and queries.

So my guess you have a tool type table and it has something like
typeID
typeDescription
otherTypeFields

and in your data table you are saving typeID, but your pull down shows the typeDescription. In my opinion and many others on this forum, your table should show the long typeID. However, on your form you would have a combo to enter the typeID from a pull down that shows the type description but enters the typeID.

To be honest, each of these tables contain only two fields – an auto number (PK) and the variable (which is pulled into the tool log).

This may be overkill. I do not know what you mean by variables (data bases do not store variables). If your 'types' are just a short name then why have an autonumber PK. Why not make the short name the PK. If my type is "lawn mowers" then make that a PK. With a PK that can change ensure you apply cascade updates. So if you change lawn mowers to "lawn tractors" it updates throughout the db in all foriegn keys.

 

[Mayhem9]

Hi MajP,

Thanks for this. It is apparent however, that I am not doing a good job of explaining things here and a lot has to do with a lack of knowledge of Access.

I have a form that I use to input all my data and this has all the fields that I listed my last post, with the exception of the PK and also ImagePath (which is used to control the picture shown in the form).

The reason that I use an AutoNumber PK for all my tables, is that is how most of the databases I have looked at have been set up. That is basically how I have learned so far, a I find it easier to learn through doing rather than reading. Although, I must admit that this site is the easiest to read that I have found thus far.

If lawn mower was changed to lawn tractor, wouldn't the updates still occur, as the text is linked to the AutoNumber PK (isn't it)??

and in your data table you are saving typeID, but your pull down shows the typeDescription. In my opinion and many others on this forum, your table should show the long typeID. However, on your form you would have a combo to enter the typeID from a pull down that shows the type description but enters the typeID.

I’m really sorry but I don’t really understand what you are saying. When I open the table, the actual text value (e.g. box wrench) is shown. When I used the lookup wizard in designing the table, I chose to hide the PK. I don’t mind redoing this, as I have all the data in Excel (I actually logged all the tools in Excel and used auto filter to search but there was too much horizontal scrolling for my liking). So it wouldn’t be difficult to rebuild the table and import the data again. After all, I am sure that once I get the hang of things, I will find more uses for Access, so I might as well learn!

Essentially, I tried to create a search form to input the search string for the query (as shown in the tutorial I linked to). Given that it is these pull downs are throwing a wrench in the works, what are my options to search my Tool_Log form (which I previously incorrectly referred to as my table – I guess I figured the data was in the table and not the form, so the search was actually searching the table).

I look forward to your thoughts and comments.

Cheers,
Darren

 

[dhookom]

The sample in the demo isn't typical since it is a table that doesn't use any lookup tables. Since you are using lookup tables, you must create a query that you search rather than searching the table. The MainQuery must include what you have referred to as "their own individual tables". Then set the criteria against the fields you referred to as "variable".

I generally use and autonumber primary key in all of my tables. This is a much debated area but I just do what is easy to me and always works for me.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

http://www.4shared.com/file/194332244/23269eed/SearchForm.html

Probably the best is to demo this.
Take a look at all combos and look at
bound column
column count
column widths

Look at the IDs in the table. There are no look ups in the tables only the form.

The search form is primitive because it only allows you to search one field at a time.
If you can understand this then, I will show the next step to have a button to make a complex search and a key word search.

 

[Mayhem9]

Thanks Majp and Duane,

Ok, I think I am part way there but I am unable to replicate what you are doing. I can (well I think I can) see that the lookup is reference to the PK, which is hidden (column width = 0) and then the chosen field is shown.

What I cannot figure out is how to do it. I am guessing from the posts here and the table, that the look-up wizard wasn’t used when designing the table. I tried using it in creating a form but I get an empty drop down list. I tried creating an unreferenced combo list and then entering:

SELECT Customers.CustomerID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;

in the row source and selecting table/query as the row source type. What am I doing wrong? Is it my thinking, my execution or should I stick to Excel??

I will keep trying but I’m not winning so far.

 

[dhookom]

When I open the table, the actual text value (e.g. box wrench) is shown.

What is shown is probably not what is stored. If you want to search a table for what is stored, you should search for what is actually stored, not what is shown in the table.

If you want to provide a text box to search for Location you would need to add the location table to your record source and include the LocationTitle field to allow searching.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

if this is your query for your combo

SELECT Customers.CustomerID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;

it returns something like
customerID CompanyName
1 Joes Garage
2 Donut Shop
3 McDonalds

To display only the names, but to save the value

ColumnCount:2
BoundColumn:1
ColumnWidths:0";3"

Return 2 columns
Assign the value of the combo to the first column
Hide the first column, and make the second 3"

So I see McDonalds, but when I select it the combo equals 3. Then I filter the table on the value 3.

 

[Mayhem9]

Thank you both, once again for your help,

I have figured out where I was going wrong - I had created the form in design view but I hadn’t associated it with the table that the data was to be stored in! So now that I have that ‘small oversight’ sorted I think I will be able to replace the look-ups in my table and replace them with the look-up combo boxes in the corresponding form.

In regards to Duane’s comments, is there any way to check what is stored in the table? My guess from what you have said Duane is that despite "Box Wrench" being displayed in the table, "9" (the PK) might actually be what is being stored.

If I read your MajP’s final sentence, I would imagine that it is the PK that is stored in the table. If this is case, then what is the difference between creating the look-ups in the form, as opposed to the table?

Once I can figure this out, I should be able to figure out if the search form and query that is outlined in the online tutorial I initially tried can be used. If not, then I’ll have to do some more thinking (and questioning).

Thanks again to you both – you are actually providing me with the information to think and learn, rather than just giving me the code and the solution. I appreciate your time and assistance.

Cheers,
Darren.

 

[MajP]

http://www.mvps.org/access/lookupfields.htm

In regards to Duane's comments, is there any way to check what is stored in the table? My guess from what you have said Duane is that despite "Box Wrench" being displayed in the table, "9" (the PK) might actually be what is being stored.

Yes this is absolutely the issue. When you put a lookup in a table "wrench" is displayed, but that is an illusion because the table has 9. So if you try to search for "wrench" you cannot because only 9 is in your table. So you have two options. I choose the first.
1)Provide a search form that shows you wrench but actually returns 9 and looks for 9 in the table
2) Include a link to the type table and return "wrench", now search for wrench.

If you want to use the tutorial, you need to create a query that links to the type table. And search the linked field. I still think the type of search form I showed is far better than the generic keyword search. Just my opinion with a couple of thousand databases under my belt.

 

[dhookom]

Regarding lookup fields in table check out The Evils of Lookup Fields in Tables then just re-read this thread to add to the argument against lookup fields.

You can generally look at the table design to determine if the field is a lookup and what value is actually being stored. If the field is Long Integer, you can be sure it isn't storing text.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks - I'll have a play with the searches and see ho much trouble I can get myself in!

So what happens when the look-up is in the form and not the table: does the PK or the value you are looking up get stored?

The one thing I did like about the search form in the tutorial was you could search one of the fields, leaving the other blank. However, I am unsure if you could search multiple fields at the same time (e.g. 1/2" and box wrench).

I probably need to try a few different types of search and see what fits my needs.

Cheers,
Darren

 

[dhookom]

You generally would set up your forms with combo boxes so the foreign key matches the primary key of the related, lookup table.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Guys,

I removed all the look-ups from my table and replaced them with text fields that are populated from the look-ups that I created in the form. I retained the same names and they appear to work as intended. However, I have hit a problem with some code that I had associated with the look-ups (before I made the changes).

To save on having a button on the main form to open up each form for the look-ups, I use the following code to open the form by double-clicking the combo box. I found the code ages ago and find it very useful. The only addition I would like is if it could open to a new record, rather than the first. At the moment though it isn’t working at all:

Private Sub ManID_DblClick(Cancel As Integer)
On Error GoTo Err_ManID_DblClick
    Dim lngManID As Long

    If IsNull(Me![ManID]) Then
        Me![ManID].Text = ""
    Else
        lngManID = Me![ManID]
        Me![ManID] = Null
    End If
    DoCmd.OpenForm "Manufacturer", , , , , acDialog, "GotoNew"
    Me![ManID].Requery
    If lngManID <> 0 Then Me![ManID] = lngManID

Exit_ManID_DblClick:
    Exit Sub

Err_ManID_DblClick:
    MsgBox Err.Description
    Resume Exit_ManID_DblClick
End Sub

This simply throws an error if you type in something that isn’t in the combo box:

Private Sub ManID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Any ideas why these no longer work? The examples above are for my manufacturer look-up (I have five in total). Here are some of the details for the look-up (not sure if it helps):

Name: ManID
Control Source: ManID
Row/Source Type: Table/Query
Row Source: SELECT Manufacturer.ManID, Manufacturer.Man FROM Manufacturer ORDER BY [Man];

Any help would be greatly appreciated.
Cheers,
Darren

 

[Mayhem9]

Perhaps I was somewhat premature?

I just tried using a combo box to search within a form and it worked for the field that I chose where the data is typed into the form but not on the field using the look-up. In this instance, it is showing the PK.

Have I screwed up when creating the look-ups in the form?? Could it be a relationship problem, as when I deleted the look-ups from the table I had to delete the relationships? I have now found out that when I created the look-ups in the form that the relationships are not automatically created.

Any help is greatly appreciated.

Cheers,
Darren.

 

[MajP]

You will have to show your code and explain your fields. It is hard to guess where your mistake is. Did you look at the example I provided? If you post your db, it will be easier to provide help.