Having a problem in the expression builder, very new to Access. It thinks I'm trying to use Automation objects?

[J Blizzard]

I've made my first Access database and watched a few hours of videos to learn how to use Access, but I've run into something that I wasn't able to figure out. I have a Table named "Bunnies" which I'll use to track each rabbit that I have in a rabbitry. Each rabbit will have a tag # on its ear, which I've called "TagNumber" in an effort to not use special characters or symbols. I then created a Form named "NewBunnyForm" to enter new information into the Bunnies Table, rather than directly interfacing with the table. All of this works so far, but I want to make sure I don't enter a tag # that is already in use by a different rabbit. I went to the Form's Property Sheet and selected the text entry for TagNumber, which for some reason is called Text77, went to Validation Rule, opened the Expression Builder, and clicked various Expression Elements and Categories in the GUI to come up with "<>[Bunnies]![TagNumber]" thinking that I would disallow values which are already found in the TagNumber field in the Bunnies Table. However, I'm getting possibly the most confusing error message: "The expression [Bunnies] you entered in the form control's ValidationRule property contains the error The object doesn't contain the Automation object 'Bunnies.'."

I feel like I'm not trying to automate anything or import from a pdf or write a macro or any of the other stuff that came up when I tried to figure out what even this error is talking about. Did I format something incorrectly? Am I not allowed to check a Table from a Form? Is there a type mismatch or something? I also tried other Operators like In, but that threw the same error message and my search results for that all point to SQL, which I'm trying to avoid using.

I did a search for this error on this forum as well, but nothing matched. I'd appreciate any help on this!

 

[J Blizzard]

Lots of good ideas here so far, I definitely appreciate the tips and will try to be deliberate with the details. For now, I'm inputting generational records of rabbits and there is a decent amount of what we'll call "line breeding" where you may see a parent multiple times in the genealogy. I want to make sure each rabbit only exists once in the main table, and each rabbit has a "Tag Number" which is a combination of letters and numbers, and is tattooed on their ear, so I'm using that as a uniqueness check but I don't want to go ahead and fill out an entire record before finding out they already exist in the table, so I put TagNumber at the top of the form and used a validation rule to stop me at that point. Also the Tag Numbers are assigned pretty randomly by the rabbitry they come from; some have a system, some are just awful, but they're always unique.

 

[Micron]

I don't want to go ahead and fill out an entire record before finding out they already exist in the table,

I'm not seeing an issue with that. Maybe you don't realize Access is not like Excel when it comes to data input. In xl, what you enter is entered and that's it. In Access, if you enter all the fields and don't commit the record and back out/cancel, there is nothing entered. What you have to understand with that is how records are committed - you do not have to manually force that. Simply moving off the record will attempt a save of that record, and that can even be caused by moving off of a subform onto the main form via mouse clicks.

So if you enter all the details and duplicate what should be a unique value, it's easy to prevent the duplication. In such cases, all the uncommitted data is held and either the issue has to be fixed or the record needs to be cancelled. However, if you're manually deciding on the next number you can easily know what the last number is in more than one way. One would be to display the last value used in an unbound control by using an expression as its controlsource. Another would be to calculate the last number and use that as the control's default value. Another is via table design as already mentioned. If your data is actually a number field, it's simple. If you are entering digits in a text field, it's not so simple.

 

[J Blizzard]

I guess primarily I want to avoid wasting time of entering rabbit weight, gender, name, tag, rabbitry, family details, all of that, only to see that I had already entered that rabbit, so to save time I spent 12 hours learning Access. Discarding the duplicate data is fine, but then I lose all that time of data entry. Also the Tag Number is completely out of my control. I had a nice batch of a few rabbits from one rabbitry where they used an organized system (LLTK12, LLTR11, LLTR12, LLTR21, etc) but then my next few individuals came from a different rabbitry where there was no system (gg1, ov24, EDWARD, -, TRAY).

 

[welshgasman]

If your TagNumber cannot be duplicated, you could make it a unique index. Then just DlookUp() the tag number in the AfterUpdate of the Tag Number control.
If they could be duplicated, then you could add the supplierID as well as an index. That would then be unique, unless they are so rubbish that they repeat their tags?
As your record will not have been saved at that time, it will only pick up one that had been entered and saved previously.

 

[J Blizzard]

As I keep testing and googling, you guys' suggestions are making more and more sense haha. I think that's a great idea.

 

[Micron]

I guess primarily I want to avoid wasting time of entering rabbit weight, gender, name, tag, rabbitry, family details, all of that, only to see that I had already entered that rabbit

I've had this open all day long while doing stuff around the house. My suggestion was going to be to first enter the number you want to use. In AfterUpdate event, save the record (Me.Dirty = False). If it's a dupe, Access will balk. Not practical if you have other fields that are set to be required though as Access may complain about those too.