Selecting a corresponding table field based on text field.

[michaeljohnh]

For some reason cant quite wrap my brain around what should be a relatively simple problem.

Scenario:
User enters their login name in a field on a form. The form name is Login and the field is Text1. User clicks on a button, called Command1. Based on Text1, the form looks at the matching Associate field (ie, user enters michael and the record where Associate field is michael is selected) and its corresponding Pref1 field both which are in Table2. The Pref1 field holds a variable that determines which form opens up.

I presume the above is done with VBA for the Onclick event for Command1. What would the code look like? I realize a combo box would be easier than a text field but Im trying to make the db a little more secure by not displaying everyones login name.




Thanks in advance.

[ajetrumpet]

when you are doing something like this, the best thing is probably to write a universal function that you can pass variables to. for example, the following function takes a "criteria" variable. the criteria is used to determine which form is opened:

public function whichform( criteria as string)

   if criteria = "mark" then
      docmd.openform "form1"
   else
      docmd.openform "form2"
   end if

end function

make sense?

[michaeljohnh]

I do understand basicly what youre saying about using a public function to store the value which makes a lot of sense. But the core problem I am having isnt so much the storing of the information but the association of information.

So what I dont understand how to do is to take the user entered text field, compare it to the user name field in a table and then extract from the table the corresponding Pref1 field.

[ajetrumpet]

take the user entered text field, compare it to the user name field in a table

dlookup("username", "table", "[username] = '" & me.UserTextField & "'")

and then extract from the table the corresponding Pref1 field.

dlookup("pref1", "table", "[username] = '" & me.UserTextField & "'")

so, in essence, use the second code.

you're looking up based on input. the second piece of code is just fine.

(hth)

[michaeljohnh]

Perfect! Thank you very much. Conceptually I knew what I needed to do but could not find the right command and syntax. I gave you the rep boost. Hopefully you level up .

[ajetrumpet]

you level up .

only if we're playing dragon warrior on NES. good luck sir.