Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    108

    Unanswered: Record Numbers in a Tabular Form

    now, i know that access is a relational database and does not have record numbers, however i know what i am trying to do has to be possible:

    In my form, i have a subform, that is tabular.

    upon loading the form, regardless of the ID, i want numbers on the left side of the form, displaying the current line item. so for ex:


    here is my table:
    Code:
    clm1....clmn2...clmn3
    rec1.....rec1.....rec1
    rec2.....rec2.....rec2
    rec3.....rec3.....rec3
    rec3.....rec4.....rec4
    rec5.....rec5.....rec5
    The form presents some of the data:

    Code:
    clm1....clmn2...clmn3
    rec1.....rec1.....rec1
    rec5.....rec5.....rec5
    but i would like it to present that data with line numbers like such:

    Code:
    ln-num..clm1....clmn2...clmn3
    ...1....rec1.....rec1.....rec1
    ...2....rec5.....rec5.....rec5
    i know this is possible in reports, but i need it on the form...any ideas?
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Add a numeric field to the table. Number each record with VBA?

    Why do you want to do this?? The normal way is to have a primary key field that identifies each record, not an arbitrary line number.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2006
    Posts
    108
    well i have the ID, my issue lies on the generation of the form.

    at work, we use an access database to process RFQ (request for quotes)

    i have 2 tables (RFQ and RFQ_parts) where RFQ contains the RFQ data and RFQ_parts contains the different part numbers for a specific RFQ

    when i load the RFQ, i could use the ID field to number the record but then it would look like this:

    Code:
    ln-num..clm1....clmn2...clmn3
    ...1....rec1.....rec1.....rec1
    ...5....rec5.....rec5.....rec5
    what i would like to happen, is when the form is generated, it creates "line numbers" in front of each record.


    so that it basically shows the records like this:

    Code:
    ln-num..ID....clm1....clmn2...clmn3
    ...1...1....rec1.....rec1.....rec1
    ...2...5....rec5.....rec5.....rec5
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why? I often have subforms [of RFQ Parts] that would show:

    237 ....
    7732 ..
    23......
    3822 ..

    But so what? You don't even need to show the user these values. As long as Access can identify which record it is, then it doesn't matter.

    I understand what you want to do, no need to explain it again. The only way that I can think of atm to do it is as stated in post 2, but I really really strongly advise you to just not go there. You just don't need to do what you are asking for.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jul 2006
    Posts
    108
    well the reason being, is when we reference the RFQ parts to simplify matters we say "line item 1 and 2" or "line item 17" needs w/e..

    is there a way to do this in a query/SQL

    and what event could i write the VBA in, i dont recall an event that fires for each record loaded into the tabular form.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so store an item number as part of the claim record number......

    As I see it you want something like an orderitem number.... there is no inbuilt mechanism that Im aware of to do that in any db front end.... so you will have to do it yourself
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    there is no inbuilt mechanism that Im aware of to do that in any db front end.
    MySQL has this function - an option of the identity function if memory serves.

    And it's also there is in SQL Server 2005+, but as a derived value in the form of Row_Number()
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2006
    Posts
    108
    epic.fail.for.access

    they have it built in for the report designer but not the form designer. that sucks

    thanks for all yalls help


    EDIT:

    i dont know why but georgev, you sprung an idea out of me..
    i created a function in the from's VBA and called it row_number
    and set the datasource of the textbox to:
    Code:
    =(Rownumber() + 1)
    i dimmed a public variable above the function, and returned that variable as the functions value, then added 1 to the value of the variable:

    Code:
    Dim x As Integer
    Public Function rownumber()
    rownumber = x
    x = x + 1
    End Function
    worked like a charm

    It may not work for every situation, but because of the way i have built my access app, users cannot navigate through the "Details" section of a record, only tabular forms, thus the variable is reset every time a new record opens.
    Last edited by jwalker343; 12-08-08 at 14:38.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •