How do I get an unbound text box to populate a table?

[Harry F.]

Hi,

I have two text boxes that ar epopulated by the the user's Windows log-in,
and their name, which is matched to their log-in via a table. Is there a way
to have the two values (Person's name and NT log-in) be entered into fields
on a different table? Currently I have this code in there, but I get an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Douglas J Steele]

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

 

[Harry F.]

When I compile, it tells me tables! is an undefined variable. If I dont
compile, it gives me an error message related to the OnOpen procedure.

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have two text boxes that ar epopulated by the the user's Windows log-in,
and their name, which is matched to their log-in via a table. Is there a way
to have the two values (Person's name and NT log-in) be entered into fields
on a different table? Currently I have this code in there, but I get an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Douglas J Steele]

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you need a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I compile, it tells me tables! is an undefined variable. If I dont
compile, it gives me an error message related to the OnOpen procedure.

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have two text boxes that ar epopulated by the the user's Windows log-in,
and their name, which is matched to their log-in via a table. Is

there a
way

to have the two values (Person's name and NT log-in) be entered into fields
on a different table? Currently I have this code in there, but I get an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Harry F.]

Doug,

I tried the first part of your response, and got the same error. In terms
of a query, perhaps I should explain how the form is used. We have four
managers who assign work to analysts on our team. I want the record assinged
by the manager to be stamped with the manager's name and ID as it is entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you need a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I compile, it tells me tables! is an undefined variable. If I dont
compile, it gives me an error message related to the OnOpen procedure.

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's Windows log-in,
and their name, which is matched to their log-in via a table. Is there a
way
to have the two values (Person's name and NT log-in) be entered into
fields
on a different table? Currently I have this code in there, but I get an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Douglas J. Steele]

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

I tried the first part of your response, and got the same error. In terms
of a query, perhaps I should explain how the form is used. We have four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's Windows log-in,
and their name, which is matched to their log-in via a table. Is there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but I
get an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Harry F.]

Doug,

I want it when a new record is created. I've created a button to create new
records, and I was hoping to put it in the button's on click procedure. Am
I on the wrong track?

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

I tried the first part of your response, and got the same error. In terms
of a query, perhaps I should explain how the form is used. We have four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's Windows
log-in,
and their name, which is matched to their log-in via a table. Is
there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Douglas J. Steele]

Creating a new record will generate both a BeforeInsert and a BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

I want it when a new record is created. I've created a button to create
new
records, and I was hoping to put it in the button's on click procedure.
Am
I on the wrong track?

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

I tried the first part of your response, and got the same error. In
terms
of a query, perhaps I should explain how the form is used. We have
four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you
need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's
Windows
log-in,
and their name, which is matched to their log-in via a table.
Is
there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Harry F.]

Doug,

I'm still getting a type mismatch error message when I try to create a new
record, even after inserting it into the BeforeUpdate Event. The line that
is highlighted in the debugger is

Set R = D.OpenRecordset("tblAllRequests")

Please Help!

Thanks,
Harry

Creating a new record will generate both a BeforeInsert and a BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

I want it when a new record is created. I've created a button to create
new
records, and I was hoping to put it in the button's on click procedure.
Am
I on the wrong track?

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I tried the first part of your response, and got the same error. In
terms
of a query, perhaps I should explain how the form is used. We have
four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you
need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's
Windows
log-in,
and their name, which is matched to their log-in via a table.
Is
there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Douglas J Steele]

Change your declaration to

Dim D As DAO.Database, R As DAO.Recordset, strTable As String


I'm guessing you have references set to both ADO and DAO, with the ADO
reference being higher in the list than the DAO reference. That means that R
was actually declared as an ADO recordset since the Recordset object exists
in both models, and Access stops at the first one it finds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I'm still getting a type mismatch error message when I try to create a new
record, even after inserting it into the BeforeUpdate Event. The line that
is highlighted in the debugger is

Set R = D.OpenRecordset("tblAllRequests")

Please Help!

Thanks,
Harry

Creating a new record will generate both a BeforeInsert and a BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

I want it when a new record is created. I've created a button to create
new
records, and I was hoping to put it in the button's on click procedure.
Am
I on the wrong track?

:

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I tried the first part of your response, and got the same error. In
terms
of a query, perhaps I should explain how the form is used. We have
four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you
need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's
Windows
log-in,
and their name, which is matched to their log-in via a table.
Is
there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Harry F.]

Doug,

That worked wonderfully! Thank you so much for your assistance!

Regards,
Harry

Change your declaration to

Dim D As DAO.Database, R As DAO.Recordset, strTable As String


I'm guessing you have references set to both ADO and DAO, with the ADO
reference being higher in the list than the DAO reference. That means that R
was actually declared as an ADO recordset since the Recordset object exists
in both models, and Access stops at the first one it finds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I'm still getting a type mismatch error message when I try to create a new
record, even after inserting it into the BeforeUpdate Event. The line that
is highlighted in the debugger is

Set R = D.OpenRecordset("tblAllRequests")

Please Help!

Thanks,
Harry

Creating a new record will generate both a BeforeInsert and a BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I want it when a new record is created. I've created a button to create
new
records, and I was hoping to put it in the button's on click procedure.
Am
I on the wrong track?

:

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I tried the first part of your response, and got the same error. In
terms
of a query, perhaps I should explain how the form is used. We have
four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you
need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's
Windows
log-in,
and their name, which is matched to their log-in via a table.
Is
there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Harry F.]

I recently split this database, and this functionality no longer works. Do I
need to set the DB to a different database? Anyone have any other ideas why
this might not work?

Doug,

That worked wonderfully! Thank you so much for your assistance!

Regards,
Harry

Change your declaration to

Dim D As DAO.Database, R As DAO.Recordset, strTable As String


I'm guessing you have references set to both ADO and DAO, with the ADO
reference being higher in the list than the DAO reference. That means that R
was actually declared as an ADO recordset since the Recordset object exists
in both models, and Access stops at the first one it finds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I'm still getting a type mismatch error message when I try to create a new
record, even after inserting it into the BeforeUpdate Event. The line that
is highlighted in the debugger is

Set R = D.OpenRecordset("tblAllRequests")

Please Help!

Thanks,
Harry

:

Creating a new record will generate both a BeforeInsert and a BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I want it when a new record is created. I've created a button to create
new
records, and I was hoping to put it in the button's on click procedure.
Am
I on the wrong track?

:

Do you only want their stamp on it when they change a record? If so, put
logic in the form's BeforeUpdate event to assign the values to the field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I tried the first part of your response, and got the same error. In
terms
of a query, perhaps I should explain how the form is used. We have
four
managers who assign work to analysts on our team. I want the record
assinged
by the manager to be stamped with the manager's name and ID as it is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset based on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your application).

However, given what you're trying to do, it would look as though you
need
a
query that limits the records returned to the user in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I compile, it tells me tables! is an undefined variable. If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have two text boxes that ar epopulated by the the user's
Windows
log-in,
and their name, which is matched to their log-in via a table.
Is
there a
way
to have the two values (Person's name and NT log-in) be entered
into
fields
on a different table? Currently I have this code in there, but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Brendan Reynolds]

You code would have been returning a table-type recordset, because that is
the default when you use the name of a local table. It will now be opening a
dynaset, because that is the default for a linked table. There are a couple
of things you can do with a table-type recordset that you can't do with a
dynaset, such as setting the Index property or calling the Seek method. If
that's what you're trying to do, see the article at the following URL ...

http://www.mvps.org/access/tables/tbl0006.htm

If that's not what you're trying to do, please give a little more
information as to what you're trying to do, and what happens when you try.

--
Brendan Reynolds
Access MVP

I recently split this database, and this functionality no longer works. Do
I
need to set the DB to a different database? Anyone have any other ideas
why
this might not work?

Doug,

That worked wonderfully! Thank you so much for your assistance!

Regards,
Harry

Change your declaration to

Dim D As DAO.Database, R As DAO.Recordset, strTable As String


I'm guessing you have references set to both ADO and DAO, with the ADO
reference being higher in the list than the DAO reference. That means
that R
was actually declared as an ADO recordset since the Recordset object
exists
in both models, and Access stops at the first one it finds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I'm still getting a type mismatch error message when I try to create
a new
record, even after inserting it into the BeforeUpdate Event. The
line
that
is highlighted in the debugger is

Set R = D.OpenRecordset("tblAllRequests")

Please Help!

Thanks,
Harry

:

Creating a new record will generate both a BeforeInsert and a
BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I want it when a new record is created. I've created a button to
create
new
records, and I was hoping to put it in the button's on click
procedure.
Am
I on the wrong track?

:

Do you only want their stamp on it when they change a record? If
so,
put
logic in the form's BeforeUpdate event to assign the values to
the
field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I tried the first part of your response, and got the same
error.
In
terms
of a query, perhaps I should explain how the form is used. We
have
four
managers who assign work to analysts on our team. I want the
record
assinged
by the manager to be stamped with the manager's name and ID as
it
is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset
based
on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your
application).

However, given what you're trying to do, it would look as
though
you
need
a
query that limits the records returned to the user in
question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
When I compile, it tells me tables! is an undefined
variable.
If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi,

I have two text boxes that ar epopulated by the the
user's
Windows
log-in,
and their name, which is matched to their log-in via a
table.
Is
there a
way
to have the two values (Person's name and NT log-in) be
entered
into
fields
on a different table? Currently I have this code in
there,
but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If

 

[Harry F.]

I'm just trying to have two fields in the table autopopulate with unbound
textboxes from a form. Do I need to add something to the code now that the
tables are linked?

You code would have been returning a table-type recordset, because that is
the default when you use the name of a local table. It will now be opening a
dynaset, because that is the default for a linked table. There are a couple
of things you can do with a table-type recordset that you can't do with a
dynaset, such as setting the Index property or calling the Seek method. If
that's what you're trying to do, see the article at the following URL ...

http://www.mvps.org/access/tables/tbl0006.htm

If that's not what you're trying to do, please give a little more
information as to what you're trying to do, and what happens when you try.

--
Brendan Reynolds
Access MVP

I recently split this database, and this functionality no longer works. Do
I
need to set the DB to a different database? Anyone have any other ideas
why
this might not work?

Doug,

That worked wonderfully! Thank you so much for your assistance!

Regards,
Harry

:


Change your declaration to

Dim D As DAO.Database, R As DAO.Recordset, strTable As String


I'm guessing you have references set to both ADO and DAO, with the ADO
reference being higher in the list than the DAO reference. That means
that R
was actually declared as an ADO recordset since the Recordset object
exists
in both models, and Access stops at the first one it finds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I'm still getting a type mismatch error message when I try to create
a new
record, even after inserting it into the BeforeUpdate Event. The
line
that
is highlighted in the debugger is

Set R = D.OpenRecordset("tblAllRequests")

Please Help!

Thanks,
Harry

:

Creating a new record will generate both a BeforeInsert and a
BeforeUpdate
event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I want it when a new record is created. I've created a button to
create
new
records, and I was hoping to put it in the button's on click
procedure.
Am
I on the wrong track?

:

Do you only want their stamp on it when they change a record? If
so,
put
logic in the form's BeforeUpdate event to assign the values to
the
field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

I tried the first part of your response, and got the same
error.
In
terms
of a query, perhaps I should explain how the form is used. We
have
four
managers who assign work to analysts on our team. I want the
record
assinged
by the manager to be stamped with the manager's name and ID as
it
is
entered
for tracking purposes. Can this not be done?

I appreciate your assistance,
Harry

:

Ah. That makes sense. If you're trying to open a recordset
based
on a
particular table, you use:

Set R = D.OpenRecordset("tblAllRequests")

(assuming tblAllRequests is the name of a table in your
application).

However, given what you're trying to do, it would look as
though
you
need
a
query that limits the records returned to the user in
question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
When I compile, it tells me tables! is an undefined
variable.
If I
dont
compile, it gives me an error message related to the OnOpen
procedure.

:

What's the error?

See whether

If IsNull(R![FunnelManagerID]) Then

works any better

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi,

I have two text boxes that ar epopulated by the the
user's
Windows
log-in,
and their name, which is matched to their log-in via a
table.
Is
there a
way
to have the two values (Person's name and NT log-in) be
entered
into
fields
on a different table? Currently I have this code in
there,
but
I
get
an
error message.


Dim D As Database, R As Recordset, strTable As String


Set D = CurrentDb()
Set R = D.OpenRecordset(tables!tblAllRequests)

If R![FunnelManagerID] Is Null Then
R.Update
R![FunnelManagerID] = Me!txtUserName
R.Update
End If

If R![FunnelManagerName] Is Null Then
R.Update
R![FunnelManagerName] = Me!txtUserName
R.Update
End If