Dcount With Dates

[referee]

hi,

in a table (tblplanner) i have this fields:
holidaydate = date
weeknumber= number
daynumber= number
forname =text
backname=text

now on my form they can select a name from a combobox the other fiekds wil filling automaticly thats no problem
when the above combination already exist in table i want a messagebox thats tell me that this record exist

the combo box called (cboOperator) is on a subform called frmregistration
the headform called frmstart

can someone help me with this code please.

thx

 

[referee]

hi all,

can someone help me with this please

thx

 

[stephen81]

Where does the combobox get the list of names from? Can you post your database and I'll try and help?

 

[referee]

thanks for your reply

this is the sql from the combobox


SELECT qryOperators.Achternaam, qryOperators.Voornaam, qryOperators.Persnr, qryOperators.Team, qryOperators.Ploeg, qryOperators.Werkpostnr, qryOperators.Afdeling
FROM qryOperators
GROUP BY qryOperators.Achternaam, qryOperators.Voornaam, qryOperators.Persnr, qryOperators.Team, qryOperators.Ploeg, qryOperators.Werkpostnr, qryOperators.Afdeling
HAVING (((qryOperators.Team)=[forms]![frmPLANNER_A]![txtteam]) AND ((qryOperators.Ploeg)=[forms]![frmPLANNER_A]![PLOEG]));


The data go to the table (tblPlanner)
is this what you want to see?
the db is too big to send it

i hope you can help me

thx

 

[stephen81]

Have you tried compacting your database to make it smaller. You can also delete the records (compact it afterwards) although leaving a few examples in might be useful.

 

[Ashfaque]

Hi,

As you say, when you are selecting name form the cboOperator, the recordset will trasfer to the tblPlanner.

To know record already exist or not, you need minimum 2 fields to compare with each other. There are 2 ways to prevent from entering the same.

The first simple way is to make your field with No Duplicates argument or set primary key in original table.

The second way could be like follows:

If you are selecting a name form cboOperator and comparing to with more than one field in your tblPlanner (data table) the code will be as follows:

Place the following code in After Update event of your combo cboOperator and try to play. I compared 2 fields (Backname and weeknumber) likewise you may increase the criteria for checking.

Private Sub CboOperator_AfterUpdate()
Dim cdn As String
cdn = "[backname] = '" & Nz(cboOperator, "") & "'"
cdn = cdn & " And weeknumber = " & weeknumber

If DCount("*", "tblPlanner ", cdn) > 0 Then
MsgBox " SORRY, RECORD ALREADY EXIST, TRY AGAIN", bOKOnly, "Warning!!!"
Me.Undo
Exit Sub
Else

' DO WHAT YOU WANT
End sub

Hope this will help.

Regards,

Ashfaque

 

[referee]

HI

thanks for reply

i do the following and i have an error (type????)


Dim cdn As String
cdn = "[Achternaam] = '" & Nz(cmbOPERATOR.Column(0), "") & "'" And "[OPERATOR_VOORNAAM] = '" & Nz(cmbOPERATOR.Column(1), "") & "'"
cdn = cdn & " And VERLOFWEEK = " & Me!VERLOFWEEK And VERLOFDAG = " & Me!VERLOFDAG" And VERLOFDATE = " & Me!VERLOFDATE"


If DCount("*", "tblPLANNEN ", cdn) > 0 Then
MsgBox " SORRY, RECORD ALREADY EXIST, TRY AGAIN", bOKOnly, "Warning!!!"
Me.Undo
Exit Sub
Else

END IF

can you tell me what syntaxes are wrong or what else???
the combobox is in a subform called frmsubtest and the headform called frmhead
VERLOFWEEK AND VERLOFDAG are numbers
VERLOFDATE is a date
all others are text


THX