complex question

[Lauren B]

I am attempting to write a code so that when a command button is clicked, a
field in the form is automatically populated.

In the "On Click" event procedure, I want to have code that indicates the
following statement be entered into the field "Coverage Area":

ed^<jurisdiction>^ALL

I need the <jurisdiction> part of the statement to automatically be replaced
with the actual jurisdiction of the user. This particular field is located
on another table. Both the form that contains the command button and the
form that contains the field "jurisdiction" and subforms within a larger
form.

If everything was on one form, I would use the code:

Coverage Area = "ed^<" & [jurisdiction] & ">^ALL"

however, how can I refered a field (ie. jurisdication) when it is located on
a different table and essentially a different form?

To complicate things even more, each user has a jursidiction for ED
information and a jurisdiction for OTC information. The table that contains
this data has three fields: User_ID, Jurisdiction, and Type. When I write
the code for the aforemention command button, I need it to pull the
Jurisdiction where Type = ED. Is there a way to specify this?

Thank you very much for any assistance.

LB

 

[Guest]

Use the Code
Coverage Area = "ed^<" & DLookUp("[jurisdiction]","TableName","User_ID = '"
& User_ID & "' And Type = '" & Type & "'") & ">^ALL"
Basically, Look up the help on the DLookup function to get the correct 3rd
part of the function that is relevant for your form.