Hi all.

This code:

Private Sub cboNamen_AfterUpdate()

Dim intRowCount As Integer
Dim dblLicenceFee As Double
Dim intSelectCount As Integer

dblLicenceFee = 0#
intSelectCount = 0

For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
If (Me![cboNamen].Selected(intRowCount) = True) Then
dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
intRowCount))
intSelectCount = intSelectCount + 1
End If
Next intRowCount

If (intSelectCount = 0) Then
MsgBox "No licence selected!", vbInformation
Else
Me![txtAvgLicence] = dblLicenceFee / intSelectCount
End If

End Sub

I put in the event you can see of a multi selectable combobox (Access 2007).
The code is OK, all records are filled, but in certain specific
circumstances (I know exactly when) I get an ‘Invalid use of Null’ error in
the line that adds the LicenceFee. But when I go to error correction and
press F5, the code continues and completes without any error.
I tried requerying the control, build in a loop (in case of a timing issue),
using other events, but I cannot get it fixed.
I feel it might be a bug in Access. I have created a specific database to
check the behavior. How can I get it to someone to confirm the behavior and
maybe get it to Microsoft?

Thanks,

Ronald.

The code's ok, but it could be better:

Private Sub cboNamen_AfterUpdate()

Dim dblLicenceFee As Double
Dim varSelected As Variant

dblLicenceFee = 0#

With Me![cboNamen]
If .ItemsSelected.Count = 0 Then
MsgBox "No licence selected!", vbInformation
Else
For Each varSelected In .ItemsSelected
dblLicenceFee = dblLicenceFee + CDbl(Nz(.Column(2, varSelected), 0))
Next varSelected
Me![txtAvgLicence] = dblLicenceFee / .ItemsSelected.Count
End If
End With
1
End Sub


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Ronald" <wit@yahoo> wrote in message
news:F1999383-751D-4C0A-B538-(E-Mail Removed)...
> Hi all.
>
> This code:
>
> Private Sub cboNamen_AfterUpdate()
>
> Dim intRowCount As Integer
> Dim dblLicenceFee As Double
> Dim intSelectCount As Integer
>
> dblLicenceFee = 0#
> intSelectCount = 0
>
> For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
> If (Me![cboNamen].Selected(intRowCount) = True) Then
> dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
> intRowCount))
> intSelectCount = intSelectCount + 1
> End If
> Next intRowCount
>
> If (intSelectCount = 0) Then
> MsgBox "No licence selected!", vbInformation
> Else
> Me![txtAvgLicence] = dblLicenceFee / intSelectCount
> End If
>
> End Sub
>
> I put in the event you can see of a multi selectable combobox (Access
> 2007).
> The code is OK, all records are filled, but in certain specific
> circumstances (I know exactly when) I get an 'Invalid use of Null' error
> in
> the line that adds the LicenceFee. But when I go to error correction and
> press F5, the code continues and completes without any error.
> I tried requerying the control, build in a loop (in case of a timing
> issue),
> using other events, but I cannot get it fixed.
> I feel it might be a bug in Access. I have created a specific database to
> check the behavior. How can I get it to someone to confirm the behavior
> and
> maybe get it to Microsoft?
>
> Thanks,
>
> Ronald.
>

Those Nulls can be tricky to deal with. Can you wrap your problematic Fields
with NZ()? Also, take a look at this:
http://articles.techrepublic.com.com...1-6125114.html

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Ronald" wrote:

> Hi all.
>
> This code:
>
> Private Sub cboNamen_AfterUpdate()
>
> Dim intRowCount As Integer
> Dim dblLicenceFee As Double
> Dim intSelectCount As Integer
>
> dblLicenceFee = 0#
> intSelectCount = 0
>
> For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
> If (Me![cboNamen].Selected(intRowCount) = True) Then
> dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
> intRowCount))
> intSelectCount = intSelectCount + 1
> End If
> Next intRowCount
>
> If (intSelectCount = 0) Then
> MsgBox "No licence selected!", vbInformation
> Else
> Me![txtAvgLicence] = dblLicenceFee / intSelectCount
> End If
>
> End Sub
>
> I put in the event you can see of a multi selectable combobox (Access 2007).
> The code is OK, all records are filled, but in certain specific
> circumstances (I know exactly when) I get an ‘Invalid use of Null’ error in
> the line that adds the LicenceFee. But when I go to error correction and
> press F5, the code continues and completes without any error.
> I tried requerying the control, build in a loop (in case of a timing issue),
> using other events, but I cannot get it fixed.
> I feel it might be a bug in Access. I have created a specific database to
> check the behavior. How can I get it to someone to confirm the behavior and
> maybe get it to Microsoft?
>
> Thanks,
>
> Ronald.
>

There are no Null values in the table!
When I print the value of the control in immediate mode, it is displayed.
And, like I said, when I press F5 the code continues without an error.

"ryguy7272" wrote:

> Those Nulls can be tricky to deal with. Can you wrap your problematic Fields
> with NZ()? Also, take a look at this:
> http://articles.techrepublic.com.com...1-6125114.html
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Ronald" wrote:
>
> > Hi all.
> >
> > This code:
> >
> > Private Sub cboNamen_AfterUpdate()
> >
> > Dim intRowCount As Integer
> > Dim dblLicenceFee As Double
> > Dim intSelectCount As Integer
> >
> > dblLicenceFee = 0#
> > intSelectCount = 0
> >
> > For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
> > If (Me![cboNamen].Selected(intRowCount) = True) Then
> > dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
> > intRowCount))
> > intSelectCount = intSelectCount + 1
> > End If
> > Next intRowCount
> >
> > If (intSelectCount = 0) Then
> > MsgBox "No licence selected!", vbInformation
> > Else
> > Me![txtAvgLicence] = dblLicenceFee / intSelectCount
> > End If
> >
> > End Sub
> >
> > I put in the event you can see of a multi selectable combobox (Access 2007).
> > The code is OK, all records are filled, but in certain specific
> > circumstances (I know exactly when) I get an ‘Invalid use of Null’ error in
> > the line that adds the LicenceFee. But when I go to error correction and
> > press F5, the code continues and completes without any error.
> > I tried requerying the control, build in a loop (in case of a timing issue),
> > using other events, but I cannot get it fixed.
> > I feel it might be a bug in Access. I have created a specific database to
> > check the behavior. How can I get it to someone to confirm the behavior and
> > maybe get it to Microsoft?
> >
> > Thanks,
> >
> > Ronald.
> >

Hi Douglas.

Thanks for your code! Any enhancements are always appreciated!
But it does not function correctly, the result is always 0.
In immediate mode the .ItemsSelected.Count does display the correct number
of items selected. But after the For Each line is processed the code
immediately goes to the ‘Me![txtAvgLicence] = dblLicenceFee /
..ItemsSelected.Count’ line, so no average is calculated.

Also my initial question still stands. Why do I get an ‘Invalid use of Null’
error while the table does not contain any empty fields? You use the Nz
function to trap the error but then you only get a wrong answer and don’t get
an indication anything is wrong.
Why when I press F5 (or F8) after the ‘Invalid use of Null’ error occurs,
code continues without any error! If there really is a Null value the error
should reappear!
I have a database dedicated to this error to prove it.

Any additional help or suggestion is very welcome.

Ronald.


"Douglas J. Steele" wrote:

> The code's ok, but it could be better:
>
> Private Sub cboNamen_AfterUpdate()
>
> Dim dblLicenceFee As Double
> Dim varSelected As Variant
>
> dblLicenceFee = 0#
>
> With Me![cboNamen]
> If .ItemsSelected.Count = 0 Then
> MsgBox "No licence selected!", vbInformation
> Else
> For Each varSelected In .ItemsSelected
> dblLicenceFee = dblLicenceFee + CDbl(Nz(.Column(2, varSelected), 0))
> Next varSelected
> Me![txtAvgLicence] = dblLicenceFee / .ItemsSelected.Count
> End If
> End With
> 1
> End Sub
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "Ronald" <wit@yahoo> wrote in message
> news:F1999383-751D-4C0A-B538-(E-Mail Removed)...
> > Hi all.
> >
> > This code:
> >
> > Private Sub cboNamen_AfterUpdate()
> >
> > Dim intRowCount As Integer
> > Dim dblLicenceFee As Double
> > Dim intSelectCount As Integer
> >
> > dblLicenceFee = 0#
> > intSelectCount = 0
> >
> > For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
> > If (Me![cboNamen].Selected(intRowCount) = True) Then
> > dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
> > intRowCount))
> > intSelectCount = intSelectCount + 1
> > End If
> > Next intRowCount
> >
> > If (intSelectCount = 0) Then
> > MsgBox "No licence selected!", vbInformation
> > Else
> > Me![txtAvgLicence] = dblLicenceFee / intSelectCount
> > End If
> >
> > End Sub
> >
> > I put in the event you can see of a multi selectable combobox (Access
> > 2007).
> > The code is OK, all records are filled, but in certain specific
> > circumstances (I know exactly when) I get an 'Invalid use of Null' error
> > in
> > the line that adds the LicenceFee. But when I go to error correction and
> > press F5, the code continues and completes without any error.
> > I tried requerying the control, build in a loop (in case of a timing
> > issue),
> > using other events, but I cannot get it fixed.
> > I feel it might be a bug in Access. I have created a specific database to
> > check the behavior. How can I get it to someone to confirm the behavior
> > and
> > maybe get it to Microsoft?
> >
> > Thanks,
> >
> > Ronald.
> >
>
>
> .
>

Not sure what you mean by "no average is calculated"

Are you sure that the value you need to be averaging is the third column of
your list box? (remember that the Column collection starts numbering at 0)

Are you sure that you've got the ColumnCount property of the list box set to
at least 3?

Are you sure you've got all of the periods in there correctly? Without the
With structure, the code would be

Private Sub cboNamen_AfterUpdate()

Dim dblLicenceFee As Double
Dim varSelected As Variant

dblLicenceFee = 0#

If Me![cboNamen].ItemsSelected.Count = 0 Then
MsgBox "No licence selected!", vbInformation
Else
For Each varSelected In Me![cboNamen].ItemsSelected
dblLicenceFee = dblLicenceFee + _
CDbl(Nz(Me![cboNamen].Column(2, varSelected), 0))
Next varSelected
Me![txtAvgLicence] = _
dblLicenceFee / Me![cboNamen].ItemsSelected.Count
End If

End Sub


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Ronald" <wit@yahoo> wrote in message
news:3606AB55-0CF6-4941-B3EE-(E-Mail Removed)...
> Hi Douglas.
>
> Thanks for your code! Any enhancements are always appreciated!
> But it does not function correctly, the result is always 0.
> In immediate mode the .ItemsSelected.Count does display the correct number
> of items selected. But after the For Each line is processed the code
> immediately goes to the 'Me![txtAvgLicence] = dblLicenceFee /
> .ItemsSelected.Count' line, so no average is calculated.
>
> Also my initial question still stands. Why do I get an 'Invalid use of
> Null'
> error while the table does not contain any empty fields? You use the Nz
> function to trap the error but then you only get a wrong answer and don't
> get
> an indication anything is wrong.
> Why when I press F5 (or F8) after the 'Invalid use of Null' error occurs,
> code continues without any error! If there really is a Null value the
> error
> should reappear!
> I have a database dedicated to this error to prove it.
>
> Any additional help or suggestion is very welcome.
>
> Ronald.
>
>
> "Douglas J. Steele" wrote:
>
>> The code's ok, but it could be better:
>>
>> Private Sub cboNamen_AfterUpdate()
>>
>> Dim dblLicenceFee As Double
>> Dim varSelected As Variant
>>
>> dblLicenceFee = 0#
>>
>> With Me![cboNamen]
>> If .ItemsSelected.Count = 0 Then
>> MsgBox "No licence selected!", vbInformation
>> Else
>> For Each varSelected In .ItemsSelected
>> dblLicenceFee = dblLicenceFee + CDbl(Nz(.Column(2, varSelected),
>> 0))
>> Next varSelected
>> Me![txtAvgLicence] = dblLicenceFee / .ItemsSelected.Count
>> End If
>> End With
>> 1
>> End Sub
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://www.AccessMVP.com/DJSteele
>> (no e-mails, please!)
>>
>> "Ronald" <wit@yahoo> wrote in message
>> news:F1999383-751D-4C0A-B538-(E-Mail Removed)...
>> > Hi all.
>> >
>> > This code:
>> >
>> > Private Sub cboNamen_AfterUpdate()
>> >
>> > Dim intRowCount As Integer
>> > Dim dblLicenceFee As Double
>> > Dim intSelectCount As Integer
>> >
>> > dblLicenceFee = 0#
>> > intSelectCount = 0
>> >
>> > For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
>> > If (Me![cboNamen].Selected(intRowCount) = True) Then
>> > dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
>> > intRowCount))
>> > intSelectCount = intSelectCount + 1
>> > End If
>> > Next intRowCount
>> >
>> > If (intSelectCount = 0) Then
>> > MsgBox "No licence selected!", vbInformation
>> > Else
>> > Me![txtAvgLicence] = dblLicenceFee / intSelectCount
>> > End If
>> >
>> > End Sub
>> >
>> > I put in the event you can see of a multi selectable combobox (Access
>> > 2007).
>> > The code is OK, all records are filled, but in certain specific
>> > circumstances (I know exactly when) I get an 'Invalid use of Null'
>> > error
>> > in
>> > the line that adds the LicenceFee. But when I go to error correction
>> > and
>> > press F5, the code continues and completes without any error.
>> > I tried requerying the control, build in a loop (in case of a timing
>> > issue),
>> > using other events, but I cannot get it fixed.
>> > I feel it might be a bug in Access. I have created a specific database
>> > to
>> > check the behavior. How can I get it to someone to confirm the behavior
>> > and
>> > maybe get it to Microsoft?
>> >
>> > Thanks,
>> >
>> > Ronald.
>> >
>>
>>
>> .
>>

I meant that the addition is not performed because the code immediately jumps
from the 'For Each ...' line to the 'Me![txtAvgLicence] = ...' line. So the
result always is 0.

I'm very sure column 2 is the right column. I checked it in immediate mode.
Column 0 is the checkbox of the multi select combobox and column 1 is the
name of the licencee.

The table in the rowsource of the combobox contains 5 records.

I also tried:
Dim ctlControl as ComboBox

Set ctlCombobox = Me![cboNamen]
With [ctlCombobox]
and so on but it makes no difference.

"Douglas J. Steele" wrote:

> Not sure what you mean by "no average is calculated"
>
> Are you sure that the value you need to be averaging is the third column of
> your list box? (remember that the Column collection starts numbering at 0)
>
> Are you sure that you've got the ColumnCount property of the list box set to
> at least 3?
>
> Are you sure you've got all of the periods in there correctly? Without the
> With structure, the code would be
>
> Private Sub cboNamen_AfterUpdate()
>
> Dim dblLicenceFee As Double
> Dim varSelected As Variant
>
> dblLicenceFee = 0#
>
> If Me![cboNamen].ItemsSelected.Count = 0 Then
> MsgBox "No licence selected!", vbInformation
> Else
> For Each varSelected In Me![cboNamen].ItemsSelected
> dblLicenceFee = dblLicenceFee + _
> CDbl(Nz(Me![cboNamen].Column(2, varSelected), 0))
> Next varSelected
> Me![txtAvgLicence] = _
> dblLicenceFee / Me![cboNamen].ItemsSelected.Count
> End If
>
> End Sub
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "Ronald" <wit@yahoo> wrote in message
> news:3606AB55-0CF6-4941-B3EE-(E-Mail Removed)...
> > Hi Douglas.
> >
> > Thanks for your code! Any enhancements are always appreciated!
> > But it does not function correctly, the result is always 0.
> > In immediate mode the .ItemsSelected.Count does display the correct number
> > of items selected. But after the For Each line is processed the code
> > immediately goes to the 'Me![txtAvgLicence] = dblLicenceFee /
> > .ItemsSelected.Count' line, so no average is calculated.
> >
> > Also my initial question still stands. Why do I get an 'Invalid use of
> > Null'
> > error while the table does not contain any empty fields? You use the Nz
> > function to trap the error but then you only get a wrong answer and don't
> > get
> > an indication anything is wrong.
> > Why when I press F5 (or F8) after the 'Invalid use of Null' error occurs,
> > code continues without any error! If there really is a Null value the
> > error
> > should reappear!
> > I have a database dedicated to this error to prove it.
> >
> > Any additional help or suggestion is very welcome.
> >
> > Ronald.
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> The code's ok, but it could be better:
> >>
> >> Private Sub cboNamen_AfterUpdate()
> >>
> >> Dim dblLicenceFee As Double
> >> Dim varSelected As Variant
> >>
> >> dblLicenceFee = 0#
> >>
> >> With Me![cboNamen]
> >> If .ItemsSelected.Count = 0 Then
> >> MsgBox "No licence selected!", vbInformation
> >> Else
> >> For Each varSelected In .ItemsSelected
> >> dblLicenceFee = dblLicenceFee + CDbl(Nz(.Column(2, varSelected),
> >> 0))
> >> Next varSelected
> >> Me![txtAvgLicence] = dblLicenceFee / .ItemsSelected.Count
> >> End If
> >> End With
> >> 1
> >> End Sub
> >>
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://www.AccessMVP.com/DJSteele
> >> (no e-mails, please!)
> >>
> >> "Ronald" <wit@yahoo> wrote in message
> >> news:F1999383-751D-4C0A-B538-(E-Mail Removed)...
> >> > Hi all.
> >> >
> >> > This code:
> >> >
> >> > Private Sub cboNamen_AfterUpdate()
> >> >
> >> > Dim intRowCount As Integer
> >> > Dim dblLicenceFee As Double
> >> > Dim intSelectCount As Integer
> >> >
> >> > dblLicenceFee = 0#
> >> > intSelectCount = 0
> >> >
> >> > For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
> >> > If (Me![cboNamen].Selected(intRowCount) = True) Then
> >> > dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
> >> > intRowCount))
> >> > intSelectCount = intSelectCount + 1
> >> > End If
> >> > Next intRowCount
> >> >
> >> > If (intSelectCount = 0) Then
> >> > MsgBox "No licence selected!", vbInformation
> >> > Else
> >> > Me![txtAvgLicence] = dblLicenceFee / intSelectCount
> >> > End If
> >> >
> >> > End Sub
> >> >
> >> > I put in the event you can see of a multi selectable combobox (Access
> >> > 2007).
> >> > The code is OK, all records are filled, but in certain specific
> >> > circumstances (I know exactly when) I get an 'Invalid use of Null'
> >> > error
> >> > in
> >> > the line that adds the LicenceFee. But when I go to error correction
> >> > and
> >> > press F5, the code continues and completes without any error.
> >> > I tried requerying the control, build in a loop (in case of a timing
> >> > issue),
> >> > using other events, but I cannot get it fixed.
> >> > I feel it might be a bug in Access. I have created a specific database
> >> > to
> >> > check the behavior. How can I get it to someone to confirm the behavior
> >> > and
> >> > maybe get it to Microsoft?
> >> >
> >> > Thanks,
> >> >
> >> > Ronald.
> >> >
> >>
> >>
> >> .
> >>
>
>
> .
>

Just to be certain. You're calling this "a multi selectable combobox", but
there's no such thing. Combo boxes do not have a MultiSelect property: only
list boxes do. Is this a combo box?


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Ronald" <wit@yahoo> wrote in message
news:19A81DED-0BAF-4A04-8FE0-(E-Mail Removed)...
>I meant that the addition is not performed because the code immediately
>jumps
> from the 'For Each ...' line to the 'Me![txtAvgLicence] = ...' line. So
> the
> result always is 0.
>
> I'm very sure column 2 is the right column. I checked it in immediate
> mode.
> Column 0 is the checkbox of the multi select combobox and column 1 is the
> name of the licencee.
>
> The table in the rowsource of the combobox contains 5 records.
>
> I also tried:
> Dim ctlControl as ComboBox
>
> Set ctlCombobox = Me![cboNamen]
> With [ctlCombobox]
> and so on but it makes no difference.
>
> "Douglas J. Steele" wrote:
>
>> Not sure what you mean by "no average is calculated"
>>
>> Are you sure that the value you need to be averaging is the third column
>> of
>> your list box? (remember that the Column collection starts numbering at
>> 0)
>>
>> Are you sure that you've got the ColumnCount property of the list box set
>> to
>> at least 3?
>>
>> Are you sure you've got all of the periods in there correctly? Without
>> the
>> With structure, the code would be
>>
>> Private Sub cboNamen_AfterUpdate()
>>
>> Dim dblLicenceFee As Double
>> Dim varSelected As Variant
>>
>> dblLicenceFee = 0#
>>
>> If Me![cboNamen].ItemsSelected.Count = 0 Then
>> MsgBox "No licence selected!", vbInformation
>> Else
>> For Each varSelected In Me![cboNamen].ItemsSelected
>> dblLicenceFee = dblLicenceFee + _
>> CDbl(Nz(Me![cboNamen].Column(2, varSelected), 0))
>> Next varSelected
>> Me![txtAvgLicence] = _
>> dblLicenceFee / Me![cboNamen].ItemsSelected.Count
>> End If
>>
>> End Sub
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://www.AccessMVP.com/DJSteele
>> (no e-mails, please!)
>>
>> "Ronald" <wit@yahoo> wrote in message
>> news:3606AB55-0CF6-4941-B3EE-(E-Mail Removed)...
>> > Hi Douglas.
>> >
>> > Thanks for your code! Any enhancements are always appreciated!
>> > But it does not function correctly, the result is always 0.
>> > In immediate mode the .ItemsSelected.Count does display the correct
>> > number
>> > of items selected. But after the For Each line is processed the code
>> > immediately goes to the 'Me![txtAvgLicence] = dblLicenceFee /
>> > .ItemsSelected.Count' line, so no average is calculated.
>> >
>> > Also my initial question still stands. Why do I get an 'Invalid use of
>> > Null'
>> > error while the table does not contain any empty fields? You use the Nz
>> > function to trap the error but then you only get a wrong answer and
>> > don't
>> > get
>> > an indication anything is wrong.
>> > Why when I press F5 (or F8) after the 'Invalid use of Null' error
>> > occurs,
>> > code continues without any error! If there really is a Null value the
>> > error
>> > should reappear!
>> > I have a database dedicated to this error to prove it.
>> >
>> > Any additional help or suggestion is very welcome.
>> >
>> > Ronald.
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> The code's ok, but it could be better:
>> >>
>> >> Private Sub cboNamen_AfterUpdate()
>> >>
>> >> Dim dblLicenceFee As Double
>> >> Dim varSelected As Variant
>> >>
>> >> dblLicenceFee = 0#
>> >>
>> >> With Me![cboNamen]
>> >> If .ItemsSelected.Count = 0 Then
>> >> MsgBox "No licence selected!", vbInformation
>> >> Else
>> >> For Each varSelected In .ItemsSelected
>> >> dblLicenceFee = dblLicenceFee + CDbl(Nz(.Column(2,
>> >> varSelected),
>> >> 0))
>> >> Next varSelected
>> >> Me![txtAvgLicence] = dblLicenceFee / .ItemsSelected.Count
>> >> End If
>> >> End With
>> >> 1
>> >> End Sub
>> >>
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://www.AccessMVP.com/DJSteele
>> >> (no e-mails, please!)
>> >>
>> >> "Ronald" <wit@yahoo> wrote in message
>> >> news:F1999383-751D-4C0A-B538-(E-Mail Removed)...
>> >> > Hi all.
>> >> >
>> >> > This code:
>> >> >
>> >> > Private Sub cboNamen_AfterUpdate()
>> >> >
>> >> > Dim intRowCount As Integer
>> >> > Dim dblLicenceFee As Double
>> >> > Dim intSelectCount As Integer
>> >> >
>> >> > dblLicenceFee = 0#
>> >> > intSelectCount = 0
>> >> >
>> >> > For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
>> >> > If (Me![cboNamen].Selected(intRowCount) = True) Then
>> >> > dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
>> >> > intRowCount))
>> >> > intSelectCount = intSelectCount + 1
>> >> > End If
>> >> > Next intRowCount
>> >> >
>> >> > If (intSelectCount = 0) Then
>> >> > MsgBox "No licence selected!", vbInformation
>> >> > Else
>> >> > Me![txtAvgLicence] = dblLicenceFee / intSelectCount
>> >> > End If
>> >> >
>> >> > End Sub
>> >> >
>> >> > I put in the event you can see of a multi selectable combobox
>> >> > (Access
>> >> > 2007).
>> >> > The code is OK, all records are filled, but in certain specific
>> >> > circumstances (I know exactly when) I get an 'Invalid use of Null'
>> >> > error
>> >> > in
>> >> > the line that adds the LicenceFee. But when I go to error correction
>> >> > and
>> >> > press F5, the code continues and completes without any error.
>> >> > I tried requerying the control, build in a loop (in case of a timing
>> >> > issue),
>> >> > using other events, but I cannot get it fixed.
>> >> > I feel it might be a bug in Access. I have created a specific
>> >> > database
>> >> > to
>> >> > check the behavior. How can I get it to someone to confirm the
>> >> > behavior
>> >> > and
>> >> > maybe get it to Microsoft?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Ronald.
>> >> >
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:(E-Mail Removed)...
> Just to be certain. You're calling this "a multi selectable combobox", but
> there's no such thing. Combo boxes do not have a MultiSelect property:
> only list boxes do. Is this a combo box?


Since this is Access 2007, maybe the combo box is bound to a mult-value
field.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

True, not the combobox itself is multi selectable.
The control source has been made multi selectable.
Table design - specific field - tab LookUp:
Control: Combobox
Rowsource: the right table
Allow multiple values: Yes
(I hope that's te correct text, have to translate it from Dutch).
To be honest, I didn't know this, but the person who came to me for help did.

When in form design, you draw the field in Add ... fields (Ribbon left to
Properties) to the form, automatically a combobox is added.

Still, the control is a combobox and I should not get the 'Invalid use of
Null' error.


"Douglas J. Steele" wrote:

> Just to be certain. You're calling this "a multi selectable combobox", but
> there's no such thing. Combo boxes do not have a MultiSelect property: only
> list boxes do. Is this a combo box?
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "Ronald" <wit@yahoo> wrote in message
> news:19A81DED-0BAF-4A04-8FE0-(E-Mail Removed)...
> >I meant that the addition is not performed because the code immediately
> >jumps
> > from the 'For Each ...' line to the 'Me![txtAvgLicence] = ...' line. So
> > the
> > result always is 0.
> >
> > I'm very sure column 2 is the right column. I checked it in immediate
> > mode.
> > Column 0 is the checkbox of the multi select combobox and column 1 is the
> > name of the licencee.
> >
> > The table in the rowsource of the combobox contains 5 records.
> >
> > I also tried:
> > Dim ctlControl as ComboBox
> >
> > Set ctlCombobox = Me![cboNamen]
> > With [ctlCombobox]
> > and so on but it makes no difference.
> >
> > "Douglas J. Steele" wrote:
> >
> >> Not sure what you mean by "no average is calculated"
> >>
> >> Are you sure that the value you need to be averaging is the third column
> >> of
> >> your list box? (remember that the Column collection starts numbering at
> >> 0)
> >>
> >> Are you sure that you've got the ColumnCount property of the list box set
> >> to
> >> at least 3?
> >>
> >> Are you sure you've got all of the periods in there correctly? Without
> >> the
> >> With structure, the code would be
> >>
> >> Private Sub cboNamen_AfterUpdate()
> >>
> >> Dim dblLicenceFee As Double
> >> Dim varSelected As Variant
> >>
> >> dblLicenceFee = 0#
> >>
> >> If Me![cboNamen].ItemsSelected.Count = 0 Then
> >> MsgBox "No licence selected!", vbInformation
> >> Else
> >> For Each varSelected In Me![cboNamen].ItemsSelected
> >> dblLicenceFee = dblLicenceFee + _
> >> CDbl(Nz(Me![cboNamen].Column(2, varSelected), 0))
> >> Next varSelected
> >> Me![txtAvgLicence] = _
> >> dblLicenceFee / Me![cboNamen].ItemsSelected.Count
> >> End If
> >>
> >> End Sub
> >>
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://www.AccessMVP.com/DJSteele
> >> (no e-mails, please!)
> >>
> >> "Ronald" <wit@yahoo> wrote in message
> >> news:3606AB55-0CF6-4941-B3EE-(E-Mail Removed)...
> >> > Hi Douglas.
> >> >
> >> > Thanks for your code! Any enhancements are always appreciated!
> >> > But it does not function correctly, the result is always 0.
> >> > In immediate mode the .ItemsSelected.Count does display the correct
> >> > number
> >> > of items selected. But after the For Each line is processed the code
> >> > immediately goes to the 'Me![txtAvgLicence] = dblLicenceFee /
> >> > .ItemsSelected.Count' line, so no average is calculated.
> >> >
> >> > Also my initial question still stands. Why do I get an 'Invalid use of
> >> > Null'
> >> > error while the table does not contain any empty fields? You use the Nz
> >> > function to trap the error but then you only get a wrong answer and
> >> > don't
> >> > get
> >> > an indication anything is wrong.
> >> > Why when I press F5 (or F8) after the 'Invalid use of Null' error
> >> > occurs,
> >> > code continues without any error! If there really is a Null value the
> >> > error
> >> > should reappear!
> >> > I have a database dedicated to this error to prove it.
> >> >
> >> > Any additional help or suggestion is very welcome.
> >> >
> >> > Ronald.
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> The code's ok, but it could be better:
> >> >>
> >> >> Private Sub cboNamen_AfterUpdate()
> >> >>
> >> >> Dim dblLicenceFee As Double
> >> >> Dim varSelected As Variant
> >> >>
> >> >> dblLicenceFee = 0#
> >> >>
> >> >> With Me![cboNamen]
> >> >> If .ItemsSelected.Count = 0 Then
> >> >> MsgBox "No licence selected!", vbInformation
> >> >> Else
> >> >> For Each varSelected In .ItemsSelected
> >> >> dblLicenceFee = dblLicenceFee + CDbl(Nz(.Column(2,
> >> >> varSelected),
> >> >> 0))
> >> >> Next varSelected
> >> >> Me![txtAvgLicence] = dblLicenceFee / .ItemsSelected.Count
> >> >> End If
> >> >> End With
> >> >> 1
> >> >> End Sub
> >> >>
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://www.AccessMVP.com/DJSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >> "Ronald" <wit@yahoo> wrote in message
> >> >> news:F1999383-751D-4C0A-B538-(E-Mail Removed)...
> >> >> > Hi all.
> >> >> >
> >> >> > This code:
> >> >> >
> >> >> > Private Sub cboNamen_AfterUpdate()
> >> >> >
> >> >> > Dim intRowCount As Integer
> >> >> > Dim dblLicenceFee As Double
> >> >> > Dim intSelectCount As Integer
> >> >> >
> >> >> > dblLicenceFee = 0#
> >> >> > intSelectCount = 0
> >> >> >
> >> >> > For intRowCount = 0 To Me![cboNamen].Recordset.RecordCount - 1
> >> >> > If (Me![cboNamen].Selected(intRowCount) = True) Then
> >> >> > dblLicenceFee = dblLicenceFee + CDbl(Me![cboNamen].Column(2,
> >> >> > intRowCount))
> >> >> > intSelectCount = intSelectCount + 1
> >> >> > End If
> >> >> > Next intRowCount
> >> >> >
> >> >> > If (intSelectCount = 0) Then
> >> >> > MsgBox "No licence selected!", vbInformation
> >> >> > Else
> >> >> > Me![txtAvgLicence] = dblLicenceFee / intSelectCount
> >> >> > End If
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> > I put in the event you can see of a multi selectable combobox
> >> >> > (Access
> >> >> > 2007).
> >> >> > The code is OK, all records are filled, but in certain specific
> >> >> > circumstances (I know exactly when) I get an 'Invalid use of Null'
> >> >> > error
> >> >> > in
> >> >> > the line that adds the LicenceFee. But when I go to error correction
> >> >> > and
> >> >> > press F5, the code continues and completes without any error.
> >> >> > I tried requerying the control, build in a loop (in case of a timing
> >> >> > issue),
> >> >> > using other events, but I cannot get it fixed.
> >> >> > I feel it might be a bug in Access. I have created a specific
> >> >> > database
> >> >> > to
> >> >> > check the behavior. How can I get it to someone to confirm the
> >> >> > behavior
> >> >> > and
> >> >> > maybe get it to Microsoft?
> >> >> >
> >> >> > Thanks,
> >> >> >
> >> >> > Ronald.
> >> >> >
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>