Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / December 2005

Tip: Looking for answers? Try searching our database.

Absolute position

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
J Shrimps, Jr. - 28 Nov 2005 11:46 GMT
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 - 28 Nov 2005 12:30 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I can locate the record I'm looking
> for with
[quoted text clipped - 9 lines]
> identifies the record 'cause I've checked
> with the debugger, I get 0 every time.
OfficeDev18 - 28 Nov 2005 16:15 GMT
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

>I can locate the record I'm looking
>for with
[quoted text clipped - 9 lines]
>identifies the record 'cause I've checked
>with the debugger, I get 0 every time.

Signature

Sam

J Shrimps, Jr. - 29 Nov 2005 11:40 GMT
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.
> Try two things: 1- remove the semi-colon ";" from the set statement and make
> it just
[quoted text clipped - 43 lines]
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200511/1
Allen Browne - 29 Nov 2005 12:34 GMT
Is it possible ... in a NON-UPDATABLE ... to STORE ...

Huh????

Am I missing something, or is that an oxymoron?

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Posting boils down to this:
> Is it possible, once a record is located in
[quoted text clipped - 3 lines]
> YES:  Explanation___________
> NO: Just can't happen, let it go.
John Vinson - 29 Nov 2005 17:21 GMT
>Posting boils down to this:
>Is it possible, once a record is located in
[quoted text clipped - 3 lines]
>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. - 30 Nov 2005 11:50 GMT
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.

> >Posting boils down to this:
> >Is it possible, once a record is located in
[quoted text clipped - 17 lines]
>
>                   John W. Vinson[MVP]
John Vinson - 30 Nov 2005 17:58 GMT
>Thanx for all the help.
>Local Access/VB users group sez
[quoted text clipped - 20 lines]
>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. - 01 Dec 2005 02:25 GMT
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.

> >Thanx for all the help.
> >Local Access/VB users group sez
[quoted text clipped - 25 lines]
>
>                   John W. Vinson[MVP]
John Vinson - 02 Dec 2005 17:45 GMT
>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]    
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.