Combo Box that returns most frequent data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a flightlog database that has a combo box on the "New Flight"
entry form that allows the user to choose a duty symbol for that flight (i.e.
"PI", "IP"). What I would like to be able to do is run a code from the
default value function of the combo box that will query the "Duty Symbol"
column of the underlying table and return the most frequently input entry as
the default value for the combo box. In effect the database will adapt to
the user. Any ideas would be greatly appreciated.
 
This will take more code than I have time to write at the moment, but let me
give you a conceptual idea of how you can do this. You will have to write a
function that will be called from the Default Value property. Here is what
that function will have to do:

Create an two dimensional Array with the same number of rows as the number
of items in the combo box.

Put the bound column value of each row in the combo box in the array.
aryList(0,0) = Me.MyCombo.itemdata(0)

Do a For Next loop through the array. In the loop, do a DCount on the table
and you want to find the most frequent values using the value in the current
element of the array as the criteria. Put the results in the second element
of the array.

strItem = array(x,0) = Nz(DCount("[MyField]", "MyTable", "[MyField] = '" &
strItem & "'"),0)

Look through the array and find the highest count.

Dim lngHighCount as Long
Dim lngHighIndex as Long
lngHighCount = 0
lngHighIndex = 0
For x = 0 to UBound(aryList)
If aryList(x,1) > lngHighCount Then
lngHighCount = aryList(x,1)
lngHighIndex = x
End If
Next x

Then return the value of the list

MyFunction = aryList(x,0)
 
How about putting as the source of the combo box a SUM Query

Group by the field you want
Count id or what ever in that table is always there.

Use that query as source query and sort descending on the count

You can even pull in both fields so they can see the value and the #
times.
 
Thanks for the input, Klatuu. I will give that code a shot and see if it
produces the results that I want. The biggest problem is that the RowSource
for the combo box that I am trying to set a default for is a subquery run
from within the form based on the value of another combo box. I may be
trying to do more than my limited knowledge of Access is suited.

Klatuu said:
This will take more code than I have time to write at the moment, but let me
give you a conceptual idea of how you can do this. You will have to write a
function that will be called from the Default Value property. Here is what
that function will have to do:

Create an two dimensional Array with the same number of rows as the number
of items in the combo box.

Put the bound column value of each row in the combo box in the array.
aryList(0,0) = Me.MyCombo.itemdata(0)

Do a For Next loop through the array. In the loop, do a DCount on the table
and you want to find the most frequent values using the value in the current
element of the array as the criteria. Put the results in the second element
of the array.

strItem = array(x,0) = Nz(DCount("[MyField]", "MyTable", "[MyField] = '" &
strItem & "'"),0)

Look through the array and find the highest count.

Dim lngHighCount as Long
Dim lngHighIndex as Long
lngHighCount = 0
lngHighIndex = 0
For x = 0 to UBound(aryList)
If aryList(x,1) > lngHighCount Then
lngHighCount = aryList(x,1)
lngHighIndex = x
End If
Next x

Then return the value of the list

MyFunction = aryList(x,0)


Chris said:
I am creating a flightlog database that has a combo box on the "New Flight"
entry form that allows the user to choose a duty symbol for that flight (i.e.
"PI", "IP"). What I would like to be able to do is run a code from the
default value function of the combo box that will query the "Duty Symbol"
column of the underlying table and return the most frequently input entry as
the default value for the combo box. In effect the database will adapt to
the user. Any ideas would be greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top