Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    85

    Unanswered: default value for textbox

    Hey,

    I have my default values for my text box working, but i have one problem. The field is an auto number field. I have the text box showing my the next number in its sequence. it gives me the last known number in the DB incremented by 1. The problem is that with auto number that may not be the next number. so is there a way to show what number is next in the Autonumber? (it is incremented). here is what i have:

    Code:
    Private Sub Form_Load()
    txtfundid = DMax("FundNameId", "Portfolio_Manager_Section", "") + 1
    txtcompanyid = DMax("[Management CompanyID]", "Address_Information", "") + 1
    
    End Sub
    thanks,
    Mike
    ________

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    If you are calculating the max ID anyway, is there a reason why you don't just remove the auto number and just use the value you calculated?

    Steve

  3. #3
    Join Date
    Jun 2012
    Posts
    85
    heres why, I ave my auto number giving my ID, for my company, that ID number is then put into every table undercompany ID a one to many relationship. that allows me to keep track of information for the company, i search on that ID number. I have made a spreadsheet in excel to send to potential clients. they fill out my questionaire send it back to me. I run macro which ssets my data up in excel ready to append to my access tables. from there i append all the data from my one form. the catch is i need to what numbers are comin next so i can fill in the company id in the spreadsheet so its ready to import. With referential integrity it wont allow me to do it unless i have the corresponding ID. make sense?

    thats why i need to know the next Id number. auto number is nice so then i dont have to type in the number each time i add in a company.
    Mike
    ________

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Referential integrity doesn't require auto numbers in order to work. It doesn't matter whether the database's own code generates the number, or if your code does.

    If you want to know what the next number is, you need to move away from auto numbers and implement your code in the Value property, rather than the Default property.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so create a row in your db, that identifies the organisation, take note of that value, then add the value to the excel spreadsheet on import.
    or import the data into a temporary table, then run a query which requests the ID and adds it to the DB
    OR as Weejas suggests allocate a number yourself.

    autonumber is there as an easy way to uniquely identify a specific row whch is a requirement of a relational db.. that each and every row can be uniquely addressed/identified. whether you create that unique identifier as a system generated value, a combination of several columns or a manually created value is irrelevant. but do not be tempted to use autogenerated numbers outside the scope of the system that generates then number. ferisntance such numbers should not be used for order numbers, GRN's invoices and so on.. unless you take pleasure in peeing off accountants who like to see sequential numbers. I do enjoy winding up accountants just for the hell of it, but missing numbers to them causes grief at audit time when things are stressfull enough
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2012
    Posts
    85
    thanks fo rthe input guys, appreciate it!!!
    Mike
    ________

Posting Permissions

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