Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Value In Field (A2000)

    On a parts booking subform, I'm trying to make the following code work.
    If the user enters a nett cost and there is no description in the [Item] field, I need the message box to popup with the following options.
    VBYes = setfocus in the [Item] Field.
    VBNo = backspace the nett field so no value added.
    By this the user cannot enter a nett value unless he has a description.
    Can any one help with the part of the code causing the problem :-
    ------------------------------------------------------------------------------------
    If Me.Item = Null Then
    ------------------------------------------------------------------------------------



    Private Sub Nett_AfterUpdate()
    If Me.Item = Null Then
    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String

    strMsgPrompt = "You have No Part Description, Do You Want To Add One Now"
    strMsgTitle = "!!"

    intButType = vbYesNo + vbCritical + vbDefaultButton1
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbYes Then
    Forms!frmParts!Item.SetFocus

    Else
    SendKeys "{backspace}"
    End If
    End If
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Value In Field (A2000)

    Dave,

    In the first place, you should replace

    If Me.Item = Null Then

    by

    If IsNull(Me.Item) Then

    In the second place, I would suggest using the BeforeUpdate event instead of the AfterUpdate event. It has a Cancel argument you can set to True if you want to cancel the update.

    The code would be something like

    Private Sub Nett_BeforeUpdate(Cancel As Integer)
    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If IsNull(Me.Item) Then
    strMsgPrompt = "You have No Part Description, Do You Want To Add One Now?"
    strMsgTitle = "!!"
    intButType = vbYesNo + vbQuestion
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbYes Then
    Forms!frmParts!Item.SetFocus
    Else
    Cancel = True
    End If
    End If
    End Sub

    As an alternative, you can defer the checking until the BeforeUpdate event of the form.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    I've been not been able to make it work on the Before_Update but can on the After_Update with the following::-

    Private Sub Nett_AfterUpdate()
    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If IsNull(Me.Item) Then
    strMsgPrompt = "You have No Part Description, Do You Want To Add One Now?"
    strMsgTitle = "!!"
    intButType = vbYesNo + vbCritical
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbYes Then
    Me.Item.SetFocus
    Me.Nett = 0


    Else
    Me.Nett = 0

    End If
    End If
    End Sub

    This still leaves a re-dundant record.
    Can the Me.Nett=0 be changed to something that will delete the record if the user selects No ??

    Thanks
    Dave

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Value In Field (A2000)

    Dave,

    Is this for new records only? If it's also for existing records, it seems a bit drastic to delete the record if the user leaves Item empty.

    You might experiment with

    Me.Undo

    Regards,
    Hans

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    I was just afraid if the user entered a value in the [Nett] instead of the [Retail], I wanted a deterrant on the No option.

    Dave

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Value In Field (A2000)

    To give a formal answer: the instruction to delete the current record is

    RunCommand acCmdDeleteRecord

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    also, if it is so critical, why not disable the nett cost field until the Description field had been completed?

    Peter

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    Bat17
    Hmmmmmm

    Had a look at this with the following.

    If IsNull (Me.Item) then
    Me.Nett.Enabled=False
    Me.Retail.Enabled=False
    End If

    The problem is the form is a datasheet subForm, the whole column becomes dis-abled instead of the current row.

    Hans,
    tried your last delete comment, the cursor go's to the next field in the row.
    I've changed it a little bit to try and completely remove the record.
    I feel something simple is going on here preventing me from doing this.
    Any other suggestions are welcome.


    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If IsNull(Me.Item) Then
    strMsgPrompt = "You Cannot Enter A Nett Value Without A Part Description !!"
    strMsgTitle = "!!"

    intButType = vbOKOnly + vbCritical + vbDefaultButton1
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbOKOnly Then
    RunCommand acCmdDeleteRecord

    End If
    End If

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    I think the fact that I have calculated fields within the form, this is preventing me from Cancelling or Undoing the record.
    The Calc fields are ness to show Individual profits on each part.
    Back to the drawing board I think, unless some-one has any further advice.
    Dave

    I solved it with this, it's very effective and won't let you exit the field unless a part is added.

    Private Sub Item_Exit(Cancel As Integer)

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer

    If IsNull(Me!Item) Or Me!Item = "" Then
    strMsg = "You Cannot Proceed Unless You Have A Part Description."
    strTitle = "!!"
    intStyle = vbOKOnly
    MsgBox strMsg, intStyle, strTitle
    Cancel = True
    End If

    End Sub


    Thanks all once again.

    Dave

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Value In Field (A2000)

    Dave,

    You want code that fires if somebody enters a value in Nett while Item is empty. But what if the user enters something in Item, then in Nett, and finally deletes the entry in Item? The AfterUpdate event of Nett won't catch that. You can add another event procedure for Item, but it might be better to handle it at the form level.

    I have attached a zipped Access 97 database that illustrates two variants of this approach (try the two forms based on tblTest).

    Another idea might be to set a validation rule on the table. Try tblTestWithValidation.

    Regards,
    Hans
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    Thanks for the attachment Hans.

    The Undo action is what I needed.

    Dave

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Value In Field (A2000)

    Hans
    Your Undo version works perfectly.
    I have copied your code exactly but when implementing I get this error, (See Attach)

    The code halts on the [Item].SetFocus

    The calculated fields have the following in them.
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Value In Field (A2000)

    Dave,

    Don't worry, we all have to learn. From the picture, I suspect that you put the code in the BeforeUpdate event of the control (because it states that there is a problem with saving the field). That will lead to trouble, because the SetFocus interferes with the update of the control that has not yet taken place. You can use the AfterUpdate event of the control, but for reasons I explained in a previous reply, I think the check should be in the BeforeUpdate event of the form. If you did put it there, maybe there is some leftover code on the control level that is causing the problem (control-level BeforeUpdate and AfterUpdate events will be handled before the form-level events).

    Regards,
    Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •