query based form - detect no records 1

[fredzeppelin]

I'm trying to reliably (of course) determine if a DoCmd.openform returns no records.
The forms are query based, some of the queries have embedded [enter parameter ?] criteria, and some are driven from form fields.

1. A method I discovered here in this forum
recommends using Dcount("field","qry").
Tried that, works fine for form parameterized criteria. Fails when the qry has a [enter parameter ?] criteria. Says "You cancelled the previous operation:run-time error 2001"

2. On my own, I experimented with testing a mandatory field (always present in a valid record) for IsNull. That seems to always work so far.

3. I know I can create a recordset and use those tools, but that seems like a lot of overhead.

Advice?

....thanks ....brad

PS, I forget the userid of the Dcount() poster, but he signs as "Captain Awesome".
Thanks to him(her?).

 

[ZmrAbdulla]

I think you have to provide some more info about your problem.

BTW,Captain Awesome is "Grande"

Zameer Abdulla
Visit Me (New Look & style)

 

[RoyVidar]

On queries where you allow the user to enter parameter, try an inputbox

[tt]strVar = Inputbox("Enter something")
if Dcount("field","qry","somefield = " & val strVar)>0 then[/tt]

- would need some more errortesting... but you can then test it prior to firing off the openform command.

Or pick up the parameter from the calling form, which is easier to validate (more recommended approach).

Opening a recordset with "select count(somefield) from sometable where..." is often less overhead than the domain aggregates, especially if you're operating on linked tables.

Roy-Vidar

 

[ZOR]

I know its naughty, a bodge, but sometimes I would fill a hidden listbox from the recordset, and determine if there were any records - Listcount. I am risking being shunned, but don't care, its a bad day.

 

[ZmrAbdulla]

If a simple Dcount() can count your records then why do you want to load a listbox and overweigh/slowdown your form?

Zameer Abdulla
Visit Me (New Look & style)

 

[fredzeppelin]

If a simple Dcount() can count your records then why do you want to load a listbox and overweigh/slowdown your form?

Because:
1. When the form is based on a query that has a user parameter entry as a criterion for a field.

Select Foo from tblBar WHERE (((tblBar.Foo) Like "*" & [Enter search target ] & "*"))

Dcount() fails, and I expect it to, because the search target is not specified. (tho' the error messages seem to vary). If it did work, wouldn't the user have to enter twice in succession? Once for the Dcount() and once for the OpenForm.

2. When the form is based on a query that has select criteria from controls on open forms, Dcount() works fine. And I expect it to.

3. The Isnull() seems erratic, results are not always repeatable.

I guess the best thing is to rewrite the one query to avoid the [parameter entry] problem. Altho' that's the the oldest, most stable part of the project.


Is there truly no simple way to
DoCmd.Openform and determine if/howmany records are returned? Seems an everyday need.

Once again, I guess I have something bass-ackward,
.....thanks

 

[TheAceMan1]

How are ya fredzeppelin . . . . .

What you need is a way for the opened form to indicate records are loaded! I suggest an hidden unbound textbox for this. The OnLoad event of the form would set the hidden textbox to [blue]True[/blue] ([blue]Null[/blue] if not loaded). In the calling form, a loop would look for [blue]True[/blue] here [purple]as a trigger[/purple] for using [blue]DCount[/blue] or any other method to return recordcount. Bear in mind this will not work for any forms opened with [blue]acDialog[/blue] as this stops code in the calling form from running until the called form is closed

[purple]So . . . . . Ya Think?[/purple]

See Ya! . . . . . .

 

[ZmrAbdulla]

If Dcount() fails then you could use RecordsetClone

Me.RecordsetClone.RecordCount [/code

Zameer Abdulla
[url=http://www.geocities.com/zameerabd/]Visit Me (New Look & style)[/url]

 

[fredzeppelin]

Perfect. Exactly what I was looking for.

(and once I knew what to look for, the doc's were helpful as well)

thanks muchly Zameer ........brad