Question Make database with flexible search function

[Jerome]

Hello,

I am new on this forum and also new when it comes to Access. My goal is to make an inventory database in Access 2007.

The database will consist of:

1. A table holding all the data (with 8 columns), and n rows (depends on the inventory.
2. A split form with a text box to enter a search term, a combo-box to set the column that needs to be searched in the data table and a button to perform the search action (search form).

If it is possible I would like to implement a "Partial" search. For example, if the word that is searched for is metal, and in the text box "me" is typed everything that starts with me is displayed.

Is it also possible to search any part of a word? For example, if the word that is searched for is metal, and in the text box "tal" is typed every record that contains "tal" in the specified column is displayed?

I would like to start with a empty table in the split form (lower part), and if records are found that meets the search criteria, that they are displayed in the table.

In the past I've written some Excel macro's and I've tried to search the Internet for some literature on Access and macro and VBA programming. I basically know how access works but find it hard to implement. That why I registered to this forum

I hope you can give give some advice, maybe some recourses or a push in the right direction. I also don't want to reinvent the wheel when it comes to building a database. I don't know if similar thing are done before? I've tried a forum search but without satisfying results.

I've started in the general section because I don't know in which section this fits best?

Thanks in advance and with best regards,

Jerome

 

[David Eagar]

A starting point would be the criteria in the query for the subform:
like "*texttosearchfor*" - this would pick up anywhere that "me" was in the field being searched

 

[Jerome]

Thanks for you reply. Before I start with creating the database and form I would like to know if this can be done with the standard functions in access or if I need to use VBA?

 

[DCrake]

Here is a demo of what you want.

David

 

[David Eagar]

Thanks for you reply. Before I start with creating the database and form I would like to know if this can be done with the standard functions in access or if I need to use VBA?

I'm not an advanced user, so definitely can be done with standard functions

 

[David Eagar]

Before proceding too far - it is HIGHLY recommended from advanced users in this forum NOT to create combo boxes at table level. As your db gets bigger and more complicated, the chances of data corruption increases

 

[Jerome]

Here is a demo of what you want.

David

Wow that's almost what I am looking for.

Is it also possible to extend the search like this?

Is it also possible to search any part of a word? For example, if the word that is searched for is metal, and in the text box "tal" is typed every record that contains "tal" in the specified column is displayed?

Thanks in advance.

 

[David Eagar]

Does this fit?

 

[DCrake]

The only change you need to make (which D Edgar has done I suspect) is to place an asterisk in front of the like condition. Remember you will need to change the table and field names to match you own.

David

 

[Jerome]

Sorry, I mean in your example (DCrake).

In your code you have:

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & " Like '" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"

With placing an asterisk in front of the like condition you mean:

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & "*' Like '" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"

I am not familiar with searching a database

Thanks in advance.

 

[Jerome]

Does this fit?

I like the layout but the disadvantage is that all the products are in the combo box. I would like to select the column name and type the search term in a text box. But thanks for your contribution so far

 

[David Eagar]

At the risk of sounding bolshi, what you're after COULD be done, but SHOULD it?

A product can belong to 1 category, so doing a partial category search makes no sense.

Unless you have duplicate products, and you could from various suppliers, if the product name is the same, again a partial search makes no sense.

This is getting to grips with your basic data and table layout:

A product belongs to 1 category
A product can have 1 or more suppliers

What is the best way to design these tables and create the relationships between them?

 

[DCrake]

The revised code should read

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & "*' Like '[B][COLOR="Red"]*[/COLOR][/B]" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"

David

 

[Jerome]

At the risk of sounding bolshi, what you're after COULD be done, but SHOULD it?

A product can belong to 1 category, so doing a partial category search makes no sense.

Unless you have duplicate products, and you could from various suppliers, if the product name is the same, again a partial search makes no sense.

This is getting to grips with your basic data and table layout:

A product belongs to 1 category
A product can have 1 or more suppliers

What is the best way to design these tables and create the relationships between them?

Without knowing what "bolshi" means I don't you are sounding like that. I really appreciate that a lot of people are trying to help me. I have to learn a lot about Access (I know).

For example I have one column in the table filled with product names. These product names are very similar like (this is just a example and not the real names):

- Blank metal
- Black metal
- White metal
- Black plastic
- White plastic

If I want to have all the products filtered that contain "metal" a partial search would be sufficient.

This is what I mean with a partial search. Maybe my choice of definition is not correct.

Cheers

 

[David Eagar]

Bolshi is an old fashion term when the bolshovics ran the Soviet Union and ANY dissent ended up in in a 20 (or more) year trip to the Gulags in Siberia!

A Product name should be unique (unless you have multiple suppliers) - whether it is metal or plastic should be picked up by its category

 

[Jerome]

The revised code should read

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & "*' Like '[B][COLOR=Red]*[/COLOR][/B]" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"

David

When I replace the excising line of code with the line of code from above. The search function does not work anymore?

 

[DCrake]

Try this version


David

 

[Jerome]

What is the difference between this version and the first version (because I can't see any)?

I've tried to replace this line:

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & " Like '" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"

With this line (from your previous post):

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & "*' Like '*" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"

This does not work.

 

[wiklendt]

create a new query.

add any table with any field to it that has text.

then in the criteria for that field, type in

 Like * & [enter partial search text] & *

or

 Like * & metal & *

or similar.

run the query to check that it works.

now toggle to SQL view. this should get you on the right track in how to formulate the SQL in a VBA setting.

copy the relevant section and slot it into your VBA.

 

[Jerome]

Thanks for the advice, it works

StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"