Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've been trying to follow this thread, but not understanding.

    Quote Originally Posted by Squirrel_44 View Post
    The identifying (Serial) number of the item is "XX-000".
    It sounds like you have a table "Products".
    Table "Products" has (among others), 2 text fields :
    "BatchID" that has the 2 letters of the item and
    "SequenceNumber" that has the 3 character sequence number of the item.

    Does each item in the batch get the same identifying (Serial) number or does each item get a different number? If there 100 items in the batch, does every item get a "BatchID" of "BA" and a sequence number of "-055"? Or does each item get a number from 1 through 100?
    Is there a 3rd field that has the identifying (Serial) number of the item ("XX-000")? Or how is it generated? In a query?




    If an item is returned to be re-worked and it was made before last year, is the identifying (Serial) number only something like "AD"? And you want to be able to give it a sequence number? (something like "-099")?
    If an item is returned to be re-worked and it was made after last year, is the identifying (Serial) number something like "AR-061"?

  2. #17
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    Actually Ssanfu I think you are understanding quite well.

    Each Serial Number is unique to each item. So for Batch AA which has a qty of 100, the Serial Numbers will be AA-001, AA-002, AA-003 etc.

    I have the following:

    TABLES:
    Batches - Batch ID (Autonumber)
    BatchNumber (ShortText) then in the table I've written the AA, AB, AC etc.

    Products - ProductID (Autonumber) this is the final Report Number
    SequenceNumber (Number) this is the Serial Number suffix
    BatchID (Number) this is the Prefix Lettering

    FORM:
    Fields in the form are:
    BatchID (In Before Update is where the Code is)
    ProductID (Report Number)
    Text Box with the following Control Source =DLookUp("[BatchNumber]","Batches","[BatchID]=" & [BatchID]) & Format([SequenceNumber],"-00")

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yo could use a 2 stream approach as I mentioned. That would mean that db user would take one approach or the other depending on what part comes in. Or you can stick with the current approach.

    Either way, you can compare the values as in

    ?"AQ" > "AR"
    False

    ?"AQ" < "AS"
    True
    and direct code flow accordingly. So if > AQ, skip out of the auto numbering (cancel the update) and present a message, perhaps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    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
    For the database size, do a Compact and Repair, then make a copy and put it in zip format.

    Re your posts/this thread generally: It isn't that you explanation is not clear. It is more that you have omitted major pieces. We now have some idea why AQ is important. However, you are trying to maintain a scheme that may have "lived its life" and is no more. You are now faced with Items created in the last year and they are numbered/identified XX-***. Why don't the users look at the Item and record the identity-- are there no physical identifiers on each Item?

    General logic would seem:
    If Item has only a Batch number, then use your database/app to assign a unique identifier and do the overhaul.
    If Item has a full identifier (Batch + sequenceNo), then record the number and do the overhaul.

    It seems there is a breakdown in the business process for receiving Items for overhaul. What exactly is the process -step by step. Who does what, when?

  5. #20
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    Click image for larger version. 

Name:	AB 002.jpg 
Views:	8 
Size:	20.8 KB 
ID:	38113Click image for larger version. 

Name:	FormView.jpg 
Views:	8 
Size:	9.4 KB 
ID:	38114Click image for larger version. 

Name:	FormDesign.jpg 
Views:	9 
Size:	22.5 KB 
ID:	38115

    I still cant get the database under 4,500KB for whatever reason...this job is seriously driving me crazy LOL.

    I have uploaded a couple of photos though. The first being a photo of the Stamped Serial Number on one of our items. The AB determines what batch the item was manufactured under (when it was made, to what standards and other vital information for certification of the item). The 002 determines the number that it is in the batch qty of 100...this one is the 2nd in the batch. This AB-002 is unique to this specific item ONLY.

    Your general logic is correct.
    If the item has only a batch number than my code assigns a unique identifier during the overhaul
    If the item has a full identifier (Batch & SequenceNumber) then I need to be able to record that number

    My second photo is of the (very simple) FormView. The Report Number is an autonumber, no worries there.
    My third photo is of the FormDesign. The circled BatchID is where the code is (I've included it again below). The "Serial Number" is a text box that uses the DLookUp to combine the BatchID and the SequenceNumber to create the final Serial Number (unique identifier).

    I work in the Mining industry in Australia and the way overhauls work is that the Colliery (Mine Site) will determine when they can financially allow a machine to leave the mine (as this will close down part of their mine while the overhaul is underway) and come into the workshop for overhaul. We then strip the machine of all certified items and overhaul them to certified drawings, reports etc, before everything is reinstalled on the machine, reports all collated together and then machine and documentation sent back to the Colliery. Because we rely on the Colliery to decide when something comes in nothing is in any chronological order. And because we are in the Mining industry there are very strict rules on identifying every tiny item individually and being able to know where every specific item is, what machine it's installed on, what Colliery it's located at etc in case of some unfortunate disaster and everything from the Colliery's procedures, to all the different machines on the mine site, to the manufacture of a tiny item to every time it's been overhauled and can then be scrutinized to see if the manufactuerer/overhauler of a specific item could possibly be to blame, or if it was some fault of the colliery, or just an unfortunate mishap etc.

    I hope this helps.

  6. #21
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Squirrel_44 View Post
    My second photo is of the (very simple) FormView. The Report Number is an autonumber, no worries there.
    Uh..... well, maybe a worry. Autonumbers should never be displayed on forms/reports. See Microsoft Access Tables: Primary Key Tips and Techniques for the reasons. (Read it several times.)


    Do you have a Dropbox account or One Drive that you could use to share the dB?
    Downside is that a lot of people won't want/be able to download the dB due to security concerns.
    Last edited by ssanfu; 04-16-2019 at 04:11 PM.

  7. #22
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    I am not sure I entirely understand that link lol.

    I do have a dropbox account. Where can I send the database to through there?

  8. #23
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have a Box account to transfer large files. This is how it works in Box:

    You've done a "Compact and Repair" of the dB ( in Access)?
    Then you closed the dB and compressed (zipped) the dB?

    Open your DropBox account.
    Upload the zipped (compressed) dB.
    There will be a way to share just the dB file. Copy that link and paste it in a post here.

  9. #24
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    Thank you so much for that. I've never used DropBox before.

    https://www.dropbox.com/s/9jm7o1m1612inhn/TEST.zip?dl=0

  10. #25
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I tried to de-compile this dB, but it didn't make the dB smaller.
    I created a new dB in A2010 and imported all objects. The size then dropped from 4380KB to 612KB.

    For anyone interested, the zipped dB is attached.
    Attached Files Attached Files

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I tried to de-compile this dB, but it didn't make the dB smaller.
    It has 7 pictures and a PowerPoint file in a hidden table.
    Have at it!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-14-2017, 02:19 AM
  2. Reports - Manual Filter/Changing Criteria
    By DavidQMP in forum Access
    Replies: 2
    Last Post: 07-01-2016, 08:43 AM
  3. Replies: 10
    Last Post: 10-07-2015, 05:31 PM
  4. Replies: 1
    Last Post: 11-18-2014, 11:19 AM
  5. Replies: 7
    Last Post: 12-10-2013, 11:30 PM

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
  •  
Other Forums: Microsoft Office Forums