Combo box to select a field to search by

[ChrisHaynes]

I'm fairly new to Access but have been trying to create a hardware database for work.

I have one main form and then 3 separate forms for each type of hardware(Printers, Scanners, Routers). Each form contains records with relevant fields for each hardware type. For example, the printer form contains these fields:

-Department
-Printer Name
-Manufacturer
-Model
-Printer Type
-Colour Type

(This information is all linked to a table)

On the main form, I am trying to create a search tool for each hardware type. What I am trying to for each hardware type is:

-Have a combo box listing each field (Department, Printer Name, Manufacturer etc) with the label 'Printer Search By'

-and then a Text box in which you enter your search criteria called 'Printer Search For'

-and then a command button next to it called 'cmd_printergo' which will open the hardware form and look in the field chosen in the combo box and display all the records containing the criteria.

For example if I wanted to find all HP printers, I would select 'Manufacturer' in the 'Printer Search By' combo box, then type in 'HP' in the 'Printer Search For' text box.

I have tried using a macro in the command button (cmd_printergo) which opens the Printer form and then uses a filter. However I seem limited to only being able to use a particular field and can't get it to look in the 'Search by' combo box and search in only the corresponding field.


I have also tried using this code in visual basic behind the command button;

Private Sub cmd_printergo_Click()

DoCmd.OpenForm strfrm_printer, , , Me![Printer Search By combo name] & "='" & Me![Printer Search For control name] & "'"

End Sub

However this does not work and i'm getting another error as follows;

'Microsoft Access can't find the field 'Search By combo name' referred to in your expression'

Any help will be greatly appreciated!!!

 

[PHV]

[tt]DoCmd.OpenForm strfrm_printer, , , Me![Printer Search By] & "='" & Me![Printer Search For] & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChrisHaynes]

Cheers PHV but I'm getting problems with that code aswell.
I'm now getting an error message displaying;

'The action or method requires a Form Name argument'

??????

 

[ChrisHaynes]

Do you think I need to refer back to the main form when including the 'Printer Search By' and P'rinter Search For' objects?

Cheers.
Chris

 

[sdk]

Hi

The form name argument is shown as a variable. Make sure it is set, or enclose the form name in quotation marks.

Also make sure the filter value ("Me![Printer Search By]") matches exactly the control as named in the target form, otherwise more errors are likely.


Cheers

S

 

[ChrisHaynes]

Thanks for your help sdk, I have tried altering my code. However, I don't know if you fully understand what I am trying to achieve.
I am tring to link the values in the combo box to separate fields on the target form. And a text box which links to the records shown under each field. So the combo box entry will look at the records under the field matching that entry and then find the record/s which contains the text box entry.

I could select 'Department' from my combo box and enter 'ITS' in the text box. This will look at the Department field on the target form and display all the records containing 'ITS' in that field.

Would it be possible to send the database to you as it is quite difficult to explain!

Cheers.
Chris.

 

[PHV]

What is strfrm_printer ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChrisHaynes]

Its the form containg the printer database which contains the records to search through. The form contains these fields:

-Department
-Printer Name
-Manufacturer
-Model
-Printer Type
-Colour Type

The printer search i am trying to create on the main form has a combo box, in which I manually entered each field name shown above. So I am trying to link these values with the fields on frm_printer. And then the text box holds the search criteria (the user types this in)

 

[PHV]

Have you tried this ?
DoCmd.OpenForm "strfrm_printer", , , Me![Printer Search By] & "='" & Me![Printer Search For] & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChrisHaynes]

Yeah, but i get an error message saying :

The form name 'strfrm_printer' is misspelled or refers to a form that doesn't exist

The form is definately spelt 'frm_printer'?

Any ideas???

 

[PHV]

YOU have to know the names of YOUR form !

 

[ChrisHaynes]

Yeah its frm_printer. I have it sort of working now. I took away 'str' so the code is now:

DoCmd.OpenForm "frm_printer", , , Me![Printer Search By] & "='" & Me![Printer Search For] & "'"

It works briliantly for the department field but not for the others. If I select 'Printer name' in the combo box and enter 'PRT_2' in the text box i get a message saying

'Syntax error (missing operator) in query expression 'Printer Name='PRT_2'.

The same error aplies for all other fields in the combo box exept Department, which seems strange?

 

[ChrisHaynes]

It's because I have spaces in the field names, because it works with the 'Model' field aswel. I will try to include brackets.

Thanx for your help anyways.
Chris.

 

[ChrisHaynes]

I got there in the end! The code that worked with fields with spaces was:

DoCmd.OpenForm "frm_printer", , , "[" & Me![Printer Search By] & "]" & "='" & Me![Printer Search For] & "'"

Cheers 4 your help PHV.

Chris.