cancel event code won't work

[Sasoriza28]

I may be going at this the wrong way but I have this code in the onformat event under details for a report:
If IsNull(Me![10amSnack2]) Then DoCmd.CancelEvent

I am trying to make labels for snacks but if the snack field is empty I want it to skip to the next record so that I don't waste my label paper. But this code doesn't seem to be working. Also if you could tell me how to include other fields into the code, that would be great.

 

[vbaInet]

Filter out those snacks where the Snacks field is null in by doing so in your query.

 

[Sasoriza28]

Thanks for replying. I have tried that. The problem is that I have 3 snack times 10am, 3pm and 8pm. Some people do not get any snacks and some get multiple. I have to have a separate label per snack. I end up with 3 separate queries that I do not know how to put on 1 report. So I was hoping that code would work. Do you know of a different way I can do this?

 

[vbaInet]

Show us each of your queries.

 

[Sasoriza28]

Not sure of how to best show you. I uploaded a screen shot of the 10amsnack query and the others are set up the same.

 

[vbaInet]

Go to the SQL view of the query, copy and paste it here.

 

[Sasoriza28]

SELECT [Master list].room, [Master list].PatientName, [10amsnack].[10am]
FROM 10amsnack INNER JOIN [Master list] ON [10amsnack].[Pt ID] = [Master list].ID;

 

[vbaInet]

Where are your other two queries?

 

[Sasoriza28]

Sorry they are set up the same way. I was trying this one of 2 ways. Either have 10amsnack 1-3, 3pmsnack 1-3, HS snack 1-3 on the master table and use the above code to hide the irrelevant results. Or have a separate table for 10am, 3pm and HS snacks, create a query to join the room number and name to it from the master table. I can't use the label wizard the second way and when I try to go into report design and join all of those queries it does not work because they are based on the same table.

SELECT [Master list].room, [Master list].PatientName, [3pm snack].[3pm snack]
FROM [Master list] INNER JOIN [3pm snack] ON [Master list].ID = [3pm snack].PTID;

SELECT [Master list].room, [Master list].PatientName, HSsnack.[HS snack]
FROM [Master list] INNER JOIN HSsnack ON [Master list].ID = HSsnack.PTID;

 

[vbaInet]

Can you show us your 10amsnack, 3pmsnack, HSSnack and Master list queries. Or are all these tables?