Form coding help please

[Gazza]

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.

 

[AccessVandal via AccessMonster.com]

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]

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]

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:

 

[Gazza]

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

 

[AccessVandal via AccessMonster.com]

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:

 

[Gazza]

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

 

[Gazza]

Ok one last problem with this form.

When i finish entering data and get to the select textbox i can press the
enter key and it adds the record even though there is nothing entered into
the textbox.

Is there any way to stop this from happening.

Thanks
Gareth

 

[strive4peace]

Hi Gareth,

use TAB instead of ENTER -- or set the default behavior for the ENTER
key to go to the next control, not the next record


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*