Hello I am currently building a form that requires a number to be entered to recalculate stock amounts. I have managed to get it all working and every time the stock goes below 0 a msgbox comes up to state "Insufficient Stock". However when this occurs it triggers the msgbox "You Must Enter Numbers Only!" from the keypress sub. The two sub's being used are below. Any advice would be much appreciated.
Private Sub Text48_AfterUpdate()
Dim NewQty As String
NewQty = Me.Text16 - Me.Text48
If NewQty < 0 Then
Me!Text54 = "0"
MsgBox ("Insufficient Stock")
Else
Me!Text54 = NewQty
End If
End Sub
Private Sub Text48_KeyPress(KeyAscii As Integer)
If (KeyAscii > 47 And KeyAscii < 58) Or (KeyAscii = 8) Then
KeyAscii = KeyAscii
Else:
MsgBox ("You Must Enter Numbers Only!")
KeyAscii = 0
Me.Text48 = ""
End If
End Sub
always better if you give controls meaningful names but is this correct?
Me!Text54 = "0"
if it is supposed to be zero, than you should assign as a number
Me!Text54 = 0
you might also want to consider using
Me.Text54
this works with intellisense but more importantly, when you come to compile your code, it will error if the name is spelt wrong. Using the ! character means it won't error until the code is executed, i.e. the form is open.
If I am understanding this correctly, it's because of the end user dispositioning the previous msgbox with the Enter key (which then triggers the second block of code's Keypress event).
If my understanding is correct (?) of what is going on here....then, naturally, you would want to accomodate not just that, but the other way(s) people typically respond to a modally displayed 'ok' button/msgbox........Most notably, the Spacebar.
If I am understanding this correctly, it's because of the end user dispositioning the previous msgbox with the Enter key (which then triggers the second block of code's Keypress event).
If my understanding is correct (?) of what is going on here....then, naturally, you would want to accomodate not just that, but the other way(s) people typically respond to a modally displayed 'ok' button/msgbox........Most notably, the Spacebar.
The problem seemed to be caused by the enter keypress to clear the message box. By adding that to the if statement it would appear to have rectified the issue. I have not yet encountered any other problems from this but it is still under development so might have other issues down the line.
Private Sub Text48_KeyPress(KeyAscii As Integer)
If (KeyAscii > 47 And KeyAscii < 58) Or (KeyAscii = 8) Or (KeyAscii = 13) Then
KeyAscii = KeyAscii
Else
MsgBox ("You Must Enter Numbers Only!")
KeyAscii = 0
Me.Text48 = ""
End If
End Sub
The problem seemed to be caused by the enter keypress to clear the message box. By adding that to the if statement it would appear to have rectified the issue.
I don't think you're understanding what I'm saying. Yes, I understand that changing the Keypress code to accomodate Enter, solved the problem of pressing Enter on the msgbox. It seems silly to me not to accomodate the other Keypresses that you already know people might do to a msgbox - but if you're happy I'm happy.
The most important thing to learn about forms is what event is used for which purpose. They are not interchangeable. You are using the wrong events to properly implement your code.
Before you start changing code, please rename your controls to something meaningful. Either use the name of the bound field as the name of the control (default when you drag fields from the field list or allow Access wizards to build the forms) OR use the bound name with a prefix that identifies the control type - txt, cbo, cmd, lst, opt, etc. Using a different name for the control than the name of the field it is bound to allows you to separate your code references so that it is clear whether you are referring to the control or to the actual bound field.
Me.myfield = reference to the bound field
Me.txtmyfield = reference to the textbox holding the bound field
I'm going to continue to use YOUR bad names to avoid confusion since I don't know what the actual names should be. The validation code needs to go in either the control's BeforeUpdate event or the Form's BeforeUpdate event. I chose the control's BeforeUpdate event although if this were my project, I would use the form's BeforeUpdate event so that I could keep all the code regarding a single field in one place. When you put validation code in a control event, you MUST also put validation code in the Form's BeforeUpdate event if you also need to ensure that a field is not empty. Remember, if you never dirty a control, it's BeforeUpdate event will not fire and so you can never use the control events to check for null values. When you do arithmetic, you must use numeric data types. String is NOT numeric so that was the first code change. Then I removed the quotes from around the zero because you do not use quotes around numeric items. Quotes are used to delimit text data types, octothorps (#) are used to delimit date data types, and NO delimiter is used for numeric data types. The important change I made was to cancel the update, unless you cancel the update, you can raise error messages until the cows come home and they will not stop bad data from being saved. Only YOU can stop the save and you do that by issuing the cancel command. Notice that the AfterUpdate event does not have a Cancel argument. That's because at the time the AfterUpdate event runs, it's too late to stop the save
Code:
Private Sub Text48_BeforeUpdate(Cancel)
Dim NewQty As Integer
NewQty = Me.Text16 - Me.Text48
If NewQty < 0 Then
Me.Text54 = 0
MsgBox ("Insufficient Stock"), vbOKOnly
Cancel = True
Else
Me.Text54 = NewQty
End If
End Sub
Rather than using the KeyPress event, you should be using the Change event if you want to validate each character as it is typed or use the BeforeUpdate event to validate the entire value one time. When you use the Change event, remember, it fires once for EACH character and your code should be referenceing the .text property rather than the .value property when you use this event. Me.somefield.Text. Referencing a field as Me.somefield is the same as Me.somefield.Value since .value is the default property of a control.
The most important thing to learn about forms is what event is used for which purpose. They are not interchangeable. You are using the wrong events to properly implement your code.
Before you start changing code, please rename your controls to something meaningful. Either use the name of the bound field as the name of the control (default when you drag fields from the field list or allow Access wizards to build the forms) OR use the bound name with a prefix that identifies the control type - txt, cbo, cmd, lst, opt, etc. Using a different name for the control than the name of the field it is bound to allows you to separate your code references so that it is clear whether you are referring to the control or to the actual bound field.
Me.myfield = reference to the bound field
Me.txtmyfield = reference to the textbox holding the bound field
I'm going to continue to use YOUR bad names to avoid confusion since I don't know what the actual names should be. The validation code needs to go in either the control's BeforeUpdate event or the Form's BeforeUpdate event. I chose the control's BeforeUpdate event although if this were my project, I would use the form's BeforeUpdate event so that I could keep all the code regarding a single field in one place. When you put validation code in a control event, you MUST also put validation code in the Form's BeforeUpdate event if you also need to ensure that a field is not empty. Remember, if you never dirty a control, it's BeforeUpdate event will not fire and so you can never use the control events to check for null values. When you do arithmetic, you must use numeric data types. String is NOT numeric so that was the first code change. Then I removed the quotes from around the zero because you do not use quotes around numeric items. Quotes are used to delimit text data types, octothorps (#) are used to delimit date data types, and NO delimiter is used for numeric data types. The important change I made was to cancel the update, unless you cancel the update, you can raise error messages until the cows come home and they will not stop bad data from being saved. Only YOU can stop the save and you do that by issuing the cancel command. Notice that the AfterUpdate event does not have a Cancel argument. That's because at the time the AfterUpdate event runs, it's too late to stop the save
Code:
Private Sub Text48_BeforeUpdate(Cancel)
Dim NewQty As Integer
NewQty = Me.Text16 - Me.Text48
If NewQty < 0 Then
Me.Text54 = 0
MsgBox ("Insufficient Stock"), vbOKOnly
Cancel = True
Else
Me.Text54 = NewQty
End If
End Sub
Rather than using the KeyPress event, you should be using the Change event if you want to validate each character as it is typed or use the BeforeUpdate event to validate the entire value one time. When you use the Change event, remember, it fires once for EACH character and your code should be referenceing the .text property rather than the .value property when you use this event. Me.somefield.Text. Referencing a field as Me.somefield is the same as Me.somefield.Value since .value is the default property of a control.
