Edit user name

[accessnewb]

umm..what I meant to ask is. I am not sure how to convert this macro code:



SetTempVar
Name ActiveControlValue
Expression = [Screen].[ActiveControl]


SearchForRecord
Object Type
Object Name
Record First
where condition =="[lngID]=" & [TempVars]![ActiveControlValue]


to vb code! The syntax is not the same for both. This is the code I used in vb to convert the macro (wrote the code manually as access doesn't support macro to code conversion):


Private Sub cmbEditUser_AfterUpdate()
Me.txtUserName = Me.cmbEditUser.Column(1)
TempVars("ActiveControlValue") = Screen.ActiveControl
DoCmd.SearchForRecord , "", "", "[lngEmpID] = " & "[TempVars]![ActiveControlValue]"
End Sub

But I am getting an error stating "Tempvars can only store data. They cannot store objects"

[June7]

I don't know what the TempVars structure is for nor why you need to do the Search. What are you trying to do?

[accessnewb]

I have another hidden textbox called 'lngEmpID' and based on the value in the textbox, I have written a query. Like, update field set field.value = "blah" where empId = lngEmpID. So I need the value in lngEmpID also to be automatically generated based on comboBox values. But when I use the code
Me.lngEmpID = Me.combobox.Column(1)
for the lngEmpID field, I get the error "Run time error: You can't assign a value to this object"

But the macro helped in automatically updating the lngEmpID field. So I wanted to convert that to vb code.

[June7]

First of all should be Me.lngEmpID = Me.cmbEditUser (no column reference needed and Column(1) gets the name, not ID - index starts with 0). Not sure why can't update the hidden textbox. Is it unbound? Is there an expression in ControlSource?

Second, the ID is available in the combobox, why the hidden textbox?

Third, you want to update a value in another table? Why? What value?

[accessnewb]

The ID is not available in the combo box, Only the name is available. Like if the name "Jessica" has the Id "10", then the name "Jessica" is displayed in the comboBox and "10" is displayed in the hidden textbox.

So, basically what happens is, based on what the user selects in teh comboBox, the hidden textbox ID gets updated and also the userName textbox gets updated (this is currently working now). User can change the value in the userName textbox and click an update button. The update button has the query I mentioned above. I want the code to update values based on userID, because the comboBox has only a name and 2 (or more) users can share the same name.

Hope I am being clear!

[June7]

The combobox does have the ID. It is in the RowSource SQL statement. It is just that the ID column is hidden because of the 0 width. But can still get the ID value from combobox. The BoundColumn is the actual value of the combobox, regardless of the display. Because first column is the ID and is also the BoundColumn and it is the ID that desired, Column(0) reference not needed.

[accessnewb]

err, I am sorry, but I didn't understand that. Should I use column(0) to get the user name?

so my query would be:

update field set field.value = "blah" where empId = "Me.combobox.Column(0)"

[June7]

Column indexing begins with 0.

The RowSource SQL returns fields in this order: lngID, strEmpName

Column(0) gets lngID
Column(1) gets strEmpName

I still don't understand why you want to do this update.

[accessnewb]

yayy! It works! Just one quick question, what do I do to display column(0). I mean, when I click on the comboBox, I should see, empID|empName. I know I gotta set something in properties, but not sure what.

thanks a ton btw!

[June7]

ColumnWidths property

If you want to show the ID then change value from 0 to whatever.

[accessnewb]

Works!! thanks again! Am gonna mark this as solved :-)