Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: Access Form String + Autonumber

    I am a bit of a novice when it comes to Access forms and am having an issue with a field I am trying to create. I have a field called Area which is a string and a field called ID which is an autonumber. My intent is to have a field called report ID that is the Area field and ID field combined so that when a user creates a new record it automatically assigns the report id.

    I have tried including the Area and ID fields in the form and then setting the default value of Report ID as =[Area]&"-"&[ID]. When I do this and create a new record it simply lists "Area-" and never includes the ID.

    Does anyone have any ideas how I can tackle this issue?

  2. #2
    Join Date
    May 2010
    Posts
    601
    Normally you do NOT store this in the table but calculate it as needed.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As Boyd said, this sort of thing is re-calculated, as needed, not stored in your table. In your form move your =[Area]&"-"&[ID] from the Default Value Property to the ReportID Control Source Property and it should work.

    If you need it later, in a report, for instance, simply do the same thing, with an unbound textbox.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2010
    Posts
    6
    Thanks, the move to control source worked great for what I need to display. Now I run into another problem.

    I have another form that captures report issues and one of fields is Report ID, which is populated by the Report IDs created in the records of my original form. How would I reference that Report ID if doesn't actually exist in a table?

  5. #5
    Join Date
    May 2010
    Posts
    601
    I assume that the field [ID] is the actual primary key.

    On the form I would use a combo box that displays the Report ID but is bound to the actual primary key.

    You can make it appear to the User the the Report ID is the primary key when in reality underneath a different field is the actual primary key.

    Note: I normally use an autonumber as the primary key for every table. I usually always hide this field from the users.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I especially agree with that added note. As someone, maybe John Vinson, once said, "Autonumbers are not really fit for human consumption!" They really are intended for internal "housekeeping" in Access and should not be used for anything that a user would need to refer to/see.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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