1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sequential numbering in Access

Discussion in 'Business Applications' started by stuarta, Jun 4, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    Can anyone here help me with a problem I have in Access.

    I'm trying to create a form which would have 3 checkboxes, for Air, Electrical and Mechanical and next to them, an reference number for each.

    This reference number needs to be sequential depending on the date

    i.e
    M01/05/08 would be first mechanical job in may 08
    E10/05/08 would be 10th electrical job in may 08

    and then the first number would reset to 01 at the beginning of each month. I've been given some code, but I'm a bit of a beginner in access so unsure what to do.

    So far, I've created 3 checkboxes with 3 text boxes next to that which references back to the 3 columns in the table, labelled air, electrical and mechanical. I've been told to enter this code
    Code:
    Public Function GenerateProductID(ProdType As String) As String
    
    Dim Prefix As String
    Dim StrNextID As String
    Dim Rs As DAO.RecordSet
    
    Prefix = Left(ProdType,1) ' (E)lectrical, (M)echanical or (A)ir
    
    'Select all product codes that begin with the prefix and end with the current month and year
    Set Rs = CurrentDb.OpenRecordset("Select * From TblProducts Where Left(fldProductCode,1)='" & Prefix & "' And Right(fldProductCode,5) = '" & Format(Date(),"mm/yy") & "';")
    
    'Is there any previous orders for this period?
    If Rs.EOF And Rs.BOF Then
       StrNextID = "01"
    Else
       StrNextID = Format(Rs.RecordCount +1 ,"00")
       Rs.Close
    End If
    
    Set Rs = Nothing
    
    GenerateProductID = StrNextID
    
    Exit Function
    but not sure where

    and then

    Code:
    Me.ControlName = GenerateProductID(Me.ProductType)
    and also

    Code:
     If Me.CheckBoxA = True Then
      Me.ControlName = GenerateProductID("Air")
      'Add the remaining code here 
    End If
    
    If Me.CheckBoxM = True Then
      Me.ControlName = GenerateProductID("Mechanical")
      'Add the remaining code here 
    End If
    
    If Me.CheckBoxE = True Then
      Me.ControlName = GenerateProductID("Electrical")
      'Add the remaining code here 
    End If
    which I'm assuming replaces the previous section.

    It was mentioned that I have to create an unbound box for the first function which I've done but not sure where to enter it and guessing that I have to put something on the checkboxes in the 'after update' field.

    Can someone help me please.
    Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    If this is Record based then can you have all 3 Check boxes ticked at the same time, or is this a case of just One of the three applies?
    If it is just one of the 3 applies then I would use an Option Group rather than the Check Boxes and just one Reference Number Field unless you have a special reason for showing all 3.
    That is pretty good VBA Code, but without running it I am not sure if this line is correct
    StrNextID = Format(Rs.RecordCount +1 ,"00")
    as it creates the next number OK, but does not appear to add the Prefix or Month and Year back in to it.
    The Public Function actually goes in a Module.
    The 3 "If Me.CheckBox" etc go into the After Update procedure each rlevant CheckBox.
    I think the
    Me.ControlName = GenerateProductID(Me.ProductType)
    goes where he has written
    'Add the remaining code here
    in each of the CheckBox events.
    i.e Set the type of Checbox Ticked, Go and get the Next number in the correct Sequence and then enter it in the Required Field to display it.
     
  3. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok,

    I've placed the

    Code:
    Private Sub CheckBoxM_AfterUpdate()
    If Me.CheckBoxM = True Then
      Me.controlname = GenerateProductID("Mechanical")
      Me.controlname = GenerateProductID(Me.ProductType)
    
    End If
    End SubCODE]
    
    into each of the relevant checkboxes after update.  I had no errors until I added the last line.
    
    I inserted the function into a module and just called it module1.
    
    The 3 checkboxes I have got are named CheckBoxM/A/E.  I have nothing in the unbound text box which displays the ID which I'm assuming I need.
    
    I also get an error message when I check the boxes saying
    
    The expression After Update you entered as the event property setting produced the following error:  Ambiguos name detected: CheckBoxA_AfterUpdate
    
    Oh and yep, I possibly need 3 ticked at the same time
     
  4. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok I've got rid of that error, had a rogue piece of code in there. Noticed that in the function it was referencing to a table called TblProducts and field called ProductCode so I've created a table called this, got 1 column with Electrical/Mechanical/Air under producttype and another with A/E/M under productcode.

    I've altered the checkbox to

    Code:
    Private Sub CheckBoxM_AfterUpdate()
    If Me.CheckBoxM = True Then
      Me.Module1 = GenerateProductID("Mechanical")
      Me.Module1 = GenerateProductID(Me.Mechanical)
    
    End If
    End Sub
    where module1 is the name of my module. I now get a compile error saying Method or data member not found on the .module1.

    Hopefully getting closer
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    stuarta, in your Module1 does this still appear as the first line
    Public Function GenerateProductID(ProdType As String) As String

    if so then you are referencing the wrong thing here
    Me.Module1 = GenerateProductID("Mechanical")

    it should be
    Me.ControlName = GenerateProductID("Mechanical")
    where "ControlName" in the Me.ControlName is the name of your Unbound Field.
     
  6. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok, I've changed to

    Code:
    Private Sub CheckBoxM_AfterUpdate()
    If Me.CheckBoxM = True Then
      Me.mref = GenerateProductID("Mechanical")
      Me.mref = GenerateProductID(Me.Mechanical)
    
    End If
    End Sub
    where mref is the name of my text box.

    I'm now getting however

    The expression AfterUpdate you entered as the event property setting produced the following error: A problem occured while Microsoft Office Access was communicating with the OLE server ActiveX Control
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    stuarta, you do not need the me.ref code in twice, so delete the second line.
     
  8. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok deleted the 2nd line and still same error.

    I not sure if my tables and fields are correct for the references
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    In VBA the Table and Field names have to exact otherwise it won't work.
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Can you attach a Compacted & Repaired, Zipped copy of the database?
     
  11. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok thanks, I've attached the db. It's only basic at the moment as I want to get this numbering working before I proceed any further
     

    Attached Files:

  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    stuarta, sorry I should have specified it needs to be saved as an Access 2000-2002 dtabase for me to open it. I don't have 2007.
     
  13. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok reattached as 2000
     

    Attached Files:

  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    stuarta, the Function refers to the Products table but it is looking for a Product id that is in the same format as your "ref" fields are ie. "A01/06/2008", so it can't find it.
    I don't think that your table and form design are correct, the person supplying your original code also thought that you should only have one "ref" field.
    Can you confirm that each record can only have one of the 3 types of ref, air, electrical or mechanical please?
     
  15. stuarta

    stuarta Thread Starter

    Joined:
    May 3, 2006
    Messages:
    281
    ok, thought possibly the design was wrong somewhere. What I'm after as a result is for the individual id to display like Asequential number/month/year so A01/05/08 and then the sequential number resets every month.

    One job might have Air, Electrical and Mechanical work needing to be done on it so will need 3 id's for each section.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Sequential numbering Access
  1. Jack1000
    Replies:
    4
    Views:
    285
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/718398

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice