Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Access forms serial numbers

    I have table to register filled receipt costumer,receipt are with serial numbers,book has 20 receipt, steward are charge with book, and are registred in another table, example
    Name: John Smith, receipt from: 202021 to 202040
    Can u make code to allow adding receipt from 202021 to 202040 only yo John Smith, and to be informed when all 20 receipt are registred on database.
    Thanks in any advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. How are the tables organized (names, columns names & data types) ?
    2. What relationship(s) exist(s) among the tables?
    3. How do you define the ranges of serial numbers (rule) and how do you store them ?
    4. Do you want to insert the serial number in one batch (bulk operation), or one by one as needed?
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    11

    Access forms serial numbers

    1.I have one table with , Name (text) , Surname(text), Id numbers (numbers), Date (date/time),time (date/time),( numbers (numbers),currency (currency) etc
    2.There is no relationship because i have just one table,
    3.I define the ranges of serial numbers with auto numbers, and I auto store, I just add from forms,
    4.I want to insert the serial number in one batch.
    Thanks in replay.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As you are relying on agents with a book of 20 receipts
    what you could do is use some VBA to allocate/reserve 20 receipts at the time of issue
    how you do that depends on you

    you could store the magic number somewhere (eg in a config tabel or similar)
    you could store the fact that you have issued a recipt block to someone, andpull that value + 20 as the next block available
    you could write 20 blank receipts in a table.
    you could put some code behind the form that records receipts and use that to trigger whatever warning / actions you need

    you cannot rely on autonumber for this

    to find out when all 20 receipts have been used, then you need to place some code behind the mechanism which records when a receipt is used and possibly issues another receipt book to that agent.

    doing all this in one table is I suspect meaningless. you could work out when a receipt book is exhausted when mod(receiptno,20) = 0, which relies on the modulus function.

    not understanding your table design or business requirement its hard to go further
    What is your primary key?
    be careful about using spaces in column names (it can cause problems later)
    be careful about using reserved words as column or table names as this will cause problem later

    presumably numbers is the cash amount and currency is the currency code, or is numbers the quantity of something and currency the cash value (assuming this is a single currency transaction system)

    My honest advice would be to revsit your table design
    what I think you have is at least two tables
    one for agents
    one for payments (identified by the receipt no)
    the PK for agent probably should be an autonumber (but could be one of those compound elements so favoured by accounting software. often these comprise 4 or 6 letters of the name and 2,3 or 4 digits to make the value unique
    eg
    Kallukallu could be Kalluk0072, assuming there were 71 other agents with the name prefix Kalluk

    the pk of the recipts table should be the receipt number.
    there shoudl be a FK from the receipts table to the agent table.
    you coudl if you wished allcoate a block of receipts (with zero values) if you wished.
    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
  •