Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006

    Question Unanswered: Creating unique Ref Text and Number


    I am building a database and the unique refference , the reference i have for each record is text-number eg mal-201

    the problem i have is that there are alot of refference already used in the numbering convension which i cannot change . so i cannot use Auto Number

    At the moment i have a Main form detailing the reff company contact person est with a Sub table detailing the products they have

    Main form mal-201

    Sub Form ps2

    The ideal result i would like is that when you click add new record the refference that would be selected would be the next available

    I have worked with access for a while but this is my first build i know very little VBA

    Can anyone help me?

    Kind Regards

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    in an ideal world use an autonumber column

    failing that consider using another approach to get the next available number

    the basic approach is to write a function that retrieves the next value
    the method of how you implement it is up to you

    the easiest, and simpliest is:-

    read the current highest value,... using a select max(mycolumn) as NextID from mytable
    add one to it,
    write your record,

    ..however that is not safe if the application is in a multi user environment, you could modifyt the approach to:-
    to lock the table, ... this stops others trying toget your next number... needs to be a TABLE not ROW Lock)
    read the current highest value,... using a select max(mycolumn) as NextID from mytable
    add one to it,
    write your record,
    release the lock... allows others to use the table

    ...however in a multi user environemnt that is hitting the table hard it would be better to store last used the number in another table

    secure a row level lock on the table holding the next number, ... this stops others trying to get your next number
    read the current highest value
    add one to it,
    write it back to your row containing the next number
    release the lock
    write your record
    ..thsi way round has the advantage that the base table isn't unduly tied up and the risk of read write conflicts is reduced

    So I suppose it depends what your teacher wants as part of the coursework. The quick and dirty single user approach, the slightly better multi user approach, or the multi user approach. Equally which methid do you feel upto attemtping
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2003
    ... And might I say check out IzyRider's post in the DBForums Code Posting at the top of this forum ... Nice little routine for doing sequentials ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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