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 IfcmbDistributionOrder 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? :cool:

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


:)

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.

there should be

End Sub

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

I had to do this to make mine work:

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

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

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

Thanks.

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!

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")