Assign Unique Tracking Number For Re-use

    Hello Everyone.


    Situation:
    I have about 10 staff looking after parts on aircraft. Each staff member has there own computer joined by a wirless network. Each staff member looks after 5 aircraft with 1 excel file representing 1 aircraft.


    Problem:
    When working with each aircraft we add remove parts frequently. I would like each part added to an aircraft to be assigned a unique tracking number. Therefore due to the fact that the tracking number needs to be available to multiple users of different computers I am guessing the tracking number needs to come from an external source.


    I dont want to OPEN an excel file and get the tracking number because the excel files are taking along time to open accross the network.


    So basically when a new part is added I would like to get the next tracking no available for it and update the external source to show the next available tracking number.


    Can anybody suggest the best way to go about this, and the best external soure ie perhaps a excel file (remains closed), txt file or access database???


    Thanks in advance for any help.

    Re: Get A Unique Tracking Number From An External Source


    How about just a network-accessible text file? Open with exclusive access, make a backup, read it in, add a line at the top with the new number and some CSV info (date, by whom, a/c tail number, ...), write out the rest of the data, save. Discard backups periodically.


    Edit: I have a routine (from here on Oz) that reads a text file into a string. Prepending the new info would be easy. Want me to post?

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Get A Unique Tracking Number From An External Source


    SHG,


    Yes could you post the code please or the link? I was thinking something along these lines, minus the open part as I seem to have this mind set about opening a file each time. Its due to us having problems opening files accross the network , excel files especially seem to take a long time. I think due to the fact the virus checkers are doing there thing accross a slow wireless network. I dont want to have a situation where two people are opening the file at the same time either.


    I have seen some code from the likes of XL-Dennis that uses ADO.recordsets and the microsoft jet engine to pull data from closed books. But to be honest I dont know what I talking about, let alone starting to right some code. Can you work update a closed txt file?


    Dave,
    I like the idea. Though I was hoping the table would be like 0112,0113,0114,0115 etc or is that what you achieve from the table I havnt quite got it straight in my head yet. But is there the possibility you may end up with the same number? These parts are being added and removed constantly throughout the day.


    Thanks for the help guys much appreciated.

    Re: Get A Unique Tracking Number From An External Source


    I don't see how you would end up with 2 numbers the same?? If you use your prefered number conventions, the next would simply be MAX+1

    Re: Assign Unique Tracking Number For Re-use


    Reafidy,


    Here's the function.


    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Get A Unique Tracking Number From An External Source


    Quote from Dave Hawley

    I don't see how you would end up with 2 numbers the same??


    Perhaps I dont understand but I thought if two users click at the same second different computers they would both have the same static now returned say 39332.70725


    Quote from Dave Hawley

    If you use the you number conventions


    Not sure what you mean by that? Your definately a step ahead of me, Im not sure how to get from the static number as above to a system such as 0113,0114,0115.

    Re: Assign Unique Tracking Number For Re-use


    Dave could you offer anymore thoughts on the above post? Ive had a think over the weekend but im not quite up to speed with your idea.

    Re: Assign Unique Tracking Number For Re-use


    Hi Reafity
    No code just a comment; Work had a similar situation with asset tracking if two or more staff reserved the same asset instantaneously. It was a real problem.


    To fix it, they had an Unreserved Asset data base were user made choices and reserved items
    - Refresh Data Base Button -
    Then a reserved Asset data base to check that you actual did have the reservation on item


    pike


    Congratulations on tri-nations -trophy cabinet only needs one more cup; but the quarter-finals!

    Re: Assign Unique Tracking Number For Re-use


    Hi Reafidy,


    If you use an external file, (text, random access) if your system is not automatically locking on read you will have to manually lock the file or the counter record or set the read statement option to lock the record while a user is getting the next available tracking number. Otherwise you could issue duplicates.


    I like Dave’s idea of using NOW. If you are concerned about duplicates, prefix the number with the name of the workbook that’s issuing the tracking number.


    Or would it work to have each workbook issue its own tracking number from its own counter prefixed with the workbook name or some other code that identifies the workbook issuing the tracking number.


    Is the issued tracking number then used for that part if the part is added /removed from other aircraft or do you issue a tracking number for each instance of add and remove?

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Assign Unique Tracking Number For Re-use


    Pike,


    I think somesort of link to access is a possible theory but I have never used access and dont know if you can change an access database from excel? I will look into more.


    Bill,


    Thanks for the heads up on locking the file though the problem at the moment is the lead time opening the file, because of our wireless network - so im trying not to open ANY files.


    1 file represents one aircraft and when the part goes onto an aircraft for the first time thats when it gets a new tracking number. So when the part is moved from one aicraft to another its keeps its original tracking number (ie from cradle to grave the part may have been on several aircraft but would only ever had 1 tracking number): herein lies the problem with assigning a prefix.
    I need the numbers to go like 0113,0114,0115 so that I can see which order parts have been added to the aircraft adding a prefix destroys the order.


    Thanks to everyone who is helping me with this - much appreciated some good brainstorming!


    Quote from pike

    Congratulations on tri-nations -trophy cabinet only needs one more cup; but the quarter-finals!


    Yip cant wait for the boys to bring the real cup home!

    Re: Assign Unique Tracking Number For Re-use


    Well, If you want the number pattern you mention I was thinking you simply use =MAX(MyRange) +1 in any cell and read the result back into any Workbook you need the next number in.

    Re: Assign Unique Tracking Number For Re-use


    Hi Dave,


    So you are saying you have a list of parts in a seperate file with =MAX(MyRange) +1 in a cell that the other aircraft can look for the next part. But how do you update the list to show that a part has been takin?


    EDIT: Sorry I edited this post after dave had replied in the next post sorry for confusion

    Re: Assign Unique Tracking Number For Re-use


    No, you would have one central file where all these numbers and parts are stored. The other files would have a one cell reference to the =MAX(MyRange) +1. Then you simply need to check the central file, with a VLOOKUP (in code) to determine if the part already has been assigned a unique number. If it has, VLOOKUP will result in the number, if not, you use the result cell of MAX(MyRange) +1.

    Re: Assign Unique Tracking Number For Re-use


    I think you can use Access as follows:


    Create two tables in Access
    First Table “Tracking Number” consisting of Tracking Number (key), Part Number (secondary key), Date, etc. Set the Tracking Number up in the Tracking Number Table as “Auto Number”


    Second Table “Part Tracking” consisting of Part Number (key), Aircraft, Date, etc



    Step 1. Start the tracking of a part


    Excel adds a record to the “Tracking Number” table via ADO and the Tracking Number is automatically assigned via "auto number".


    Excel reads the record created back to get the assigned tracking number.


    Then Excel adds a record to the Part Tracking Table via ADO to start the tracking process.


    Step 2 Tracking the part each time add or remove or junk or return to stock etc.


    Excel reads the Tracking Number table secondary key to get the tracking number for that part.
    Excel writes a record to the “Part Tracking” table.


    You now have all the data you need to track the part. You can add as many fields as you need to identify the part, what aircraft it was used on, who authorized the add or removal, defective part data, etc.


    Some how you need a unique part number to find the correct tracking number as the part moves, unless the first step you physically affix the tracking number to the part. How will you do this?



    This is of the top of my head, but know it can be done. I'll research some of the code.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Assign Unique Tracking Number For Re-use


    Dave,


    Okay I understand more.


    So there will be a seperate file containing all the parts in a list and the formula =MAX(MyRange) +1 in a cell.
    I will have a cell which references this formula in each of the aircraft files and code to check if that number has been takin with vlookup.


    What i dont understand is once you assign a new part to an aircraft and gets is tracking number lets say number 0114 using the method you described how then do you update the parts list to show that number 0114 has now been takin?


    Bill,


    I have been researching this method too. Im cool setting up the access file but the ADO connection part has me totally confused, i will continue to research it, if you find some code please do post.
    In each of the aircrafts file when the component is added it has a part no and serial no that go with it but these are not always unique so I need the unique tracking number.



    Thanks again guys.

    Re: Assign Unique Tracking Number For Re-use


    Reafidy


    Time for bed 1:30 AM here. I'll research the ADO code tomorrow

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Assign Unique Tracking Number For Re-use


    Quote

    What i dont understand is once you assign a new part to an aircraft and gets is tracking number lets say number 0114 using the method you described how then do you update the parts list to show that number 0114 has now been takin?

    You could have a 3rd column that reads "taken", or a validation list which decreases as part numbers are used.

    Re: Assign Unique Tracking Number For Re-use


    Quote from Dave Hawley

    You could have a 3rd column that reads "taken", or a validation list which decreases as part numbers are used.


    Okay let me try and clear this up:


    We have a list of tracking nos in a file lets call it: TrackList
    And we have our aircraft files containing the parts lets call one for example: AircraftA


    So we open AircraftA and add a new part
    We get the new tracking no for this part using vlookup and our formula to search TrackList
    How then are we going to update TrackList to show that we have used the next availble tracking no?
    ie from what you are saying I have to open the TrackList file to add the value "taken" in the column next to the tracking no we have used?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login