Results 1 to 10 of 10
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Continuous Form/Combo Box

    Hi all!

    I'm using a continuous form to generate drawing numbers for steel fabrications. Each number is simply incremented by 1 every time but is prefixed with the page format size (A1, A2, A3...).

    I have the combo box pulling the format size from tbl_Format and then using a text box to combine the combo box selection with the Drawing_ID auto number
    Code:
    =[Format_Size] & "" & [Drawing_ID]
    .



    This is all working fine, except for when you want to abort the input by pressing esc as the form disregards the number created initially, then skips to the next number. Also, if the format size is changed then the number will not update the prefix in the text box.

    Please could someone point me in the right direction to stop this happening!

    Many thanks!

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Store the two values separately, and concatenate the for display purposes.
    Storing the effective result of the two fields means trying to update them all the time if either change.

    As for the esc problem, capture the esc key event in the forms Before_Update event and issue a cancel = true and Me.Undo, that should prevent the record form being saved which is why it misses the number out.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Hi again Minty,

    By storing the values separately, do you mean in a separate table containing just those two fields?? They already exist as separate fields in tbl_Drawing_Register as Drawing_ID and Format_Size so I thought that the code posted above as the Control Source of the text box would concatenate them??

    Esc key code, as I have it is below but doesn't appear to have the desire effect

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    
    Cancel = True
    
    
    Me.Undo
    
    
    End Sub

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sorry I misinterpreted your post a bit. If you are only displaying the data then your approach should work. You could move the concatenation into a query based on your table, that should solve it not updating, if you add a refresh to your after combo update event.

    If Drawing ID is an autonumber, (and for simplicity sake it probably should be) then is is a little harder to control the missing number issue, as the numbers are issued as soon as a new record is created (On an access table, the same isn't true for a Linked SQL table), and as you have observed they are discarded if the record is cancelled.

    Unless it's absolutely super important to have a truly sequential number don't worry about it, if it is simply a unique reference to that drawing then leave it as it is.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    You could move the concatenation into a query based on your table
    This seems like a possible solution. Do you think I would be able to somehow create a query that temporarily holds the information before writing it to the table where it is stored? I'm also wondering how this would work with multi users. For example, if more than one user tried to create a new drawing number in the register, the information could first be stored in a temporary table until the data entry has been finalized? Then if one of the records has been discarded, the following records move up a level until such an appropriate time is available for the data to written into the register table?

    I'm no expert but thought I'd just put the idea out there.....!!

    On reflection, the drawing number would be required immediately so may not be the best idea!!
    Last edited by Beanie_d83; 06-05-2018 at 12:41 PM. Reason: Brain Fart

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't use an autonumber field for anything other than identifying the record, it should not form part of any meaningful 'in life' data. If you want an incremental number, investigate using what is known as 'dmax + 1'

  7. #7
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Hi folks,

    Just wanted to say thanks for your help with this. I've taken your advice and used Dmax +1 as the auto number was not reliable for my application! Only thing I can't seem to do is get the format of the number as "0001....." unless I start at 1000 as in the code below.

    Code:
    Private Sub cbo_Format_Select_AfterUpdate()
    
    
    If Me.NewRecord = True Then
            Me.[Drawing_Count] = Nz(DMax("[Drawing_Count]", "[tbl_Drawing_Register]"), 1000) + 1
            
        End If
    
    
    End Sub

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think if you set the format to 00000 in the table you will see what you need ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Unfortunately not as I think it's because I'm using code to create the number? When I change the code to , 0000)+1 it seems to automatically change it back to just , 0)+1.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    minty is talking about the format property or format function

    e.g. format(1,"00000") will convert to text with preceding zeros - "00001"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Continuous form Combo box control
    By dinsey90 in forum Forms
    Replies: 4
    Last Post: 02-07-2018, 02:48 PM
  2. Combo box on continuous form
    By msmithtlh in forum Forms
    Replies: 12
    Last Post: 11-13-2017, 04:52 PM
  3. Filter continuous form by combo box
    By revolution9540 in forum Forms
    Replies: 10
    Last Post: 08-04-2015, 02:16 PM
  4. Replies: 1
    Last Post: 11-24-2011, 07:45 AM
  5. Cascading Combo box in Continuous Form
    By neo651 in forum Access
    Replies: 1
    Last Post: 09-15-2011, 02:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums