Seek & DLookup Don't Work with Word Variables

[AvGuy]

Here's the scenario. I need to find a CustomerID for a Word customer record. I pull the Word customer info into an Access form and then try either DLookup or the Seek method (DAO) to find the CustomerID from a common unique identifier, ClassCode. That is, the Word record and the Access record both contain the unique ClassCode, but the Word record doesn't have the CustomerID. Sounds easy, doesn't it? However, neither method works, I suppose because the record source is ultimately the Word document even though the information is written to the Access form before the Seek method or DLookup is employed. The methods work fine when I setup an unbound form and manually enter the ClassCode so it's not the underlying table. Here's the A97 code:

'Get variable from Word file and place on form
Dim Wrd As Word.Application
Set Wrd = Word.Application
Dim rng as Range
Set rng = ActiveDocument.Sentences(6)
Me!ClassCode = rng
'This part works fine; the ClassCode appears on the form

'Seek CustomerID from ClassCode
Dim DB As Database
Dim rst As Recordset
Set DB = CurrentDb
Set rst = DB.OpenRecordset("Customer")
With rst
.Index = "ClassCode"
.Seek "=", Me!ClassCode
If .NoMatch Then
MsgBox "ClassCode not in database"
Else
Me!CustomerID = !CustomerID
End If
.Close
End with
Set rst = Nothing

I've tried indexing and then searching different fields and the result is the same; NoMatch is always true.

Any thoughts?
AvGuy

 

[MajP]

I do not see the problem, but I do not use "seek" often. Try just using the "rst.findfirst" instead of the seek.

 

[AvGuy]

Seek, FindFirst and DLookup do not find the record.

 

[MajP]

Could you put a debug.print on Me!ClassCode and show an exampler of what you are trying to match? It is probably a formatting thing. Could have spaces on front or back, special characters, etc when you pull it from Word. Might need to get cleaned up.

 

[AvGuy]

No, it's not a format problem. The class codes are simple, like this: PQ20663 and it's not confined to just that field. Like I said, I've indexed other fields in the table such as the customer name and then tried to find them after pulling the variable in from Word. They don't work either. It has to do, somehow, with the source of the data. I can't even find the record using DLookup from the debug window; it just returns null. I have a hunch I have to close the access to the Word file before the data becomes accessible to other Access functions. Could be a data lock thing.
Thanks for you input.

 

[MajP]

Not to beat a dead horse but is your findfirst look like this, with the single quotes?

rs.findfirst("ClassCode = '" & me.ClassCode & "'")

 

[AvGuy]

Seems to me it should be me!ClassCode rather than me.ClassCode. And no, that doesn't work. Just yields an error, "Operation is not supported for this type of object.

 

[MajP]

Seems to me it should be me!ClassCode rather than me.ClassCode

Bang or dot notation, does not make a difference. VB is a little flexible so I Could also have wrote it as below, if referring to a control called "ClassCode"

me.controls("ClassCode").value
me("ClassCode").value
me("ClassCode)
me.ClassCode

Me!Controls!ClassCode.value
Me!ClassCode.value
Me!ClassCode

Or if referring to a field without a control
Me!ClassCode
me![ClassCode]
me.ClassCode

The point is if you are referring to a string you have to include single quotes. Maybe you did that already.
rs.findfirst "ClassCode = 'x123'