Unbound textbox populating my table.

H

Harry F.

Hello all,

I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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

Thanks,
Harry
 
M

Marshall Barton

Harry said:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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


Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 
H

Harry F.

I'm not sure I follow the second piece. The table's name has remianed the
same, how do I get more than one record in my back-end table?

Marshall Barton said:
Harry said:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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


Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 
M

Marshall Barton

Well, you've never stated what you are trying to accomplish,
so I can't figure what you might need.

If the table has more than one record in it (regardless of
how they got there), then you would need to do something to
locate the record that you want to edit. The typical way to
do that is to use a query with a WHERE clause instead of
opening the recordset directly to the table. In some
circumstances you might want to use the FindFirst method
after opening the recordset.
--
Marsh
MVP [MS Access]

I'm not sure I follow the second piece. The table's name has remianed the
same, how do I get more than one record in my back-end table?

Harry said:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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
Marshall Barton said:
Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 
H

Harry F.

Thanks for the help so far. What I am trying to do is every time a new
record is added to the table, that two fields in the table are stamped with
the values from unbound text boxes on the form. Does this make sense? It
was working fine with the old code until I split the DB.

Marshall Barton said:
Well, you've never stated what you are trying to accomplish,
so I can't figure what you might need.

If the table has more than one record in it (regardless of
how they got there), then you would need to do something to
locate the record that you want to edit. The typical way to
do that is to use a query with a WHERE clause instead of
opening the recordset directly to the table. In some
circumstances you might want to use the FindFirst method
after opening the recordset.
--
Marsh
MVP [MS Access]

I'm not sure I follow the second piece. The table's name has remianed the
same, how do I get more than one record in my back-end table?

Harry F. wrote:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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
Marshall Barton said:
Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 
M

Marshall Barton

You want to "stamp" some other table when a new record is
added to the table bound to the form? If so, how does the
record in the other table relate to the record in the form?

Or do you want to "stamp" the new record at the time it is
saved?
--
Marsh
MVP [MS Access]

Thanks for the help so far. What I am trying to do is every time a new
record is added to the table, that two fields in the table are stamped with
the values from unbound text boxes on the form. Does this make sense? It
was working fine with the old code until I split the DB.


Marshall Barton said:
Well, you've never stated what you are trying to accomplish,
so I can't figure what you might need.

If the table has more than one record in it (regardless of
how they got there), then you would need to do something to
locate the record that you want to edit. The typical way to
do that is to use a query with a WHERE clause instead of
opening the recordset directly to the table. In some
circumstances you might want to use the FindFirst method
after opening the recordset.

I'm not sure I follow the second piece. The table's name has remianed the
same, how do I get more than one record in my back-end table?


Harry F. wrote:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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


:
Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 
H

Harry F.

The record on the table is the reocrd on the form. Perhaps I'm not being as
clear as I'd like. What happens is we have people logging requests through
the form. When they do so, I want the record that they logged to log the
manager's name and ID, which is captured through code on unbound text boxes
on the form. The code I have worked perfectly until I split the DB, do I
need to add something to it since the tables are split? I've read that
access considers these tables dynasets now that the DB is split, do I need to
reference this somewhere in the code?

Marshall Barton said:
You want to "stamp" some other table when a new record is
added to the table bound to the form? If so, how does the
record in the other table relate to the record in the form?

Or do you want to "stamp" the new record at the time it is
saved?
--
Marsh
MVP [MS Access]

Thanks for the help so far. What I am trying to do is every time a new
record is added to the table, that two fields in the table are stamped with
the values from unbound text boxes on the form. Does this make sense? It
was working fine with the old code until I split the DB.


Marshall Barton said:
Well, you've never stated what you are trying to accomplish,
so I can't figure what you might need.

If the table has more than one record in it (regardless of
how they got there), then you would need to do something to
locate the record that you want to edit. The typical way to
do that is to use a query with a WHERE clause instead of
opening the recordset directly to the table. In some
circumstances you might want to use the FindFirst method
after opening the recordset.


Harry F. wrote:
I'm not sure I follow the second piece. The table's name has remianed the
same, how do I get more than one record in my back-end table?


Harry F. wrote:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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


:
Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 
M

Marshall Barton

I'm still not sure I understand what's not working. Putting
the value of an unbound control into the current record,
should just be a matter of binding the control. There's
nothing that prevents a bound control from being set in a
VBA procedure.

I don't know if that's what you are asking about. Maybe the
problem is that you can't retrieve the manager information.
In this case the problem probly is the fact that you are
using a dynaset type recordset. If so, you need to replace
the Index - Seek lines of code with FindFirst.

Regardless of that issue, I doubt that you have a good
reason to save both the mgr's ID and name. Normally you
should only store the ID and use that to look up the name
whenever you need to display it. OTOH, if you need to know
the name of the manager that was in effect at the time the
record was created even if that manager has moved on to
other responsinilities, then you need to save the name and
the ID is irrelevant. Think about it.
--
Marsh
MVP [MS Access]


The record on the table is the reocrd on the form. Perhaps I'm not being as
clear as I'd like. What happens is we have people logging requests through
the form. When they do so, I want the record that they logged to log the
manager's name and ID, which is captured through code on unbound text boxes
on the form. The code I have worked perfectly until I split the DB, do I
need to add something to it since the tables are split? I've read that
access considers these tables dynasets now that the DB is split, do I need to
reference this somewhere in the code?


Marshall Barton said:
You want to "stamp" some other table when a new record is
added to the table bound to the form? If so, how does the
record in the other table relate to the record in the form?

Or do you want to "stamp" the new record at the time it is
saved?

Thanks for the help so far. What I am trying to do is every time a new
record is added to the table, that two fields in the table are stamped with
the values from unbound text boxes on the form. Does this make sense? It
was working fine with the old code until I split the DB.


:
Well, you've never stated what you are trying to accomplish,
so I can't figure what you might need.

If the table has more than one record in it (regardless of
how they got there), then you would need to do something to
locate the record that you want to edit. The typical way to
do that is to use a query with a WHERE clause instead of
opening the recordset directly to the table. In some
circumstances you might want to use the FindFirst method
after opening the recordset.


Harry F. wrote:
I'm not sure I follow the second piece. The table's name has remianed the
same, how do I get more than one record in my back-end table?


Harry F. wrote:
I have two unbound textboxes populating a table base on the code below. I
recently split this database, and the population no longer occurs. Do I need
to set the DB to the back-end database? What else might be going on here?

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

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

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


:
Normally, all you need is to properly link to the table in
the back end db. Make sure the name of the front end
linking table is the same as the back end table's name.

Your code does not allow for the table contain more than one
record. Is that what you want?
 

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

Top