Open form different forms depending on the result of a query 2

[rosieb]

I want to set up a button on a menu that opens one form if the result of a query is null, but opens a different form if the query returns any records.

Can someone please get me started on the If statement?

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[PHV]

if the result of a query is null
Which sort of query ? A stored one, a Recordset ?
What have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya rosieb . . . . .

Try this in the [blue]Click[/blue] event of the button (you substitute text in [purple]purple[/purple]):

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset("[purple][b]YourQueryName[/b][/purple]", dbOpenDynaset)
   
   If rst.BOF Then
      DoCmd.OpenForm "[purple][b]FormNameIfNull[/b][/purple]"
   Else
      DoCmd.OpenForm "[purple][b]FormNameIfRecords[/b][/purple]"
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

 

[jrbarnett]

How about, as an alternative to TheAceMan1's code, which doesn't open a recordset:

If DCount ("*", "QueryName") = 0 Then
  DoCmd.OpenForm "FormWithoutRecords"
Else
  DoCmd.OpenForm "FormWithRecords"
End If

Note that of course the query will have to be saved in Access, rather than a direct SQL statement.

John

 

[rosieb]

TheAceMan1
Wow!
That was quick, and perfect, exactly what I needed. Thanks a million.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[TheAceMan1]

How are ya justride . . . . .

Excellent! DCount came to me a little to late.

[blue]rosieb![/blue] If ya use any code currently posted, go with [blue]justride's[/blue] [blue]DCount [/blue]routine.

See Ya! . . . . . .

 

[rosieb]

jrbarnett
John, show off!

Thanks, very much appreciated.

TheAceMan1
I'll take your word that John's solution is better, they both look good to me. Anyway, yours made me look up BOF, so I learned something from both!!

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)