Store a value from DLookup in table?

[bgotis]

I am using the DLookUp function to automatically fill in a user's initials when their name is selected in a combo box on my form. The function works fine, and the initials are automatically filling in on my form. The problem is, they aren't storing in my table!

I tried setting up a non-visible text box with the control set to the field in the table that I want to store the initials in and having the default value set to = the value in the lookup field - not working.

How do I get my form to store the initials in the table???

 

[pdx_man]

Don't use the default value. That is for new records. Just set the control's value

me.InitField.value = dlookup(...)

have the control source property for the text box be the field that you want to store it in the table

 

[bgotis]

Thanks for your response. A question:

When I set the value, where am I setting it? (I'm using Access 97) I tried writing the code you suggested as BeforeUpdate for the InitField and also as an AfterUpdate for the Name field. Neither worked - any other suggestions?

Thanks for your help -- *and* your patience!

 

[pdx_man]

hmmm, that should work in the BeforeUpdate field.

First, I assume the combo box is unbound. (No field value in the Control Source property)
Next, the field in the table is on the form, and I am calling it ItitField.

That should work (I have tested it on a pretend table/form) Check again. You can send me your database if you want, or I can send you my sample database.

 

[Rich]

If you already have the users name in a table why are you storing them in another?

 

[bgotis]

Reply to Rich:

Hi Rich,

I have a table with the names of the department members. I have another table where I am storing the information about what projects have been assigned to each member of the department. We want to do lookups on those projects based on the initials of the person the project is assigned to. To simplify the data entry, when we enter the new project information, I want to select the person's name from a combo box and have the selection in that field automatically update the Initials field (and consequently the project table).

Does that make sense?

 

[bgotis]

PDX:

Ruh-Roh.... I'm confused!

The Combo Box to pull up the full name is bound to the Name field in the project table. It looks up the names from the Names table, and stores the values in the projects table under "Assigned to."

Does that clarify?

Again, I appreciate your help. I fear I sound less than bright here!

 

[pdx_man]

Are you extracting the initials from the name?

Initl = left([Assigned to],1) & mid([Assigned to],Instr([Assigned to]," ")+1,1)


?

 

[pdx_man]

Are you extracting the initials from the name?

Initl = left([Assigned to],1) & mid([Assigned to],Instr([Assigned to]," ")+1,1)


?

 

[bgotis]

Eureka!

Figured out how to store the value - it is rough, but it works. I added another field to the form and bound it to the Initials field in the Names Table. I wrote a macro for the DLookup field that only works if it is set to run On Exit. The command is to goto the Dlookup Field, select all, copy, go to the new field, paste, then go on to the next field in the form. The new field *must* be set to Visible in Properties for this to work, so I had to play with the formatting so it won't really show (set the background/text colors to match the form).

PDX, thanks for your input on this. You helped me learn some new stuff, and gave me different ideas on where to go.