Add leading zeros and possibly undo

J

Judy Ward

I have a Creche table (Creche is another word for Nativity). CrecheNum is
actually a text field. In order to get the records to sort correctly, I
added leading zero(s) to make each CrecheNum 3 digits.

I have a form for users to enter new Creches. I want two things to happen:
#1 - Add leading zero(s) if needed to make the CrecheNum 3 digits
#2 - Check to see if the CrecheNum entered is already in use, and if so, undo

I have #2 working in the form BeforeUpdate event, but when I put in the code
to do #1, I get Run-time error -2147352567 telling me that the BeforeUpdate
function is preventing the form from saving the data in the field. #1 works
in the AfterUpdate event, but I can't figure out how to cancel (can't set the
focus back to txtCrecheNum).

Below is my code that is not working. I would appreciate suggestions on how
to fix what I have or how to accomplish my goals a different way.

Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
'#1 If the user enters a one-digit number, add two leading zeroes
If Me.txtCrecheNum Like "#" Then
Me.txtCrecheNum = "00" & Me.txtCrecheNum
ElseIf Me.txtCrecheNum Like "##" Then
Me.txtCrecheNum = "0" & Me.txtCrecheNum
End If
'#2 Check to see if this CrecheNum is already being used
If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" & "'" &
Me.txtCrecheNum & "'")) Then
MsgBox Me.txtCrecheNum & " is already in use. Please choose a
different Creche Number.", vbInformation
Cancel = True
Me.Undo
End If
End Sub

Thank you!
Judy
 
D

Dennis

Judy,

Can you change the data type of CrecheNum to a number field instead of a
text field? I believe that would solve your problem and you would not have
to insert leading zeros.

Also, what is the primary key to the Creche table?
Is there a significance to the actual Creche Number or is it just a number
to identify the individual Creche? If that is the case, have you thought
about making the CrecheNum field an automatic number field and let Access
automatically assign the number. This way you do not have to worry about
duplicates.

I think I would place code # 1 if not both piece of code in the CrecheNum
text box's Before Update event. This event is most useful for performing
complex validations of data in a control. Your code can cancel this event to
stop the update and place
the focus on the changed control. This way, the user will know right away
that they have entered in incorrect CrecheNum instead of waiting until after
the have entered all of the data.

Per Inside Access "Your code can cancel this event to stop the update and
place the focus on the changed control or record. This event is most useful
for performing complex validations of data on forms or in controls."


--
Dennis


Judy Ward said:
I have a Creche table (Creche is another word for Nativity). CrecheNum is
actually a text field. In order to get the records to sort correctly, I
added leading zero(s) to make each CrecheNum 3 digits.

I have a form for users to enter new Creches. I want two things to happen:
#1 - Add leading zero(s) if needed to make the CrecheNum 3 digits
#2 - Check to see if the CrecheNum entered is already in use, and if so, undo

I have #2 working in the form BeforeUpdate event, but when I put in the code
to do #1, I get Run-time error -2147352567 telling me that the BeforeUpdate
function is preventing the form from saving the data in the field. #1 works
in the AfterUpdate event, but I can't figure out how to cancel (can't set the
focus back to txtCrecheNum).

Below is my code that is not working. I would appreciate suggestions on how
to fix what I have or how to accomplish my goals a different way.

Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
'#1 If the user enters a one-digit number, add two leading zeroes
If Me.txtCrecheNum Like "#" Then
Me.txtCrecheNum = "00" & Me.txtCrecheNum
ElseIf Me.txtCrecheNum Like "##" Then
Me.txtCrecheNum = "0" & Me.txtCrecheNum
End If
'#2 Check to see if this CrecheNum is already being used
If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" & "'" &
Me.txtCrecheNum & "'")) Then
MsgBox Me.txtCrecheNum & " is already in use. Please choose a
different Creche Number.", vbInformation
Cancel = True
Me.Undo
End If
End Sub

Thank you!
Judy
Click to expand...
 
J

Judy Ward

Dennis,

Unfortunately, no, I can't change the data type of CrecheNum. What I didn't
mention is that I am helping with this database (it's not "mine"). There are
both numbers and letters already being used in the CrecheNum field.

Thank you for responding!
Judy

Dennis said:
Judy,

Can you change the data type of CrecheNum to a number field instead of a
text field? I believe that would solve your problem and you would not have
to insert leading zeros.

Also, what is the primary key to the Creche table?
Is there a significance to the actual Creche Number or is it just a number
to identify the individual Creche? If that is the case, have you thought
about making the CrecheNum field an automatic number field and let Access
automatically assign the number. This way you do not have to worry about
duplicates.

I think I would place code # 1 if not both piece of code in the CrecheNum
text box's Before Update event. This event is most useful for performing
complex validations of data in a control. Your code can cancel this event to
stop the update and place
the focus on the changed control. This way, the user will know right away
that they have entered in incorrect CrecheNum instead of waiting until after
the have entered all of the data.

Per Inside Access "Your code can cancel this event to stop the update and
place the focus on the changed control or record. This event is most useful
for performing complex validations of data on forms or in controls."


--
Dennis


Judy Ward said:
I have a Creche table (Creche is another word for Nativity). CrecheNum is
actually a text field. In order to get the records to sort correctly, I
added leading zero(s) to make each CrecheNum 3 digits.

I have a form for users to enter new Creches. I want two things to happen:
#1 - Add leading zero(s) if needed to make the CrecheNum 3 digits
#2 - Check to see if the CrecheNum entered is already in use, and if so, undo

I have #2 working in the form BeforeUpdate event, but when I put in the code
to do #1, I get Run-time error -2147352567 telling me that the BeforeUpdate
function is preventing the form from saving the data in the field. #1 works
in the AfterUpdate event, but I can't figure out how to cancel (can't set the
focus back to txtCrecheNum).

Below is my code that is not working. I would appreciate suggestions on how
to fix what I have or how to accomplish my goals a different way.

Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
'#1 If the user enters a one-digit number, add two leading zeroes
If Me.txtCrecheNum Like "#" Then
Me.txtCrecheNum = "00" & Me.txtCrecheNum
ElseIf Me.txtCrecheNum Like "##" Then
Me.txtCrecheNum = "0" & Me.txtCrecheNum
End If
'#2 Check to see if this CrecheNum is already being used
If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" & "'" &
Me.txtCrecheNum & "'")) Then
MsgBox Me.txtCrecheNum & " is already in use. Please choose a
different Creche Number.", vbInformation
Cancel = True
Me.Undo
End If
End Sub

Thank you!
Judy
Click to expand...
Click to expand...
 
K

Krzysztof Naworyta

Judy Ward wrote:


Move your formating to AfterUpdate event procedure.

In BeforeUpdate check only if "number" already exists:

Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)

dim s as string

s = format(Me.txtCrecheNum, "000")

If DCount("[CrecheNum]", "Creche", "[CrecheNum]='" & s & "'"))<>0 Then
MsgBox s & " is already in use. " & _
"Please choose a different Creche Number.", vbInformation
Cancel = True
Me.Undo
End If
End Sub

Private Sub txtCrecheNum_AfterUpdate()

Me.txtCrecheNum = format(Me.txtCrecheNum, "000")

End Sub

--
KN


||| Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
||| '#1 If the user enters a one-digit number, add two leading
||| zeroes If Me.txtCrecheNum Like "#" Then
||| Me.txtCrecheNum = "00" & Me.txtCrecheNum
||| ElseIf Me.txtCrecheNum Like "##" Then
||| Me.txtCrecheNum = "0" & Me.txtCrecheNum
||| End If
||| '#2 Check to see if this CrecheNum is already being used
||| If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" &
||| "'" & Me.txtCrecheNum & "'")) Then
||| MsgBox Me.txtCrecheNum & " is already in use. Please
||| choose a different Creche Number.", vbInformation
||| Cancel = True
||| Me.Undo
||| End If
||| End Sub



||| I have a Creche table (Creche is another word for Nativity).
||| CrecheNum is actually a text field. In order to get the records to
||| sort correctly, I added leading zero(s) to make each CrecheNum 3
||| digits.
|||
||| I have a form for users to enter new Creches. I want two things to
||| happen: #1 - Add leading zero(s) if needed to make the CrecheNum 3
||| digits #2 - Check to see if the CrecheNum entered is already in
||| use, and if so, undo
|||
||| I have #2 working in the form BeforeUpdate event, but when I put in
||| the code to do #1, I get Run-time error -2147352567 telling me that
||| the BeforeUpdate function is preventing the form from saving the
||| data in the field. #1 works in the AfterUpdate event, but I can't
||| figure out how to cancel (can't set the focus back to txtCrecheNum).
|||
||| Below is my code that is not working. I would appreciate
||| suggestions on how to fix what I have or how to accomplish my goals
||| a different way.
|||
||| Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
||| '#1 If the user enters a one-digit number, add two leading
||| zeroes If Me.txtCrecheNum Like "#" Then
||| Me.txtCrecheNum = "00" & Me.txtCrecheNum
||| ElseIf Me.txtCrecheNum Like "##" Then
||| Me.txtCrecheNum = "0" & Me.txtCrecheNum
||| End If
||| '#2 Check to see if this CrecheNum is already being used
||| If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" &
||| "'" & Me.txtCrecheNum & "'")) Then
||| MsgBox Me.txtCrecheNum & " is already in use. Please
||| choose a different Creche Number.", vbInformation
||| Cancel = True
||| Me.Undo
||| End If
||| End Sub
|||
||| Thank you!
||| Judy
 
D

Dennis

Judy,

I don't know what happened. I wrote my response in Word an pasted it into
the response window. I apparantly pasted the wrong version of my response.

Krzysztof Naworyta is correct. Follow his advice.
 
J

Judy Ward

Thank you very much. This is exactly what I needed.

Krzysztof Naworyta said:
Judy Ward wrote:


Move your formating to AfterUpdate event procedure.

In BeforeUpdate check only if "number" already exists:

Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)

dim s as string

s = format(Me.txtCrecheNum, "000")

If DCount("[CrecheNum]", "Creche", "[CrecheNum]='" & s & "'"))<>0 Then
MsgBox s & " is already in use. " & _
"Please choose a different Creche Number.", vbInformation
Cancel = True
Me.Undo
End If
End Sub

Private Sub txtCrecheNum_AfterUpdate()

Me.txtCrecheNum = format(Me.txtCrecheNum, "000")

End Sub

--
KN


||| Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
||| '#1 If the user enters a one-digit number, add two leading
||| zeroes If Me.txtCrecheNum Like "#" Then
||| Me.txtCrecheNum = "00" & Me.txtCrecheNum
||| ElseIf Me.txtCrecheNum Like "##" Then
||| Me.txtCrecheNum = "0" & Me.txtCrecheNum
||| End If
||| '#2 Check to see if this CrecheNum is already being used
||| If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" &
||| "'" & Me.txtCrecheNum & "'")) Then
||| MsgBox Me.txtCrecheNum & " is already in use. Please
||| choose a different Creche Number.", vbInformation
||| Cancel = True
||| Me.Undo
||| End If
||| End Sub



||| I have a Creche table (Creche is another word for Nativity).
||| CrecheNum is actually a text field. In order to get the records to
||| sort correctly, I added leading zero(s) to make each CrecheNum 3
||| digits.
|||
||| I have a form for users to enter new Creches. I want two things to
||| happen: #1 - Add leading zero(s) if needed to make the CrecheNum 3
||| digits #2 - Check to see if the CrecheNum entered is already in
||| use, and if so, undo
|||
||| I have #2 working in the form BeforeUpdate event, but when I put in
||| the code to do #1, I get Run-time error -2147352567 telling me that
||| the BeforeUpdate function is preventing the form from saving the
||| data in the field. #1 works in the AfterUpdate event, but I can't
||| figure out how to cancel (can't set the focus back to txtCrecheNum).
|||
||| Below is my code that is not working. I would appreciate
||| suggestions on how to fix what I have or how to accomplish my goals
||| a different way.
|||
||| Private Sub txtCrecheNum_BeforeUpdate(Cancel As Integer)
||| '#1 If the user enters a one-digit number, add two leading
||| zeroes If Me.txtCrecheNum Like "#" Then
||| Me.txtCrecheNum = "00" & Me.txtCrecheNum
||| ElseIf Me.txtCrecheNum Like "##" Then
||| Me.txtCrecheNum = "0" & Me.txtCrecheNum
||| End If
||| '#2 Check to see if this CrecheNum is already being used
||| If Not IsNull(DLookup("[CrecheNum]", "Creche", "[CrecheNum]=" &
||| "'" & Me.txtCrecheNum & "'")) Then
||| MsgBox Me.txtCrecheNum & " is already in use. Please
||| choose a different Creche Number.", vbInformation
||| Cancel = True
||| Me.Undo
||| End If
||| End Sub
|||
||| Thank you!
||| Judy
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clearing combo boxes and a text box 2
How to add leading zeros to split field string 2
VSTO C# add in Outlook.Appointment error 0
Keep First Letters, with exceptions 0
Word- Find and Replace 1
Keeping Leading Zeros; How Please ? 2
Improving Macro That Keeps First Letter 0
Stripping leading zeros 2

Top