Code help please

[Gazza2]

First of all i hope this is in the right discussion and sorry for the long post
Hopefully someone can point me in the right direction as i cant figure this out.

I have a form used to add new customers which works fine for adding customers but if i get to the end and then want to cancel it the form still adds it.

Heres the code i have so far:

Select Case Me.Select1
Case "y"
DoCmd.GoToRecord , , acNewRec
MsgBox "New record added", vbOKOnly
Me.Select1 = Null
Me.AccountCode.SetFocus

Case "n"
MsgBox "Are you sure you want to delete this record", vbYesNo
If vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "Record Deleted", vbOKOnly
Me.AccountCode.SetFocus

End If

If vbNo Then
Me.Select1 = Null
Me.Select1.SetFocus
End If
Case Else
MsgBox "Please enter 'Y' or 'N' only", vbOKOnly
Me.Select1.SetFocus

End Select

Basically what is supposed to happen is i enter all the details into the text boxes on the form and i get to the last box which is an unbound textbox(select1) and i either enter y to add the customer details to the table and a message box pops up to say the record is added (got this working).
If i get to select1 and enter n then i want a message box to pop up asking if im sure i want to delete this record with the yes and no buttons, if i select yes on the message box then the record is deleted but if i select no then i want the record to still be there and the focus set on select1 so i can select any of the other text boxes to change the details and i also want another textbox to pop up if anything else is entered into select one or there is nothing entered atall to say "please enter y or n only"

Hope this sort of makes sense

Thanks
Gareth

 

[WayneRyan]

Gareth,

You have to:

Dim Response As Integer

Response = MsgBox(...)
If Response = vbYes Then ...

Your If statements are evaluating the static vbYes and vbNo, their values
will NEVER change.

Wayne

 

[Gazza2]

Thanks for the swift reply wayne,

changed the code as you said and got it sort of working

The problem im having is that it is still adding the record if i select the vbno button or if any other character is entered into the text box.

Is there a way of returning to the form as it was after the message box because if i try to use the previous record i am unable to make changes to it as it creates duplicate values.

Heres how ive changed the code :

Select Case Me.Select1
Dim response As Integer

Case "y"
DoCmd.GoToRecord , , acNewRec
Me.Select1 = Null
MsgBox "New record added", vbOKOnly
Me.AccountCode.SetFocus

Case "n"
response = MsgBox("Are you sure you want to delete this record", vbYesNo)

If response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
Me.Select1 = Null
MsgBox "Record Deleted", vbOKOnly
Me.AccountCode.SetFocus

End If

If response = vbNo Then
Me.Select1 = Null
Me.Select1.SetFocus
End If
Case Else
If IsNull(Me.Select1) Then
MsgBox "Please enter 'Y' or 'N' only", vbOKOnly
Me.Select1 = Null
Me.Select1.SetFocus
End If

End Select

 

[Mutdogus]

Gazza,
I'm guessing that you want to have this as some sort of cue to validate your current record manually by looking at all the values on the screen?

If so I'm wondering if can't validate your record automagically with VBA eliminating the need for this code?

I can give you a partial answer. Your last case else statement only does something if the value of select1 is null. So basically your code is doing nothing but running, it doesn't do anything when a value is something other than y or n. If it's null... then it gives you the message box below.

Case Else
If IsNull(Me.select1) Then
MsgBox "Please enter 'Y' or 'N' only", vbOKOnly
Me.select1 = Null
Me.select1.SetFocus
End If

One idea I had was to use a checkbox instead of a textbox and inspect the value of that. There can only be two values, -1 and 0. This way you don't have the "y" "n" "null" problems.

if me.Checkbox = true then
        DoCmd.RunCommand acCmdDeleteRecord
        Me.select1 = Null
        MsgBox "Record Deleted", vbOKOnly
        Me.AccountCode.SetFocus
else
        Me.select1 = Null
        Me.select1.SetFocus
end if

Also since you are using a yesno message box, your values can only be yes or no. You only need one if as shown below.

Case "n"
    
    Response = MsgBox("Are you sure you want to delete this record", vbYesNo)

    If Response = vbYes Then
        
        DoCmd.RunCommand acCmdDeleteRecord
        Me.select1 = Null
        MsgBox "Record Deleted", vbOKOnly
        Me.AccountCode.SetFocus

    Else

        Me.select1 = Null
        Me.select1.SetFocus

End If

Are you using a click event on a button to call this code? Or an event in select1?

 

[gemma-the-husky]

not sure 100% on this, but

a) assuming you have a bound form, then a new record is added to your cutomers table as soon as you start to edit the entry. So closing the form still saves the (incorrect) partial entry. If you havent entered some important REQUIRED field, you will instead get the "Access cant save this at this time" message, and it wont be saved

b) now since a bound form (even in data entry mode) will allow you to enter more than 1 new customer, you need to distinguish between

1) - previously entered and saved records (for which delete is probably OK) and
2) - the current NEW record, for which you probably need to us UNDO rather than delete.

 

[Gazza2]

first of all thanks mutdogus and gemma for the help.

Mutdogus, the code you suggested for the vbyes and vbno was perfect for what i want

The select1 textbox is used to enter either y to add the record or n to delete the record entry and the code is in the afterupdate event of select1.
The problem im having with it is that i cant figure out the code so that only y or n will actually do anything so that if any other character is entered or the enter key is pressed without entering anything into select 1 then a messagebox pops up telling the user that.

Gemma, the undo command sort of works but it actually deletes the record entry. Is there a way of returning to the form with the details that were still there, I understand that the record is already in the table but it is sorted alphabetically and using goto previous wont work.

Thanks
Gareth

 

[boblarson]

Is there a way of returning to the form with the details that were still there,

If you want that to happen then your validation and undo code must be in the form's BEFORE UPDATE event and if you want to cancel the update you issue a

Cancel = True
Me.Undo

to undo the changes

or just a

Cancel = True

to stop the update and go back to editiing

 

[Gazza2]

thanks for the reply bob that works a treat.

All i need now is a way of having a message box pop up if anything other than Y or N is entered into the final checkbox.

At the moment if the text box is empty or has any character in it when the enter key is pressed it adds the record.

i have a case else statement in the afterupdate event of the textbox with the following code :-Case Else
If IsNull(Me.Select1.Value) Then
MsgBox "Please enter 'Y' or 'N' only", vbOKOnly
End If

but this doesnt work.

Thanks for the help so far everyone.

 

[Gazza2]

Ok thanks for the help everyone ive nearly got it working but a small problem has now reared its ugly head.

At the moment i have the following cod in the FORM BEFOREUPDATE event :-

Private Sub Form_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False


Dim response As Integer

Select Case Me.Select1()

Case "y"
' DoCmd.RunCommand acCmdSaveRecord
' DoCmd.GoToRecord , , acNewRec
Me.Accountcode.SetFocus
Me.Select1 = Null
msgbox "New record added", vbOKOnly

Case "n"
response = msgbox("Are you sure you want to delete this record", vbYesNo)

If response = vbYes Then
Cancel = True
DoCmd.RunCommand acCmdDeleteRecord
msgbox "Record Deleted", vbOKOnly
Me.Select1 = Null

End If
If response = vbNo Then
Cancel = True
Me.Select1 = Null
Me.Accountcode.SetFocus
End If

Case "D"

DoCmd.OpenForm "FrmDeliveryAddress"

Case Else
msgbox "Y or N only", vbOKOnly
Cancel = True
Me.Select1.SetFocus
Me.Select1 = Null



End Select


DoCmd.SetWarnings True

End Sub


the problem is that the record doesnt save to the table. If you notice the two lines ive commented out, these are the ones that cause the error and if i take them out it just returns to the first texbox with the record entry still showing.

Hope that makes sense

Thanks
Gareth.