How to create a functional Search script?

[jzyirek]

I am working on an inventory list that allows us to keep track of every item that we have within our stock. Now Someone before me created this and it is up to me to continue it though I see numerous bugs that need to be addressed (when I say bugs I mean that certain things that should have been placed in here for functionality).

First off there is no option to delete an item from the inventory.
Second there is no search option to locate the items in the inventory.
Third the Inventory is done in to Workbooks (I am not sure if this is a problem but it sure is annoying) with the entry form is always on top and it is difficult to see the items in the inventory without moving the form out of the way.

So here is my dielima, I am not too familiar with VB in Excel though I have done some little work prior with before. I would like to implement these options into the inventory to make it more efficient:

Add a search line to search for the key number (that we set up before) and if the item is found, it is loaded into the entry form so that we can easily modify it (the entry form is what was used to create the item into the inventory so I think that it shouldn't be too difficult to put it back into the form, but I don't know for sure).

And the last thing is to add a delete option to remove the item from the inventory and then I was thinking maybe to decrement all the items number but if we do that then all the items will be messup because we use a barcode setup to track all our items and they are dependent on that key number once it is printed.

Any ideas would be great, here is what we have so far:

Dim ws As Worksheet
Dim wb As Workbook
Sub CloseFile_Click()
wb.Close
End Sub

Sub CommandButton1_Click()
' TODO insert search sequence here via serial number or item number

End Sub
Sub OpenFile_Click()
' TODO change hardcode
Set wb = Workbooks.Open(*DELETED FOR SECURITY REASONS*)
Set ws = wb.Worksheets("Inventory")
End Sub
Function GetNextRecordKey()
'Uses cell in ControlVars table to maintain unique record number
'TODO: make more robust with validation using the ControlVars table nomenclature,
Dim NextRecordKey As Long
NextRecordKey = ws.Range("R2").Value + 1
ws.Range("R2").Value = NextRecordKey
GetNextRecordKey = NextRecordKey
End Function
Private Sub AddData_Click()
Dim iRow As Long

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for required fields
If Trim(Me.ProductCategory.Value) = "" Or Trim(Me.Owner.Value) = "" Or Trim(Me.SerialNumber.Value) = "" Or Trim(Me.Description.Value) = "" Then 'Or (Trim(Me.Part.Value) = "" Or Trim(Me.Source.Value) = "" Or Trim(Me.Cost.Value) = "" Or Trim(Me.Vendor.Value) = "") Then
Me.Part.SetFocus
MsgBox "Please enter/choose the required data"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = GetNextRecordKey()
ws.Cells(iRow, 2).Value = Me.ProductCategory.Value
ws.Cells(iRow, 3).Value = Me.Description.Value
ws.Cells(iRow, 4).Value = Me.OtherNumber.Value
ws.Cells(iRow, 5).Value = Me.SerialNumber.Value
ws.Cells(iRow, 6).Value = Me.Part.Value
ws.Cells(iRow, 7).Value = Now
ws.Cells(iRow, 8).Value = Me.Cost.Value
ws.Cells(iRow, 9).Value = Me.Source.Value
ws.Cells(iRow, 10).Value = Me.Owner.Value
ws.Cells(iRow, 11).Value = Me.Usage.Value
ws.Cells(iRow, 12).Value = Me.Notes.Value
ws.Cells(iRow, 13).Value = Me.Generation.Value
ws.Cells(iRow, 14).Value = Me.Vendor.Value
ws.Cells(iRow, 15).Value = Me.FName.Value
'clear the data except that which persists to next record input
Me.Part.Value = ""
Me.OtherNumber.Value = ""
Me.Notes.Value = ""
Me.Cost.Value = ""
Me.SerialNumber.Value = ""
Me.Part.SetFocus
Me.Owner.ListIndex = -1
Me.ProductCategory.ListIndex = -1
Me.Source.ListIndex = -1
Me.Usage.ListIndex = 2
Me.Generation.ListIndex = -1
Me.Vendor.ListIndex = -1
Me.FName.Value = ""
End Sub
Private Sub ClearDescription_Click()
'clears the persistent data for the description
Me.Description.Value = ""
End Sub

Private Sub UserForm_Click()
End Sub

Any help would be great and sorry for the long post

 

[Scott Huish]

I guess my first question is why are you using Excel for Inventory instead of a database like Access?

 

[jzyirek]

I asked the same question but that is what the company I work for decided that they wanted to use.