By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

unique entry

100+
P: 250
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!
Feb 5 '09 #1
Share this Question
Share on Google+
18 Replies


Expert 100+
P: 1,287
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 TicketNumberField = """ & txtBoxTicketNumber & """")

(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.
Feb 5 '09 #2

100+
P: 250
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!
Feb 5 '09 #3

Expert 100+
P: 1,287
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 TicketNumberField = """ & [txtBoxTicketNumber] & """"))

You will need the [ ] around the textbox names.
Feb 5 '09 #4

100+
P: 250
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?
Feb 5 '09 #5

Expert 100+
P: 1,287
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?
Feb 5 '09 #6

100+
P: 250
im doing it for the ticketnum field in the table only
Feb 5 '09 #7

Expert 100+
P: 1,287
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 TicketNumberField = """ & [TicketNumberField] & """"))
Feb 5 '09 #8

100+
P: 250
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!
Feb 5 '09 #9

Expert 100+
P: 1,287
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.
Feb 5 '09 #10

100+
P: 250
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] & """"))
Feb 5 '09 #11

Expert 100+
P: 1,287
"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?
Feb 5 '09 #12

100+
P: 250
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?
Feb 5 '09 #13

Expert 100+
P: 1,287
Strange. You have the [Yeartxt] textbox on the same form and with that name, and with a value in it?
Feb 5 '09 #14

100+
P: 250
all of the above man are correct bro... this seems a lot more difficult than it should be lol
Feb 5 '09 #15

Expert 100+
P: 1,287
I'm going to try the same thing in one of my forms and see if I can duplicate the error.
Feb 5 '09 #16

100+
P: 250
ok...i appreciate all of your help man...
Feb 5 '09 #17

Expert 100+
P: 1,287
Well I didn't get any errors when I tried, but at the same time, it didn't work at all!

Here's how I did get it to work. You can put some code in the AfterUpdate Event of the textbox.

Private Sub TicketNumtxt_AfterUpdate()
if DCount("CURYear","Master","CURYear = """ & [Yeartxt] _
& """ AND TicketNum = """ & [TicketNumtxt] & """") > 0 then
'there is already at least 1 same entry
msgbox "That Ticket Number is already used."
TicketNumtxt = ""
TicketNumtxt.SetFocus
Else
'it's ok
End If

The only problem I had with this is, it doesn't set the cursor back in the field after it clears it for some reason.
Feb 5 '09 #18

100+
P: 250
it works perfectly... thanks a ton for all your help man... greatly appreciated
Feb 5 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.