Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2013
    Posts
    4

    Unanswered: Autonumber in the subform to start from 1 for every new record on the form

    I have two tables as listed below
    1)"projectDetails" - having details of projects in it with 'project number' as primary key

    2)"transmittalDetails" - having details of transmittals corresponding to a project in which 'transmittal number' field('autonumber'ed) is the primary key & 'project number' is the foreign key

    Relationship: "projectDetails" has one-to-many relationship with "transmittalDetails" via 'project number' field (referential integrity imposed)

    Now, I have a form to feed data into "projectDetails" and a subform to feed data into "transmittalDetails".

    My issue is that when a new record is being fed into the form "projectDetails" , autonumber should automatically start from '1' for the 'transmittal number' field in "transmittalDetails" subform rather than a number which is next to the latest 'transmittal number' in the previous record of the form.

    I am very new to access so please excuse me if there have been any mistakes in technically explaining my problem over here.

    Thanks in advance!

  2. #2
    Join Date
    Dec 2013
    Posts
    4

    autonumber to tsrat from 1 for every new record on the form

    I have two tables as listed below
    1)"projectDetails" - having details of projects in it with 'project number' as primary key

    2)"transmittalDetails" - having details of transmittals corresponding to a project in which 'transmittal number' field('autonumber'ed) is the primary key & 'project number' is the foreign key

    Relationship: "projectDetails" has one-to-many relationship with "transmittalDetails" via 'project number' field (referential integrity imposed)

    Now, I have a form to feed data into "projectDetails" and a subform to feed data into "transmittalDetails".

    My issue is that when a new record is being fed into the form "projectDetails" , autonumber should automatically start from '1' for the 'transmittal number' field in "transmittalDetails" subform rather than a number which is next to the latest 'transmittal number' in the previous record of the form.

    I am very new to access so please excuse me if there have been any mistakes in technically explaining my problem over here.

    Thanks in advance!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you re using an autonumber datatype for the transmittal details...
    ..what you must understand is that an autonumber column has no meaning outside the db, its sole function si to make certain there is a unique value.

    so if you want transmittal numbers to start from 1 for each project you will need to develop your own mechanism to do that

    do a search on the site, there's plenty of examples of how to do this
    you will probably need to write a function which returns the value to use

    they range from a naive use of dlookup
    to fully fledged multi user processes that lock the table whilst a new number is identified
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2013
    Posts
    4
    Thankyou HEALDEM for the response.

    But the last two lines in your reply were way over my head.

    I am attaching the database file which should give you an exact idea where the change is needed. When a new record is added to "projectDetails" form the autonumber in the subform should start from 1.

    I get it from your earlier response that some coding is involved such that appropriate value is returned by a function. I am very new to access and dont know how to do that coding. Can you please help me out and tell me where to put which code in the file attached to this post so that I get the result I desire.

    Thanks a ton in advance!
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    private function GetNextID(ProjectID as whatever) as Long
    'set the datatype of projectid to whatever dataype it should be
    'do some validations (sanity checks for data)
    GetNextID = dlookup("transmittalnumber", transmittalDetails, "projectno = " & projectid)
    if isnull(GetNExtID) then 'we didn't find that project in table transmittaldetails
      GetNExtID = 0
    endif
    GetNExtID = GetNExtID +1 'Our new number is.....
    end function
    you will have to change table and column names to whatever names you use
    if you have use a string/text datatype for the projectid then delimit it with speech marks
    Code:
    "projectno = '" & projectid & "'")
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2013
    Posts
    4
    Where to place this code in the database file? Is GetNextID equivalent to ProjectID?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its a function that returns the next ID to use (hence why its called GetNextID)
    if you only ever want to get a new function then you could insert the code in the form that uses it. however id suggest that you insert thise code into a new code module. functionally it makes no difference, but if you are going to develop stuff in DBA, then it makes sense to have all non user interface stuff in a common code module, so you can call the same function from other forms, reports & queries int he same project, or export the code to another project

    Is GetNextID equivalent to ProjectID?
    no
    seeing as you supply the projectid to the function as a parameter it would be plain silly if they were equivalent as the function would then not be needed.

    its a function that looks in the table transmittal and returns the highest value for the transmittalid for the specified project
    ...or at least it should
    instead of dlookup use dmax

    you will also need to delimit the table name "transmittalDetails"
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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