Stuck in table data display - validation error.

[RonL]

Hello, I'm new to Access 2007, but not to relational concepts and design. I'm learning the Access interface by starting a db project. In setting up a table, I entered a validation test for a field in design view, then went to enter a row of data. When the validation test failed on that field, I realized I hadn't understood the syntax of the test, and I couldn't figure out how to make a valid entry. (ie. syntax was valid code, but I didn't know what it required. Dumb, I know.) I just wanted to cancel the row and go back to design, but I could click almost nowhere else on the screen without getting that error message. The only thing I finally was able to do was delete the whole column (and then redefine it). Couldn't find this in the help facility, so a dumb noob question: how do you escape a validation error message loop? A corollary question: I noticed there was a button for testing a validation expression on the menu bar, but it was not enabled? How do you test before being caught in the validation error loop?



Many thanks, Ron

Edit - May as well push my luck and ask this too: What's the proper expression to validate a 10 character string so that the first character is a capital letter and the remaining nine are digits? (I have a mask, but want further refinement..)

[NTC]

Esc (escape) is the key to use to get out of an invalid entry

To avoid your users having the same perplexing/frustrating experience - I would suggest you put the mask in the form; along with a label giving the format example; rather than rule enforcement at the table level.

[RonL]

Would you believe I awoke in middle of the night remembering I'd even used the word "escape" in my post and hadn't tried the key. Doh!

Speaking of escape, looks like I need to understand use of brackets in expressions to distinguish symbols from literals.

I read somewhere if you do data validation at field level, you don't need to worry about changing input routines later on. But yeah, my wife, the user, will appreciate having help in the form. Thanks for the tip!

I'd looked at early Access years ago, and opted to teach myself some Foxpro. Not an IT person, just find it relaxing, and nice to custom make an app for my work. Now retired, but my wife is starting up a home business from a hobby, so I'm back in the game. This ain't your father's Access, though!

Thx again. -Ron

[Rainlover]

Ron

I personally do not like input masks or formating, especially at the Table level.

Try this code in the After Update event of the Control on your Form.

Private Sub txtYourControl_AfterUpdate()
    Me.txtYourControl = UCase(Left(Me.txtYourControl, 1)) & Mid(Me.txtYourControl, 2)
End Sub

[RonL]

Rainlover, thanks. I'm only 3 days into learning the interface, let alone finishing the tables or setting the relations, not to mention learning visual basic - if that's what that event code is. But I understand what you're suggesting. I'm now contemplating that field - which is to be a unique catalog id - to be completely derived from data in other fields, so I may not need an entry form control at all. Not sure whether one field can be derived from others in the same record, so might need to use a non-editable form control. We'll see.

Thanks for the suggestion. -RonL

[Rainlover]

Not > learning visual basic < More correctly Visual Basic for Applications. There are some differences.

If you need a Unique ID then use AutoNumber.

[RonL]

If you need a Unique ID then use AutoNumber.

I want this field to be a unique character string(Lyymm12345),that last part of which is a substring equivalent of an autoincrementing integer, presumably the primary key, and the first part of which derives from other field entries. Any perils in this? If not, I plan to construct it with functions that read other fields in the same record. I suspect this needs to be done at a point prior to committing addition of the record(s) to table(s) - presumably during entry on the form. I gotta learn what the controller object events are and when/how to use them.

Thx, Ron

[Rainlover]

This may help.