RE: Need to populate a field in my table from the value in an unbo

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Harry,

Unless there is something else going on, since you are adding a record, all
you should need is:

'***** beg code *******
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.[FunnelManagerID]) Then
Me.[FunnelManagerID] = Me!txtUserID
End If

If IsNull(Me.[FunnelManagerName]) Then
Me.[FunnelManagerName] = Me!txtUserName
End If

End Sub
'***** end code *******

where Me!txtUserID and Me!txtUserName are unbound text/combo/list boxes and
Me.[FunnelManagerID] and Me.[FunnelManagerName] are fields in the table.


I saw in a previous response to a post of yours the question of Why you are
storing both the UserID *and* the UserName. If you have the user ID, you can
look up the user name.....kind of defeats the purpose of having a look up
table. Maybe you have a business reason to do it like this. Just wondering....

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Harry F." wrote:

Steve, Thanks for looking into this. Please see my comments.

"SteveS" wrote:

Comments inline


"Harry F." wrote:

OK,
Here's the story. I have a form used to assign work. On it, I pull the
user's Windows login, and use that to generate their name. These values are
stored in unbound text boxes atop the form. The form itself is bound to a


So you have code that gets the Windows LogIn ID, looks up the UserID and
UserName, then fills the two unbound controls txtUserID and txtUserName?

That's correct.



table that stores the work assigned. I need to populate a field in this
table with the user's name who assigned the work. I had used the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim D As DAO.Database, R As DAO.Recordset

Set D = CurrentDb()
Set R = D.OpenRecordset("tblAllRequests", dbOpenDynaset)

If IsNull(R![FunnelManagerID]) Then
R.Edit
R![FunnelManagerID] = Me!txtUserID
R.Update
End If

If IsNull(R![FunnelManagerName]) Then
R.Edit
R![FunnelManagerName] = Me!txtUserName
R.Update
End If
End Sub

It worked before I split the database. (I think).


Your code opens a record set based on a table "tblAllRequests". The table is
not ordered and opens to a random record. If the field "FunnelManagerID" or
field "FunnelManagerName" is NULL, then an ID and/or name is entered into the
respective field - for *one* record in the record set.

(BTW, you should always close the record set and set the RS and


Pretty much, yeah.


Now it no longer works. What can I do to have the unbound textbox populate
the field for every record? Can I? Please help!


Do you really want to change the ID/user name for *every* record in the
table when new work is assigned?? Or just the current record being
created/edited?


Just the current record. Am I completely off in what I'm doing?

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

.

Relevant Pages