Where is getstring?

[C. Clark]

I've read so much in lots of different forums about the wonderful getstring()
method for recordsets, and really need it for some code I'm trying to use.
Here's what I've got so far:

Dim dbs As Database
Dim VenueNew, VenueOld, VenNewName, VenOldName, sql As String
Dim rs As Recordset
VenueOld = eqpVenue.OldValue
VenueNew = Me.eqpVenue
If VenueOld <> VenueNew Then
Set dbs = CurrentDb
sql = " SELECT vnuVenue FROM tblVenue WHERE vnuID = " &
VenueOld & ";"
Set rs = dbs.OpenRecordset(sql)
VenOldName = rs.GetString()
sql = " SELECT vnuVenue FROM tblVenue WHERE vnuID = " &
VenueNew & ";"
Set rs = dbs.OpenRecordset(sql)
VenNewName = rs.GetString()
dbs.Execute " INSERT INTO tblRepairs
(rprComments,rprEquipID) VALUES ('Moved from " & VenOldName & " to " &
VenNewName & "', '" & Me.eqpID & "');"
dbs.Close
Me.Refresh
End IF

I've truncated some stuff, but what I'm attempting to make the code do is
get the old and new values of a form field directly related to one table, and
insert a record into another table with both the old and new values. The hard
part lies in the fact that the field that gets changed for this is lniked to
another table via the lookup wizard using the autonumber primary key. So I
need to use the SQL select to lookup the text field related to the primary
key field, and want the getstring to grab the text out of the recordset
returned and inserted into the sql insert query.

According to what I've read, any ADO 2.x Library should include it. I'm
using Access 2003 with VBA 6.3, and I've got ADO 2.8 refereneced, as well as
ADO Recordset 2.8, and DAO 3.6. But despite all this, and trying different
versions of ADO, I can't get the recordset.getstring() method to show up or
be recognized.
Is there something I'm doing wrong? Is there a better way to do what I'm
trying to do?

Thanks in advance - Chris.

 

[tina]

well, if i read the post correctly, you have a Lookup field in one of your
tables. the first thing i'd do is get rid of it. for more information, see
http://www.mvps.org/access/lookupfields.htm.

if you have the record you want to work with in TableA, and a foreign key
field whose "text" value is in a related table, suggest you just link the
two tables into a query, and pull the fields from each table that you need
in the VBA recordset. if you're not great a writing "freehand" SQL
statements (like me), you can do this in query design view and then go to
SQL view, copy the SQL statement, and paste it into your VBA code. you'll
need to concatenate multiple lines, because in VBA it's just a text string.

once you've done the above, you don't need some special function to retrieve
the text value you need; just refer to the fieldname in the SQL statement,
as

rs("FieldName")

hth

 

[Douglas J. Steele]

You may have ADO referenced, but your code is using DAO:

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sql)

The equivalent in ADO would be:

Dim rs As ADODB.Recordset

sql = " SELECT vnuVenue FROM tblVenue WHERE vnuID = " & VenueOld
Set rs = New ADODB.Recordset
rs.Open sql, CurrentProject.Connection

BTW, your second declaration line may not be doing what you think it does.

Dim VenueNew, VenueOld, VenNewName, VenOldName, sql As String

declares sql as sql, and the other 4 variables as Variants. VBA doesn't
allow short circuiting: if you want all five variables declared as strings,
you need

Dim VenueNew As String, VenueOld As String, VenNewName As String, VenOldName
As String, sql As String

And on the topic of declaration, if you've got references set to both ADO
and DAO, you really should ensure that you're disambiguating all
declarations. You can see how I'm declaring as an ADO recordset above: if
you wanted to be sure you got a DAO recordset, you should use Dim rs As
DAO.Recordset.

The list of objects with the same names in both the ADO and DAO models (and
hence which should always be disambiguated) is Connection, Error, Errors,
Field, Fields, Parameter, Parameters, Property, Properties and Recordset

 

[C. Clark]

well, if i read the post correctly, you have a Lookup field in one of your
tables. the first thing i'd do is get rid of it. for more information, see
http://www.mvps.org/access/lookupfields.htm.

Unfortunately, any major changes in how my tables are linked now would most
likely break the whole database - I have 4 tables linked back to the first
table, plus a different table linked to one of those 4. Not to mention a whol
load of forms and reports already set up using the primary keyed links. It'd
basically mean I'm starting from the beginning, and for something I've been
working on for almost two months now, that's out of the question, especially
considering my unofficial deadline is "As soon as I can get it done".
Along those same lines, for one example, the main table (listing the
equipment inventory) includes a lookup to a table which lists vendors and
includes all the essential vendor information, linking the equipment record
to its vendor. How would you suggest I do this without using lookups? This is
for users who aren't going to have to want to put in each vendor's
information everytime - right now its a drop box with the vendor name. Most
of the forms then have a link to view another form that shows the vendor's
contact info and such. Note that I used this way of doing it so that someone
couldn't accidentally enter a wrong name, spell it wrong, or what have you on
data entry and then have it not work right when they try to filter by x
company or whatnot.

From the link:
I dispute claim two that sorting by "company name" in their example won't
work, as I've already done it in my database.
#6, my users aren't really going to care what's going on behind the lookup
fields.
#7, this will be a single computer database. No queries or transfers to
another engine. This is Access and Access alone.
#8, the only security in this, if I can figure out how to do it, will be to
lock regular users from viewing underlying tables and designs. Everything is
to be run by forms and a few printed reports. Only I will be able to view
tables and designs.
Call me naieve, but I don't understand what 4 is saying. 3 and 5 are null at
this point, 1 simply describes how it works, I knew that.

I'm also trying to avoid straight Access queries as I've always viewed their
output as simply a redundant table to something I already could have created.

Thanks for the advice, maybe I'll try it in my next database project.

Chris

 

[C. Clark]

You may have ADO referenced, but your code is using DAO: <snip>

Ok, ADO/DAO is new to me. What are the differences and which do I really
want to be using?

BTW, your second declaration line may not be doing what you think it does.
<snip> VBA doesn't allow short circuiting:

Oi, I guess that's what I get for assuming VBA would be as easy as VB.Net,
or any other more advnaced programming language. (Maybe it should be called
VBL for Lite...)

Dim VenueNew As String, VenueOld As String, VenNewName As String, VenOldName
As String, sql As String

And on the topic of declaration, if you've got references set to both ADO
and DAO, you really should ensure that you're disambiguating all
declarations.

Again, I'm kinda new to working with VBA (I've actually worked with VB.Net
longer...) so which do I want to be using? Would it be easier just to drop
one of the references?

Thanks for your help, I'll be working on implementing changes immediately.
Chris

 

[Douglas J. Steele]

If you're strictly dealing with a Jet database (i.e.: your data is in an MDB
file), I'd stick with DAO: it was designed specifically for use with Jet
databases, and is a bit faster therefore.

If you're dealing with other DBMS, ADO may be better.

As to your VBL comment, many languages don't allow short-circuiting either
(in fact, I didn't think VB.Net did). I'd hardly call the inability a
liability! But if you're familiar with VB.Net, then you should be familiar
with having to be very explicit in your declarations (i.e.: you know how to
disambiguate)

 

[tina]

Unfortunately, any major changes in how my tables are linked now would most
likely break the whole database

you don't need to make any major changes at all. i didn't mean to get rid of
the *field*, just get rid of the Lookup. open the data table in Design view,
click on the Lookup field, in the Field Properties section of the view,
click the Lookup tab and change the Display Control property to Text Box.
open the Indexes list and remove any *duplicate* index(es) on that field.
save and close the table. open the Relationships window and remove any
*duplicate* links between the parent (Lookup) table and the data table
(these would show up as additional copies of the data table with _1 or _2 or
_3, etc, at the end of the name).

when you need to see the "text" value from a parent (Lookup) table in a form
or report, add the table to the underlying query, linked to the data table,
and include the "text" field in the query output.

the table, index, and relationships changes are going to make your database
run better and be easier to work with at the development level; if you
haven't already read the link i posted, i really recommend that you do.

hth