Need to populate a field in my table from the value in an unbound

[Harry F.]

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
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).

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

 

[SteveS]

Comments inline

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?

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

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?

 

[SteveS]

Harry,

I just found your previous posts. I took a test mdb, created a form for
entry and split the mdb. I added code in the form before update to add text
from three unbound text boxes to the new record. I didn't have any problem.

So what is the record source for the form? Is it a table or a query?

What are the fields in the table?

What code is behind to form?

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

Comments inline

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?

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

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?

 

[Harry F.]

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

Comments inline





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.

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?

 

[SteveS]

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.)

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

Comments inline





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.

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?

 

[Harry F.]

Steve,

I'll give that a shot. Thanks for the help. As to why I need both, I'm
only going to track the name. Again, I appreciate you taking the time.

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.)

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

Comments inline


:

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?

 

[SteveS]

No problem. If you have any more questions, post back. I'll monitor this
thread for a few more days.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

I'll give that a shot. Thanks for the help. As to why I need both, I'm
only going to track the name. Again, I appreciate you taking the time.

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.)

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

:

Comments inline


:

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?