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.

Solved: Access - Counter

Discussion in 'Business Applications' started by chudok01, Dec 30, 2010.

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

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    I have Items and components in an inventory system. The way it currenlty works is each item or component counted gets a tag #. when entered into db it then takes any item that may have components and does a break out of those items also. But these items do not have a tag # associated to them. I would like for it to take the parent item tag # and add something to it, such as .1, .2, .3.

    Example
    Apples Tag 1
    Oranges Tag 2
    Apple slices Tag 3


    Apples has slices and seeds so they woud be
    Slices Tag 1.1 or 1.A
    Seeds Tag 1.2 or 1.B


    Does that make sense? I am not sure how to get this to do that in a query....
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    To get the correct relationships you should have something in the Master table that relates the records in the Sub Tables.
    I think you will need VBA code to do what you want in terms of the Tag # though, as you need to find the last value for apples, ie. 1.B to know you need 1.C.
     
  3. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    I already have the relationship with tag #. I have 2 tables the original table that has to inventory entered with tag #'s. I have another table that has all the components. Each component has the tag # associated to it that associates it to the main inventory item - so currently in the compoment table there are many of the same tag numbers. How do I add an counter and something to say add the tag # to the counter? I am thinking it would work better with #'s and not letters. My goal is to have new tag #'s so this can be entered into a seperate inventory system for counting purposes.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you need them to be in Numerical order you will need a VBA recordset to find the last value.
    A much simpler alternative would be to concatenate the Tag # and an Autonumber field.

    If don't actually need the Concatenated ID for a counting purposes as a Query can do that for you.
     
  5. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    If don't actually need the Concatenated ID for a counting purposes as a Query can do that for you.


    I'm not sure if I understand what you are referring to above....

    I get the autonumber, but how can I have the autonumber restart at 1 after each change of tag #?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You can't, that is why you would need a VBA recordset to reset to 1 and increment the Tag #.
    But for counting you can just use a query to count all tag #s.
    It depends on what you need the new number for.
     
  7. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    VBA recordset is above my head. Any ideas on where I can get help with that? I need new unique numbers that reflect back to their parent tag #.

    I am signed up for a VBA class but that does not start until March.....
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can provide you with the VBA code.
    Can I suggest that you create a Query that has the Tag # field and the new field in it.
    Group By the Tag # and set the new field to Max.
    That will find the last record for each Tag #.
    I need the query's name and the Exact names of the 2 fields.
    Where will have the VBA code?
     
  9. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    "Tag Query" - name of new query

    Names of the 2 fields
    "Tag #" - original with tag #'s
    "Tag Count" - new field set to max
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I will try and create soemthing for you & post it tomorrow.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry for the delay, we had the Grandchildren for the afternoon.
    Take a look at the attached database, when you enter a new record it will increment any current Tag #s or start at 001 for a new one

    Here is wishing you a Healthy, Happy & Prosperous New Year.
     

    Attached Files:

  12. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    Thanks! This looks great, I have a question though. The duplicate tag numbers are already in a table, so how do I use the code from the form to take the table with duplicate tag numbers and add the tag count field?
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You can use a modified version of the code in the Form's "On Current Event Procedure" that will update each Tag Count when the Record is viewed.
    To use it in the On Current event you would need

    Dim rs As Object, zeroes As String, temps As String, tempv As Integer, SQL As String
    On Error GoTo Eventerror
    If isnull(Me.[Tag Count]) Then
    zeroes = "000"
    SQL = "SELECT TagTableQuery.* " & _
    "FROM TagTableQuery " & _
    "WHERE [Tag #] = " & Me.[Tag #]

    Set rs = CurrentDb.OpenRecordset(SQL)
    'MsgBox rs.RecordCount
    If rs.RecordCount <> 0 Then
    tempv = Val(Right(rs![MaxOfTag Count], 3)) + 1
    temps = Str(tempv)
    temps = Right(temps, Len(temps) - 1)
    If Len(temps) < Len(zeroes) Then temps = Left(zeroes, (Len(zeroes) - Len(temps))) & temps
    Me.[Tag Count] = Me.Tag__ & "." & temps
    Else
    Me.[Tag Count] = Me.Tag__ & ".001"
    End If
    rs.Close
    Set rs = Nothing
    End If
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
     
  14. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    Getting an Error #94 Invalid use of Null......
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, that occurs when the tag # is the first one.
    So use this instead.

    Dim rs As Object, zeroes As String, temps As String, tempv As Integer, SQL As String
    On Error GoTo Eventerror
    If IsNull(Me.[Tag Count]) Then
    zeroes = "000"
    SQL = "SELECT TagTableQuery.* " & _
    "FROM TagTableQuery " & _
    "WHERE [Tag #] = " & Me.[Tag #]

    Set rs = CurrentDb.OpenRecordset(SQL)
    'MsgBox rs.RecordCount
    If rs.RecordCount <> 0 And Not IsNull(rs![MaxOfTag Count]) Then
    tempv = Val(Right(rs![MaxOfTag Count], 3)) + 1
    temps = Str(tempv)
    temps = Right(temps, Len(temps) - 1)
    If Len(temps) < Len(zeroes) Then temps = Left(zeroes, (Len(zeroes) - Len(temps))) & temps
    Me.[Tag Count] = Me.Tag__ & "." & temps
    Else
    Me.[Tag Count] = Me.Tag__ & ".001"
    End If
    rs.Close
    Set rs = Nothing
    End If
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
     
  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...
Thread Status:
Not open for further replies.

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

  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