Using VBA to Assign value to control

[jkfeagle]

I have been pulling my hair out (OK, I would be if I had enough to do so) over trying to assign a value to a control on a form. Basically, when a change is made to the form, I'm using the AfterUpdate event to trigger the code. The code in the event is

Me.txtWho = Environ("UserName")

Simple, straightforward or so I thought. Problem is, it gives me an error stating I cant' assign a value to that object. txtWho is bound to a field in the query underlying the form and the control is enabled and not locked. Can anyone shed some light on this for me please????

 

[RuralGuy]

You should be using the BeforeUpdate event of the form for such code.

 

[jkfeagle]

What I'm wanting to do is if the user makes any changes to the data in the form, his username gets tagged to the record. Would that effect being able to assign the value?

 

[jkfeagle]

I went ahead and moved the code to the Form_Open event and am still getting the same error. That really is the key question. Why won't it let me assign the control a value??

 

[RuralGuy]

Is your query updateable? can you change the value of this field in the query without the form? Did you try the BeforeUpdate event?

 

[jkfeagle]

Yes. I tried updating at the query level and it let me add the info. I also tried the Before_Update and it prevented the form from opening, telling me that the OpenForm action had been cancelled (the form is launched from a command button on another form).

 

[RuralGuy]

Code in the BeforeUpdate event of a form should *not* stop you from opening that form. What code did you use? What code are you using to open the form?

 

[jkfeagle]

This opens the form:

Private Sub cmdOpenF_BudgetItems_Click()
On Error GoTo Err_cmdOpenF_BudgetItems_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "F_Budget_Items"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenF_BudgetItems_Click:
Exit Sub

Err_cmdOpenF_BudgetItems_Click:
MsgBox Err.Description
Resume Exit_cmdOpenF_BudgetItems_Click

End Sub


And here is the BeforeUpdate

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtWho = sUser
End Sub

 

[RuralGuy]

Does that code compile? I thought you were using Me.txtWho = Environ("UserName") to tag the record. As long as your code compiles I see nothing in what you posted that would stop the F_Budget_Items form from opening.

 

[jkfeagle]

sUser is defined elsewhere using the code you mentioned. Thanks for your help anyway. Any ideas on why it won't allow me to assign a value to the control?

 

[RuralGuy]

Does you code compile? Can you change any other control with this method?

 

[jkfeagle]

My code compiles but I cannot change any control using this method. It works fine if I type it in but try to change it with VBA and it chokes. Any ideas?

 

[jkfeagle]

Never mind. Finally figured it out and had to do with how the variable was defined if you can believe that! Thanks for your help RG!

 

[RuralGuy]

Outstanding! Thanks for posting back with your success.