Page 1 of 6 123456 LastLast
Results 1 to 15 of 86
  1. #1
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83

    AutoNumber with constraints and interfield dependencies?

    I'm new here - first post. I'm also a neophyte with Access but can already understand how powerful this database is. I have a desired task that may however be outside of this programs capabilities. First the background:



    We have a freezer that holds canes (9 of them). These canes hold boxes (10 per cane) and the boxes hold tubes (96 tubes per box). The tubes are arrayed in the box from left to right and top top bottom. Each tube is placed in a specific position and is identified by a scanned barcode. The position within the box is identified by a number going across (1-12) and by a letter going down (A - H). Tubes are added first to position A1, then A2, A3 and so on through A12. Then the next row starts with B1, B2, B3...hopefully you see the pattern.

    Within Access I have a table named "Freezer Log" with four fields that I would like to have auto populated in an interdependent manner. These fields are labeled (creatively) Cane, Box, Row and Column. When we add (one or more) tube(s), I would like to have a form with a simple question: how many tubes are you freezing today Fritz?". The answer (let's say it's 30) will trigger the appropriate position fields to be filled and then the first entry in a fifth field named "Barcode ID" will be selected and stand ready to receive the first input from the barcode reader. Once entered, it will automatically move down one entry and continue to do so until all of the specified vials have been stored.

    Here then is the task (and my question): can Access be set up so that it works within the following "rules"

    1. Only 12 positions are possible in each row and only 8 positions in each column (96 total positions per box).
    2. The "AutoNumber" needs to increment column "letters" and row "numbers" as specified above in #1, and then automatically jump to (and AutoNumber) a new box when the preceding box is full.
    3. Access needs to advance to the next barcode entry position once the tube is read into the software (assuming that can be done since we can do so in Excel). It then needs to recognize when the requested number of entries are complete.

    So, can this be done within this software application? If yes, maybe point me in the right direction so I can learn how to do so. Your patience with my long question is truly appreciated and your assistance gratefully acknowledged in advance.

    Accessed

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    can Access be set up so that it works within the following "rules"....
    Shouldn't be a problem. Would require a fair amount of VBA code, but doable.

    Do tubes get removed/deleted?
    For instance, Cane 1 is full. Can Cane 1, Box 5, Tube "E3" be deleted/removed? Would that location be reused?


    Will need to come up with different names for "Row" and "Column". These are reserved words in Access and shouldn't be used as object names.

  3. #3
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    It certainly does help Steve - thanks. VBA you say? Cold shivers runneth down me spine ;-)

    Yes, tubes will be deleted and the positions re-used on a regular basis.

    Can't use row and column? Awww crap. Rather an industry standard but will figure something out.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can't use row and column? Awww crap. Rather an industry standard but will figure something out.
    Could use "BoxRow" and "BoxColumn".... or FIELD names also could be "Apples" and "Bananas".... it is just easier if the field name is revelant to what the data is.
    Users should never see the field names - data interaction should be using forms.
    And there are labels on the form so if a field name is "BoxRow" (or "Apples"), the label caption could be "Row".

    Yes, tubes will be deleted and the positions re-used on a regular basis.
    Is there a method/scheme for filling empty tube locations? When you have to enter 30 tubes, do you start at Cane #1, Box1, Row "A", Column 1 and search for the first empty tube location?

  5. #5
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83

    Help!!

    Quote Originally Posted by ssanfu View Post
    Is there a method/scheme for filling empty tube locations? When you have to enter 30 tubes, do you start at Cane #1, Box1, Row "A", Column 1 and search for the first empty tube location?
    No doubt there will need to be. But I'll worry about that after I get some basic functions working. Thanks for the simple solution to the row and column field names.

    So, spent a good chunk of time (trying to) craft the necessary VBA just to retain the information in my first form so that the only parameter that has to be entered is the barcode. So far I displayed my uncanny ability for epic failure. Any help to set me straight appreciated.

    Here is the entry form I created:

    Attachment 24781

    I used the command wizard to advance to the next entry using the button at the top. Here's that code:

    Attachment 24782

    From the internet, I found this description on how to auto populate form fields with the last entry as a default value. Here's the original description:

    http://allenbrowne.com/ser-24.html

    Following his instructions, I created the requisite module with this code:

    Code:
    Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
    On Error GoTo Err_Handler
        'Purpose: Carry over the same fields to a new record, based on the last record in the form.
        'Arguments: frm               = the form to copy the values on.
        '           strErrMsg         = string to append error messages to.
        '           avarExceptionList = list of control names NOT to copy values over to.
        'Return:    Count of controls that had a value assigned.
        'Usage:     In a form's BeforeInsert event, excluding Surname and City controls:
        '               Call CarryOver(Me, strMsg, "Surname", City")
        Dim rs As DAO.Recordset         'Clone of form.
        Dim ctl As Control              'Each control on form.
        Dim strForm As String           'Name of form (for error handler.)
        Dim strControl As String        'Each control in the loop
        Dim strActiveControl As String  'Name of the active control. Don't assign this as user is typing in it.
        Dim strControlSource As String  'ControlSource property.
        Dim lngI As Long                'Loop counter.
        Dim lngLBound As Long           'Lower bound of exception list array.
        Dim lngUBound As Long           'Upper bound of exception list array.
        Dim bCancel As Boolean          'Flag to cancel this operation.
        Dim bSkip As Boolean            'Flag to skip one control.
        Dim lngKt As Long               'Count of controls assigned.
    
    
        'Initialize.
        strForm = frm.Name
        strActiveControl = frm.ActiveControl.Name
        lngLBound = LBound(avarExceptionList)
        lngUBound = UBound(avarExceptionList)
    
    
        'Must not assign values to the form's controls if it is not at a new record.
        If Not frm.NewRecord Then
            bCancel = True
            strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
        End If
        'Find the record to copy, checking there is one.
        If Not bCancel Then
            Set rs = frm.RecordsetClone
            If rs.RecordCount <= 0& Then
                bCancel = True
                strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no records." & vbCrLf
            End If
        End If
    
    
        If Not bCancel Then
            'The last record in the form is the one to copy.
            rs.MoveLast
            'Loop the controls.
            For Each ctl In frm.Controls
                bSkip = False
                strControl = ctl.Name
                'Ignore the active control, those without a ControlSource, and those in the exception list.
                If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
                    For lngI = lngLBound To lngUBound
                        If avarExceptionList(lngI) = strControl Then
                            bSkip = True
                            Exit For
                        End If
                    Next
                    If Not bSkip Then
                        'Examine what this control is bound to. Ignore unbound, or bound to an expression.
                        strControlSource = ctl.ControlSource
                        If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
                            'Ignore calculated fields (no SourceTable), autonumber fields, and null values.
                            With rs(strControlSource)
                                If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
                                    And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
                                    If ctl.Value = .Value Then
                                        'do nothing. (Skipping this can cause Error 3331.)
                                    Else
                                        ctl.Value = .Value
                                        lngKt = lngKt + 1&
                                    End If
                                End If
                            End With
                        End If
                    End If
                End If
            Next
        End If
    
    
        CarryOver = lngKt
    
    
    Exit_Handler:
        Set rs = Nothing
        Exit Function
    
    
    Err_Handler:
        strErrMsg = strErrMsg & Err.Description & vbCrLf
        Resume Exit_Handler
    End Function
    
    
    Private Function IsCalcTableField(fld As DAO.Field) As Boolean
        'Purpose: Returns True if fld is a calculated field (Access 2010 and later only.)
    On Error GoTo ExitHandler
        Dim strExpr As String
    
    
        strExpr = fld.Properties("Expression")
        If strExpr <> vbNullString Then
            IsCalcTableField = True
        End If
    
    
    ExitHandler:
    End Function
    
    
    Public Function HasProperty(obj As Object, strPropName As String) As Boolean
        'Purpose: Return true if the object has the property.
        Dim varDummy As Variant
    
    
        On Error Resume Next
        varDummy = obj.Properties(strPropName)
        HasProperty = (Err.Number = 0)
    End Function
    Finally, I used this code in the "Before Insert" event section using the "Form" property sheet:

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
            Dim strMsg As String
            Call CarryOver(Me, strMsg)
            If strMsg <> vbNullString Then
                MsgBox strMsg, vbInformation
            End If
        End Sub
    In theory, as soon as the barcode is entered, the other fields are supposed to populate with the parameters for the other fields. Unfortunately, what I get instead - as soon as I begin to type a number in the barcode field - is:
    There is an invalid use of the . (dot) or ! operator or invalid parentheses (Dummy)

    I added the (Dummy) part - artistic license and all.

    Anybody that can help a poor newbie understand what is wrong? As before - thanks in advance for patience and assistance.

  6. #6
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Dang - messed that up pretty bad. Can't figure out how to delete posts 6 and 7. Maybe a kind moderator can lend a hand?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Deleted 6 and 7.

    Just briefly looked at your posts. I'm not certain of your needs, but you might consider a multi dimensional array, or some other structure.
    If records(tubes) are always added in a certain sequence, this may be "relatively simple" with such an array.

    Good luck.

  8. #8
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    Deleted 6 and 7.
    Gracias Amigo!

    Quote Originally Posted by orange View Post
    Just briefly looked at your posts. I'm not certain of your needs, but you might consider a multi dimensional array, or some other structure.
    If records(tubes) are always added in a certain sequence, this may be "relatively simple" with such an array.
    I'm hoping to do something that in my mind should be rather simple? We typically will add 20+ vials to our freezer at a time. I simply want to keep all the fields constant during those entries, with the exception of the barcode, which must change with each entry. Once I get that part working, then I want to create some VBA code that will sequentially increment the cane, box, row and column positions based on a series of coded rules. For now we'll have to work through one box at a time and "leave behind" empty positions when we thaw a vial.

    I looked up multidimensional array and that seems to require more programming skills, of which I have none.

    If someone reads this and wants to earn a little extra movie cash flaunting their Access skills, send me a PM. Do a great job and it could become a regular gig for you!

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From Post #5

    Quote Originally Posted by Accessed View Post
    Unfortunately, what I get instead - as soon as I begin to type a number in the barcode field - is:
    Major EDIT: I tried the code as you posted it and do not/did not get any errors, so I deleted most of what I posted before.
    I single stepped through the code and can find nothing wrong.

    (Some how I missed the line that said the code was provided by Allen Browne.......)

    You could post your dB for analysis. Do a "Compact and Repair", then Zip it)

    In this instance, I would probably still use unbound controls (see below)
    ---------------------------------------

    One question is "How do you know where to start adding vials?"
    The starting location could be Cane 1, Box 2, Row E, Column 3.
    But if you had 30 vials to add and that starting location did not have 30 consecutive empty slots, what to do?

    (--below--)
    To add new vials, I might use unbound controls. Enter the information, scan in the barcode and have a button run code to save the record (Append query) and increment the location to the next location and clear the bar code control.

    You could have code check if there is room for the number of vials to add, find a location to start, verify the required number of locations is available, etc.
    Last edited by ssanfu; 05-31-2016 at 06:59 PM.

  11. #11
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by ssanfu View Post
    From Post #5

    This is partly because you are using the BeforeInsert event. The BeforeInsert event occurs when the first character is typed in a new record, but before the record is actually created. You type the first character and the BeforeInsert event fires.
    Somewhat surprisingly (to me) I understand this. But wait for it....

    Quote Originally Posted by ssanfu View Post
    Looking at the BeforeInsert event code, the variable "strMsg" is created but never has a value assigned to it.
    Then the function CarryOver(Me, strMsg) is called. But the function CarryOver requires 3 parameters - you provide 2.
    Not at all surprisingly to me (not at all, at all), you are now speaking Greek! This is precisely why for years I have opened the Access application and just as quickly shut it back down. But alas, such vast power should be reserved for the true Gods of Greek mythology. I will aspire to be their chamber maid.

    So study I will and come to understand what the code is trying to do and how to fix it.

    Silly me - the voices inside my head told me this wouldn't be that hard.

    I'm kidding - that's not what they really said ;-)

    Quote Originally Posted by ssanfu View Post
    That is as far as I got reviewing the code.
    You have done well master Jedi....

    P.S. is there a grade below novice? Might be best to tuck me away in that slot for a while to come...

    ---------------------------------------

    One question is "How do you know where to start adding vials?"
    The starting location could be Cane 1, Box 2, Row E, Column 3.
    But if you had 30 vials to add and that starting location did not have 30 consecutive empty slots, what to do?

    To add new vials, I might use unbound controls. Enter the information, scan in the barcode and have a button run code to save the record (Append query) and increment the location to the next location and clear the bar code control.

    You could have code check if there is room for the number of vials to add, find a location to start, verify the required number of locations is available, etc.[/QUOTE]

  12. #12
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by ssanfu View Post
    From Post #5
    One question is "How do you know where to start adding vials?"
    The starting location could be Cane 1, Box 2, Row E, Column 3.
    But if you had 30 vials to add and that starting location did not have 30 consecutive empty slots, what to do?

    To add new vials, I might use unbound controls. Enter the information, scan in the barcode and have a button run code to save the record (Append query) and increment the location to the next location and clear the bar code control.

    You could have code check if there is room for the number of vials to add, find a location to start, verify the required number of locations is available, etc.
    Good grief! I almost forgot to answer the question! I will simply have the new deposit start at the first empty position (record). We will remove/extract tubes as needed, but there will be a critical threshold that will trigger a new freeze that will replace the missing tubes. When a tube is removed, we will simply transfer the record to an archive and leave the vacated position empty until it is again filled.

    There's many additional statements that provide critical insight and no doubt I will revisit throughout the process to integrate those ideas.

    Too-da-loo

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may find this construct from Datapig based on 2 listboxes useful.
    Occupied locations and unoccupied locations is what I'm thinking.

    Did you look through the thread I mentioned in post #9?

  14. #14
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    You may find this construct from Datapig based on 2 listboxes useful.
    Occupied locations and unoccupied locations is what I'm thinking.

    Did you look through the thread I mentioned in post #9?
    I did! Truly a wealth of information in the last couple of days - more than my Mendelian pea sized brain can grasp. I'm trying to work through everything, but other "responsibilities" tend to side track my efforts.

    So far though really pleased with what you lads - and ladettes - have offered up :-)

    Please let me add - uncanny how not just similar, but identical the two cases are. Everything he wanted to do I am trying to do. Only difference really is his 100 vials versus our 96. Somehow I feel like I lost four of something somewhere.

    You either have an amazing memory or great search skills :-)
    Last edited by Accessed; 05-31-2016 at 09:56 PM. Reason: Why not?

  15. #15
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by ssanfu View Post
    From Post #5



    Major EDIT: I tried the code as you posted it and do not/did not get any errors, so I deleted most of what I posted before.
    I single stepped through the code and can find nothing wrong.

    (Some how I missed the line that said the code was provided by Allen Browne.......)

    You could post your dB for analysis. Do a "Compact and Repair", then Zip it)

    .
    I think I can do that. There's some secret squirrel stuff I might need to cleanse, but I think I can do so. My biggest concern now is that there might be something in the default settings that haven't been set, or need to be set. Any thoughts on something really silly and simple I should be looking at?

Page 1 of 6 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  2. Need to use constraints
    By tsvetkovdimitar in forum Access
    Replies: 4
    Last Post: 11-19-2013, 01:39 PM
  3. Object dependencies
    By Rob S in forum Access
    Replies: 4
    Last Post: 08-03-2012, 04:00 PM
  4. Query with multiple date range constraints.
    By younggunnaz69 in forum Queries
    Replies: 2
    Last Post: 12-26-2011, 10:45 AM
  5. Replies: 1
    Last Post: 11-07-2011, 11:42 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