Code query help

[Kyosa]

i have a service code table of about 11,000 records on which i do calculations based on some values found there. i have another table that contains about 45 exceptions to the standard table.
basically i have an unbound combo box on a form that has the main table dynamically assigned as it's souce (based on other criteria). i need to know how i can check to see if the value picked in that combo box happens to appear in the exception table so i can then apply a different set of calculatin instructions.
i've tried everything i can think of and i think i'm about to go CRAZY! Any help would be most greatly appreciated!!!!!!!!!

 

[KMITCH]

Kyosa

Have you tried the DLookup function? I forget all of the
differend field events you can trigger off of but basically, you would want to execute the DLookup whenever
the value in the field changes. You can set it up
to check your exceptions table for a match to the value
in your combo box and then use the fact that you either
do or do not get a record returned as your decision point
in which calculation to proceed with. I can't get to
Access on my machine right now to give you the syntax
but the Access help files provide a few good examples.

Hope this helps.

Keith

 

[Kyosa]

Keith,
i've tried using dlookup but i'm doing something wrong i think because no matter how i try and define my criteria, it only returns the very first value in the exception table.

i need a drink!

 

[KMITCH]

Private Sub Combo0_Change()
Dim Bas1 As Long
[Forms]![Form1]![Combo0Result] = DLookup("Ex1", "tblException", "Ex1 = " & [Forms]![Form1]![Combo0])
End Sub


Kyosa,

I mocked it up and can send you the .mdb if you would
like. Basically, I created 2 tables: tblBase and
tblException. I have 1 form (Form1) which has a
ComboBox based on the field Bas1 in tblBase. The
above code triggers off of the On Change event of the
ComboBox and populates the form field Combo0Result if
any matches are found in tblException. You can modify
this by using "If Isnull(DLookup(xxx))" instead of
assigning the result to a field.

Hope this helps.
Keith

 

[Kyosa]

keith,

if you could send it to me that would rock. i keep getting a data type mismatch when i try it..damn thing is driving me up the wall. as near as i can tell everything is defined correctly . anyway, if you could send it to
jkyo@yahoo.com i would appreciate it!!!

thanks!