J.J.,
You are right, and it is not an XP bug, I get the same behaviour in A2K. One way to overcome it is to use Me.Undo instead, only that wipes out the previous (wrong) entry, like my original code intended to, instead of leaving it there for editing.
Nikos
When I comment the (me.inputcontrolname = null) line then i get run-time error '2108' "You must save the field before you execute the GoToConrol action, the GoToControl method, or the SetFocus method."
Then I comment the next line (me.inputcontrolname.setfocus) and then works but after msgbox it goes to the next control. that souldn't happen or? it's before update event procedure. your code and the clearing of the control is not the problem, but i'm starting to think that my access xp has some bugs because there aren't any validation rules on the table set.
tnx one more time for ideas
J.J.,
Perhaps there is a validation rule on the field in the underlying table that will not accept Null values. The intention was to clear out the control for new entry, but come to think of it it's probably better not to, so the user can see / edit their entry. To achieve this, just comment out or remove this line altogether.
HTH,
Nikos
TNX very much it works but with little problem.
wenn I enter the wrong number then after the msgbox access gives me run-time error '-2147352567(80020009)
The macro or a function set to the Beforeupdate or ValidationRule property for this field is preventing mydbs from saving data in the field.
Clicking on DEBUG it goes to the code, line:
Me.InputControlName = Null
what should i do?
(of course i changed the inputcontorlname to the actual name of the control also that's not the problem)
thanks one more time
J.J.
J.J.,
Paste this code in the Before Update event of your control where the user enters the input number:
Dim vInput As String
Dim vWeight(13) As Integer
Dim vDgt As String
Dim vSum As Long
Dim vRes As Integer
vInput = Me.InputControlName 'change to actual control name here
If Len(vInput) <> 13 Then GoTo Invalid
For i = 1 To 6
vWeight(i) = 8 - i
vWeight(i + 6) = 8 - i
Next
vSum = 0
For i = 1 To 12
vDgt = Mid(vInput, i, 1)
If Not (Asc(vDgt) >= 48 And Asc(vDgt) <= 57) Then GoTo Invalid
vSum = vSum + Val(vDgt) * vWeight(i)
Next
vRes = vSum Mod 11
Select Case vRes
Case 1
GoTo Wrong_Check_Digit
Case 0
If Val(Right(vInput, 1)) = 0 Then
GoTo Continue
Else
GoTo Wrong_Check_Digit
End If
Case Else
If Val(Right(vInput, 1)) = 11 - vRes Then
GoTo Continue
Else
GoTo Wrong_Check_Digit
End If
End Select
Continue:
'Other code to execute if validation succeeds
Exit Sub
Invalid:
msg = "Input must be a 13-digit number, including leading zeroes."
GoTo Failed
Wrong_Check_Digit:
msg = "The Check digit is wrong!"
Failed:
ttl = "Invalid Input"
typ = vbCritical
MsgBox msg, typ, ttl
Me.InputControlName = Null
Me.InputControlName.SetFocus
You will need to change InputControlName to the actual name of the control on your form.
The code checks for correct length and non-numeric characters, as well as for check digit correctness. If either is wrong, it shows a message box with the appropriate message, clears the control and sets focus back to it.
The field in the underlying table should be text, so you don't have to rely on formatting to keep any leading zeroes, plus it makes it easier to manipulate (like in the validation code above). Numeric fields should be used when you expect to perform mathematical operations on them, which is usually not the case with codes (much like telephone numbers, zip codes etc).
HTH,
Nikos
Hi guys
I'm not so good at programming in general so i need a little help from you.
The problem is next I have a field in that can be entered exact 13 numbers (like 0308964384007).
Before update the number must be checked with following algorythm:
I will asign every of the numbers with a character so you can better folow the formula,
0308964384007=abcdefghijklm
ZZZ=(7*a)+(6*b)+(5*c)+(4*d)+(3*e)+(2*f)+(7*g)+(6*h)+(5*i)+(4*j)+(3*k)+(2*l)
In my example ZZZ makes 191 and than we divide ZZZ with 11 but with rest (R).
If the rest is 1 then entered number is wrong, if the rest is 0 then the last carachter (m) must be 0, and when
the rest is between 1 and 11 then m=11-R
191/11=17
R=4
11-4=7 => m=7 the number is correct.
and should I make the field text or number in table?
hope you have some ideas for me, and thanks in advance for the trouble
J.J
