Inventory System

Printable View

- 4th February 2014, 16:20
Demon

Inventory System

I've piled up quite a lot of stuff over the years. It's now a chore to find a part, let alone remember what I have and how many. This is my solution done in Access 2002.

I enter the specs the same way they will appear on the labels for that part. I select a colour to make it easier to find where the drawer goes in the wall. I often pull out a few and bring them to wherever I'm working. There's nothing that looks more like a black-on-white label than another black-on-white label.

http://i991.photobucket.com/albums/a...ps2fa68696.jpg

I wanted a flexible system; each part can have different specs.

http://i991.photobucket.com/albums/a...psad175a77.jpg

I go through my stuff, look at specs on the datasheet, type in the quantities on hand and click the PRINT button.

http://i991.photobucket.com/albums/a...ps47916c64.jpg

(Huh? maximum of 3 images per post now?)

I'm still designing the database, queries, forms and reports. It's been many moons since I last used Access and VB so I'm a little rusty. This should make things a LOT easier to find stuff later. Labels will go on the drawer cabinets as well as the larger "surplus" cabinets.

The barcode scanner app on my phone works beautifully. It scans my label barcodes really fast and accurately.

My bluetooth dongle has been shipped so I should be getting it "soon". Still haven't looked at which BASIC I will use to make an app that will transmit the scanned number from the phone clipboard to my laptop. Still haven't looked at how I can get that received number on the laptop onto Access.

Project moves on.

Robert
- 4th February 2014, 16:25
Demon

Re: Inventory System

(cont.)

Then I print labels for the drawers and run a query that resets the PRINT buttons to OFF.

(sample data used for testing the system)

http://i991.photobucket.com/albums/a...ps7e438fe0.jpg

So when I add more parts later, only the new ones with PRINT ON will get printed.

Robert

EDIT: I tested the barcode scanner for fun, it can read up to 10" away without problems. That's very good considering the size of the barcode.
- 14th February 2014, 02:14
Demon

Re: Inventory System

Got the bluetooth USB dongle today. I paid about $5.62USD total for a $0.99 item, shipping alone was $6.16USD, go figure.

I tried it out and the laptop and android phone saw themselves properly. Scanning the barcode and sending the data is 3 clicks and the data is received in a HTML file. It comes through the COM12 port. I found an Access utility that can scan COM ports, just a matter of tying it all together.

So at least my idea seems to be holding water.

(Scan of Logic Sample barcode)
http://i991.photobucket.com/albums/a...ps2799c3d3.jpg
- 14th February 2014, 02:43
Demon

Re: Inventory System

App sends 150 bytes.

Code:

<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/></head><body><a href="140204102645">140204102645</a></p></body></html>

Barcode is easily identified after <a href=".

I don't have to use Windows XP Bluetooth utility to handle things (what I used so far to manually test communications - C:\WINDOWS\system32\fsquirt.exe). The Access COM utility is designed to do it all:

Code:

' modCOMM - Written by: David M. Hitchner ' ' This VB module is a collection of routines to perform serial port I/O without ' using the Microsoft Comm Control component. This module uses the Windows API ' to perform the overlapped I/O operations necessary for serial communications. ' ' The routine can handle up to 4 serial ports which are identified with a ' Port ID. ' ' All routines (with the exception of CommRead and CommWrite) return an error ' code or 0 if no error occurs. The routine CommGetError can be used to get ' the complete error message.

Utility available here:
http://www.thescarms.com/vbasic/CommIO.aspx

Might as well mention which free app from The Play Store:
MobiScan v2.0.13 lib2.9.26
https://play.google.com/store/apps/d...scanners&hl=en

www.manateeworks.com to purchase SDK to build your own barcode app.

Robert
- 14th February 2014, 13:03
amgen

Re: Inventory System

you are doing cool stuff !!
if you don't mind me saying
d
- 16th February 2014, 17:36
Demon

Re: Inventory System

Thanks, too bad I'm banging my head against a wall right now. LOL

The utility I found above is for true RS232 communications, it doesn't handle bluetooth enumerating. I found MScomm32.OCX, much leaner utility but with the same problem, no bluetooth enumerating. I googled "android bluetooth" for fun and it is overwhelming what goes on in the background. I'm hoping I don't need all those features and might be able to make a skeleton interface - I don't need security and all bells and whistles.

So the new plan was to see what it sent back and forth across COM2 and simulate it myself. I can't find a COM port sniffer that can monitor while I transfer a file manually using the Bluetooth icon on the taskbar. Google is literally swamped by HHD "free" serial monitor but it's just a demo screen with NO functionality on the COM ports - something like $60 for pay version. I've scrolled through at least 10 pages of google, mostly HHD using different URLs. Did I mention that HHD floods google with different URLs for the same useless freeware.

I tried Wireshark by Softonic but they force the install of a browser utility. Can't install the free version without that darned browser add-on - not interested.

I tried RealTerm, but it can't sniff while the port is in use. Payware for that SPY add-on.

So I took another angle and tried emulating a keyboard. Windows Accessibility has a feature to use Serial Keys from a COM port, no workie - most likely for true RS232 communication, not bluetooth.

Just tried SmartwedgeLite for bluetooth scanners, it sees the port and phone nicely but it doesn't do anything. The port is green but the phone doesn't seem connected. There's a big barcode on the screen, that is most likely to be scanned to configure the scanner properly (I have a broken handheld scanner that works like that).

So that's where I am. Stuck with a working Access system and an incoming barcode number on COM2. I can't find an interface/module/bridge whatever to close that gap.

Robert
- 16th February 2014, 19:53
amgen

1 Attachment(s)

Re: Inventory System

OK then, I have a Blue Tooth (BT) module (from a few years back) sending serial data that is spit out from my PIC continually @56khz. Now on older pc's I used a BT dongle and my windows8 pc has BT in it already. So you use the SPP.......'serial port profile' on pc which just makes a faux port....(kind of software type port) that shows up in ports available in your VB.....consider VB.net free in visual studio from Microsoft.
And I recently tried an app on my android phone that could also see the data over BT utilizing SPP.
following is hyper-term displaying continuous data over BT, the port is the added port but the baud doesn't seem to matter because it still comes in at different settings !
Attachment 7249
don
- 16th February 2014, 20:29
Demon

Re: Inventory System

I tried Microcode Studio Plus Terminal and it says the port is in use or not available. Too bad 'cause I like this one the most.

I tried HID USBwatch to look at the USB side and it says I don't have a serial port on my laptop. I connected a PCMCIA card with RS232 but it didn't help.

I tried MeLabs hyperterm and got it started on COM2 but nothing showed up on the screen.

I tried Windows Hyperterm and got it started on COM2 but nothing showed up on the screen.

I checked Device manager and the bluetooth dongle is connected to COM2 9600 8 N 1.

But I can receive files manually via the Bluetooth Receive File feature without fail.

Lost...
- 16th February 2014, 20:36
Demon

Re: Inventory System

Quote:

Originally Posted by amgen

.... So you use the SPP.......'serial port profile' on pc which just makes a faux port....(kind of software type port) that shows up in ports available in your VB..

I don't know how/where to set COM2 as SPP?

Quote:

...consider VB.net free in visual studio from Microsoft...

I have Access 2002 on my laptop, it uses VB6.3. I have VB6 Enterprise but I don't have much room left on this aging P4-1900 laptop. I'd really like to stay with VB6 if at all possible.

Robert

EDIT: I'm trying this tweak now: http://stackoverflow.com/questions/1...profile-spp-in
- 16th February 2014, 22:54
Demon

1 Attachment(s)

Re: Inventory System

Did that tweak, no difference.

At least I know the COM2 properties. Hadn't noticed RFCOMM earlier.
Attachment 7251

So if I read this right:
https://developer.bluetooth.org/Tech...Pages/SPP.aspx
RFCOMM is an adaption of RS232 via bluetooth. So I guess I can go back to MCcomm32.OCX module.

I have no idea why I can't even get a few bytes of jibberish through a Terminal. The connection is complete, I can receive a file manually via the Bluetooth icon.

Robert
- 17th February 2014, 01:35
amgen

1 Attachment(s)

Re: Inventory System

the old BT stuff may be a little different from newer BT drivers.

Attachment 7252

the driver picks its own ports.

don
- 17th February 2014, 03:39
Demon

Re: Inventory System

I know, mine were between COM12 - 18, depending on which USB slot I used. But to use the COMM modules for Access, they have to be between 1 - 4. So I go in DEVICE MANAGER, Ports (LPT & COM), click on the COM port, PROPERTIES/ADVANCED and then override 12-18 to 1-4, whichever is not used on my laptop.

I've seen different versions of that bluetooth panel. Laptop is WinXP, haven't checked downstairs what it looks like on Win7.

Now I'm having stabilitity problems. I uninstalled the bluetooth dongle this afternoon, and now it's not coming back on without intervention when I reboot.

Robert

EDIT: Ok, this is weird. I tried to update the driver because only 1 of the 2 bluetooth devices came on, halfway at that. Driver was up to date.

So I pulled out the dongle, the BT device and COM disappearred as they should. I inserted it in another USB slot and all 3 came up properly. I moved it back to the original slot and they all came up properly as well, even kept it under COM2.

Oh well, at least there are no warnings now.
- 17th February 2014, 04:46
Demon

Re: Inventory System

GAAAAAAAAAAAAAA!!!!

A little nagging voice told me to try another app. I scrolled through a zillion apps until one caught my eye with bluetooth and SPP, darn thing worked on the first shot!

(the red square turned green instantly, as soon as they were paired, and I don't even have to push buttons to transmit)
http://i991.photobucket.com/albums/a...psa2ebf451.jpg

Now I know why there was a payware SDK for that first app. I thought it was to customize the app to your liking. NOooooo, it was so that you could alter the communication protocol so it would be compatible with standard bluetooth - uninstall. I'm not staying with them just for not coming out and saying in the description what they were doing.

All this time, argh.

Oh well, at least now I know what was wrong. The app I'm using now has a small scan window and it's a demo only. At least now I know to look for another one with a full size window, even if it's gonna be a few bucks. Now I know why that first one was feature rich and without advertisements.

Robert
- 17th February 2014, 17:39
Demon

Re: Inventory System

Hope one day I will put an order on my parts that are hanging around too. Good job Robert.

As for the VBA, I think it is Visual Basic for Applications.

Ioannis
- 18th February 2014, 10:23
Ioannis

Re: Inventory System

Ooops.... sorry I completely edited your post....

Very sorry about that. Is there a way to get it back?

Ioannis
- 18th February 2014, 14:49
Demon

Re: Inventory System

BWAHAHAHAHAHAHA!!!!

Too funny! I did that too once, clicked EDIT instead of REPLY. But I was lucky to notice something was worng before I posted. I'm happy it's you that's the first to mess up. LMAO!!! I had come THAT close.

You're right, it is for Applications. I saw that elsewhere on google later yesterday.

As for my post, I was just whining about how frustating things can be when you reboot in the morning and things don't work. :D I don't remember my post, but I had linked to my saga on the Access forums:
http://www.access-programmers.co.uk/...29#post1331829

Those guys have been an invaluable resource in relearning Access and VB. I used Big Resource also, but they have a truckload of ads, and posts that link to another link; a bit frustrating when you have a lot of research to do.

I might put this system up for download for a few bucks when I'm done. There must be a few guys out there with the same need to label hundreds of drawers, ability to scan and connect directly to Access. I've seen several inventory apps, but it's not quite the same. They are generic in nature while this is specifically for use by the hobbyist with too much "stuff".

Robert
:)
- 18th February 2014, 16:29
Demon

Re: Inventory System

I did learn a bit about bluetooth protocols with this project. As I mention on the other forum, the Bluetooth utility can receive files manually just fine because it uses RFCOMM. The utilities freely available for Access use SPP which is an extra layer over RFCOMM.

Mobiscan:
PRO - the core is really a charm; ultra fast recognition, can scan in any orientation, can use the entire width of the image, no ads, free.
CON - RFCOMM protocol, takes 3 strokes to send data, would have to receive file manually, open file and copy&paste in Access.

(still researching for a way to intercept data from RFCOMM, there are no examples on google of modules that interface with RFCOMM form Access)

TEC-IT:
PRO - SPP protocol, autosend of data without keystrokes, piece of cake to read in Access, free.
CON - slow recognition, scan orientation not adjustable, scan window MUCH smaller, small ads, sends random data every once in a while in freeware - need to rescan - no big deal, payware has same small window - just no ads or random data, have to reconnect phone every time.

The ideal app would be the core of Mobiscan and the transmission of TEC-IT. So I wrote to both developpers, compared each app and asked it they planned to address these issues in the future. :D

So for now I will use TEC-IT, it's the less-painful app to use. There are several payware bluetooth barcode apps, but they don't have demo versions to test. I will be inputting a lot of data and printing a ton of labels the first time, but after that scanning to maintain inventory will be minimal. I don't have to get hung up on the ultimate app.

How many components do you take to your workbench to do some testing? Maybe a dozen, even 2 dozens wouldn't be a big deal to scan IF I use parts from all drawers. And even then, I will scan as I take parts out, and then as I put them back in. I'm not talking about heavy volume.

Robert
- 18th February 2014, 17:02
Demon

Re: Inventory System

I already had a reply from TEC-IT:

Quote:

GetBlue is using third party barcode scanner apps for reading the barcodes. When selecting the data source, GetBlue shows a dialog with the installed 3rd party barcode scanners apps. You may select a specific scanner application or choose Always ask. In the latter case GetBlue asks you for the camera scanner to be used.

Please search the Google Play Store for an app which fit your needs. After you have found one select this app in the camera scanner settings. ...

I replied that I did not see this dialogue in the demo version so I asked if this was available only in the payware. I also asked if it meant I could use Mobiscan as a front end.

While I was replying to TEC-IT, Mobiscan replied. LOL Unfortunately...

Quote:

...many thanks for your feedback but i'm sorry to inform you that we do not plan to integrate and develop such features because our Barcode Scanners app is only meant to be a simple demonstration of our mobiScan barcode decoding technology which can be integrated in applications of various kinds.

Oh well, not bad. There is still hope of using Mobiscan from within TEC-IT, which was my dream-app. LOL

I hope I understood properly. It appears as if both app is designed to be integrated with another app, which would make the ultimate app in my case.

Robert
- 18th February 2014, 20:15
Ioannis

Re: Inventory System

Quote:

Originally Posted by Demon

I might put this system up for download for a few bucks when I'm done. There must be a few guys out there with the same need

Sure, I am one! Looking forward!

Beside the mess, it is funny to have your avatar and my message! Sorry again.

Ioannis
- 8th February 2017, 03:50
Demon

Re: Inventory System

Quote:

Originally Posted by Demon

... I don't have much room left on this aging P4-1900 laptop...

Real happy I rambled on about this project in this forum. :D

I had made backups of what I considered "important" on my old laptop and desktop, turns out I missed a few, like this project.

Now to get this up and running on these newer PCs with Win10 so I can finish what I started.

Win 10 really doesn't make things easy for you to change settings. They made it more user friendly, I think they made it incredibly harder to find what seemed so much easier on XP.