Results 1 to 4 of 4
  1. #1
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10

    Help with Sequential Numbering

    I have a two tables called “tblProject” and “tblSample” related by “Project_No” field (one to many) as shown below. Each Project has Many Samples.

    tblProject
    Project_No Project_Name
    P060801 A
    P060802 B

    tblSample
    Sample_ID Project_No Sample_No Sample_Code
    1 P060801 01 P060801-01
    2 P060801 02 P060801-02
    3 P060802 01 P060802-01
    4 P060802 02 P060802-02
    5 P060802 03 P060802-03

    I also have “frmProject” which is the main form and “frmsample” which is the subform.

    Each time I add a new sample relating to a particular project and click on the “Save” button on the form “Sample”, I want to generate a sequence of numbers for that particular project. The sample number should restart for a new project selected.



    I need help with writing the code for generating sequence of sample numbers for the “Sample_No” field based on the project I select.

    As shown in the table above I can concatenate to display that way I want it to later for the “Sample_Code” field. I have about 3 weeks experience with access and know very little coding let alone using conditions. (I think I need to use “Dmax” and maybe some if statements but have no clue as to how)

    I would really appreciate it if I could get help with the coding or at least how to go about it.

    Thank you very much for you time!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The first sample db on this link should help.

  3. #3
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10
    Thank you much RuralGuy, that was perfect!!!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're welcome. We must be ready for the Solved thread now, right?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Assign A Sequential Number To A Table Row
    By KramerJ in forum Programming
    Replies: 11
    Last Post: 04-08-2009, 08:48 AM
  2. create sequential id
    By proudestmnky1 in forum Programming
    Replies: 0
    Last Post: 12-16-2008, 12:10 AM
  3. Numbering records
    By knightjp in forum Database Design
    Replies: 0
    Last Post: 08-17-2008, 07:07 AM
  4. Auto Numbering
    By rkruczk in forum Forms
    Replies: 0
    Last Post: 10-09-2006, 04:25 AM
  5. Page numbering glitch
    By kfinpgh in forum Reports
    Replies: 3
    Last Post: 08-17-2006, 08:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums