I need a search form

[Pondpuppy123 via AccessMonster.com]

I have been following the various newsgroups and am hoping someone can help
me. I am fairly new to Access and am using 2007. I need a search form. I
looked at Allenbrowne.com and think this is what I need, but I just don't
get it. I have looked at various search posts and again, I just don't get it.
I just don't understand the concept and as I stated, I am new to access. I
have created a small database for my work. I have my tables, queries, forms
and reports all set up fine. The last thing I really need to do is set up a
search form. I want to have a form with a search box where the user can
search on several different criteria. The result should be a record that
brings up all pertainent info for the record. If someone can give me the code
and instructions on where to put the code, I would be most thankful. The
query which the form will be based on is F_PartsInventory. Here are the
search fields:

nomenclature, partnumber, serial number, and remarks

The form fields that should appear when the search is executed are:
Nomenclature, partnumber, qty, location, type, serial number and remarks.
When a nomenclature is entered, it may bring up as many as twenty different
items. Then the user will need to scroll to the correct nomenclature they
are looking for. So I would like the results to display in a continues form.
I would greatly appricate any and all help. Please be patient with me as I
am still learning Access. Thank you so much.

 

[Ken Sheridan]

I'd suggest using a form based on a query which references the search
controls as parameters. The steps are as follows:

1. Create a form and show the form header and footer via the View menu in
form design view. In the header add four unbound controls for the search
fields, leaving their ControlSource properties blank. For nomenclature,
partnumber and serial number you could use text boxes, but I'd suggest combo
boxes which list the available values, so for the nomenclature combo box the
RowSource property would be like this:

SELECT DISTINCT nomenclature FROM YourTable ORDER BY nomenclature;

substituting the actual table name of course.

Name the combo box cboNomenclature for instance.

2. Do the same for partnumber and serial number, adjusting the RowSources
appropriately and naming the controls cboPartNumber and cboSerialNumber..

3. For remarks, which sounds like a free text or memo field, you'll
probably want to search on any word or phrase within the field, so use a text
box in which the word or phrase can be typed. Name it txtRemarks say.

4. Add a command button to the header, captioned something like 'Go' or
'Search' and in its Click event procedure put the following code:

Me.Requery

NB. This goes in the event procedure, not directly in the properties sheet.
To do this select the On Click event in the properties sheet for the
control; click on the 'build' button (the one to the right with three dots);
select Code Builder in the dialogue and click OK; in the VBA window enter the
code as a new line between the two lines already in place.

5. Add a 'Show All' button to the header with this in its Click event
procedure:

Me.cboNomenclature = Null
Me.cboPartNumber = Null
Me.cboSerialNumber = Null
Me.txtRemarks = Null
Me.Requery

6. Save the form as frmPartsInventory_Search say. You'll do more to it
later as described below; it does nothing as yet.

7. Open your existing query in design view and in the File menu select
'Save as' and save a copy as qryPartsInventory_Search say.

8. In the new qryPartsInventory_Search copy of the query in design view put
the following in the 'criteria' rows of the four relevant columns:

[Forms]![frmPartsInventory_Search]![cboNomencalure] Or
[Forms]![frmPartsInventory_Search]![cbotNomencalure] Is Null

[Forms]![frmPartsInventory_Search]![cboPartNumber] Or
[Forms]![frmPartsInventory_Search]![cboPartNumber] Is Null

[Forms]![frmPartsInventory_Search]![cboSerialNumber] Or
[Forms]![frmPartsInventory_Search]![cboSerialNumber] Is Null

Like "*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *" Or
[Forms]![frmPartsInventory_Search]![txtRemarks] Is Null

Note that each of the above four parameters should be a single line in the
query design grid; they'll probably have been split over more than one line
each in your newsreader.

9. Save the query. If you reopen it in design view you'll find that Access
has moved things around quite a lot. Don't worry; it will work just the same.

10. Open the frmPartsInventory_Search in design view again and set its
RecordSource property to the name of the query, qryPartsInventory_Search.

11. Now add the Nomenclature, partnumber, qty, location, type, serial
number and remarks fields to the form's detail section from the Field List.
You'll find that it will add a label for each as well so cut each label to
the clipboard and paste them into the header, arranging them in a line below
the search controls so they form column headings for the fields below. You
can change their Caption properties if you wish.

12. Arrange the controls bound to the fields in a line across the top of
the detail section and line them and their labels up correctly.

13. Drag the bottom edge of the detail section up so its just deep enough
to accommodate the line of bound controls. Unless you are putting anything
in the footer drag the bottom of that up too so its zero height. Leave the
form window at the depth you want the form to be when opened.

14. Set the Default View property of the form to 'Continuous Forms' in its
properties sheet.

15. Save the form and open it in form view. It should show all records when
opened, but when you select/enter values in any one or more of the search
controls in the header and click the Search button it should be filtered to
just the matching records. Clicking the Show All button should empty the
search controls and show all records again.


I should point out that I'm not using Access 2007, so I'm assuming things
are much the same as in earlier versions as far as what I've described above
is concerned.

Good luck, but please post back if you need more help.

Ken Sheridan
Stafford, England

 

[Pondpuppy123 via AccessMonster.com]

Ken, Thank you so much for your help. I followed your instructions step by
step and I cannot get it to work. I erased everything and redid it three
times. Still no luck. When I open the form in the last step, no items show
up at all. I copy and pasted all the code just to make sure I didnot make a
typo. I did notice that in step 1, "Select Distinct nomenclature FROM Your
Table ORDER BY nomencluatue", this statement is not even close to what came
up in the Row Source by default. I tried the default and the form did not
work, then I changed each row souce to match the statement you wrote and it
didn't work. I noticed that your statment said "Your Table". I used the
table that the qurey is based on and the query the qryPartsInventory_Search
and it still didn't work.

When I type in Noumenclature, part number and serial numberthe error says,
"Not item in list. When I type a value in the remarks, nothing happens at
all. Got any ideas as to what is wrong?

thanks you for your patience and detailed explaination. It helps me alot to
learn.

I'd suggest using a form based on a query which references the search
controls as parameters. The steps are as follows:

1. Create a form and show the form header and footer via the View menu in
form design view. In the header add four unbound controls for the search
fields, leaving their ControlSource properties blank. For nomenclature,
partnumber and serial number you could use text boxes, but I'd suggest combo
boxes which list the available values, so for the nomenclature combo box the
RowSource property would be like this:

SELECT DISTINCT nomenclature FROM YourTable ORDER BY nomenclature;

substituting the actual table name of course.

Name the combo box cboNomenclature for instance.

2. Do the same for partnumber and serial number, adjusting the RowSources
appropriately and naming the controls cboPartNumber and cboSerialNumber..

3. For remarks, which sounds like a free text or memo field, you'll
probably want to search on any word or phrase within the field, so use a text
box in which the word or phrase can be typed. Name it txtRemarks say.

4. Add a command button to the header, captioned something like 'Go' or
'Search' and in its Click event procedure put the following code:

Me.Requery

NB. This goes in the event procedure, not directly in the properties sheet.
To do this select the On Click event in the properties sheet for the
control; click on the 'build' button (the one to the right with three dots);
select Code Builder in the dialogue and click OK; in the VBA window enter the
code as a new line between the two lines already in place.

5. Add a 'Show All' button to the header with this in its Click event
procedure:

Me.cboNomenclature = Null
Me.cboPartNumber = Null
Me.cboSerialNumber = Null
Me.txtRemarks = Null
Me.Requery

6. Save the form as frmPartsInventory_Search say. You'll do more to it
later as described below; it does nothing as yet.

7. Open your existing query in design view and in the File menu select
'Save as' and save a copy as qryPartsInventory_Search say.

8. In the new qryPartsInventory_Search copy of the query in design view put
the following in the 'criteria' rows of the four relevant columns:

[Forms]![frmPartsInventory_Search]![cboNomencalure] Or
[Forms]![frmPartsInventory_Search]![cbotNomencalure] Is Null

[Forms]![frmPartsInventory_Search]![cboPartNumber] Or
[Forms]![frmPartsInventory_Search]![cboPartNumber] Is Null

[Forms]![frmPartsInventory_Search]![cboSerialNumber] Or
[Forms]![frmPartsInventory_Search]![cboSerialNumber] Is Null

Like "*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *" Or
[Forms]![frmPartsInventory_Search]![txtRemarks] Is Null

Note that each of the above four parameters should be a single line in the
query design grid; they'll probably have been split over more than one line
each in your newsreader.

9. Save the query. If you reopen it in design view you'll find that Access
has moved things around quite a lot. Don't worry; it will work just the same.

10. Open the frmPartsInventory_Search in design view again and set its
RecordSource property to the name of the query, qryPartsInventory_Search.

11. Now add the Nomenclature, partnumber, qty, location, type, serial
number and remarks fields to the form's detail section from the Field List.
You'll find that it will add a label for each as well so cut each label to
the clipboard and paste them into the header, arranging them in a line below
the search controls so they form column headings for the fields below. You
can change their Caption properties if you wish.

12. Arrange the controls bound to the fields in a line across the top of
the detail section and line them and their labels up correctly.

13. Drag the bottom edge of the detail section up so its just deep enough
to accommodate the line of bound controls. Unless you are putting anything
in the footer drag the bottom of that up too so its zero height. Leave the
form window at the depth you want the form to be when opened.

14. Set the Default View property of the form to 'Continuous Forms' in its
properties sheet.

15. Save the form and open it in form view. It should show all records when
opened, but when you select/enter values in any one or more of the search
controls in the header and click the Search button it should be filtered to
just the matching records. Clicking the Show All button should empty the
search controls and show all records again.

I should point out that I'm not using Access 2007, so I'm assuming things
are much the same as in earlier versions as far as what I've described above
is concerned.

Good luck, but please post back if you need more help.

Ken Sheridan
Stafford, England

I have been following the various newsgroups and am hoping someone can help
me. I am fairly new to Access and am using 2007. I need a search form. I

[quoted text clipped - 19 lines]

I would greatly appricate any and all help. Please be patient with me as I
am still learning Access. Thank you so much.

 

[Ken Sheridan]

Does the table on which your query is based on include columns (fields) named
nomenclature, partnumber and serial number and remarks? If so the RowSource
properties for the combo boxes and the parameters for the
qryPartsInventory_Search query which I gave you should be OK. When you say
"what came up in the Row Source by default" I assume you are referring to the
drop down list you get if you click on the arrow at the right end of the
property in the combo box's properties sheet. Those are just a list of
tables and queries in the database. You can use a table or query as the
RowSource, but you can also use an SQL SELECT statement as I gave you; this
is itself a query in SQL form.

Its hard to pin down where you are going wrong at this distance, but if you
can copy and paste the SQL of your original query into a reply here that
might help the dog see the rabbit. To do this open your query in design view
and then from the View menu select SQL View. You can then copy the SQL to
the clipboard and paste it in your reply. I'm sure with a bit more head
scratching we can get you up and running.

Ken Sheridan
Stafford, England

Ken, Thank you so much for your help. I followed your instructions step by
step and I cannot get it to work. I erased everything and redid it three
times. Still no luck. When I open the form in the last step, no items show
up at all. I copy and pasted all the code just to make sure I didnot make a
typo. I did notice that in step 1, "Select Distinct nomenclature FROM Your
Table ORDER BY nomencluatue", this statement is not even close to what came
up in the Row Source by default. I tried the default and the form did not
work, then I changed each row souce to match the statement you wrote and it
didn't work. I noticed that your statment said "Your Table". I used the
table that the qurey is based on and the query the qryPartsInventory_Search
and it still didn't work.

When I type in Noumenclature, part number and serial numberthe error says,
"Not item in list. When I type a value in the remarks, nothing happens at
all. Got any ideas as to what is wrong?

thanks you for your patience and detailed explaination. It helps me alot to
learn.

I'd suggest using a form based on a query which references the search
controls as parameters. The steps are as follows:

1. Create a form and show the form header and footer via the View menu in
form design view. In the header add four unbound controls for the search
fields, leaving their ControlSource properties blank. For nomenclature,
partnumber and serial number you could use text boxes, but I'd suggest combo
boxes which list the available values, so for the nomenclature combo box the
RowSource property would be like this:

SELECT DISTINCT nomenclature FROM YourTable ORDER BY nomenclature;

substituting the actual table name of course.

Name the combo box cboNomenclature for instance.

2. Do the same for partnumber and serial number, adjusting the RowSources
appropriately and naming the controls cboPartNumber and cboSerialNumber..

3. For remarks, which sounds like a free text or memo field, you'll
probably want to search on any word or phrase within the field, so use a text
box in which the word or phrase can be typed. Name it txtRemarks say.

4. Add a command button to the header, captioned something like 'Go' or
'Search' and in its Click event procedure put the following code:

Me.Requery

NB. This goes in the event procedure, not directly in the properties sheet.
To do this select the On Click event in the properties sheet for the
control; click on the 'build' button (the one to the right with three dots);
select Code Builder in the dialogue and click OK; in the VBA window enter the
code as a new line between the two lines already in place.

5. Add a 'Show All' button to the header with this in its Click event
procedure:

Me.cboNomenclature = Null
Me.cboPartNumber = Null
Me.cboSerialNumber = Null
Me.txtRemarks = Null
Me.Requery

6. Save the form as frmPartsInventory_Search say. You'll do more to it
later as described below; it does nothing as yet.

7. Open your existing query in design view and in the File menu select
'Save as' and save a copy as qryPartsInventory_Search say.

8. In the new qryPartsInventory_Search copy of the query in design view put
the following in the 'criteria' rows of the four relevant columns:

[Forms]![frmPartsInventory_Search]![cboNomencalure] Or
[Forms]![frmPartsInventory_Search]![cbotNomencalure] Is Null

[Forms]![frmPartsInventory_Search]![cboPartNumber] Or
[Forms]![frmPartsInventory_Search]![cboPartNumber] Is Null

[Forms]![frmPartsInventory_Search]![cboSerialNumber] Or
[Forms]![frmPartsInventory_Search]![cboSerialNumber] Is Null

Like "*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *" Or
[Forms]![frmPartsInventory_Search]![txtRemarks] Is Null

Note that each of the above four parameters should be a single line in the
query design grid; they'll probably have been split over more than one line
each in your newsreader.

9. Save the query. If you reopen it in design view you'll find that Access
has moved things around quite a lot. Don't worry; it will work just the same.

10. Open the frmPartsInventory_Search in design view again and set its
RecordSource property to the name of the query, qryPartsInventory_Search.

11. Now add the Nomenclature, partnumber, qty, location, type, serial
number and remarks fields to the form's detail section from the Field List.
You'll find that it will add a label for each as well so cut each label to
the clipboard and paste them into the header, arranging them in a line below
the search controls so they form column headings for the fields below. You
can change their Caption properties if you wish.

12. Arrange the controls bound to the fields in a line across the top of
the detail section and line them and their labels up correctly.

13. Drag the bottom edge of the detail section up so its just deep enough
to accommodate the line of bound controls. Unless you are putting anything
in the footer drag the bottom of that up too so its zero height. Leave the
form window at the depth you want the form to be when opened.

14. Set the Default View property of the form to 'Continuous Forms' in its
properties sheet.

15. Save the form and open it in form view. It should show all records when
opened, but when you select/enter values in any one or more of the search
controls in the header and click the Search button it should be filtered to
just the matching records. Clicking the Show All button should empty the
search controls and show all records again.

I should point out that I'm not using Access 2007, so I'm assuming things
are much the same as in earlier versions as far as what I've described above
is concerned.

Good luck, but please post back if you need more help.

Ken Sheridan
Stafford, England

I have been following the various newsgroups and am hoping someone can help
me. I am fairly new to Access and am using 2007. I need a search form. I

[quoted text clipped - 19 lines]

I would greatly appricate any and all help. Please be patient with me as I
am still learning Access. Thank you so much.

 

[Pondpuppy123 via AccessMonster.com]

Thanks again for your help. I went back through the table, form, query, etc
and took another look at it. I thought that maybe I might be having some
interference with some other stuff I have been working on so, I then created
a new blank database. I imported the T_TwinParts and only that table. Then I
followed your instructions step by step again. When I put the combo box on
the form, I canceled the wizard and then inputted the info from your notes.
Before, I used the combo wizard... Anyway, when I finished the instructions
here is what I got.

When I enter an item in the nomenclature box and hit search, nothing happens;
When I put a part number in the PartNumber combo box and hit search, the form
filters to just that item like it should. When I put an entry in the Remarks
section, it works sometimes. But not all the time. Here is a copy of the
SQLfor the query :

SELECT T_TwinParts.ID, T_TwinParts.Nomenclature, T_TwinParts.PartNumber,
T_TwinParts.Qty, T_TwinParts.Location, T_TwinParts.Remarks, T_TwinParts.[Acft
Type], T_TwinParts.[Required O/H], T_TwinParts.[Serial Number]
FROM T_TwinParts
WHERE (((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.Remarks) Like
"*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *") AND (
(T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber])) OR (((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.Remarks) Like
"*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *") AND (
(T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) AND (([Forms]![frmPartsInventory_Search]![cbotNomencalure])
Is Null)) OR (((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.Remarks) Like "*" & [Forms]!
[frmPartsInventory_Search]![txtRemarks] & " *") AND ((T_TwinParts.[Serial
Number])=[Forms]![frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null)) OR (((T_TwinParts.
Remarks) Like "*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *")
AND ((T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) AND (([Forms]![frmPartsInventory_Search]![cbotNomencalure])
Is Null) AND (([Forms]![frmPartsInventory_Search]![cboPartNumber]) Is Null))
OR (((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.[Serial Number])
=[Forms]![frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null)) OR (((T_TwinParts.
PartNumber)=[Forms]![frmPartsInventory_Search]![cboPartNumber]) AND (
(T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) AND (([Forms]![frmPartsInventory_Search]![cbotNomencalure])
Is Null) AND (([Forms]![frmPartsInventory_Search]![txtRemarks]) Is Null)) OR
(((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.[Serial Number])=[Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null)) OR (((T_TwinParts.[Serial
Number])=[Forms]![frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null)) OR (((T_TwinParts.
Nomenclature)=[Forms]![frmPartsInventory_Search]![cboNomencalure]) AND (
(T_TwinParts.PartNumber)=[Forms]![frmPartsInventory_Search]![cboPartNumber])
AND ((T_TwinParts.Remarks) Like "*" & [Forms]![frmPartsInventory_Search]!
[txtRemarks] & " *") AND (([Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) Is Null)) OR (((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.Remarks) Like
"*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *") AND (([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR (((T_TwinParts.
Nomenclature)=[Forms]![frmPartsInventory_Search]![cboNomencalure]) AND (
(T_TwinParts.Remarks) Like "*" & [Forms]![frmPartsInventory_Search]!
[txtRemarks] & " *") AND (([Forms]![frmPartsInventory_Search]![cboPartNumber])
Is Null) AND (([Forms]![frmPartsInventory_Search]![cboSerialNumber]) Is Null))
OR (((T_TwinParts.Remarks) Like "*" & [Forms]![frmPartsInventory_Search]!
[txtRemarks] & " *") AND (([Forms]![frmPartsInventory_Search]!
[cbotNomencalure]) Is Null) AND (([Forms]![frmPartsInventory_Search]!
[cboPartNumber]) Is Null) AND (([Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) Is Null)) OR (((T_TwinParts.Nomenclature)=[Forms]!
[frmPartsInventory_Search]![cboNomencalure]) AND ((T_TwinParts.PartNumber)=
[Forms]![frmPartsInventory_Search]![cboPartNumber]) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR (((T_TwinParts.
PartNumber)=[Forms]![frmPartsInventory_Search]![cboPartNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR (((T_TwinParts.
Nomenclature)=[Forms]![frmPartsInventory_Search]![cboNomencalure]) AND ((
[Forms]![frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR ((([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null));

I also wanted to add this info: Ie
Nomenclature PartNumber Remarks
Packing MS28775-011 Packing for
Fuel bowl

Packing MS29513-04 Tach Generator
Packing

Packing NAS 1611-01

As an example, when the user puts in the word packing, I would like all
packings to be filtered. The user may not have the partnumber to input into
the search. Also, I would like the user to be able to do the text search in
the remarks without having the partnumber or nomenclature. In this case, if
I put in just the word packing in the remarks, I would like it to show all
results for "Packing". Then the user can determine the correct item based
off the remarks section. In this example, the fuel bowl packing and tach
generator packing would show up in the filter. The third packing would not
show up because there is no entry in the remarks section for the third
packing. The user would then determine which packing he needs. I need the
tach generator packing because I am replacing the tach generator. Does that
make sense and can it be done. Thanks again for your help and patience.

Does the table on which your query is based on include columns (fields) named
nomenclature, partnumber and serial number and remarks? If so the RowSource
properties for the combo boxes and the parameters for the
qryPartsInventory_Search query which I gave you should be OK. When you say
"what came up in the Row Source by default" I assume you are referring to the
drop down list you get if you click on the arrow at the right end of the
property in the combo box's properties sheet. Those are just a list of
tables and queries in the database. You can use a table or query as the
RowSource, but you can also use an SQL SELECT statement as I gave you; this
is itself a query in SQL form.

Its hard to pin down where you are going wrong at this distance, but if you
can copy and paste the SQL of your original query into a reply here that
might help the dog see the rabbit. To do this open your query in design view
and then from the View menu select SQL View. You can then copy the SQL to
the clipboard and paste it in your reply. I'm sure with a bit more head
scratching we can get you up and running.

Ken Sheridan
Stafford, England

Ken, Thank you so much for your help. I followed your instructions step by
step and I cannot get it to work. I erased everything and redid it three

[quoted text clipped - 127 lines]

 

[Ken Sheridan]

One thing which won't help; you've inherited a typo from me! I should warn
you, I'm the world's worst typist. In the query you've got cboNomencalure,
not cboNomenclature. My apologies for that.

The query can actually be simplified a lot. The mess you see at present is
a result of the way Access translates what you enter in design view. It
works the same, but its difficult to read. After correcting my typo, and
simplifying the SQL we get:

SELECT ID, Nomenclature, PartNumber,
Qty, Location, Remarks, [Acft Type],
[Required O/H], [Serial Number]
FROM T_TwinParts
WHERE (Nomenclature=Forms!frmPartsInventory_Search!cboNomenclature
OR Forms!frmPartsInventory_Search!cboNomenclature IS NULL)
AND (PartNumber=Forms!frmPartsInventory_Search!cboPartNumber
OR Forms!frmPartsInventory_Search!cboPartNumber IS NULL)
AND (Remarks LIKE "*" & Forms!frmPartsInventory_Search!txtRemarks & " *"
OR Forms!frmPartsInventory_Search!txtRemarks IS NULL);

You can copy and paste the above straight into the query in SQL view in
place of the existing, but make sure you save it in SQL view not design view
or Access will churn it all up again. You'll find that even saving it in SQL
view it won't stay quite the same next time you open it, but the differences
will be minor.

It should work now. I've recreated your T_TwinParts from the information
in the query (though I may have got the data types wrong, but that's
irrelevant to this problem) table and also the frmPartsInventory_Search form
in a simplified format, and it does work, so if you still have problems you
can always mail me at kenwsheridan<at>yahoo<dot>co<dot>uk and I'll send it to
you.

It finds records where values match all the values you select or enter in
the search controls, so if you select nomenclature Packing and enter Fuel
bowl in remarks it will find the first row in you example, not the second and
third as only the nomenclature matches your search criteria. If you select
Packing as nomenclature and leave both the part number and remarks search
controls blank it would find all three. From what you say that's my
understanding of how you want it to work.

Good luck,

Ken Sheridan
Stafford, England

Thanks again for your help. I went back through the table, form, query, etc
and took another look at it. I thought that maybe I might be having some
interference with some other stuff I have been working on so, I then created
a new blank database. I imported the T_TwinParts and only that table. Then I
followed your instructions step by step again. When I put the combo box on
the form, I canceled the wizard and then inputted the info from your notes.
Before, I used the combo wizard... Anyway, when I finished the instructions
here is what I got.

When I enter an item in the nomenclature box and hit search, nothing happens;
When I put a part number in the PartNumber combo box and hit search, the form
filters to just that item like it should. When I put an entry in the Remarks
section, it works sometimes. But not all the time. Here is a copy of the
SQLfor the query :

SELECT T_TwinParts.ID, T_TwinParts.Nomenclature, T_TwinParts.PartNumber,
T_TwinParts.Qty, T_TwinParts.Location, T_TwinParts.Remarks, T_TwinParts.[Acft
Type], T_TwinParts.[Required O/H], T_TwinParts.[Serial Number]
FROM T_TwinParts
WHERE (((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.Remarks) Like
"*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *") AND (
(T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber])) OR (((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.Remarks) Like
"*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *") AND (
(T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) AND (([Forms]![frmPartsInventory_Search]![cbotNomencalure])
Is Null)) OR (((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.Remarks) Like "*" & [Forms]!
[frmPartsInventory_Search]![txtRemarks] & " *") AND ((T_TwinParts.[Serial
Number])=[Forms]![frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null)) OR (((T_TwinParts.
Remarks) Like "*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *")
AND ((T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) AND (([Forms]![frmPartsInventory_Search]![cbotNomencalure])
Is Null) AND (([Forms]![frmPartsInventory_Search]![cboPartNumber]) Is Null))
OR (((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.[Serial Number])
=[Forms]![frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null)) OR (((T_TwinParts.
PartNumber)=[Forms]![frmPartsInventory_Search]![cboPartNumber]) AND (
(T_TwinParts.[Serial Number])=[Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) AND (([Forms]![frmPartsInventory_Search]![cbotNomencalure])
Is Null) AND (([Forms]![frmPartsInventory_Search]![txtRemarks]) Is Null)) OR
(((T_TwinParts.Nomenclature)=[Forms]![frmPartsInventory_Search]!
[cboNomencalure]) AND ((T_TwinParts.[Serial Number])=[Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null)) OR (((T_TwinParts.[Serial
Number])=[Forms]![frmPartsInventory_Search]![cboSerialNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null)) OR (((T_TwinParts.
Nomenclature)=[Forms]![frmPartsInventory_Search]![cboNomencalure]) AND (
(T_TwinParts.PartNumber)=[Forms]![frmPartsInventory_Search]![cboPartNumber])
AND ((T_TwinParts.Remarks) Like "*" & [Forms]![frmPartsInventory_Search]!
[txtRemarks] & " *") AND (([Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) Is Null)) OR (((T_TwinParts.PartNumber)=[Forms]!
[frmPartsInventory_Search]![cboPartNumber]) AND ((T_TwinParts.Remarks) Like
"*" & [Forms]![frmPartsInventory_Search]![txtRemarks] & " *") AND (([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR (((T_TwinParts.
Nomenclature)=[Forms]![frmPartsInventory_Search]![cboNomencalure]) AND (
(T_TwinParts.Remarks) Like "*" & [Forms]![frmPartsInventory_Search]!
[txtRemarks] & " *") AND (([Forms]![frmPartsInventory_Search]![cboPartNumber])
Is Null) AND (([Forms]![frmPartsInventory_Search]![cboSerialNumber]) Is Null))
OR (((T_TwinParts.Remarks) Like "*" & [Forms]![frmPartsInventory_Search]!
[txtRemarks] & " *") AND (([Forms]![frmPartsInventory_Search]!
[cbotNomencalure]) Is Null) AND (([Forms]![frmPartsInventory_Search]!
[cboPartNumber]) Is Null) AND (([Forms]![frmPartsInventory_Search]!
[cboSerialNumber]) Is Null)) OR (((T_TwinParts.Nomenclature)=[Forms]!
[frmPartsInventory_Search]![cboNomencalure]) AND ((T_TwinParts.PartNumber)=
[Forms]![frmPartsInventory_Search]![cboPartNumber]) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR (((T_TwinParts.
PartNumber)=[Forms]![frmPartsInventory_Search]![cboPartNumber]) AND (([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR (((T_TwinParts.
Nomenclature)=[Forms]![frmPartsInventory_Search]![cboNomencalure]) AND ((
[Forms]![frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null)) OR ((([Forms]!
[frmPartsInventory_Search]![cbotNomencalure]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboPartNumber]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![txtRemarks]) Is Null) AND (([Forms]!
[frmPartsInventory_Search]![cboSerialNumber]) Is Null));

I also wanted to add this info: Ie
Nomenclature PartNumber Remarks
Packing MS28775-011 Packing for
Fuel bowl

Packing MS29513-04 Tach Generator
Packing

Packing NAS 1611-01

As an example, when the user puts in the word packing, I would like all
packings to be filtered. The user may not have the partnumber to input into
the search. Also, I would like the user to be able to do the text search in
the remarks without having the partnumber or nomenclature. In this case, if
I put in just the word packing in the remarks, I would like it to show all
results for "Packing". Then the user can determine the correct item based
off the remarks section. In this example, the fuel bowl packing and tach
generator packing would show up in the filter. The third packing would not
show up because there is no entry in the remarks section for the third
packing. The user would then determine which packing he needs. I need the
tach generator packing because I am replacing the tach generator. Does that
make sense and can it be done. Thanks again for your help and patience.

Does the table on which your query is based on include columns (fields) named
nomenclature, partnumber and serial number and remarks? If so the RowSource
properties for the combo boxes and the parameters for the
qryPartsInventory_Search query which I gave you should be OK. When you say
"what came up in the Row Source by default" I assume you are referring to the
drop down list you get if you click on the arrow at the right end of the
property in the combo box's properties sheet. Those are just a list of
tables and queries in the database. You can use a table or query as the
RowSource, but you can also use an SQL SELECT statement as I gave you; this
is itself a query in SQL form.

Its hard to pin down where you are going wrong at this distance, but if you
can copy and paste the SQL of your original query into a reply here that
might help the dog see the rabbit. To do this open your query in design view
and then from the View menu select SQL View. You can then copy the SQL to
the clipboard and paste it in your reply. I'm sure with a bit more head
scratching we can get you up and running.

Ken Sheridan
Stafford, England

Ken, Thank you so much for your help. I followed your instructions step by
step and I cannot get it to work. I erased everything and redid it three

[quoted text clipped - 127 lines]

I would greatly appricate any and all help. Please be patient with me as I
am still learning Access. Thank you so much.