DCount not recognizing criteria

[ecwhite]

Hello,

Please DCount function in the form code is not recognizing the criteria, it
keeps inserting the record even when the patient id number has already been
inserted in the database. Each patient ID should only be in the Database once
and no duplicates.

q_duplicate_idnbr_lookup - This is a query

If DCount("[table1.id_nbr]", "table1", "q_duplicate_idnbr_lookup" =
Me.id_nbr) = 0 Then

insert new record

else

msg " No duplicate"

End If

Thanks for your help.

 

[Danny J. Lesandrini]

The syntax for DCount is more like this ...


DCount("[Field]", "

", "[Field]=" & value)

You can't use the query name in the last parameter. It goes in
the middle param and you need a filter field in the last one.

 

[ecwhite]

Hello,

Sorry, i did not want to make the post confusing by adding too many of what
i did that did not wor. I already tried that and it gave me an error " data
type mismatch in criteria expression". The id datatype in the MYSQL table is
a VARCHAR, in the access front end it shows up in the table as text but the
number looks like this 8899200000.

I declared the variable as Variant.

Thanks

The syntax for DCount is more like this ...


DCount("[Field]", "

", "[Field]=" & value)

You can't use the query name in the last parameter. It goes in
the middle param and you need a filter field in the last one.

--
Danny J. Lesandrini
(e-mail address removed) www.amazecreations.com

Hello,

Please DCount function in the form code is not recognizing the criteria, it
keeps inserting the record even when the patient id number has already been
inserted in the database. Each patient ID should only be in the Database once
and no duplicates.

q_duplicate_idnbr_lookup - This is a query

If DCount("[table1.id_nbr]", "table1", "q_duplicate_idnbr_lookup" =
Me.id_nbr) = 0 Then

insert new record

else

msg " No duplicate"

End If

Thanks for your help.

 

[ecwhite]

Hello,

I have fixed by changing it to

DCount("[Field]", "

", "[Field]='" & value & "'")

The syntax for DCount is more like this ...


DCount("[Field]", "

", "[Field]=" & value)

You can't use the query name in the last parameter. It goes in
the middle param and you need a filter field in the last one.

--
Danny J. Lesandrini
(e-mail address removed) www.amazecreations.com

Hello,

Please DCount function in the form code is not recognizing the criteria, it
keeps inserting the record even when the patient id number has already been
inserted in the database. Each patient ID should only be in the Database once
and no duplicates.

q_duplicate_idnbr_lookup - This is a query

If DCount("[table1.id_nbr]", "table1", "q_duplicate_idnbr_lookup" =
Me.id_nbr) = 0 Then

insert new record

else

msg " No duplicate"

End If

Thanks for your help.

 

[Stefan Hoffmann]

hi,

Please DCount function in the form code is not recognizing the criteria, it
keeps inserting the record even when the patient id number has already been
inserted in the database. Each patient ID should only be in the Database once
and no duplicates.

You should really create a unique index in your table for this field to
ensure this constraint.



mfG
--> stefan <--