Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Brisbane
    Posts
    1

    Unanswered: Autoenter into another field from an SQL query (help).

    Hi,

    I've been trying this all day with no luck.

    I have a form with a field called "RESORTCODE" and a field called "VOUCHER#".

    If someone enters a new resort code - then I want the voucher number to be the highest number from the vouchers table - and insert a new record into the vouchers table on save (saving the client number from the client that just ordered, the resort_code and the next available voucher_id).

    The vouchers table looks like this:
    ---------------------------------------------------
    | voucher_id | resort_code | client_number |
    ----------------------------------------------------
    1 SP 10002
    2 SP 98212
    3 SP 10004
    1 CC 12341


    The Query would be something like this in pseudo code:
    ##VOUCHER_NUMBER_FIELD## = SELECT max(voucher_id)+1 FROM vouchers WHERE resort_code = '##RESORTCODEFIELD##';

    AND oninsert:
    INSERT into vouchers (voucher_id, resort_code, client_number) VALUES (xxxx, xxx, xxx);


    Sorry for my lack of access knowledge...
    It's a little off to the side from normal RDMBS apps.

    Cheers,
    Morgan
    Attached Thumbnails Attached Thumbnails screenshot.gif  

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Your voucher number would get:
    voucher = dmax("voucher_id", "vouchers","resort_code = '" & _ RESORTCODEFIELD & "' ") + 1

    THis would be put into after update of your resort code

    You can control the inserting into the voucher table via a linked sub form

Posting Permissions

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