CODEWITHSUNDEEP
ms-accessformsms-access-2007vba
myClone
myClone

Reputation: 1739

If [ComboBox] Is Null Statement in VBA/Access 2007

I have a form with one ComboBox (YearToBeBuilt) and two textBox fields (Cost and YearofExpenditureCost). All controls are linked to a main table and the table is updated once the selections/entries have been made on the form.

I have written a procedure in VB called ReCalcIt()which performs the following procedure when called: 

Private Sub ReCalcIt()
If Me.YearToBeBuilt = "" Then
    Me.YearofExpenditureCost = Me.Cost
Else
    Me.YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
End If
End Sub

When I wrote this I thought that this would do the following:

If the ComboBox [YearToBeBuilt] is blank (e.g.-no selection made) then the textbox [YearOfExpenditureCost] will return the value of the TextBox [Cost]. Else, the calculation for YearofExpenditureCost is performed.

But this is not working the way it should

What am I doing wrong? I am a VBA n00b so perhaps my syntax is incorrect?

Upvotes: 2

Views: 30835

Answers (3)

CristiC
CristiC

Reputation: 22698

Try it with 

If Len(Me.YearToBeBuilt & vbNullString) = 0

So the code will look like this: 

Private Sub ReCalcIt()
If Len(Me.YearToBeBuilt & vbNullString) = 0 Then
    Me.YearofExpenditureCost = Me.Cost
Else
    Me.YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
End If
End Sub

Upvotes: 4

Dr Xorile
Dr Xorile

Reputation: 1009

It seems to me that this might be dependent on the format of the combo box? If it's a number, then 

If Len(Me.YearToBeBuilt & vbNullString) = 0

doesn't work. Because the Me.YearToBeBuilt has a value of 0, and so the above returns 1.

I'm wondering if there is a more robust method? Maybe

Me.YearToBeBuilt.ListIndex = -1

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91356

The usual way is to say:

If IsNull(MyControl) Then

See also: Access VBA: If Form Value <> NULL then run query else, end if. - not doing anything

Upvotes: 0

Related Questions