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!

 

[bobsan42]

Hi,
First of, I read thousands of pages to learn how to use Access, but this was after reading thousands of pages learning other things like dBase, Pascal, Fortran ... And I can't say I know everything about it. Far from it, really. But one has to start somewhere...
If you think about it you'll see <>[Bunnies]![TagNumber] makes no sense - I personally cannot find any logic in it.
Normally to ensure Unique records - you need to create an index for this field in the Table Definition, and turn on its unique property.
However too much limitations on the data structure create a lot of headaches if you are not too careful. So to bring the problem closer to us we can make a check while entering the data in the form.
Your validation rule must check if there is no such tag number already used.
So if you enter B2 in Text77, then if we count all records in table Bunnies where TagNumber is B2, the result must be 0:

DCount("TagNumber","Bunnies",[Text77])=0

Don't forget to enter Validation text.
This method has the added benefit of checking the data during entering, before an attempt is made to save it. At an early stage. But is quite a burden for maintenance in large projects.

 

[J Blizzard]

Hi,
First of, I read thousands of pages to learn how to use Access, but this was after reading thousands of pages learning other things like dBase, Pascal, Fortran ... And I can't say I know everything about it. Far from it, really. But one has to start somewhere...
If you think about it you'll see <>[Bunnies]![TagNumber] makes no sense - I personally cannot find any logic in it.
Normally to ensure Unique records - you need to create an index for this field in the Table Definition, and turn on its unique property.
However too much limitations on the data structure create a lot of headaches if you are not too careful. So to bring the problem closer to us we can make a check while entering the data in the form.
Your validation rule must check if there is no such tag number already used.
So if you enter B2 in Text77, then if we count all records in table Bunnies where TagNumber is B2, the result must be 0:

DCount("TagNumber","Bunnies",[Text77])=0

Don't forget to enter Validation text.
This method has the added benefit of checking the data during entering, before an attempt is made to save it. At an early stage. But is quite a burden for maintenance in large projects.

Sure so I'd be happy to explain the logic in the statement. It is intended to look through all Tag #'s in the Bunnies table, which may look something like "AB1, AB2, CD5, MN2" and if none of those are equal to the user input, then it would allow the user input to proceed. Alternatively, I guess it could look through all Tag #'s, and if some of them are equal to the user input then it can count those and then make an additional comparison to the user input -- I just thought it would make more sense to do less math to get the same result, since we're both looking for a result of 0 or 1. However, I tried your suggestion and it is giving me two strange results. Sometimes it falsely says that there is a match, other times it says: There is a " in the form control's ValidationRule property.

The quotes make sense to me but I am still learning. Is there a different way to do it?

 

[bobsan42]

Sorry, my access skills are getting a bit rusty. And I mean no offence by anything I say.
The idea of what you're trying to achieve is clear and I believe I can help.
Try the options below (they are almost the same, but I would recommend the first one):

DCount("TagNumber","Bunnies","TagNumber='" & [Text77] & "'")=0
'or
DCount("TagNumber","Bunnies","TagNumber=Forms!Bunnies!Text77")=0

you may have to change the name after Form! if your userform is not called Bunnies

 

[bobsan42]

The quotes make sense to me but I am still learning. Is there a different way to do it?

Learning is good. So keep it up.
There are usually many ways a goal can be achieved:

• Validation rules like this is one way
• or a general validation function to return false if if a duplicate exists - since you're probably using a bound form, this should not be too complicated to write (in VBA)
• Access has a Macro programming capability, also tables can have separate data macros - I am not too much into these, but feel free to explore.

However, userforms are not the only way to enter and edit data

• so, like I said before, to actually restrict records with duplicate tag numbers, this must be done at table definition level: create an Index of TagNumber field and make it UNIQUE. This ensures that a tag number cannot be duplicate, no matter how you try to add it. This check is done by the database engine at the time of saving a record and it throws back an error.
• Table fields themselves also have validation rules

But table checks are triggered when an attempt is made to save the data. Which is a bit too late sometimes.
My prefered option, although it's not always applicable, is to give the user as little things to do as possible, since the users' creativity can always surprise you in all kinds of ways .

• Use a predefined tag pattern and just give the user the next number. Of course if you are entering numbers which are already in use this is not the best option.

Validation rules may have strange (side)effects - when you edit an existing record, they are still checked. So if accidentally enter in editmode in Text77 keeping the same number will give you a validation warning. In such a case press Esc to cancel the EditMode.

 

[J Blizzard]

This is all very creative, I'm surprised there isn't a more built-in way to stop a creative user before getting to the table level. But this works! So thank you for the solution. I think next I am going to add an additional check where, if the record already exists but it's actually the same record as itself as you mentioned then it will allow it. I'm doing all this because I really want to avoid entering the entire form only to find out I had already entered it previously - I'd rather catch it on the first field.

 

[J Blizzard]

This is my final solution, I cried a lot but it works.

DCount("TagNumber","Bunnies","TagNumber='" & [TagNumber] & "' and [ID] <> " & Nz([ID],0))=0

It counts the number of times that the tag I'm trying to create already exists AND for each time that it does exist, it only counts it if the ID I'm trying to create is different than the ID for which the tag already exists.

 

[welshgasman]

which for some reason is called Text77,

Access automatically names controls and increments their number.
You are meant to then change them to something you will recognise 6 months down the road, perhaps txtTagNumber ?
However calling something a number and having it as text is alien to me.

 

[welshgasman]

I forgot to mention, if you use the wizards to create a form/report then it names the controls the same as the source fields.
I then prefer to prefix them with the type of control cbo, txt, lst etc.....

 

[Micron]

IMO the best approach would have been to set the number field to be a unique index in table design view and let Access handle it. Writing code for something that built in features can handle just seems superfluous to me. In addition, if you try to handle this by setting a control's controlsource property, you can't bind that control to the table field (and from the post I gathered that is the case). That being said, if you're looking to have the next unique number automatically generated for you, that is a different story. I looked for clues on that in the first post but couldn't determine the requirements or method around data input.

@J Blizzard; it seems you're off to a good start by not using special characters (and no spaces I hope) in object names and doing all that reading. Do give all objects meaningful names as suggested though. As for the error, when you think about it, you were trying to avoid duplication by referring to a whole set of records in one field. The reference would not restrict itself to the record you just happen to be on. Besides, at the time of creation, the record does not actually exist so there's that too.