Question Modifying the Standard Inventory Database to allow for individual item recording

swinster

Registered User.
Local time
Today, 21:45
Joined
May 25, 2007
Messages
28
Hi all,

I'm looking to create a stock control log where by we can record goods moving in and out of our company. We are a Not for Profit organisation that looks after Video equipment for schools and colleges and so have many items coming for our suppliers and moving to and from various studios. We need to keep track or ALL individual serial numbers. We also have a need to link in test records on this equipment.

The built in inventory DB in Access 2007 is quite good, but I'm sure there would be a way to add a sub-form/table to the "Inventory Transaction List" whereby multiple items in/out can have individual serial number recorded (using a hand-held bar code scanner). Of course, I could just add one line in this table for each individual item received or sent, but this will be time consuming, especially if we have multiple items (or the same type - e.g. 20 specific camera models) move at one time. We don't really need to track re-order levels but again we do need to track where an individual item is and any given time.

On top of this we, test these individual items to make sure they all work properly. These test are relatively simple but are different for each item - e.g. a Camera will be tested for Pan/Tilt (up/down/left/right), Zoom in/out, and focus; whereas a screen may be tested for showing an image at specific resolutions.

I was not sure if I should simply put all possible tests for all items as individual field in a single table, but this would be very wasteful of space. Better (I think) would be to create individual tables for tests and then somehow link these to the sub-table of the "Inventory Transaction List" so that only the correct test will be shown for the item concerned. It guess it would be possible to Use the "Category" field in the "Inventory" table to link through individual items to the correct test schedule.

I'm thinking as I type this how it might be possible, but my first concern is to get multiple entries of same type items with individual serial number working. I am very rusty in this department.

Help please...
 
Hmm, the more I think about this the more awkward I thinks it going to be.

In the "Inventory Transaction List" we have a Quantity field. Depending on this quantity, will depend on how many items I will need to individually record. If these individual items are recorded in a separate table, lets say the "Individual Items" table, then changing the quantity field must dynamically add records to this table that will then need to be updated with the relevant serial numbers.

Of course, you may make a mistake with the quantity, so records will need to be dynamically removed as well.

Whether something like this can be be dynamically added to a form I don't know.

Any thoughts?
 
How will you know which are moving? Creating x records from a quantity field is doable, but doesn't seem like the cleanest approach. One way might be a multiselect listbox along with controls for "From" and "To" locations (or whatever your structure uses). Then code to add records for each selected item in the listbox is fairly simple.

Another way, if you're using a scanner, would be to set up the From and To locations and then use an unbound textbox to receive the barcode. You could code it to automatically add a record when a barcode was scanned, then wait for another scan.
 
How will you know which are moving? Creating x records from a quantity field is doable, but doesn't seem like the cleanest approach.
Yeah - I said it was awkwark.

One way might be a multiselect listbox along with controls for "From" and "To" locations (or whatever your structure uses). Then code to add records for each selected item in the listbox is fairly simple.

Another way, if you're using a scanner, would be to set up the From and To locations and then use an unbound textbox to receive the barcode. You could code it to automatically add a record when a barcode was scanned, then wait for another scan.

I like the second option. The scanner enters a caridge return after scanning a serial, so the code could look for that. I could have a label with a calculated number based on the quantity entered and the number of records that allready link to that transaction, so decremented showing the user how many more serials should be input. Once the total number of records added matches the quantity I could disable the text box.

Wadya think?
 
Sounds good. Not sure about the quantity bit, but you know your process better than I (I would think you'd just scan whatever items were sitting in front of you). The update events of the textbox would be the place for your code. I generally validate data with the before update event, take action on it with the after update event.
 
Sounds good. Not sure about the quantity bit, but you know your process better than I (I would think you'd just scan whatever items were sitting in front of you). The update events of the textbox would be the place for your code. I generally validate data with the before update event, take action on it with the after update event.

Your right about the Quantity field. I think its nice to see how many item have been moved at each transaction, but rather than enter this, it could simply be a code updated label that reflect how many items have been added/scanned into a lining table...


[Edit - updated in next post]
 
Last edited:
Slightly updated relationships with proper naming convention

Relationships2.png


Also, a and updates list of things it need to do and that I would to achieve (not complete)

As with the original, the DB will be based around the "inventory Transaction List". This is roughly what I "should"/want to be able happen.

1)Enter a new transaction (goods in/out) for an product.
2)Most of the time we have a serial number attached to an individual item, but even if they don't, we need to record that an item is gone too/come from a specific location.
3)I then should scan in the individual items associated with the transaction.
4)If the item scanned has a record in the TblEquipmentSerials Table, then add a record to the TbleEquipmentLocation Table linking the Serial Number to the Transaction number - also indicating the new location of the item
a.Could also automatically create a new transaction with the relevant item if we need to.
b.It we have selected/editing a record with associated item but the scanned item does not match the item selected then warn the user.
5)If the serial Number doesn't exist in the Serial Table, then we need to add it to the table and link it to the Item selected, then do 4) above.
a.If no tem is selected then warn user and get them to select an item.
6)(similar to 5) If there is no serial number we need to add a "NA" record to the Serial Table, link it to the Item, then do 4) above.
a.Could extend this later to provide for our own Tracking Number label.

What should we be able to do:

1)Look at a product and see the number we have plus the serials for individual items and their location.
2)Check the history of a specific item.
3)Check what/how many items are at what locations.
4)Find the information for a individual item based on its serial.
5)Check Who supplies/manufactures what
6)Can’t think of anything else at the mo, but something will probably come up.
 
To achieve this, I was thinking of using an unbound Text box (as suggested by pbaldy) on the Transactions form that ran VBA code using the AfterUpdate method. When an item is scanned or the serial in entered, there could be a couple of possibilities: -

Firstly, the user may NOT have selected/created a record for a Transaction, so the code will look up matching serial number in the TblEquipmentSerialNumbers. It is possible that multiple matches maybe found relating to different products, so the user must select the correct product. Alternatively no serial would be matched, therefore the user must select the appropriate product for the item scanned and so a new record must be added to TblEquipmentSerialNumbers relating the serial to the Inventory Product. Once the correct serial number record has been established, a new transaction record will need to be created based on the Inventory Product related to the Individual item, then a linking record needs to be added to TblEquipmentLocation, so that the selected Serial is related to the Transaction, finally the location the Item has come from and going to needs to be assigned - hopefully both the Transaction and Serial should relate back to the SAME Inventory Product....

Secondly, the user may have selected/created a transaction with an item pre-selected. Therefore the serial number lookup in TblEquipmentSerialNumbers only need to be concerned with matching Inventory Products. Again, if the Serial doesn't exist, it needs to be added to TblEquipmentSerialNumbers, then a record needs to be added to TblEquipmentLocation in the same way as shown above.


Although the tables appear to work, I'm useless in creating/understanding queries. For instance, the table TblEquipmentLocation links to both the TblEquipmentSerialNumbers and TblInventoryTransactions, which in turn both link back to TblInventory. Records added to the table need to be such that for a given Transaction for a Inventory Product, the individual items must also be related to the Product.

In the raw table design for:
TblEquipmentLocation
TransactionID -- FK to - TransactionID in TblInventoryTransactions
EquipmentSerialNumberID - FK to - EquipmentSerialNumberID in TblEquipmentSerialNumbers

Whilst the look ups in the table work for the individual fields, editing the raw tables 'could' give a spurious result in the the related Transaction and Serial do not match to the same Inventory Product.

However, if I'm not sure if this matters as I obviously won't be editing the table directly, and most of the record manipulation of this table will (!) be handled by code - although I'm not sure if this is the best method.


The biggest hassle I can see is with item that don't have serial numbers. Ideally we would assign a tracking number (possibly based on the AutoNumber for the table) and print out a label with bar code - unfortunately we don't have a bar code printer, but I suppose these are reasonably cheap.
 
I've been pretty tied up between work and volunteering at a PGA tournament. I don't see a question in there right off. Bar code printers can be relatively cheap. We just bought one for around $150 (Dymo I think it was). More than you ever wanted to know about bar codes:

http://www.granite.ab.ca/access/barcode.htm
 
Hi pbaldy, its Ok for the mo, I often use the forums to blow off my own thought. I find it useful to try and discribe what I need to do to people who have not clue as to what I'm up to.

At the moment I and looking at VBA code to select records and manipulate records. I think I know where I want to go, even if I don't really know how I'm going to get there....
 
Ok, getting there.

During my code execution, I setup an ADO recordset that is based on a query using the scanned bar code and selected product of the Transaction. If no product is selected and no serial number is matched (i.e. and empty recordset), I need to ask the user to select a matching Product so I can ADD a new record to the table.

I can open a new form suspending code execution using, :-

DoCmd.OpenForm FormName:="FrmInventorySelect", WindowMode:=acDialog

Then when I double click on a item I want the info to be sent back to the calling form then close the FrmInventorySelect with DoCmd.Close.

I can get the ID of the Product selected, but how to I pass this info back into the calling form?

I suppose I could set up an invisible text box on the calling form, then alter the value of that, but this seem a bit messy. Normally I would pass a parameter between routines, but I'm not sure how to do this with forms?
 
One common way is to hide rather than close the second form, get the value from it:

Me.ControlName = Forms!SecondFormName.ControlName

then close the second form.
 
Cheers. All is now good and although I got both yours and my method to work, I think yours is much neater.

I have another seemingly 'simple' question.

How do I maintain focus on the text box after it has been update using the carriage return?

When you hit return the focus shifts from the text box to the next item in the TAB list. I have tried a few separate methods to set the focus back to the text box but nothing seem to work.

I basically want the "AfterUpdate" event to fire but then for the focus to remain on the text box. I'm sure I'm missing something simple here.

Other than that, all is going well. I'll post the DB when functionally complete.
 
The layman's explanation (because it's the only one I know) is that since you're in the update event of the control, when it's done it wants to do what it otherwise would have, ie move to the next control. You can force the issue by setting focus elsewhere in your code and then back to that control, which should leave focus there:

Me.SomeOtherTextbox.SetFocus
Me.ThisTextbox.SetFocus
 
Thanks very much for this. I completely redid the data structure today as it wasn't making sense. I have one more issue again I suspect it is relativly stright forward. Sorry to be a pain.

I want to copy the last record in a form/recordset to a new record in the same form/record set.

Basically, when opening the form I immediately set the recordset to a new record using:

DoCmd.GoToRecord acActiveDataObject, , acNewRec

If the user then enters a serial, I want to copy the last record in the record set to this new record - as a sort of starting "deafult" value set for the user to update if needed. I suppose I could loop through the fields of the Last Record in the set, copying them into an array, then use AddNew to add them back into the new a record, but I'm guessing there is probably a simpler method using a "Do.Cmd RunSQL" command to runn an Insert/append query of somekind.
 
Last edited:
I'm having trouble understanding specifically what you're trying to accomplish. I can't tell if something like this is what you want:

http://www.mvps.org/access/forms/frm0012.htm

Or if an append query is appropriate, or perhaps opening a recordset to find the most recent values from that serial and putting them on the form.
 
Hey pbaldy,

Thanks for this. I was indeed looking at ADO to loop through certain fields to add them as default values for an entry of a new record.

My methodology: -

  1. Open transaction form and automatically go to a new record.
  2. Scan in an item. If the user has not selected a previous transaction (i.e. we are still on a new transaction) then we need to add some info in the new record, otherwise I will have nothing to be able to relate information too later on.
  3. Borrow certain info from the last record to give the use a starting point and copy it into the new record.
Now, I have copy across some code on the web that might just do the trick - http://allenbrowne.com/ser-24.html

Just reading though it now, but it seems to do what I would have created but with knobs, bells, whistles and big flashing lights.
 
Hi again,

I have finally got to the point where we have a functional system, albeit with certain problems. As the DB was based on a standard MS one and I have played with all of the Table, Field, Query and Form names, plus added some extra tables and re-arrange relationships, some of the original functionality has broken. Still this can be fixed a a later date.

My main concern is with some really odd VBA happenings. When I first start the DB and the code runs by entering a value into the scan box, I get a compile error saying that one of my "with" statements is not a proper user defined type!!!! I have also had an issue saying that my parameter passing are one the wrong type - but this is totaly false (as they are both defined as long)!!! In addiiton, I sometime get a break in the code although no breakpoint has been defined!!!

The really odd thing though is that if I simply close the debugger and run the same action again by making change in the scan box and pressing return, everything works as it should!!! Argghhh. I really don't understand this. I have tried a compact and repair but no joy.

Any ideas?

Also, I'm getting really frustrated at the form sizing. No mater what I do I can't seem set a form to a fixed size. As they get opened in different modes, the sizes change completely. Again, really frustrating.

Anyhow, I have attached the rough DB to this post.(Deleted DB - see later)
 
Last edited:
There are also some logical error in the above DB, but I will work on those.

The next thing I need to be able to do is to create a way of recording tests carried out on individual items. Each individual item will relate to a Product, and each product will be of an equipment type, such that:

Equipment Type --> Camera
Product --> Polycom APZ4
Individual Item --> 6785685321

Now, each item will need to be tested and could be tested multiple times

Each equipment type will have different tests - i.e Camera Tests = Pan Left (yes/No), Pan Right (Yes/No), Tilt Up (yes/no), Tilt down (yes/no), Zoom In/Out (Yes/No), Focus (yes/No)

I suppose the way to do this is create a "Test Schedule" Table which relate to Equipment Type such that

TestID (PK)
EquipmentType (Fk)
TestDescription (String)
TestType (???)

Now, most of the tests are going to be Yes/No although there might be other response that are needed - so I don't think I can make the Test Type field Yes/No - somehow I need to allow for different types in the filed - but I'm not sure how I would do this?

The next thing is to relate the different tests in the test schedule to an individual item. Again I'm not quite sure how to do this.

I think I will need to create a linking table such that: -

TbleTestsConducted
TestConductedID (PK)
SeriaNumberID (FK)
TestID (FK)
Result (??)
DateConducted (date/Time)
Comments (memo)

For each item there will be multiple tests needed to be conducted. E.g. for and item that is of type Camera, there are 6 tests, which will mean six records will need to be added to the above table for each item, each time its tested. The records to be added will be where the Equipment Type of the Item matches the Equipment Type of the test - I think????

Although I can explain this in words, I have no idea how to actually implement this. Can any give me some pointers?
 
I haven't managed to get much further with this. Have any people got thoughts on how I would approach above?

Pbaldy - I can get the text box to retain focus, now I want to make sure that everything in the text box is selected. Although I written code to do this:

With ScanItem
.SelStart = 0
.SelLength = Len(.Text)
End with

which works as I step through the code, as soon as the code finishes the text remains unselected.
 

Users who are viewing this thread

Back
Top Bottom