Opening forms subject to criteria

[lorraine]

The answer to this is probably staring me in the face!
I have a main form which opens a subform through a cmd button. I would like the subform to open in datasheet view if it has any data and in normal view if there is no data. I've tried numerous differnet ways but A2K keeps chucking them out!!
Thanks in advance
Lorraine

 

[ntp]

One way you can do this is to create a recordset based o n the table or query your subform is based on.

For example if your sub form is based on table "tblSubFormData" then you would create a record set like this:

Dim dbs as database
Dim rst as recordset
Dim intFormView as Integer

1. Set dbs = CurrentDb
2. Set rst = dbs.TableDefs("tblSubFormData").openrecorset
3. if rst.recordCount = 0 then
4. intFormView = acNornal
5. else
6. intFormView = acFormDS
7. end if
docmd.openform "frmSubForm", intFormView

If the sub form were based on a query you would replace line 2 in the code above with:

set rst = dbs.OpenQueryDef("qrySubFormData").Openrecord

This would go in your command button onClick event.

ntp

 

[lorraine]

Thanks for the advice it worked!!)