Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: Combo box on a form

    Hi I have a combo box on a form named "shift" I populate the field by a value list. I want to stop the user from leaving the field blank. I have tried using the validate property but cant get it to work. I have tried code

    Private Sub Shift_BeforeUpdate(Cancel As Integer)

    If Me.Shift = "" Or IsNull Then
    MsgBox "You Must enter a valid Shift here"
    Cancel = True
    End If

    End Sub

    This does not work either.

    Not sure what to try next any help would be great

    Thanks Bob

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to understand what you try to achieve however, this is syntactically incorrect:
    Code:
    If Me.Shift = "" Or IsNull Then
    The correct syntax is:
    Code:
    If Me.Shift = "" Or IsNull(Me.Shift) Then
    You could also use:
    Code:
    If Nz(Me.Shift,"") = "" Then
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You cannot use the Shift_BeforeUpdate event, as the user can simply do nothing with the Combobox and the event won't fire! This kind of Validation (to verify that a Control has actually been populated) has got to go in the Form_BeforeUpdate event. This will do the job:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     If Nz(Me.Shift, "") = "" Then
       MsgBox "You Must Enter a Valid Shift!"
       Cancel = True
       Shift.SetFocus
     End If
    End Sub

    BTW, Validation that can be done in the BeforeUpdate event of a Control, would be Validation to check whether or not the data that was actually entered was appropriate. Things like 'is the ID Number that was entered 6 characters long?' or 'are the first 3 characters alpha and the remaining characters numeric?

    Linq ;0)>
    Last edited by Missinglinq; 03-31-13 at 19:12.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2012
    Posts
    56

    Thanks Missinglinq

    Hi its almost where I want to be. I get the validation message boxes ok but when I hit the ok it wants to move to my tab control and cant because the record is not saved yet so stuck between the to any more thoughts.

    Thanks Bob

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry, but I don't understand what "it wants to move to my tab control" means. When you hit OK the only thing that should happen is that the saving of the Record is canceled and Focus is returned to the Combobox, awaiting the user's correction of the problem.

    Can you copy and paste the actual code you're using?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Oct 2012
    Posts
    56

    Update on combo box on form

    Thanks Ling,

    Here is the code I have as per your suggestion on the Sub_beforeUpdate event

    Code
    ----------------------------------------
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Nz(Me.Shift, "") = "" Then
    Forms!frmShiftMain!Shift.SetFocus
    MsgBox "You Must Enter a Valid Shift!"
    Cancel = True

    End If

    End Sub
    -------------------------------------------

    I have a tab control on which I have several subforms. I use the following code to use the return key instead of the tab key to navigate to the first subform and all the other subforms this is consistent throughout my application.

    Code
    --------------------------------------------
    Private Sub TabCtl18_KeyUp(KeyCode As Integer, Shift As Integer)

    If KeyCode <> conNavKey Then
    Forms!frmShiftMain!FsubShiftBarLiquor.SetFocus
    End If

    End Sub
    ----------------------------------------------

    It stops on the Forms!frmShiftMain!FsubShiftBarLiquor.SetFocus

    Not sure why

    Thanks Bob

Posting Permissions

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