Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Question Unanswered: custom autonumber

    Hi everyone...

    I was wondering if there is a way to do a recordcount of all records in a specific table, and then save that recordcount (along with other information) as the default field value of a record in the same table.

    I know that you can run a query asking for a count of records in a specific table, but whenever I try to run the count function in my 'default value' area of my field (in the table design view) I get an error saying the field for that table can't be found. For instance, the following query works fine for me:

    SELECT Count(Table1.id) AS recordcount
    FROM Table1;


    However, when I insert =Count(Table1.id) into my default value area in the design view for my table, I get the following error:

    Could not find field 'table1.id'

    Can anyone help me?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    why would you ever want to do this!

    assuming you've got an important "why" and you have zero concerns about two users trying to make a new record at the same time (A handles this with a "pure" autonumber, but your code is going to get complicated (a transaction??) if you attempt to duplicate this functionality) ...then write yourself a global function that returns the value you are looking for and try that as default value.

    WARNING: i didn't try it (and i don't agree with the idea of doing it).

    can't you get the same result safer & sweeter using a regular autonumber plus your funny-field: COUNT the records with autonumber less than your new record autonumber and stuff that value in the funny-field?

    izy

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    how would you go about that, though? what's the syntax?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    code for the "wrong" solution goes something like this:

    in a global module:

    public function MyRecordCountPlusOne() as long
    MyRecordCountPlusOne = DCOUNT("[ID]", "Table1") + 1
    end function

    ...and make MyRecordCountPlusOne the default


    but i still don't like the idea if you have >1 users



    izy

Posting Permissions

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