Absolute position

[J Shrimps, Jr.]

I can locate the record I'm looking
for with
Set rst = db.OpenRecordset("tblData;")
rst.FindFirst left("Code", 13) = Me.cmbLookup
but I need to know where this record lies -
i.e. what row number in the dataset.
This is a dymanically created linked
text file so it can't be indexed or updated.
rst.Cursorlocation doesn't seem to work
and if I place rst.AbsolutionPosition right
after the rst.FindFist - which correctly
identifies the record 'cause I've checked
with the debugger, I get 0 every time.

 

[Allen Browne]

The only way I can duplicate your results is if the FindFirst does not
result in a match. Did you test rs.NoMatch after the FindFirst?

I'm guessing you will get a no-match anyway, because the left-most 13
characters of the string "Code" will always be the entire 4 characters of
that string.

 

[OfficeDev18 via AccessMonster.com]

Try two things: 1- remove the semi-colon ";" from the set statement and make
it just

Set rst = db.OpenRecordset("tblData")

2- I understand that "Code" is a variable, and you're searching for the first
13 bytes of that variable. If I understand correctly, you will want to change
your search command as follows:

rst.FindFirst "Left(Code, 13) = '" & Me.cmbLookup & "'"

I know it's hard to read on the screen, so I'll spell it out. First of all,
remove the quotes around the word 'Code' as it's a variable, not a literal.
Second, the FindFirst is looking for a SQL WHERE clause without the word
WHERE. Hence, give it SQL syntax, enclosed in quotes. After the equals sign
("="), put a single quote followed by a double-quote. The single quote
denotes the opening of a literal string (which will be provided by the "& Me.
cmbLookup" clause), and the double-quote to close the quotation and allow VBA
to solve for Me.cmbLookup (which cannot be done in a quotation). Follow the
'& Me.cmbLookup' with an ampersand followed by a space, a double-quote, a
single-quote, and a double-quote (& "'"). The ampersands ("&"), of course, do
the concatenation of the string. The first double-quote opens the quotation
for the last part of the SQL statement, the single-quote closes the literal
string, and the last double-quote closes the entire SQL WHERE clause. See the
FindFirst method in the Help file for additional details.

HTH

 

[J Shrimps, Jr.]

Posting boils down to this:
Is it possible, once a record is located in
a non-updatable linked ASCII text file,
to store the record number of that
record in a variable?
YES: Explanation___________
NO: Just can't happen, let it go.

 

[Allen Browne]

Is it possible ... in a NON-UPDATABLE ... to STORE ...

Huh????

Am I missing something, or is that an oxymoron?

 

[John Vinson]

Posting boils down to this:
Is it possible, once a record is located in
a non-updatable linked ASCII text file,
to store the record number of that
record in a variable?
YES: Explanation___________
NO: Just can't happen, let it go.

Yes, it is possible.

Dim vPos As Long
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("LinkedTableName")
rs.FindFirst "<criteria>"
If Rs.NoMatch Then
MsgBox "Not found"
Else
vPos = rs.AbsolutePosition
End If


John W. Vinson[MVP]

 

[J Shrimps, Jr.]

Thanx for all the help.
Local Access/VB users group sez
not possible as recordset doesn't know
the position of the record until the LAST
record is reached and I'm seeking the
first record that matches a unique criteria -
only one record will match.
Plan to start at the top of the linked list table,
Movenext untill record is found, then
store contents of rst!Field1 into variable
strCode.

I tried mapping variable StrRow to Rst.AbsolutePosition,
(from original Posting -
"and if I place rst.AbsolutionPosition right
after the rst.FindFist - which correctly
identifies the record 'cause I've checked
with the debugger, I get 0 every time.")

StrRow always returned 0 'cause rst doesn't know
anything about Rst.FindFirst -
rst.Absoluteposition
will always return the position of
the first record in the table.

 

[John Vinson]

Thanx for all the help.
Local Access/VB users group sez
not possible as recordset doesn't know
the position of the record until the LAST
record is reached and I'm seeking the
first record that matches a unique criteria -
only one record will match.
Plan to start at the top of the linked list table,
Movenext untill record is found, then
store contents of rst!Field1 into variable
strCode.

I tried mapping variable StrRow to Rst.AbsolutePosition,
(from original Posting -
"and if I place rst.AbsolutionPosition right
after the rst.FindFist - which correctly
identifies the record 'cause I've checked
with the debugger, I get 0 every time.")

StrRow always returned 0 'cause rst doesn't know
anything about Rst.FindFirst -
rst.Absoluteposition
will always return the position of
the first record in the table.

Is this a DAO recordset (my assumption) or ADO? If the latter, use the
Find method instead.

John W. Vinson[MVP]

 

[J Shrimps, Jr.]

This is a linked fixed-width ASCII text file.
I autfill doesn't provide me with a
rst.find
I have rst.findnext, but no
rst.find.

 

[John Vinson]

This is a linked fixed-width ASCII text file.
I autfill doesn't provide me with a
rst.find
I have rst.findnext, but no
rst.find.

I'm not well acquainted with ADO (he said blushing) - try findfirst
and findnext, or... erm... try the Help.

John W. Vinson[MVP]