Search form

[GavGun1986]

Hi

Ok basically I have gone through the majority of these forums, looked on the web and everything and I still cannot work out how to code this database to get what i want so i really hate asking ppl to do stuff for me but really need assistance with this because tearing hear out.

I have data in this database in a table and i have created a form of combo boxes. I want the user to be able to populate each combo box with the data they kno and then press one button and search for the data. I want the data to be presented back in a subform which can be printed off as a report. Ideally i would like little check boxes so the user can choose which fields they are searching. I would also like to be able to add new fields however I think i kno how to achieve this.

Any help will give me a lifeline so im very grateful

Gav

 

[jzwp22]

Well, before you get too far with search forms, you need to fix your table structure. Your database is not normalized. As an example, you have repeating groups: Child level 3 P/N, Child Level 2 P/N...

In order to help you sort out your table structure, we need a little more background & detail on what you are trying to accomplish with your database. Could you please provide more information?

 

[GavGun1986]

Well, before you get too far with search forms, you need to fix your table structure. Your database is not normalized. As an example, you have repeating groups: Child level 3 P/N, Child Level 2 P/N...

In order to help you sort out your table structure, we need a little more background & detail on what you are trying to accomplish with your database. Could you please provide more information?

I don't know what normalized is, sorry, The repeating groups are supposed to be there so don't worry.

I want the user to be able to type in part number, fair number, fair date, process, data card, customer, or customer delivery date data (the user might not know all the fields and if they dont they will leave it blanks and i want the search to not include this item). Once the results are filtered to the search criteria i want to be able to use it in a report.

I just cant get the multiple search tool to work, i want the search options to all be in combo boxes aswell.

Any help you can give me with this is greatly appreciated! Thank you so much for replying

 

[lcook1974]

I looked at the database and didn't see a search function or anything...All I see is a sheet 1 and sheet 1 (form).

Do you have any "events" happening? have you started a code for it?

In order to have all them as "drop down" boxes you will need to create a query for each "drop down".

Attached is my database I am currently trying to get the search function to work. It works if I ONLY have the Vendors name in there but if I try dates it doesn't want to play nice.

Thought this may start to get you on the right track, and hopefully get me some help on this thread as well.

Larry

 

[jzwp22]

Repeating groups are a sign that the database is not normalized and should not be there at all. Here is a link that explains normalization in a little more detail: http://http://en.wikipedia.org/wiki/Database_normalization

Normalization is critical to any successful database application, so it is beneficial to get a good understanding of it before you go further with your form.

If you could provide more detail on what the data consists of, then we can help you get started. But from what I see, you have parts that are at certain levels which implies that you have parts that are made of other parts. You would need a parts table; here is a very simple one:

tblParts
pkPartID primary key, autonumber
txtPartNumber
txtPartName

Since a part can consist of other parts, you need a junction table to link the main part with the parts that make it up:

tblPartAssemblies
pkPartAssembliesID primary key, autonumber
fkMainPartID foreign key-->tblParts
fkSubPartID foreign key-->tblParts

I'm not sure what a fair is and how it relates to the parts. Do you actually mean like a county fair and you are constructing stuff at the fair using the parts? Details like that would help us get a better understanding of what you are trying to do with your database.

 

[jzwp22]

Regarding lcook1974's database, I cleaned it up a bit (it is attached). The syntax in the stWhere variable was incorrect that's why it did not play well when the dates were added. I also eliminated the like* which were unnecessary. I usually use a debug.print statement (i.e. debug.print stWhere) , run the code and look at the contents of the variable in the VB immediate window to see the syntax. That usually points you in the right direction to find and correct errors. In your database, I also saw that you had an input mask for the date fields at the table level. I took that out and changed it to a short date format. I would let Access store the raw data in the table without any formats. You can display it in forms/queries and reports any way you want. I also noticed that you had a field name called invoice#. It is generally better not to use spaces or special characters in your table and field names as they might cause problems later on.

 

[lcook1974]

THANKS jzwp22!!

But it wouldn't let me open the Database..."unrecognized format".

it has a file extension as "BillTracking2.accdb".

Is there a way to change the file extension? or could you send me the database again?

Larry

 

[jzwp22]

Sorry about that, it is in an Access 2007 file format. I'll have to convert it to an earlier format when I get home this evening. I don't have Access 2007 here at work.

 

[lcook1974]

THANK YOU! That would be great!

Larry

 

[jzwp22]

Here is the database in an Access 2002-2003 format.