make sure a record is not entered twice

[Jon123]

in the before update fied I want to eable to check the table and verify that a part is not entered twice based of certian fields. So in the table I have the following fields
part number - system - position - Active part

So when entering a part I want to match the part number - system - position can all be the same but if the active part is Yes then I want to stop the record from updating

does this make sense?

jim

 

[Jon123]

I think I have the code for the number field and the text fields but how do I code the yes no fields??

 

[pbaldy]

It would help to see your existing code, but generally you'd add:

...AND YesNoField = True

to your test.

 

[Jon123]

If DCount("[PartNumber]", "tble-Parts History", "[PartNumber] =" & Me.[PartNumber] & " AND [Customer] = " & Me.[customer] & " AND [Chamber] =" & Me.[Chamber]) > 0 Then

This is the code how would I add the Yes / No = True to this

 

[pbaldy]

Try:

If DCount("[PartNumber]", "tble-Parts History", "[PartNumber] =" & Me.[PartNumber] & " AND [Customer] = " & Me.[customer] & " AND [Chamber] =" & Me.[Chamber] & " AND YesNoField = True") > 0

 

[Jon123]

If DCount("[Part Number]", "tble-Parts History", "[Part Number] = " & Me.[Part Number] & " AND [system] = " & Me.[var1] & " AND [Chamber] =" & Me.[Chamber] & " AND [Currently in use] = True") > 0 Then

when this runs I get an error message Run time error 2471

 

[pbaldy]

For starters, you would have to bracket the table name due to the inadvisable space and symbol. If that doesn't fix it, what are the data types of all the fields?

 

[Jon123]

If DCount("[Part Number]", "[tble-Parts History]", "[Part Number] = " & Me.[Part Number] & " AND [system] = " & Me.[system] & " AND [Chamber] =" & Me.[Chamber] & " AND [Currently in use] = True") > 0 Then

I think the problem is in the part number field when I stop the code and move the mouse over each field the chamber and system show the correct value but the part number does not. This is a text field but the values are entered 0000-00000 which is the format for part numbers could the - be causing my problem?

 

[jsv2002]

Might not be the format in sense of being text but have had problems with Dcount if the table and form fields are not formated as the same data types eg. table is text and form field is number plus you have a - symbol or special charcter used in the text as a seperator try just a straight number for error elimination.

just a thought good luck John

 

[pbaldy]

A text value requires an adjustment. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

 

[Jon123]

ok so I got part of it working if I use

If Not IsNull(DLookup("[text26]", "[Tble-Parts History]", "[Part Number] = '" & Me![Text26] & "'")) Then

then if the part number is in the table or not in the table I can program from there. But now I need to add the system and position and the yes / no field but I'm getting type mismatch errors
please help

jon

 

[pbaldy]

If they're numbers:

If DCount("[Part Number]", "[tble-Parts History]", "[Part Number] = '" & Me.[Part Number] & "' AND [system] = " & Me.[system] & " AND [Chamber] =" & Me.[Chamber] & " AND [Currently in use] = True") > 0 Then

 

[Jon123]

The part number field is Text field but its numbers the system is test but all numbers and the 3rd field is text no numbers does this make a difference?

 

[pbaldy]

The value for any field whose data type is text must be surrounded by single quotes, as I did with the part number field. It doesn't matter what the actual values may be, just what the data type in the table is.

 

[Jon123]

When I put in the code from your last post I get this error.
Run-time error '2471':
The expression you entered as a query parameter produced this error: 'D'

what does this mean? 'D'

jon

 

[pbaldy]

Looks like one of Access' mystery errors. Can you post the db?

 

[Jon123]

I would rather not post to much IP did you have a more controlled email account that you are willing to use?

 

[pbaldy]

Sure

pbaldy
gmail
com

 

[Jon123]

I can try to simplify it down if that would be better

 

[pbaldy]

Better for you probably. If you post it here, more people will see it and potentially be able to help.