Handling an empty combobox

Question

Hi I am trying to autogenerate reports based on answers in comboboxes.

I am trying to check if the combobox is empty but not having success... Here is my code both address and username are comboboxes...

    Option Compare Database
Option Explicit


Private Sub generateReport_Click()

    Dim address As String
    Dim UserName As String
    
    
    
    address = Me.custAddress
    UserName = Me.UserName
    
    
    
     If Len(address.Value Or UserName.Value) > 0 Then
         MsgBox "Please Choose a Form Name!", vbOKOnly
         Me.custAddress.SetFocus
    
    End If
    Debug.Print (address)
    
    
    
    DoCmd.OpenReport "Generation", acViewPreview, address & UserName, _
        WhereCondition:="AddressLine1 = '" & address & UserName & "'"
    
   
    
End Sub

Edited version:

Option Compare Database
Option Explicit


Private Sub generateReport_Click()

    
    
    If custAddress.Value = vbNullString Or UserName.Value = vbNullString Then
        Debug.Print ("fail")
    Else
        DoCmd.OpenReport "Generation", acViewPreview, Me.custAddress & Me.UserName, _
        WhereCondition:="AddressLine1 = '" & Me.custAddress & Me.UserName & "'"
    End If
    
End Sub

`If address = vbNullString Or UserName = vbNullString Then` or you can take out the string variables and just `If custAddress.Value = vbNullString Or UserName.Value = vbNullString Then` — Raymond Wu, Aug 21 '21 at 13:02
Thanks it worked although I got another error "Invalid qualifier" which I found out was due to the click function beeing private? Can You explain why — Jerry, Aug 21 '21 at 13:07
not exactly sure but remove `Dim UserName As String` statement if you haven't, you are confusing it since you have a combobox named `UserName` too or rename that variable to something else — Raymond Wu, Aug 21 '21 at 13:16
Oh I see but I have just changed the code to be a bit better I think although it wont jump in the if statement to print fail in the immediate window. I posted the edited code in main question under edited. — Jerry, Aug 21 '21 at 13:27
Step through the code and at the `If statement`, check the result of `?custAddress.Value = vbNullString` in the immediate window to see if it's True, same for `?UserName.Value = vbNullString` — Raymond Wu, Aug 21 '21 at 13:30
They return as false and it will never step into the if statement I dont really get it tbh — Jerry, Aug 21 '21 at 13:35
Print out the value of both comboboxes' value and see what is it giving? @Jerry — Raymond Wu, Aug 21 '21 at 13:38
Sorry they return null. And ?custAddress.Value = vbNullString + ?UserName = vbNullString are both returning as null — Jerry, Aug 21 '21 at 13:40
Perhaps it's a space? Try Len function on both values and see if you want to change your If conditions to that criteria — Raymond Wu, Aug 21 '21 at 13:42
That worked thanks gotta practice some more not used to debugging in VBA just started two days ago — Jerry, Aug 21 '21 at 13:44
Usually comparison to vbNullString should work but perhaps its an Access thing (which i have never use before) — Raymond Wu, Aug 21 '21 at 13:46
Does this answer your question? [Combobox null in if statement](https://stackoverflow.com/questions/19929186/combobox-null-in-if-statement) — Raymond Wu, Aug 21 '21 at 13:52
I tried using that earlier before I asked the question since I got a null error 94 i think or 97 — Jerry, Aug 21 '21 at 13:59

score 1 · Answer 1 · answered Aug 21 '21 at 16:22

You will either have a Filter or a WhereCondition, so try:

Option Compare Database
Option Explicit

Private Sub GenerateReport_Click()

    Dim Wherecondition  As String
    
    If IsNull(Me!custAddress.Value + Me!UserName.Value) Then
        MsgBox "Please Choose a Form Name!", vbOKOnly
        Me!custAddress.SetFocus
    Else
        ' Seems like a strange address line, though ...'
        Wherecondition ="AddressLine1 = '" & Me!custAddress.Value & Me!UserName.Value & "'" 
        DoCmd.OpenReport "Generation", acViewPreview, , WhereCondition
    End If
    
End Sub

Handling an empty combobox

1 Answers1