Nearly there...

[Nightowl4933]

I have a database where a form with user-defined search criteria and the clicking of a Command Button opens another form with the results.

What I would like to do is not open the form if there are no matching results and use a MsgBox instead.

I asked this forum for guidance on writing the search criteria here and used plog's suggestion in Post #7 - just in case that will help decide the 'strategy'.

Thanks,

Pete

 

[gemma-the-husky]

if you have a query then

if dcount("*","myquery")=0 then
msgbox "no items"
exit sub
end if

 

[Cronk]

And if there is no saved query, open a recordset based on the input criteria, and test if the recordcount is less than one.

 

[Nightowl4933]

Thanks, guys.

Would I do this from the criteria form that opens the results form?

It's not based on a query, just a series of text boxes with the values formulated in to a filter string (DoCmd.OpenForm "frmApplicationSearchResult", , , strFilter)

Thanks

 

[Cronk]

Seems obvious to me then that you would replace in your code

DoCmd.OpenForm "frmApplicationSearchResult", , , strFilter

with

dim rst as recordset
set rst = currentdb.openrecordset("Select from tblYourTable where " & strFilter)
if rst.recordcount <1 then
  msgbox "Too bad, no records to display",64
else
   DoCmd.OpenForm "frmApplicationSearchResult", , , strFilter
endif

On the other hand, if your form is not based on a single table, replace tblYourTable with the form's recordsource

 

[Nightowl4933]

Thanks, Cronk

It didn't work to start with, but then I realised you left a deliberate mistake in the SELECT statement, just to make sure I earned your help

All the best,

Pete

 

[Nightowl4933]

Oops. Although this works, it's brought up a "Data type mismatch in criteria expression" error, so I'll have to see what's caused that!

Tomorrow - it's too late now!

Good night.

 

[Nightowl4933]

OK, I think I've got to the bottom of this, but I'm not sure the best way to resolve it...

The code you provided, Cronk, works with all the fields except one - and I've written a new 'test' query using the same criteria with the table and I get the same error: "Data type mismatch in criteria expression", so I looked at the table and noticed the field relating to the affected field is Data Type 'Number, Long Integer', although the field (PlanningAuthority) actually contains text.

However, the (PlanningAuthority) field has a relationship with another table where the field (Authority) is Data Type 'Text' and (for a reason I don't understand) the second table is shown in the Relationships window twice and there is a relationship with itself with the same field - i.e. tblPlanningAuthority and tblPlanningAuthority_1 with a Join Type of 1.

My guess is I will need to look at the main table and change the data type to Text, but I will need to delete the relationships first then reinstate them. I tried this late last night, but lost myself in the process, didn't get it resolved and had to revert to a back-up!

Could someone please let me know if my theory is correct and what else I may have missed and would need to do.

Thank you.

 

[Nightowl4933]

OK, so I've looked at this a bit more and realised why it's happening (although not what to do about it!)

The field in the main table is a lookup to tblPlanningAuthority, which looks like this:

SELECT tblPlanningAuthority.ID, tblPlanningAuthority.Authority
FROM tblPlanningAuthority
ORDER BY tblPlanningAuthority.[Authority];

...and the result is the ID, not the name of the authority. Could I change this to resolve the problem?

Thanks

 

[Nightowl4933]

OK, so my solution is this: Use the [ID] data to find the records, not the [Name].

Simples!