how do I change a field's value on a selected record using VBA?

[Guest]

I have a table that contains the names of other tables in my database and
when a new table is added, i'd like to add its name to the list as part of a
VBA module. Seems a pretty basic function, but I can't find any instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is required.

 

[TC]

(UNTESTED)

dim sql as string, v1 as string, v2 as integer
v1 = "abc" ' value for field F1.
v2 = 99 ' value for field F2.
sql = "INSERT INTO MyTable (F1, F2) VALUES (""" & v1 & """, " & v2 &
")"
msgbox sql
dbengine(0)(0).execute sql, dbfailonerror

- or -

with dbengine(0)(0).openrecordset("MyTable", dbopendynaset)
.addnew
![F1] = v1
![F2] = v2
.update
end with

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
..addnew
![RateTable] = NewName
..update
end with

Hope this helps

 

[Guest]

Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
with that. I tried with db as the object instead of tdf and got the same.

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

I have a table that contains the names of other tables in my database and
when a new table is added, i'd like to add its name to the list as part of a
VBA module. Seems a pretty basic function, but I can't find any instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is required.

 

[Guest]

Don't know for sure but it sounds like the data you are trying to insert into
the table is not of the same datatype as the field you are trying to put it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
with that. I tried with db as the object instead of tdf and got the same.

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

I have a table that contains the names of other tables in my database and
when a new table is added, i'd like to add its name to the list as part of a
VBA module. Seems a pretty basic function, but I can't find any instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is required.

 

[Guest]

Thanks, Michal, but I don't think it can be that, as the thing grinds to a
halt before it gets to that point - stops at the OpenRecordset statement. I
can't see what the problem is from reading the help pages.

rgds,

Leo

Don't know for sure but it sounds like the data you are trying to insert into
the table is not of the same datatype as the field you are trying to put it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
with that. I tried with db as the object instead of tdf and got the same.

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

I have a table that contains the names of other tables in my database and
when a new table is added, i'd like to add its name to the list as part of a
VBA module. Seems a pretty basic function, but I can't find any instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is required.

 

[Douglas J. Steele]

Try changing your declaration to

dim rst as DAO.recordset

I'm assuming that you've got references set to both the Microsoft DAO 3.6
Object Library and the Microsoft ActiveX Data Objects 2.1 Library, and the
ADO one is higher in the list (With any code module open, select Tools |
References from the menu bar to see what I'm talking about)

When you have both references, you'll find that you need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, Michal, but I don't think it can be that, as the thing grinds to a
halt before it gets to that point - stops at the OpenRecordset statement.
I
can't see what the problem is from reading the help pages.

rgds,

Leo

Don't know for sure but it sounds like the data you are trying to insert
into
the table is not of the same datatype as the field you are trying to put
it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Hmm. I'm getting a Type Mismatch error when it tries to open the
recordset
with that. I tried with db as the object instead of tdf and got the
same.

:

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

I have a table that contains the names of other tables in my
database and
when a new table is added, i'd like to add its name to the list as
part of a
VBA module. Seems a pretty basic function, but I can't find any
instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is
required.

 

[Guest]

Hmmm The code I sent you makes use of DAO as opposed to ADO that could be the
problem I suppose. This type of code works for me all the time doing exactly
what you are trying to do.

Check to see if you have a reference to Microsoft DAO 3.51 Object Library in
your database.

In case you don't know how to do this. Open any module and click on
Tools>References and then scroll through the list and then select the
reference you need to add.

This might solve the problem otherwise I think it will take one of the MVP's
to answer this.

My only other thought is where are you getting the new table name from?
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Thanks, Michal, but I don't think it can be that, as the thing grinds to a
halt before it gets to that point - stops at the OpenRecordset statement. I
can't see what the problem is from reading the help pages.

rgds,

Leo

Don't know for sure but it sounds like the data you are trying to insert into
the table is not of the same datatype as the field you are trying to put it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
with that. I tried with db as the object instead of tdf and got the same.

:

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

I have a table that contains the names of other tables in my database and
when a new table is added, i'd like to add its name to the list as part of a
VBA module. Seems a pretty basic function, but I can't find any instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is required.

 

[Guest]

They should pay you guys Douglas!
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Try changing your declaration to

dim rst as DAO.recordset

I'm assuming that you've got references set to both the Microsoft DAO 3.6
Object Library and the Microsoft ActiveX Data Objects 2.1 Library, and the
ADO one is higher in the list (With any code module open, select Tools |
References from the menu bar to see what I'm talking about)

When you have both references, you'll find that you need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, Michal, but I don't think it can be that, as the thing grinds to a
halt before it gets to that point - stops at the OpenRecordset statement.
I
can't see what the problem is from reading the help pages.

rgds,

Leo

Don't know for sure but it sounds like the data you are trying to insert
into
the table is not of the same datatype as the field you are trying to put
it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

Hmm. I'm getting a Type Mismatch error when it tries to open the
recordset
with that. I tried with db as the object instead of tdf and got the
same.

:

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

I have a table that contains the names of other tables in my
database and
when a new table is added, i'd like to add its name to the list as
part of a
VBA module. Seems a pretty basic function, but I can't find any
instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is
required.

 

[Guest]

Thanks, guys!

Leo

They should pay you guys Douglas!
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Try changing your declaration to

dim rst as DAO.recordset

I'm assuming that you've got references set to both the Microsoft DAO 3.6
Object Library and the Microsoft ActiveX Data Objects 2.1 Library, and the
ADO one is higher in the list (With any code module open, select Tools |
References from the menu bar to see what I'm talking about)

When you have both references, you'll find that you need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, Michal, but I don't think it can be that, as the thing grinds to a
halt before it gets to that point - stops at the OpenRecordset statement.
I
can't see what the problem is from reading the help pages.

rgds,

Leo

:

Don't know for sure but it sounds like the data you are trying to insert
into
the table is not of the same datatype as the field you are trying to put
it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

Hmm. I'm getting a Type Mismatch error when it tries to open the
recordset
with that. I tried with db as the object instead of tdf and got the
same.

:

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
.addnew
![RateTable] = NewName
.update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


:

I have a table that contains the names of other tables in my
database and
when a new table is added, i'd like to add its name to the list as
part of a
VBA module. Seems a pretty basic function, but I can't find any
instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is
required.

 

[Douglas J. Steele]

Good idea! <g>