Edit user name

[accessnewb]

Hi all,
I have a combo box which displays all the users from a table. Based on the value in the combo box, a textbox gets automatically updated. For example, if I am selecting John in the combobox, then the textbox also says John. I am using the textbox to edit userName.

Now the problem is, the Textbox is bound to the UserName field in the table. So when I type "Johnny" in the box, then it immediately gets updated in the table. I want it to get updated only when I click on the update button.



I understand this is happening cos the textbox is bound to a field in the table. I am also aware that I can make the textbox "unbound" and write code on click of the update button to manually update the table (by using a query). But if I make the textbox as an unbound field, then the value doesn't change based on what I select in the comboBox.

So it is possible to make the value in the textbox change based on combobox selection and also update the field only onClick of a button?

[June7]

Use the AfterUpdate event of the combobox to set value of the unbound textbox.

[accessnewb]

I did! This is the macro I used:

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


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

But still, I need to make the textbox bound to a field in the table for it to automatically update based on combo values :-(

[June7]

Unbound textbox, use code in the button click to set the value of the field in the form's RecordSource.

Me!fieldname = Me.textboxname

This form is bound to the table of user info, right?

I only use VBA, no macros.

[accessnewb]

Yep! It is bound to a field in user info table. Alright, I tried your method. I made textbox unbound and used this code:

Private Sub txtUserName_Click()
Me!strEmpName = Me.txtUserName
End Sub

where stEmpName is the name of the field in the table and txtUserName is the name of the textbox.

Doesn't work . The textbox is not updated based on combo values

[June7]

Should be Click event of a button, not the textbox.

I want to be clear - the form's RecordSource is the user info table?

[accessnewb]

yep, the forms recordsource is the user info table. Btw, just a doubt. If i write code on the click event of a button, then won't the value in the textbox get updated only on click of the button? I want the value to be updated when the comboBox value changes

[June7]

I want it to get updated only when I click on the update button.

I offered advice based on this statement. Please clarify what you want to do.

Combobox sets unbound textbox

Button click sets field of record source

[accessnewb]

Sorry that I am not being clear. The comboBox should affect the unbound textbox. Like, if I select the user "Lisa" in the ComboBox, then the value "Lisa" should appear in the textbox. If I change the value Lisa to "LisaLee" or something, and click the update button, then the value should get saved in the DB.

Hope I am being clear. Sorry again for all the confusion.

[June7]

Yes, that is what I have suggested.

I thought you already had code (the macro you posted) that sets the value of the textbox to the name. I looked at the macro again and not sure it is doing that. As I said, I only use VBA, not macros.

Show me the RowSource SQL for the combobox.

[accessnewb]

This is the row source:

SELECT Login.lngID, Login.strEmpName
FROM Login;


How do I convert my macro code to VBA?

[June7]

With form or report in design view, select Convert Macros to VBA from the ribbon. Or just delete the macro and in the Event property select [Event Procedure], double click the ellipses (...) to go to the the VBA procedure.

This code should be in the combobox AfterUpdate

Me.textbox = Me.combobox.Column(1)

This will get the name from second column of the combobox. Column index starts with 0.

[accessnewb]

oh great! I tried converting macro to vb code and I got this error:

"There was a problem opening the macro "[Embeded Macro]". Do you want to continue?"

I checked online and found that this is a bug with access 2010 and microsoft is currently fixing it

http://answers.microsoft.com/en-us/o...b-d52b9bd0994a

So hmm...looks like I manually need to convert it. Sorry to bother u again, but how do I 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????

[accessnewb]

Alright, that simple one line code worked:

Me.textbox = Me.combobox.Column(1).

Thanks!

Now how do I convert the macro to vba??

[June7]

Create VBA procedure by:
With form in design view, in the AfterUpdate event property select [Event Procedure], double click the ellipses (...) to go to the the VBA procedure, type the code.