Criteria that yeilds no records (1 Viewer)

[markdooler]

Hi Everyone.

Need some help again.

So the setup.

I have a table (Day Sheet) of jobs and each job has an owner in the form of their initials. there is a field in this table called Serviceadvisor.

I have a query (Sadaysheetqry) set to display the data and it has a criteria of [Please enter initials] set in the Serviceadvisor field.

I then have a form (Sadaysheetfrm) that loads the data and when you open it it asks you to enter the initials. Once you enter them it only shows the jobs assigned to that person.

What i am trying to achieve:

The form is a split view and in the form there are some macro buttons that link to other forms (pretty simple).

When we enter the initials, if they do not exist, it loads no data in the dataset section and the macro buttons dont appear either.

What i would like to do is return an error message box that reads "There are no jobs assigned to a Service Advisor with those initials, please re-enter", then i would like it to reload the form and thus bring up the input box again.

Any ideas?

Ta

 

[Minty]

Why not have a combobox at the top of the form, with the Service Advisers listed, and then include that in your forms query criteria?

Remove the ability to put rubbish data in, in the first place?

 

[markdooler]

Why not have a combobox at the top of the form, with the Service Advisers listed, and then include that in your forms query criteria?

Remove the ability to put rubbish data in, in the first place?

Yea i tried that but it kept going wrong.

I created a new query that just displayed the initials

I created a combo box linked to the initials and tried to get this to show as the criteria for the query but it wasnt having it.

creating the forms and combo boxes is not an issue its just linking it to the criteria that i am struggling with

 

[Minty]

In the query designer, in the criteria section start typing

Forms!

and the name of your forms should appear. once selected type

!

after the form name and the form controls should appear, select the name of your combo.
Bob should be your uncle!

 

[markdooler]

In the query designer, in the criteria section start typing

Forms!

and the name of your forms should appear. once selected type

!

after the form name and the form controls should appear, select the name of your combo.
Bob should be your uncle!

Yea this is what i did before but i still get the same results. When i open the query i get the above box not a combo box.

the form shows correctly when i open in form view and the combo box i have created works (however it does show duplicate values which i could do with removing, but thought i would tackle that when this bit works) so i know its not the form setup issue, must be something in the attached query setup?

 

[Minty]

You will get that if you open the query and the form isn't open in normal view.

You are asking the query to get a parameter from the form. If the form is closed or in design view, the query can't find the parameter, so it asks you for it.

Edit: To get your service advisors add a group by (totals icon) to your combo row source.

 

[markdooler]

You will get that if you open the query and the form isn't open in normal view.

You are asking the query to get a parameter from the form. If the form is closed or in design view, the query can't find the parameter, so it asks you for it.

Edit: To get your service advisors add a group by (totals icon) to your combo row source.

Ah that makes sense now! the penny just dropped on having the form open!

Also sorry but "Edit: To get your service advisors add a group by (totals icon) to your combo row source." how??? lol

 

[Minty]

I don't know the query you are running to populate your Combo, but if you post it up we can help.

 

[Isaac]

Ah that makes sense now! the penny just dropped on having the form open!

Also sorry but "Edit: To get your service advisors add a group by (totals icon) to your combo row source." how??? lol

Click the ellipsis button next to the RowSource property of the combobox. That will open up a traditional Query design view type of window. In that window, look for a TOTALS button near the top, right-center.