Re: Finding a record number using SQL

I am all ready using a "auto number" field as the primary key but I want to
use a query to visually display the data into a grid and use an order by to
sequentially sort the data for the user. It might not be the most logical
way of doing this but it works best for my situation.


I am only looking at trying to find the row number after an insert and
refresh so that I can point to the newly inserted record by setting the
recno property within the query.

I know that I can user a query to logically order the records within the
query but am not sure of the right syntax? Just wonder if it is possible and
if so how would the sql statement look like.

Jeff
"Del Murray" <Del.Murray@xxxxxxxxxxxxxx> wrote in message
news:4759cf06$1@xxxxxxxxxxxxxxxxxxxxxxxxx
In any DB you need a primary unique key. In Accesss you can use a
"counter" type large integer and the system will increment it for you.
Include the column defined as counter in your recordset. Then when you
insert a record and post it, the db will populate the field for you. You
then do a "locate" on the key. Something like this ...

disablecontrols
insert
fieldbyname('xxx').asstring = 'sometheing'
fieldbynaem('yyy').asfloat := 12345
post
savekey := fieldbyname('counterfield').asfloat
locate('counter', savekey,[])
enablecontrols





.

Relevant Pages

  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... Jerry Whittle, Microsoft Access MVP ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: Determining if a form has a table or query recordset source
    ... how to obtain the primary key of a query? ... the generic answer is: "who says any query has one?". ... > set for insertion into an audit trail table, ... > Dim audID As Long ...
    (microsoft.public.access.security)
  • Re: [PHP] peer review (was php framework vs just php?)
    ... those POST/GET elements, the global elements and the development of the sql ... generation of a SELECT query ... html with embedded php variables ... //get the primary key for the table ...
    (php.general)