if [combobox] is empty then msgbox - not working (1 Viewer)

[flect]

Hi folks

I'm trying to do a bit of cleanup on my db at the moment and i'm running into a problem with a combo box on my form.

the main elements are the form - frmNavigation
the combo box in question - cmbDistributionOrder
and the button - btnNewOrder

What i'm trying to do is create a simple msgbox when someone clicks the button to create a new order but hasn't selected from the combo box - should be pretty straightforward but something's not working.

Private Sub btnNewOrder_Click()
Dim intanswer As Integer
If Forms!frmnavigation!cmbDistributionOrder is Null Then
    intanswer = _
        MsgBox("No Distribution selected, please select from list", vbInformation + vbOKOnly, "Select Distribution")

        Else
    
    Forms!frmnavigation.Visible = False
    DoCmd.Minimize
DoCmd.openForm ("tblOrder"), acNormal, , , acFormAdd
End If

cmbDistributionOrder is unbound with the row source

SELECT Distribution.DistributionNumber, Distribution.DistributionDate FROM Distribution ORDER BY Distribution.DistributionDate DESC;

The message box works fine if i change the code to work with a numerical value

e.g:

If Forms!frmnavigation!cmbDistributionOrder > 500

works, but obviously only after updating and that's not what i want to happen.

so i'm guessing 'null' isn't the correct thing to use for an empty combo box.

any ideas?

 

[flect]

UPDATE!!!!!

wow - 30 seconds after posting I figured it out :-D

correct command is

If isNull(Forms!frmnavigation!cmbDistributionOrder) Then

Private Sub btnNewOrder_Click()
Dim intanswer As Integer
If isNull(Forms!frmnavigation!cmbDistributionOrder) Then
    intanswer = _
        MsgBox("No Distribution selected, please select from list", vbInformation + vbOKOnly, "Select Distribution")

        Else
    
    Forms!frmnavigation.Visible = False
    DoCmd.Minimize
DoCmd.openForm ("tblOrder"), acNormal, , , acFormAdd
End If
End Sub

 

[dreamz]

Okay so i'm trying to get this to work. I copied the code, changed the form and title name but it keeps saying error.

any ideas?

Error = Expected End Sub

I havn't used access in 3years so i'm rusty.

 

[flect]

there should be

End Sub

at the end of it - must've missed it when i pasted it in.

 

[getout]

I had to do this to make mine work:

If IsNull(Forms!frmnavigation!cmbDistributionOrder) = True Then

 

[flect]

That seems strange,

you shouldn't need

 = True

as Null will never be true or false - only null.


that's where i got confused initially - it's a little different to SQL where you can use IS Null or IS NOT Null

the correct sytnax should be

Private Sub yourcontrol_Click()
If isNull(Forms!yourformname!yourcontrolname) Then
***DoWhatEver***
        Else
**WhateverElse***
End If
End Sub

 

[getout]

You're right. I don't know what I wasn't doing before not to make it work.

Thanks.

 

[hockey8837]

Hi,
I ended up using...

(>=[Forms]![frmReportGenerator]![cbozip] Or Like "*") And (<=[Forms]![frmReportGenerator]![cbozip] Or Like "*")

...in the query based off the combo box... seems to work okay!

 

[hockey8837]

Hi,
I ended up using...

(>=[Forms]![frmReportGenerator]![cbozip] Or Like "*") And (<=[Forms]![frmReportGenerator]![cbozip] Or Like "*")

...in the query based off the combo box... seems to work okay!

Actually, that was returning all records regardless of whether I selected a zip code. So, now I'm using:

Between Nz([Forms]![frmReportGenerator]![cbozip],"") And Nz([Forms]![frmReportGenerator]![cbozip],"ZZZZZZ")

 

[MayoRR5]

Guys, I've been trying for ages to get this message to appear but have had no joy.

Please help

Code:
Private Sub MTORec_IsoRev_DblClick(Cancel As Integer)
If Me.frm_MTOCtrl_DocRevCtrl.Form!QualType = "" Then
MsgBox ("You must select a document below in order to revise it!")

Else

Call MTORec_IsoRev_Part2

End If
End Sub

Also tried:-
If IsNull(Me.frm_MTOCtrl_DocRevCtrl.Form!QualType) Then
If IsNull(Me.frm_MTOCtrl_DocRevCtrl.Form!QualType) Or Me.frm_MTOCtrl_DocRevCtrl.Form!QualType = "" Then

It recognises when there an entry and will run through to the next sub seamlessly but I cannot show an error when there is no selection made in the combobox that controls this.

Thanks in advance for any help

 

[nanscombe]

How about...

If nz(Me.frm_MTOCtrl_DocRevCtrl.Form!QualType,"") = "" Then

Another option that I have only just seen mentioned today ...

If Len(Me.frm_MTOCtrl_DocRevCtrl.Form!QualType & vbNullString) = 0 Then

 

[pr2-eugin]

Or another preferred method would be to use the ListIndex property of the ComboBox to see if a selection is made..

Private Sub MTORec_IsoRev_DblClick(Cancel As Integer)
    If Me.frm_MTOCtrl_DocRevCtrl.Form!QualType.ListIndex = -1 Then
        MsgBox ("You must select a document below in order to revise it!")
    Else
        Call MTORec_IsoRev_Part2
    End If
End Sub

-1 is returned when no selection is made..

 

[MayoRR5]

OK guys, errors as follows,


Code1:-
If Me.frm_MTOCtrl_DocRevCtrl.Form!QualType.ListIndex = -1 Then
Error:-
Run-time error '438'
Object doesn't support this property or method


Code 2:-
If Nz(Me.frm_MTOCtrl_DocRevCtrl.Form!QualType, "") = "" Then
Error:-
Run-time error '-2147352567 (80020009)'
You entered an expression that has no value


Code3:-
If Len(Me.frm_MTOCtrl_DocRevCtrl.Form!QualType & vbNullString) = 0 Then
Error:-
Run-time error '2427'
You entered an expression that has no value


Code 1 returns same even when a selection has been made.

Codes 2 & 3 work perfectly when a selection has been made and call next sub.

 

[pr2-eugin]

Try the following code..

If Me[COLOR=Red][B]![/B][/COLOR]frm_MTOCtrl_DocRevCtrl.Form!QualType.ListIndex = -1 Then

 

[MayoRR5]

Returns error:-

Run-time error '2465'
Database can't find the field 'frm_MTOCtrl_DocRevCtrl' referred to in your expression



Nightmare lol

 

[pr2-eugin]

Okay where is the code Run from i.e. where is this MTORec_IsoRev control located in?? Is it from the Sub form or this control is on the Main form and the ComboBox control in the Subform or the other way around??

 

[MayoRR5]

VBA Control is on "top" form, while combobox is on subform, within "top" form.

Hope that makes sense.

 

[pr2-eugin]

I am sorry but it does not make any sense.. could you either take a snapshopt of what your form looks like or the details of what is what in your reply?

 

[MayoRR5]

Screenshot attached with notes.. Hope this helps.

 

[MayoRR5]

Still no joy with this guys. Any help is greatly appreciated.