Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: combo box cannot be null-need to prevent

    In which control is it most programmatically correct to use If IsNull??????

    I have a dialog box[Update an Account] that lets user select an account name from a combobox(cboAccount), then it opens a form [UpdateAcct21709] with the account details for them to make changes to it.

    It has an UPDATE button(UpdateOKbtn) to bring them to UpdateAcct21709, and a cancel button(btnXUPDATE) to change their mind - which means they wouldn't use cboAccount at all

    The problem I have is - UPDATE button will open a blank UpdateAcct21709 if they do not pick a name in cboAccount. This is the On Click event for UPDATE:
    Private Sub UpdateOKbtn_Click()
    On Error GoTo Err_UpdateOKbtn_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "UpdateAcct21709"

    stLinkCriteria = "[ACCOUNT]=" & "'" & Me![cboAccount] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Update an Account"

    Exit_UpdateOKbtn_Click:
    Exit Sub

    Err_UpdateOKbtn_Click:
    MsgBox Err.Description
    Resume Exit_UpdateOKbtn_Click

    End Sub

    I went around and around - moving the If IsNull on differnet events, nothing works smoothly enough for the user.
    I tried 1.) to incorporate in the UpdateOKbtn On Click event, 2.) [UpdateAcct21709] On Load; 3.) [Update An Account] on Close

    For now, I have the following Event On Exit in cboAccount:
    Private Sub cboAccount_Exit(Cancel As Integer)
    If IsNull(Me.cboAccount) Then
    MsgBox "You must choose the account to update", vbOKOnly
    Me.cboAccount.SetFocus
    Cancel = True
    End If
    **********this does work, it returns user to combo box....BUT you can't choose the cancel button because the same thing happens - it sets focus back to cboAccount, overiding the btnXUPDATE code:
    Private Sub btnXUPDATE_Click()
    On Error GoTo Err_btnXUPDATE_Click


    DoCmd.Close

    Exit_btnXUPDATE_Click:
    Exit Sub

    Err_btnXUPDATE_Click:
    MsgBox Err.Description
    Resume Exit_btnXUPDATE_Click

    End Sub
    For now I have removed the cancel button. The dialog box is stuck unless cboAccount is entered and Update is chosen.

    Can anyone come up with the best scenario in which control it is most programmatically correct to use If IsNull??????

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    On your dialog form hide your Update and Cancel Buttons. When the user selects the Account Name, use the After Update event to display the buttons so that the user can make a choice.

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Test for Combo box

    Is this one complicated than I realize? The test for a combo box is:

    If Me.cboClass.ListIndex = -1 Then......

    I've never heard of using "isnull" to test whether a combobox item has been seleced. The above works flawlessly for me.


    John S.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by praxis1949 View Post
    I've never heard of using "isnull" to test whether a combobox item has been seleced.
    Sorry, but IsNull() is used every day in validation code for comboboxes as well as other controls! Which is not to say that it can't be done your way. It just usually isn't.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    The Curse of Nulls!

    Thanks for the comment. I make no claims of great expertise, but I have always come to grief playing with nulls. For example, could be me and Access 2003, but I found "isNull()" would not work properly in a function when a value was passed from a text box (to a module function) -- had to use a variant in the function to receive the value ("Public Function SQLFixup(TextIn As Variant) As String"). As for comboboxes, can't imagine a validation test where I would use isNull(), being as the listindex = -1 approach works perfectly (and logically, does non-selection of an item or loading nothing into a combobox constitute a null?).

    Regards

    John S

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by Poppa Smurf View Post
    On your dialog form hide your Update and Cancel Buttons. When the user selects the Account Name, use the After Update event to display the buttons so that the user can make a choice.
    I like this idea! Can you help me through some of the syntax ? Not familiar with this string at all

  7. #7
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Example:

    Private Sub cboClassList_Click()
    If Me.cboClass.ListIndex = -1 Then
    MsgBox "You must choose a class!", , "Guangxi University"
    cboClass.SetFocus
    Else
    DoCmd.OpenReport "rptByClass", acPreview

    End If

    End Sub

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    For this solution I will use two Command Buttons called btn_update to open your required form and btn_cancel when they change their mind.

    The combo box for the accounts I will call cmb_accounts

    Here is the syntax

    When the combo box gets the focus we need to hide the two buttons. This is done by setting the VISIBLE property of each button to FALSE on the GOTFOCUS event for the combo box.

    Private Sub cmb_accounts_GotFocus()
    btn_update.Visible = False
    btn_cancel.Visible = False
    End Sub


    After the user selects an account from the drop down list we now use the AFTERUPDATE event of the combo box to display the two buttons. This is done by setting the VISIBLE property of each button to TRUE.

    Private Sub cmb_accounts_AfterUpdate()
    btn_update.Visible = True
    btn_cancel.Visible = True
    End Sub

  9. #9
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by praxis1949 View Post
    Example:

    Private Sub cboClassList_Click()
    If Me.cboClass.ListIndex = -1 Then
    MsgBox "You must choose a class!", , "Guangxi University"
    cboClass.SetFocus
    Else
    DoCmd.OpenReport "rptByClass", acPreview

    End If

    End Sub
    Can you help me follow this - I'm not understanding how cboClassList became cbo.Class.ListIndex, and cbo.Class ? Are they 3 different controls?

    I am trying a variation of this to test 2 combo boxes called Zip and INTLZIP, but I'm not sure you can use " or " or "and" in this syntax.
    The scenario is:If Zip and INTLZIP are blank when you tab into the next control CONTACT - I would like a message, then to Set Focus back to Zip. (Neither combo boxes are required because user will only use one or the other, depending on address. So it has to be allowed that 1 of them is blank and 1 is not)
    This test is being done to prevent the user from getting out of the record w/o completing the address, not to open a form or report.
    If the test is positive - I don't want anything to happen, the user will be on CONTACT where they belong if they have completed the address by selecting either the Zip or INTLZIP

    This was my attempt = I put in the following event using CONTACT On Enter, but nothing happened at all. So I moved it to CONTACT On Got Focus - and nothing happened. I also tried it with just the first combobox


    Private Sub CONTACT_GotFocus()
    If Me.Zip = -1 Or Me.INTLZIP = -1 Then
    MsgBox "You must fill in the zip code or int'l zip code field before continuing", , "My Corporation"
    Zip.SetFocus
    Else
    Contact.SetFocus

    End If

    End Sub

    Can you suggest changes that would make this work for me?

  10. #10
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    The -1 test is always on the "listindex" property, and I checked how I use it -- its always (I think) is used off a button to test that something has been selected in required combo boxes.

    You code should read:

    If Me.Zip.listindex = -1 Or Me.INTLZIP.listindex = -1 Then...

    If this doesn't work, then perhaps something is happening in the event (getfocus()) that I don't understand.

    Regards
    John S

    Regards

    John S

  11. #11
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Private Sub CONTACT_GotFocus()
    If Me.Zip = -1 Or Me.INTLZIP = -1 Then
    MsgBox "You must fill in the zip code or int'l zip code field before continuing", , "My Corporation"
    Zip.SetFocus
    Else
    Contact.SetFocus

    End If

    End Sub
    Can you suggest changes that would make this work for me?
    Does the following happen when the Contact field gets the focus? When either one or both combo boxes are Null, the error message is the displayed and the cursor moves to the Zip combo box.

Posting Permissions

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