Form coding help please Gazza 11/30/2008 8:54:04 PM I am having a few form coding problems and i hope someone can help. Basically i have the form which is used to add new customers to a table. The first text box (accountcode) works how its supposed to in that i enter an accountcode and it goes to the next box if the code doesnt already exist,but it will not let you goto the next box until a code is entered. The postcode textbox however is giving me a serious headache. If the user presses the enter key whilst in the text box but has not entered anything then a message box should display saying that a postcode must be entered, this doesnt work atall and just adds the record to the table and goes to a new record. And finally the very last text box is used to enter the record into the table, delete the record entry or return to the beggining of the record to change some details. The problem im having with this is that the record is also added to the table if any other character is entered into the text box or if it is just entered through. I just need a text box to pop up sying that either Y or N only to be entered and return to the text box without adding the record. Thanks in advance and sorry for the long and awkward post.

Re: Form coding help please "AccessVandal via AccessMonster.com" <u18947[ at ]uwe> 12/1/2008 1:28:06 AM I your case, I would suggest that you use the Form’s Before Update Event. If the user leave a blank in the textbox or input gibberish, how or what do your code handle the input? Or use the textbox BeforeUpdate event? Or Why not a Combo Box? Give us the code. Gazza wrote: [Quoted Text] >I am having a few form coding problems and i hope someone can help. > >Basically i have the form which is used to add new customers to a table. >The first text box (accountcode) works how its supposed to in that i enter >an accountcode and it goes to the next box if the code doesnt already >exist,but it will not let you goto the next box until a code is entered. > >The postcode textbox however is giving me a serious headache. If the user >presses the enter key whilst in the text box but has not entered anything >then a message box should display saying that a postcode must be entered, >this doesnt work atall and just adds the record to the table and goes to a >new record. > >And finally the very last text box is used to enter the record into the >table, delete the record entry or return to the beggining of the record to >change some details. The problem im having with this is that the record is >also added to the table if any other character is entered into the text box >or if it is just entered through. I just need a text box to pop up sying that >either Y or N only to be entered and return to the text box without adding >the record. > >Thanks in advance and sorry for the long and awkward post. -- Please Rate the posting if helps you Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: Form coding help please Gazza 12/1/2008 9:44:07 PM At the moment i have tried the following code in the forms beforeupdate event for the postcode textbox problem If Me.Postcode = "" Or IsNull(Me.Postcode) Then MsgBox "POSTCODE MUST BE ENTERED", vbOKOnly Me.Postcode.SetFocus End If and i have used the following code in the beforeupdate and afterupdate event of the final textbox If Me.Select1 = "" Or Null Then MsgBox "Please enter 'Y' or 'N' only", vbOKOnly End If Select Case Select1 Case "y" MsgBox "New record added", vbOKOnly Me.Select1 = Null DoCmd.GoToRecord , , acNewRec Me.Accountcode.SetFocus Case "n" Dim response As Integer response = MsgBox("Are you sure you want to delete this record", vbYesNo) If response = vbYes Then DoCmd.RunCommand acCmdDeleteRecord MsgBox "Record Deleted", vbOKOnly Me.Select1 = Null Me.Accountcode.SetFocus End If If response = vbNo Then Cancel = True Me.Select1 = Null Me.Accountcode.SetFocus End If Case Else MsgBox "Please enter 'Y' or 'N' only", vbOKOnly Me.Undo End Select "AccessVandal via AccessMonster.com" wrote: [Quoted Text] > I your case, I would suggest that you use the Form’s Before Update Event. > > If the user leave a blank in the textbox or input gibberish, how or what do > your code handle the input? Or use the textbox BeforeUpdate event? Or Why not > a Combo Box? Give us the code. > > Gazza wrote: > >I am having a few form coding problems and i hope someone can help. > > > >Basically i have the form which is used to add new customers to a table. > >The first text box (accountcode) works how its supposed to in that i enter > >an accountcode and it goes to the next box if the code doesnt already > >exist,but it will not let you goto the next box until a code is entered. > > > >The postcode textbox however is giving me a serious headache. If the user > >presses the enter key whilst in the text box but has not entered anything > >then a message box should display saying that a postcode must be entered, > >this doesnt work atall and just adds the record to the table and goes to a > >new record. > > > >And finally the very last text box is used to enter the record into the > >table, delete the record entry or return to the beggining of the record to > >change some details. The problem im having with this is that the record is > >also added to the table if any other character is entered into the text box > >or if it is just entered through. I just need a text box to pop up sying that > >either Y or N only to be entered and return to the text box without adding > >the record. > > > >Thanks in advance and sorry for the long and awkward post. > > -- > Please Rate the posting if helps you > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1 > >

Re: Form coding help please "AccessVandal via AccessMonster.com" <u18947[ at ]uwe> 12/2/2008 1:38:56 AM [Quoted Text] >At the moment i have tried the following code in the forms beforeupdate event >for the postcode textbox problem You'll need to cancel beforeupdate event. If Me.Postcode = "" Or IsNull(Me.Postcode) Then MsgBox "POSTCODE MUST BE ENTERED", vbOKOnly Cancel = True ‘<<<<< Me.Postcode.SetFocus End If >and i have used the following code in the beforeupdate and afterupdate event >of the final textbox Still, you need to cancel if this one is in the before update event. If Me.Select1 = "" Or Null Then MsgBox "Please enter 'Y' or 'N' only", vbOKOnly Cancel = True ‘<<<<< End If Is this one in the afterupdate event on a control? It does not have an argument, so you cannot use it in the afterupdate event to cancel. Use it in the beforeupdate event. Select Case Select1 Case "y" MsgBox "New record added", vbOKOnly Me.Select1 = Null DoCmd.GoToRecord , , acNewRec Me.Accountcode.SetFocus Case "n" Dim response As Integer response = MsgBox("Are you sure you want to delete this record", vbYesNo) If response = vbYes Then DoCmd.RunCommand acCmdDeleteRecord MsgBox "Record Deleted", vbOKOnly Me.Select1 = Null Me.Accountcode.SetFocus End If If response = vbNo Then Cancel = True Me.Select1 = Null Me.Accountcode.SetFocus End If Case Else MsgBox "Please enter 'Y' or 'N' only", vbOKOnly Me.Undo End Select Textbox does not control the user input for the Postcode. There’s nothing to stop gibberish input by the users. Here a site on using NotInList event. http://www.fontstuff.com/access/acctut20.htm If you want to use the form’s beforeupdate event, here’s a suggested sample. Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Postcode = "" Or IsNull(Me.Postcode) Then MsgBox "POSTCODE MUST BE ENTERED", vbOKOnly Cancel = True ‘<<<<< Me.Postcode.SetFocus Exit Sub ‘exit the subroutine Else If Me.Select1 = "" Or Null Then 'this code may not be neccessary MsgBox "Please enter 'Y' or 'N' only", vbOKOnly Cancel = True ‘<<<<< Exit Sub ‘exit the subroutine Else Select Case Select1 Case "y" MsgBox "New record added", vbOKOnly Me.Select1 = Null DoCmd.GoToRecord , , acNewRec Me.Accountcode.SetFocus Case "n" Dim response As Integer ‘lines below here. I’ll leave it to you. You can optimize the code. response = MsgBox("Are you sure you want to delete this record", vbYesNo) If response = vbYes Then DoCmd.RunCommand acCmdDeleteRecord MsgBox "Record Deleted", vbOKOnly Me.Select1 = Null Me.Accountcode.SetFocus End If If response = vbNo Then Cancel = True Me.Select1 = Null Me.Accountcode.SetFocus Exit Sub ‘exit the subroutine End If Case Else MsgBox "Please enter 'Y' or 'N' only", vbOKOnly Cancel = True ‘<<<<< Me.Undo ‘>>>undo form entry? End Select End If End If End Sub Gazza wrote: -- Please Rate the posting if helps you Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: Form coding help please Gazza 12/3/2008 12:01:01 AM thanks accessvandal for the help that works ok. just another quick question I have a form bound to a table with about ten textboxes with the first one (accountcode) as the primary key in the table being required. I have tried the following code in the before and afterupdate events of the textbox with no luck If Me.Accountcode = "" Or IsNull(Me.Accountcode) Then MsgBox "A valid accountcode must be entered before continuing", vbOKOnly Cancel = True Me.Undo End If I want the message box to pop up if the user presses the enter or tab key before entering data into the textbox. At the moment it just moves to the next field on the form and then gives me the primary key cannot contain a null value at the end of the form which i would like to stop. Thanks in advance Gareth

Re: Form coding help please "AccessVandal via AccessMonster.com" <u18947[ at ]uwe> 12/3/2008 3:26:31 AM [Quoted Text] >thanks accessvandal for the help that works ok. >just another quick question >I have a form bound to a table with about ten textboxes with the first one >(accountcode) as the primary key in the table being required. I have tried >the following code in the before and afterupdate events of the textbox with >no luck The textbox afterupdate event does not have an argument, so you can’t use Cancel=True. If Me.Accountcode = "" Or IsNull(Me.Accountcode) Then MsgBox "A valid accountcode must be entered before continuing", vbOKOnly Cancel = True ‘<<<<< remove this Me.Undo End If >I want the message box to pop up if the user presses the enter or tab key >before entering data into the textbox. >At the moment it just moves to the next field on the form and then gives me >the primary key cannot contain a null value at the end of the form which i >would like to stop. >Thanks in advance > Gareth You can use the control’s Exit or LostFocus event. I would choose on Exit because it has an Cancel argument. If Me.Accountcode = "" Or IsNull(Me.Accountcode) Then MsgBox "A valid accountcode must be entered before continuing", vbOKOnly Cancel = True Me.Undo ‘undo form entry? Me.Accountcode.SetFocus End If Gazza wrote: -- Please Rate the posting if helps you Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: Form coding help please Gazza 12/5/2008 9:00:26 PM Thanks accessvandal the code works a treat. Its also helpful when you know which event to put the code in so thanks for all the help. Gareth