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

G

Guest

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!
 
G

Guest

Comments inline


Harry F. said:
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
Click to expand...


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).
Click to expand...


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!
Click to expand...

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?
 
G

Guest

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


SteveS said:
Comments inline


Harry F. said:
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
Click to expand...


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).
Click to expand...


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!
Click to expand...

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?
Click to expand...
 
G

Guest

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

SteveS said:
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?
Click to expand...

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).
Click to expand...


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
Click to expand...

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?
Click to expand...

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

Guest

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. said:
Steve, Thanks for looking into this. Please see my comments.

SteveS said:
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?
Click to expand...

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).
Click to expand...


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
Click to expand...

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?
Click to expand...

Just the current record. Am I completely off in what I'm doing?
Click to expand...
 
G

Guest

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.

SteveS said:
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. said:
Steve, Thanks for looking into this. Please see my comments.

SteveS said:
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?
Click to expand...

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
Click to expand...

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?
Click to expand...

Just the current record. Am I completely off in what I'm doing?
Click to expand...
Click to expand...
 
G

Guest

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


Harry F. said:
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.

SteveS said:
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. said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unbound textbox populating my table. 7
How do I get an unbound text box to populate a table? 13
Unbound field calculation save in table 3
Default Value auto populate from field in same table 1
Passing the value of ActiveControl to Unbound Text Box 1
Update a date field in sub-form from dates fields on main form 1
Populating a field in a table from an unbound field in a form. 5
Using an unbound combo box to populate fields 4

Top