Multiple search criteria on custom form

[uplink600]

I have a custom form set up as a parameter query dialog box. I currently have only one control (cmbNames) on this form which allows the user to select a sales person name from a combo and search for projects by sales person. I use the following in the query criteria.

[Forms]![Project Database - Project Search]![cmbNames]

This works fine but I now need multiple seach criteria which will require two
text boxes (txtAccount & txtCustName) to enable me to futher refine the search, for example to find projects by customer & salesperson. I need all three controls to work together.

Can I use.........

[Forms]![Project Database - Project Search]![txtAccount]

[Forms]![Project Database - Project Search]![txtCustName]

and expect these all to work togther on the same query.

 

[maxmangion]

yes you can, just place your other [forms]![Project Database - Project Search]![txtAccount] on the same row in your query criteria under the relevant field.

By the way just an observation, i would change the form's name to a shorter one, preferably without using space in it !

 

[uplink600]

Thanks

I have set this up but when I run a query I do not get any records returned,
even when I search just using one criteria, for example using the original combo box to select just the sales person name.

I've renamed the form and changed the query, command button macro etc but this hasn't helped.

Any ideas please.

Thanks

VC

 

[maxmangion]

is it possible that you will upload a sample of your database ?

 

[uplink600]

I can only do some sample screen shots as word docs. Will these be ok and which would you need.

VC

 

[maxmangion]

well i asked you for the mdb file so that i could see the structure of your query and form.

 

[uplink600]

I will have to make copy and then delete all the records and change names etc as some information is confidential.

Will upload ASAP.

Thanks

 

[Jon K]

Put the criteria for the sales person field in a new column in the query grid
like the following (replacing with the correct sales person field name):-

---------------------------
Field: [SalesPersonFieldName]=[Forms]![Project Database - Project Search]![cmbNames] Or [Forms]![Project Database - Project Search]![cmbNames] Is Null

Show: uncheck

Criteria: True
---------------------------


Similarly, put the criteria for the other two fields in two columns, using the correct field names and text box names.
.

 

[uplink600]

For Maxmangion...............

Zip file is too large, do you have an email address I can send to.

Cheers

VC

 

[maxmangion]

i'd pm you

 

[DLB]

Jon K,

Field: FieldName=Forms!FormName!ControlName or Forms!FormName!ControlName Is Null

Show: uncheck

Criteria: True

Wonderful, easy solution!


Quite unlike the suggestion that we frequently get of using the Criteria:-

Forms!FormName!ControlName or Forms!FormName!ControlName Is Null​

which, after the query is saved, is very difficult for us to add other criteria.

_

 

[Jon K]

DLB,

You are right. If we put Forms!FormName!ControlName or Forms!FormName!ControlName Is Null in the criteria row, Access will split the criteria into two columns and two rows when the query is saved. It will be very difficult to add further criteria.

By moving the expression from the criteria row to the field row to combine with the field name and putting True in the criteria row, Access will leave the criteria intact. So we can easily add other criteria or edit the existing ones.

However, people were usually surprised, if not confused, when I told them to move the criteria expression to the field row.
.

 

[uplink600]

AArghhh!!!!!!!!!!!!!!!!!!!!

Can't get this to work, Thanks for all your help but when I set the query up with more than one criteria to search for I get zero records found. As soon as I return to just one query all the required records are found.

I have checked all the query design and the SQL syntax looks consistent.

Is there anything esle that may be causing this

Thanks

VC

 

[DLB]

Attached was the database that I used to test Jon K's method using a structure similar to your fields and form.

 

[uplink600]

DLB

Thanks, it's just about OK no. Can you have a look at my new thread regarding an SQL query and try and help. Once thats sorted I'm finished.

Thanks again for your time.

VC

 

[RaunLGoode]

This may be a FORMS question, but this thread was the catalyst for my question. I am using Access 2000 on a Win2K box

I am making a time entry DB containing form frmTask. In an effort to shorten a long list of active employees in ComboActCont I want the user to select a group leader from ComboGrpLd. The after update event will run a query to re-populate ComboActCont with only the employees assigned to that group leader

I have the following macro associated with ComboGrpLd:

Private Sub ComboGrpLdr_AfterUpdate()
DoCmd.OpenQuery "qryActiveContractors", acNormal, acEdit
Msg = "The contractor list has been sorted by group leader- You may now select a contractor"
MsgBox Msg, vbOKOnly, "ComboGrpLdr"
End Sub

I have the following criteria in a query:

[Forms]![frmTask]![ComboGrpLdr]

When I execute this Macro I get the following error:

Run-time error ‘3211’:
The database engine could not lock table ‘tblActiveContractors’ because it is already in use by another person or process.

I get this when the only thing I have open is frmTask. If I substitute a text box for the combo box and change the criteria everything seems to work. But the users really want a combo box.

Can somebody out there help me?