Initiate an action only if query returns data

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

I have the make-table query below, which runs as part of the OnClick event
of a button. Sometimes the query will return no records (i.e. no records are
added to the table [hours per week]). When the query does return 1 or more
records I need to initiate another action (display a messagebox and run two
further queries). How do I do that?

Hope someone can help.
Many thanks
Leslie Isaacs

The query:
SELECT staffs.practice, staffs.person, staffs.[nml hourweek going],
Sum(stafpay.number) AS SumOfnumber, stafpay.normal, stafpay.[month name]
INTO [hours per week]
FROM staffs INNER JOIN stafpay ON staffs.person = stafpay.person
WHERE (((stafpay.paytyp)="hrs/wk" Or (stafpay.paytyp)="ex hrs/wk"))
GROUP BY staffs.practice, staffs.person, staffs.[nml hourweek going],
stafpay.normal, stafpay.[month name]
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]) AND
((Sum(stafpay.number))<>[nml hourweek going]) AND ((stafpay.normal)=True)
AND ((stafpay.[month name])=[Forms]![frm x main]![month name]));
 
Use DLookup() on one of the fields from the query that cannot be null. If it
is null, no records were returned.

This kind of thing:

If IsNull(DLookup(("practice", "Query1")) Then
'do something
Else
'do something
End If
 
Allen

That's great - works a treat!

Many thanks
Les

Allen Browne said:
Use DLookup() on one of the fields from the query that cannot be null. If it
is null, no records were returned.

This kind of thing:

If IsNull(DLookup(("practice", "Query1")) Then
'do something
Else
'do something
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

PayeDoc said:
Hello All

I have the make-table query below, which runs as part of the OnClick event
of a button. Sometimes the query will return no records (i.e. no records
are
added to the table [hours per week]). When the query does return 1 or more
records I need to initiate another action (display a messagebox and run
two
further queries). How do I do that?

Hope someone can help.
Many thanks
Leslie Isaacs

The query:
SELECT staffs.practice, staffs.person, staffs.[nml hourweek going],
Sum(stafpay.number) AS SumOfnumber, stafpay.normal, stafpay.[month name]
INTO [hours per week]
FROM staffs INNER JOIN stafpay ON staffs.person = stafpay.person
WHERE (((stafpay.paytyp)="hrs/wk" Or (stafpay.paytyp)="ex hrs/wk"))
GROUP BY staffs.practice, staffs.person, staffs.[nml hourweek going],
stafpay.normal, stafpay.[month name]
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]) AND
((Sum(stafpay.number))<>[nml hourweek going]) AND ((stafpay.normal)=True)
AND ((stafpay.[month name])=[Forms]![frm x main]![month name]));
Click to expand...
Click to expand...
 
Back
Top