Help with DCount/DLookup

[Isaac]

I am still working on my database of work performance for the company
I work for. I have a form (frmtblOccurrences) based on a table
(tblOccurrences). I also have tblAgent that contains personal info on
each agent (e.g., their ID number, hire date, etc.

The results for each occurrence are sequential: coaching, oral
warning, written warning, final warning, termination

The results are entered into frmtblOccurrences whenever there is an
occurrence.

What I want is a msgbox, after the agent's name is entered (via a
combo box) that says "The next result level is ... (the result level
one higher than where they were)

I also have a results table which is the source for the combobox on
the form where the results are entered. I don't need for the program
to set the combobox to the next level.

So, in English, when an agent's name is entered (pulled down from
combobox) , and the most recent result was "oral warning" i want the
msgbox to display "The next result level is written warning." The
user will then set the results field on the form to written warning.

Again, I'm new to this, I can do a little bit in VBA, I don't
understand recordsets and how to use them at all.

Any help is appreciated!

Isaac

[Allen Browne]

DLookup() is rather limited: you cannot specify the order of the records. It
just returns the first match it finds. Since you need to define "which is
the next record", you may be able to use this extended replacement called
ELookup():
http://allenbrowne.com/ser-42.html

An alternative approach might be to use a subquery to get the desired value.
If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Isaac" <im***@sio.midco.netwrote in message
news:11*********************@a26g2000pre.googlegro ups.com...

>I am still working on my database of work performance for the company
I work for. I have a form (frmtblOccurrences) based on a table
(tblOccurrences). I also have tblAgent that contains personal info on
each agent (e.g., their ID number, hire date, etc.

The results for each occurrence are sequential: coaching, oral
warning, written warning, final warning, termination

The results are entered into frmtblOccurrences whenever there is an
occurrence.

What I want is a msgbox, after the agent's name is entered (via a
combo box) that says "The next result level is ... (the result level
one higher than where they were)

I also have a results table which is the source for the combobox on
the form where the results are entered. I don't need for the program
to set the combobox to the next level.

So, in English, when an agent's name is entered (pulled down from
combobox) , and the most recent result was "oral warning" i want the
msgbox to display "The next result level is written warning." The
user will then set the results field on the form to written warning.

Again, I'm new to this, I can do a little bit in VBA, I don't
understand recordsets and how to use them at all.

Any help is appreciated!

Isaac

[Gary Floam]

You want something like
NextLevel = DMin("WarningLevelKey", "WarningLevels","WarningLevelKey>" &
CurrentWanringLevel)
If Not(IsNull(NextLevel) then
NextLevelMessage = DLookup("WarningLevelMessage", "WarningLevels",
"WarningLevelKey=" & NextLevel"
Else
NextLevelMessage = "No Higher Level"
EndIf
Msgbox NextLevelMessage

Hope this helps,

Gary

"Isaac" <im***@sio.midco.netwrote in message
news:11*********************@a26g2000pre.googlegro ups.com...

>I am still working on my database of work performance for the company
I work for. I have a form (frmtblOccurrences) based on a table
(tblOccurrences). I also have tblAgent that contains personal info on
each agent (e.g., their ID number, hire date, etc.

The results for each occurrence are sequential: coaching, oral
warning, written warning, final warning, termination

The results are entered into frmtblOccurrences whenever there is an
occurrence.

What I want is a msgbox, after the agent's name is entered (via a
combo box) that says "The next result level is ... (the result level
one higher than where they were)

I also have a results table which is the source for the combobox on
the form where the results are entered. I don't need for the program
to set the combobox to the next level.

So, in English, when an agent's name is entered (pulled down from
combobox) , and the most recent result was "oral warning" i want the
msgbox to display "The next result level is written warning." The
user will then set the results field on the form to written warning.

Again, I'm new to this, I can do a little bit in VBA, I don't
understand recordsets and how to use them at all.

Any help is appreciated!

Isaac