Can you limit the number of records in a table per month? 1

[topwaya]

I have an append query:

INSERT INTO tblWIN ( PID, WinD, WinTyp )
SELECT TOP 5 qfltWALKParticipants.PID, Forms!fdlgWALKWinners!txtStart AS WinDate, "WALK" AS WinTyp
FROM qfltWALKParticipants
ORDER BY Rnd([PID]);

That puts 5 winners into tblWIN with a WinTyp = WALK each month. (and other queries that put 5 winners in each with different WinTyp)

I'd like to fool-proof the table so that a user can not accidently put 5 additional winners with the same WinTyp for the same month/year that is already in the tblWin.

Is there a way to do this - so if someone accidently runs the query twice in the same month, they can get an error message saying that the winners have already been chosen?

Thanks for any help!

 

[Remou]

Hi
Would it be possible to include an incremental value in your table, such as:
How do you really put an incremental value in your query?
faq701-1155

Then it would be possible to create a unique index on WinD, WinTyp and IncrementalValue, which would make it impossible to add another set for the same date and type.

 

[PHV]

Perhaps something like this ?
PARAMETERS Forms!fdlgWALKWinners!txtStart DateTime;
INSERT INTO tblWIN (PID,WinD,WinTyp)
SELECT TOP 5 PID, Forms!fdlgWALKWinners!txtStart AS WinD, 'WALK' AS WinTyp
FROM qfltWALKParticipants
WHERE Not Exists(SELECT * FROM tblWin WHERE WinTyp='WALK' AND WinD=Forms!fdlgWALKWinners!txtStart)
ORDER BY Rnd([PID]);

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

 

[topwaya]

Thank you, sorry - I should have posted what I ended up doing, I put this in the sub of the onclick event on the command button on the form to check for the winners already picked:

Private Sub Search_Click()
  Me.Visible = False

Dim rs As Recordset
Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT WinTyp,WinD FROM tblWin WHERE WinTyp='WALK' AND WinD=#" & Format(Forms!fdlgPickWalkWinners!TxtStart, "yyyy-mm-dd") & "#")

If Not rs.EOF And Not rs.BOF Then

   MsgBox ("Winner's Are Already Picked For the Month you Started From")
   DoCmd.OpenReport "rptWALKWinners", acViewPreview

Else
  MsgBox ("Click YES On the Following 2 Message Boxes")
   DoCmd.OpenQuery "qppWALKWinners", acNormal, acEdit
   DoCmd.Close acQuery, "qppWALKWinners"
   DoCmd.OpenReport "rptWALKWinners", acViewPreview
   
End If

End Sub

If there are winners already selected for that month, it won't append, but it will still display them .

 

[PHV]

You may simplify your code with only one OpenReport call:
If Not rs.EOF And Not rs.BOF Then
MsgBox ("Winner's Are Already Picked For the Month you Started From")
Else
MsgBox ("Click YES On the Following 2 Message Boxes")
DoCmd.OpenQuery "qppWALKWinners", acNormal, acEdit
DoCmd.Close acQuery, "qppWALKWinners"
End If
rs.Close
Set rs = Nothing
DoCmd.OpenReport "rptWALKWinners", acViewPreview

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

 

[topwaya]

Yes it does! Thank you!!!!!