Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Location
    Perth, WA, Australia
    Posts
    2

    Unanswered: update bound textbox

    Hi there,

    I've searched and tried VBA and dMax but can't get anything to work. Googleing with no luck as well.

    Using 2007-10 access. I have a table with fields "batch_prefix" (text) and "batch_suffix" (number).

    In a form, I'm trying to have a after update property on a combobox for the prefix that updates the suffix textbox to the max + 1 for the relevant prefix.

    The prefix is limited to another table's field but new prefixes will be added in time.

    Any help would be great.

    Cheers!
    Last edited by famethrowa; 09-14-13 at 09:36.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, the code below will do exactly what you've asked; you just need to replace 'YourTableName' with the exact name of the Table that your Form is based on:

    Code:
    Private Sub batch_prefix_AfterUpdate()
    
      If DCount("*", "YourTableName", "[batch_prefix] ='" & Me.batch_prefix & "'") < 1 Then
         Me.batch_suffix = 1
       Else
         Me.batch_suffix = DMax("batch_suffix", "YourTableName", "[batch_prefix] ='" & Me.batch_prefix & "'") + 1
       End If
    
    End Sub

    Now, if this is a stand-alone database, i.e. only one user will be using it, at any given time, this implementation of the code is fine. But if there will ever be multiple users on the database, using the AfterUpdate event of the Combobox, like this, can lead to duplication of the batch_prefix/batch_suffix combination. This can happen if

    1. UserA starts a Record
    2. Selects a batch_prefix
    3. UserA starts a Record
    4. Selects a batch_prefix before UserA's Record has been Saved to the Table
    If there are ever multiple users in the Database, at the same time, this code needs to be run at the last possible moment before the Record is Saved, which means in the Form_BeforeUpdate event. Using this method, form almost 20 years, I've never had a reported occurrence of a duplicated number being assigned.

    The only drawback to using the Form_BeforeUpdate event is that the is that the batch_suffix number will not appear on the Record until the user moves away from the Record and it is Saved.

    If you have multiple users and simply have to have the number appear immediately, the workaround is to assign the number, using the AfterUpdate event of the Combobox and immediately force a save of the Record. Depending on your needs, this may or may not be satisfactory.

    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

  3. #3
    Join Date
    Feb 2013
    Location
    Perth, WA, Australia
    Posts
    2
    Thanks Missinglinq,

    It's working well.

    It is used by multiple users but only 2 people should be interested in a particular batch at any one time and the paperwork should be only in one of those peoples hands. I've made a multiple primary key on both these fields so I think duplicates shouldn't be a problem.
    I want it to be instantly visible as many of the people using freak out about tech a bit and can't even put data into the right columns in excel (why I'm building the DB) and the visual cue will be important in re-assuring them.

    Cheers again!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Welcome to the forum!

    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

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
  •