build function to lookup a number mltple flds 1

[dj2kx]

hi,

i'm bldg (or i think so) a function to lookup the number corresponding to an option selected in a combo box on a form. There are at least 64 such fields and, apparently, writing a dlookup proc for all of them would make code messy and a lot bigger..

Here's the code I have, and the error that I get when I call the function from an afterupdate event of a field is "Expected variable or procedure, not module"

Function LkupScore()

Dim str1, str2, strwhere As String
Dim ctrl1 As Control

str1 = ActiveControl.Value
str2 = ActiveControl.Name
ctrl1 = "S" & str2

strwhere = ("[parameter] = '" & str1 & "') AND ([frmprmnme] = '" & str2 & "'")

ctrl1.Value = DLookup("[score]", "score", strwhere)

End Function

Thanks in advance...

 

[MajP]

The error means you have the module named the same as the procedure. Is the module called LkupScore?

Also this does not look correct
strwhere = ("[parameter] = '" & str1 & "') AND ([frmprmnme] = '" & str2 & "'"

you have ( outside the string. I doubt that will compile

strWhere = (

Could you explain what this does, there may be a better way. 64 combos on a form calling a dlookup sounds a little strange, but there may be a good reason.

 

[MajP]

You can also get that error if the database name is the same as a procedure. If the db is called "Test" and you have a procedure called "Test" it will fail.

 

[dj2kx]

thnx for the reply, majp..

I have an evaluation form ,which evaluates students taking oral exams.

for e.g., I have a combo fld called "Oratory Skills", whre the options are: confident, satisfactory and poor. The scores are 10, 5 and 0 respectively. I have many such evaluation parameters on the form, where I need to lookup the score based on user input...

also, if the active field on the form is named "1-1", then the field that shall store the score is named "S1-1" (already in place for each parameter), hence the code:

ctrl1 = "S" & str2

thnx in advance... appreciate your advice.

 

[MajP]

I will use different names because I am not sure which is which.

tblScore
evalCategory
evalDesc
Score

example

Oratory Skills Confident 10
Oratory Skills Satisfactory 5
Oratory Skills Poor 0
Content Good 20
Content Satisfacotry 10
Content Boring 0


So your oratory skills combo would have as its rowsource

select score, evalDesc from tblScore where evalCategory = 'Oratory Skills'

if the combo properties are
bound column: 1
column count: 2
column widths: 0;1"