Referencing recordset fields from a join recordset query

[Kheribus]

Hello,

So I have two tables that have the same structure and I am comparing them to see when records are added or removed from one to the other..

Here's a little snippet of the code:

'strSQL_deleted = "select * from tripslog left join trips on (trips.tripName = tripslog.tripName) WHERE trips.tripName is null;" 'fields in tripslog not in trips (deleted)

Set rst = invoicing.OpenRecordset(strSQL_deleted, dbOpenDynaset)
rst.MoveFirst
MsgBox rst.Fields(0)

The issues is i'm not sure how to reference the fields in the recordset that queries a join. The above rst.Fields(0) does return the primary key of the first entry, so I know there is data in the recordset.

However, I can't reference the field names (properly), ie rst!tripName returns empty and rst!trips.tripName / rst!tripslog.tripName returns empty.

Am I missing something here?

 

[spikepl]

Yup.

Please explain why you are moving records from one table to another, instead of just flagging the appropriate records, which would be a more usual procedure.

 

[Kheribus]

What I have is a hosted table which may change over time which I am comparing to a stored table that acts as an audit log for the hosted table. So, I want to do a join to look for records that are in the hosted file but not in the stored file and, in this instance, I am looking for records in the stored file that are not in the hosted file (deleted records).

In this example, after the join, i will be left with a recordset full of records that are in the stored table but not the hosted table. I would add a new row (for each row in the returned join recordset) to the stored table with a "deleted" in my update column.

Aside from the program logic, this should be fairly simple. I just need to figure out how to reference the records in a recordset that is produced by a join query.

my rst.fields(0) is working, so I know there is data in the recordset, but I don't know how to reference it by name.

 

[spikepl]

Sprinkle some square brackets over those field references.

Why recordsets? Cannot use the matching or missing records query wizards?

 

[Kheribus]

I'm using recordsets because the problem extends from here and I need to do some data manipulation. There should be an easy way that I am missing to reference the fields in the recordset, as neither rst!tripName, rst!trips.tripName, rst!tripslog.tripName, etc.. .are working

clarification when i say that referencing the field names in the recordset like rst!... it will return "item not found in this collection"

 

[MarkK]

To reference a field in a recordset by name you can use syntax like . . .

debug.print rst.fields("fieldname")

. . . and since Fields is the default property of a Recordset, you can also do . . .

debug.print rst("fieldname")

. . . or use a variable, like . . .

dim sValue as string
sValue = "fieldname"
debug.print rst(sValue)

 

[Kheribus]

Thanks.

Yeah I got it to work by using

rst.Fields("tripslog.tripName")

Thanks!