Good morning All,

I have a DB that is production info for every ProdDate and ShiftID. I want to ensure that no other data has been entered for that ProdDate and ShiftID. Now, I have two day shifts (ShiftID 1 and 2) and two night shifts (ShiftID 3 and 4). I was trying to ensure that no other data for either day or night shift has been entered. I thought about using a Count Qry and using a vb IF statement. Just wondering if there is a better way of doing it. Here is my SQL:

SELECT tblDailyProduction.ProdDate, tblDailyProduction.ShiftID, Count(tblDailyProduction.ProductionID) AS CountOfProductionID
FROM tblDailyProduction
GROUP BY tblDailyProduction.ProdDate, tblDailyProduction.ShiftID
HAVING (((tblDailyProduction.ProdDate)=[Forms]![MainMenu]![txtFromDate]) AND ((tblDailyProduction.ShiftID)=1 Or (tblDailyProduction.ShiftID)=2));

And a VB statement like:

If CountOfProductionID > 0 then
msgbox "Data for that day/shift has already been entered"
Exit sub
end if

Thanks!

travis

Don't even bother with the query, just use a DCount from your form:

If Nz(DCount("*", "tblDailyProduction", "[ProdDate]=#" & Me.txtFromDate & "# AND ([ShiftID]=1 OR [ShiftID]=2)),0) <> 0 Then
etc....

HTH.

So, just put this in the VB for the form?

Say, on the cmd_Add_Records_Click()?

That would be great, and a whole lot easier!

travis

Yes, if that is the event where you need this check to take place that would be the place to put it.

Thanks. It works like a charm.

travis