unique entry

[didacticone]

i have a table that has a year field and a ticket number field. the two combine to make our full ticket number. so an example would be

year field- "09-"
ticketnumber field- "0001"
the full ticket number would be 09-0001

what i want to know is (and im sorry if my terms are incorrect) can i link the two fields so that the entry has to be unique. because when 2010 comes we are going to use 0001 again so i cant just make the ticketnumber field unique. so i wanted to know if i can have the unique entry look in both fields... i hope im explaining that enough lol... if anyone has any suggestions it would be greatly appreciated. thanks!

[ChipR]

To do this, you could make a form with your input fields unbound. Then, when the user fills them out and clicks a button, you can grab the values from the text boxes and make sure that they are valid. You could do this by:

DLookUp ("AnyField", "TableName" , "YearField = """ & txtBoxYear & """ AND TicketNumberFie ld = """ & txtBoxTicketNum ber & """")

(assuming both fields are string types, for numbers remove the extra "")
If the result of that is null, that combination is not in the table, otherwise at least one row has those values.

If everything is valid, then you insert a new record with values from the text boxes with a DoCmd.RunSQL.

[didacticone]

i got the basis of what you are saying, but being the novice that i am at access, am not quite sure on how to correctly implement this... do you think you can break it down a little more for someone who is just learning the ropes? thanks a lot!

[ChipR]

This way does pretty much everything through code, so we should probably try a simpler solution first.

Use a regular form with the fields bound to your data fields, but set the Validation property of the ticketnumber field. You should be able to set it to:

IsNull(DLookUp ("AnyField", "TableName" , "YearField = """ & [txtBoxYear] & """ AND TicketNumberFie ld = """ & [txtBoxTicketNum ber] & """"))

You will need the [ ] around the textbox names.

[didacticone]

ok did that and it returns this message when i try to save it:

"invalid sql syntax- cannot use multple columns in a column-level CHECK constraint."

any ideas?

[ChipR]

Well that is an interesting surprise. Are you setting the validation of the field in the table, or on the input textbox on a form?

[didacticone]

im doing it for the ticketnum field in the table only

[ChipR]

Ah, that is explained by "Table Field Validation Rule Causes Error" (http://support.microsoft.com/kb/209106).

I would consider just putting this validation in the field on the form, and do it there, rather than make it do the DLookUp every time you work with any record in the table because it's kind of expensive.

I'm not positive about the table validation property if you still want to do it that way, but I think you can use

IsNull(DLookUp ("AnyField", "TableName" , "YearField = """ & [YearField] & """ AND TicketNumberFie ld = """ & [TicketNumberFie ld] & """"))

[didacticone]

im getting a different error now... would it be possible for me to send you the file and you see if you have any luck with it... and then i can see what you have done as it would be easier for me to interpret your work? if youd prefer not to i can post the error code i am getting when i do it in the field on the form.. thanks again!

[ChipR]

I can't really download anything for security reasons, but do post the error message and the exact string you are putting in the validation for the textbox on the form, and I'm sure we'll figure it out.

[didacticone]

ok well all i did was the post the string that you have given me into the valdation rule for the textbox in the form... im getting the error "there is a (n) " in the form control's validation rule property."

this is the string i used
IsNull(DLookUp ("AnyField", "Master", "Year = """ & [Year] & """ AND TicketNum = """ & [TicketNum] & """"))

[ChipR]

"AnyField" should be the name of one of the fields in your "Master" table.

Set the names of your text boxes to differentiate from the field names, and change [Year] and [TicketNum] to the names of the textboxes like [txtYear].

You should probably not use Year as a field name because there is an Access funtion with the same name.

Just to be sure, both fields are set in the table as strings?

[didacticone]

again im a newb at this but both fields are set in the table as text... i dont know if that answers your question... here is the string i used:

=IsNull(DLookUp ("num","Master" ,"CURYear = """ & [Yeartxt] & """ AND TicketNum = """ & [TicketNumtxt] & """"))

and it is returning this error

"the expression [yeartxt] you entered in the form controls validation rule property contains the error the object doesnt contain the automation object "yeartxt"."

lol any ideas with that one?

[ChipR]

Strange. You have the [Yeartxt] textbox on the same form and with that name, and with a value in it?

[didacticone]

all of the above man are correct bro... this seems a lot more difficult than it should be lol