Results 1 to 7 of 7
  1. #1
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15

    Creating a serial number that is NOT autonumber

    Hi,

    I have a table called "OrderDetails" with following fields:

    Num
    OrderID (Primary key)
    Product
    Quantity


    Price

    I want to create a data entry subform that can used to enter order details in this table such that, for a given OrderID, the Num field is automatically set to previous number + 1. For example, for OrderID = 12, if there are 4 products that need to be entered, the 4 records should automatically take 1 , 2, 3, 4.

    Any suggestions on how to do this?

    Thanks
    Reema

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Having this value generated by dependent calculation is risky. What if one of the detail records is deleted? This sequence will then have a gap.

    This sequential numbering of order detail records can be generated when need. Textboxes on report have a RunningSum property that can give this output. It can also be done in a query.

    If you really want to do this during data entry, will require some VBA coding. Try presetting the textbox DefaultValue property to 1. That way the first record will always start with 1. Then in the AfterUpdate event of the textbox, reset the DefaultValue property = Num + 1.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by June7 View Post
    Having this value generated by dependent calculation is risky. What if one of the detail records is deleted? This sequence will then have a gap.

    This sequential numbering of order detail records can be generated when need. Textboxes on report have a RunningSum property that can give this output. It can also be done in a query.

    If you really want to do this during data entry, will require some VBA coding. Try presetting the textbox DefaultValue property to 1. That way the first record will always start with 1. Then in the AfterUpdate event of the textbox, reset the DefaultValue property = Num + 1.
    Thanks for this help!! I will try these out...

    But this leads me to a more basic question - fundamentally, through VBA coding, how do I assign a value to a textbox whose control source is a table / query field?

    thanks for your help..
    reema

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Me.textboxname = some value/expression here if value is literal text include inside quotes

    or if field is included in the form RecordSource

    Me!fieldname = ditto above

    Trick is figuring out what event to put this in.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by June7 View Post
    Me.textboxname = some value/expression here if value is literal text include inside quotes

    or if field is included in the form RecordSource

    Me!fieldname = ditto above

    Trick is figuring out what event to put this in.
    Thanks - yes, what event to put it in is exactly where i am getting stuck.. I guess what I want to know is the order in which things happen at the back....

    Let's say I have a table containing 3 fields and a form with 3 textboxes which has this table as its control source. I am using the form to enter new records in the table.

    1. Focus gets on a text box1
    2. User types in data and presses enter
    3. focus is on textbox2 and user types in data
    4. focus is on textbox 3 and user types in data

    at what stage is a new record created in the table? right after step 1 or after all 4 steps are done? once i understand this correctly, i guess i can figure out what event to put the vba code in..

    thanks...

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A new record is started as soon as data entered into a field but new record and record edits are committed to table when move to another record or record otherwise loses focus (as in moving from subform record to main form) or close form or run code DoCmd.RunCommand acCmdSave. Until committed can undo and abort new record or edits.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15
    Ok, got it.

    Thanks

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

Similar Threads

  1. Automatic Serial Number in Report
    By jamil_kwi in forum Reports
    Replies: 11
    Last Post: 01-19-2015, 11:09 AM
  2. Processor Serial Number
    By Azeez_Andaman in forum Programming
    Replies: 2
    Last Post: 08-16-2011, 11:33 AM
  3. Sorta Random Serial Number Generator
    By Cuselco in forum Programming
    Replies: 3
    Last Post: 08-27-2010, 12:05 PM
  4. Help On Creating Automatic of a Serial Number
    By lm_lopes in forum Programming
    Replies: 4
    Last Post: 03-10-2010, 06:47 AM
  5. Auto Sequence String Serial Number
    By eddyc99 in forum Programming
    Replies: 2
    Last Post: 10-02-2009, 08:11 AM

Tags for this Thread

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