Hi everybody, I am new to this forum, I signed up as I am seeking some help on what most of you guys think is a very simple form! I have a job interview on Friday and I have to build a simple form that allows the user to input data into a very simple table in the database. Thing is, I haven't used Access since I was at college about 5 years ago and I am getting myself a bit lost
Like I say, my table is very simple, and it contains the following data fields:-
Account No, Customer Name, Address, Postcode, Telephone No., Credit Limit and Payment terms.
I have given the data fields the following types:-
Account No (text), Customer Name(text), Address(text), Postcode(text), Telephone No.(text), Credit Limit(currency) and Payment terms(text).
The problem I am having is validating the following:-
Account No must be 6 characters in length
Credit Limit <= £10,000
Payment Terms can only be 30, 45 or 60 days
The other fields (name, address etc also cannot be left blank)
I have set validation rules and validation text at table level e.g.<=10000 for the credit limit and it works to a certain point. I really want it to work so that when the user tabs between the text boxes, the validation is tested then.
To attempt this, I have written a small sub routine in the LostFocus event of the CustomerName textbox:-
Private Sub CustomerName_LostFocus()
If CustomerName.Text = "" Then
MsgBox ("Please enter customer name")
This flags up a message box if the text box is empty, but I want to put the cursor back into that text box using SetFocus, but it's not happening. Can anybody tell me what I am doing wrong. And would it be much easier just to code all the validation that I need for this? Thanks.
Reply With Quote
I agree with the original suggestion to do the checking behind a "save" button (or the before update event of the form). The posted code won't work if the user doesn't change anything, as the update event wouldn't fire. Focus events would fire, but not if the user skips that control with the mouse. You can't count on them doing things the way you expect.
I sometimes use conditional formatting to highlight controls that are required but not yet filled out. It seems to help the users.
The job won't involve programming in any way, I have never been a programmer, and never will be, but If I get this job, I will be using Access databases and I think the employer just wants to see that I have a knowledge of how they work if that makes sense, so I don't think he will really care what way I do it. I just thought it would be cool to have a working thing which operated completely the way he has asked.
Like I say, I tried using validation rules and masks at table level, but they don't seem to do what I want them too, so was just looking for maybe one example of code which validates one of my fields, and then I'm sure I could figure out the rest of them myself
If you're not going to be a programmer, then doing this by code would be a mistake imo.
My advice would be to get the Validation Rule and Input Mask working.
Account No must be 6 characters in length: Input Mask: aaaaaa
Credit Limit <= £10,000: Validation Rule: <=10000 Validation Text: The credit limit cannot exceed £10,000.
Payment Terms can only be 30, 45 or 60 days: Validation Rule: 30 or 45 or 60 Validation Text: The Payment Terms can only be 30, 45 or 60 days.
Otherwise you should use the BeforeUpdate property to code a validation routine:
Private Sub CompanyNo_BeforeUpdate(Cancel as Integer)
If Len(Me.CompanyNo) > 6 Then
MsgBox "The company number can't be more than six characters"
Cancel = True
Thanks StarTrekker, appreciate it. I did a bit of VB in college, and I thought the code for doing these validations would be quite simple, so that's why I though I could handle coding them.
Can you tell me, is there anyway of validating the fields such as CustomerName and Address, using these validation rules? They are just text fields so I'm not sure how to compare them to an empty value if that makes sense, but I don't want the record to be added if they are left blank by the user.