Set Value macro

[pedro]

Hi,

I have created a combo box [Name] on a form based on a table that contains a
Name and NameID field. The combo box contains only the name field and a user
can select a name from the drop down list.

I have created a text box beside the combo box that I would like to auto
populate with the NameID value using an after update macro based on the name
selected from the combo box. I would need to fire a set value action but I am
not sure what to put in the Expression box. The name of the control on the
form I am populating is called [NameID].

cheers

 

[Steve Schapel]

Pedro,

This is an irregular thing to be doing, and I suspect it is not a good idea.

You should not be using a macro at all here. Make your combobox so it
includes both fields (if it doesn't already do so). Adjust the
combobox's Column Count property to 2, and its Column Widths property so
it only shows the Name. And then in the other textbox, which should be
an unbound textbox, set the Control Source property to the equivalent of:
=[Name].[Column](1)

I may have the details wrong here, as I am not clear what your purpose
is, but this is the type of concept to shoot for. Let us know if you
need more help with it.

By the way, as an aside, 'name' is a Reserved Word (i.e has a special
meaning) in Access, and as such should not be used as the name of a
field or control. I suggest changing this if you can.

 

[Ian]

Pedro,

Steve's recommendation on making the combo box show the full name on the
drop-down (so that the user can see what full name the initials/short name
correspond to when selecting) is definitely good. However, it sounds like
you want to display the NameID on the screen once a Name has been established
(I assume this has to do with assigning accounts to different employees or
something similar).

This is pretty common, and can be done using a DLookup function to return
NameID from the table where you have stored the list of Names and
corresponding NameIDs, where Name equals the Name currently present on the
form. Just make a text box next to the combo box, and enter the Dlookup
function in it. I could write out the full function and syntax, but it's
easiest to check out Microsoft's description of the DLookup function.

Hope that helps.

Ian

Pedro,

This is an irregular thing to be doing, and I suspect it is not a good idea.

You should not be using a macro at all here. Make your combobox so it
includes both fields (if it doesn't already do so). Adjust the
combobox's Column Count property to 2, and its Column Widths property so
it only shows the Name. And then in the other textbox, which should be
an unbound textbox, set the Control Source property to the equivalent of:
=[Name].[Column](1)

I may have the details wrong here, as I am not clear what your purpose
is, but this is the type of concept to shoot for. Let us know if you
need more help with it.

By the way, as an aside, 'name' is a Reserved Word (i.e has a special
meaning) in Access, and as such should not be used as the name of a
field or control. I suggest changing this if you can.

--
Steve Schapel, Microsoft Access MVP

Hi,

I have created a combo box [Name] on a form based on a table that contains a
Name and NameID field. The combo box contains only the name field and a user
can select a name from the drop down list.

I have created a text box beside the combo box that I would like to auto
populate with the NameID value using an after update macro based on the name
selected from the combo box. I would need to fire a set value action but I am
not sure what to put in the Expression box. The name of the control on the
form I am populating is called [NameID].

cheers