Devaultvalue Problem 1

[georgp]

Hi,

I would like to show as the defaultvalue the entry which has been selected most so far. How can I achieve this? I tried several ways, but it did not work... I guess, due to a mixture of logic and syntax problems.

Thanks, georgp

 

[Edski]

You should tell us the ways that you tried.

Anyway, I know at least one method that would work.

1. On the On Current Event for the Form write some code to get the mode for that field. Then just set the DefaultValue property for the field to be the result.

Private Sub Form_Current()
Me.myFLD1.DefaultValue = getMode(myFLD1)
Me.myFLD2.DefaultValue = getMode(myFLD2)
etc...
End Sub

Function getMode(fld as field)
'returns mode of given field
[green]<insert code to get the mode>[/green]
End Function


I'm not sure but you may be able to use a crosstab query to do the same thing. Perhaps someone else can offer a possible solution using queries.

 

[georgp]

Thanks, Edski, for the hint - I will try if it works for me.
I understand that I should disclose the code. It is a mess at this stage, however, and I will do so when I found the solution.
Georg

 

[Edski]

Hi georgp,
I think using SQL is probably the best way to do this, although it could also be done using recordsets.

[blue]Function[/blue] getMode(myFLD As String, myTable as String)
[green]' returns the mode of a given field from a given table[/green]
Dim db As Database, rs As Recordset, strSQL As String

Set db = CurrentDb

strSQL = &quot;SELECT DISTINCT &quot; & myFLD & &quot;, Count(&quot; & myFLD & &quot AS Freq&quot;
strSQL = strSQL & &quot; FROM &quot; & myTable
strSQL = strSQL & &quot; GROUP BY &quot; & myFLD
strSQL = strSQL & &quot; ORDER BY Count(&quot; & myFLD & &quot DESC;&quot;

Set rs = db.OpenRecordset(strSQL)

if rs.RecordCount>0 then
rs.MoveFirst
getMode = rs.Fields(0)
else
getMode = &quot;&quot; ' or Null
End If
rs.Close
Set db = Nothing
[blue]End Function[/blue]

 

[georgp]

Hello Edski,

terrific - thanks a lot. Actually, I would like to give you TWO stars. I have not looked into the detail, just checked the SQL - and that is exactly what I was looking for.
The unfortunate thing is that this is another occasion to come to the conclusion that I should leave programming to others!! You certainly have saved hours of unsuccessful work.

Thanks again, Georg