MESSAGE BOX PROBLEM 1

[desikan]

Hi,
I have a cross tab query "Table1" which is started by a macro which is followed by a message box. I am trying to put a condition in the message box in the macro which will pop up when one field in the query for example is null.

But I find that this conditional statement works only if I open a form instead of the query itself like [Forms]![form1]![card] Is Null

Grateful for any help

Thanks.

 

[jpalmer150]

The Form is probably looking at one record at a time. However the cross tab includes all records. If the field is numeric, you could base your condition on the sum of the field.

Example: Sum([QUERY]![QUERY1]![card]] = 0

 

[desikan]

Well, thanks for your reply.
I tried your solution but it does not work.

What I really want is that if the cross tab query does not return any records, then a message box should pop up. To achieve this,I am trying to consider a text field say "card" and have the msg box pop up if this field is null.

If someone can suggest some other way, then also it is fine.

Also I am not able to dynamically update a form/report with a cross tab query so far and hence I am forced to use the query itself for any conditional statements.

Any help to create a form for the cross tab query is also welcome.

 

[MakeItSo]

Try using some vb code instead of the query and the macro:

Dim rs as Recordset, sSQL as String
sSQL= " paste the sql statement of your query here"

Set rs=currentdb.openrecordset(ssql)
lockedits=false
if isnull(rs![your desired null field]) then
  msgbox "Your NULL message",vbokonly
endif

If you initiate the query+macro from a command button, then paste the above code in the Click() event of the button..

Hope this helps,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MakeItSo]

oops - forgot something:

add a rs.Close before the End Sub of your event.

 

[desikan]

I tried your solution and it worked just the way I wanted.
Thanks a lot for your kind help.