Need Direction/Guidance

[jakebailey]

Hi Everyone,


I'm trying to come up with a solution to make our year end inventory go smooth this Friday. In the previous years this inventory can take 2 1/2 days and in the past week I've lost two employees to medical leave which means I will have to rely on other employees who are not familiar with our inventory to step in and help.

Here is what I'm trying to do:
1. Scan a UPC code into a text box
2. Validate the UPC code against a database (Access or Excel)

A. If the UPC Code does not exist then prompt user to add the item to the database
3. Once the UPC code is validated then bring back the Model number, Vendor's name, description, and UOM.
4. Write the information to the excel document along with a time stamp of when it was scanned.


Here are some other factors to keep in mind.
1. This process needs to be quick - the users will scan product as quick as the scanner will let them so it can't be held up by code waiting to execute.
2. I would prefer to do the data validation over the network so multiple users can contribute to the item database.


What I'm looking for most is some guidance on what direction I should head in. I'm not a programmer by trade but I certainly enjoy using VBA to make my life easier at work and typically don't mind working through all the trials until I somehow figure out what to do but unfortunately I don't have the luxury with inventory coming up in 5 days.
I appreciate any and all suggestions/guidance/pointers for this.

Thanks,
Jake

[Simon Lloyd]

Jake to be honest Excel isn't the way to go for this, you need the purpose built software, when allowing a shared workbook (which is what it would be) you will have trouble when two scans or more users are scanning, then eventually close and save their workbook, the will be asked whose changes should be saved, they will have no idea, if you disable this prompt then data will simply be overwritten......there are many more headaches that come with a shared workbook.

Even if you could get round all that you will still need to integrate Excel with the scanning software somehow, i think you will have to dig in your pocket and buy the basic package that comes with the scanners.

[jakebailey]

Simon - Thanks for the quick response. I agree with you that Excel isn't the best application for this but unfortunately the software solution we are deploying will not be ready until Q3 of this year. Also, I should have been a little more specific in my first post. I'm not interested in sharing a workbook over the network I'm more interested in sharing a database just for data validation only. Once the data is validated then it can write the record on the local workbook this way there will not be an issue with trying to share the workbook with other users. My current test page takes the UPC code and then searches an Access database. It would be nice to have just 1 .mdb file for all the users to validate against but if that will cause issues then I have no problem keeping the file local to the machine. See the code below

Option Explicit

Sub Access_Data()

Dim Cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim MyConn As String
Dim sSQL As String



Dim lngRow As Long
Dim lngCol As Long
Dim lngC As Long
Dim objMyField As ADODB.Field
Dim rngLocation As Range

'Set Destination
Set rngLocation = [B2]

'Set Source
MyConn = "C:\Data\Inventory.mdb"

'Create Query
sSQL = "SELECT Items.[Item], Items.[Description], Items.[UOM]" & _
"FROM [Items]" & _
"WHERE Items.[UPC]='" & Me.txtUPC.Value & "';"


'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set rs = .Execute(sSQL)
End With



'Insert current Time
rngLocation(lngRow, 1) = Time

'Write RecordSet to results area
lngRow = rngLocation.Row
lngCol = rngLocation.Column
lngC = lngCol
Do Until rs.EOF
For Each objMyField In rs.Fields
Cells(lngRow, lngC) = objMyField
lngC = lngC + 1
Next objMyField
rs.MoveNext
lngRow = lngRow + 1
lngC = lngCol
Loop
Set rngLocation = Nothing
Set Cn = Nothing

End Sub

I've already done a simple worksheet where I scan the UPC code and then do a VLookup for the remaining four values. Then do a pivot table once I'm done scanning to get the final inventory count. This alone has reduced my average count time for a specific vendor by a 65% - 70% which is great. While using Excel probably isn't the best solution it has proven to be better than anything else I've come across with such a short window to deploy.

Thanks again for any feedback.

-Jake