There are two different syntaxes available for the "Assembly" field: either a 9 digit number with no spaces in between(i.e. 100018596) OR a 9 digit part number separated by dashes (859-982-364).
Currently, assembly is set up as text data type in the database. When I open the table in design view, it allows me to add either of the syntaxes above without any issue. However, when I try using the form to do it and enter the 9 digit number without spaces, it gives me a data type mismatch error on the DCount line. I realize that this is because the 9 digit number is being read as an integer. Is there any way to avoid this and use this form to add this number regardless of which syntax is being used?
Can I ask why the single quotation marks make a difference? I'm completely new to access and VBA, so I've mostly been searching the internet to get the code I need and slightly adjusting it to fit my needs, but I'm interested in actually trying to learn and understand it.
Whenever you deal with text, it has to be enclosed in quotation marks. For numbers, you don't. Sometime you want numbers to be interpreted as text, like in your case. Without quotation marks, 859-982-364 is read as a mathematical operation 859 minus 982 etc...
I'm actually experiencing another problem now... anytime I enter any number (regardless of syntax), it is telling me that it arleady exists in the database. However, I've gone back and looked at the table and it does not... the problem just started when I added the single quotation marks. Any idea what the source could be?
You should consider making that field a primary key with No duplicates allowed. This way, data entry is made at the database design level rather than at the form level. If your form changes or you do manual entries directly in the table, you will be guarantied not to have duplictes.
ElseIf IsNull(DCount("Assembly", "AttributesTbl", "Assembly =" & Me.tbo_assembly)) Then
return something not null, therefore the If statement goes to the next Else and throws the message.
Maybe run the output of the Dcout in the debug window (Alt+G).
And in your code, insert