Increment or decrement inventory 2

[osp001]

I'm trying to create a part of my database that will allow me to increment or decrement items in inventory. I have a table (tbl_products) with two columns: item_number, and item_qty.

What I would like to do is have a form that will allow me to enter an item number, which would then add one to the appropriate line in the tbl_products table. In other words, if I have 10 of item_number 1234, and I enter "1234" again, the second column will reflect 11 in inventory.

I also need a second form that will do the reverse- decrement that line when the item_number is added.

I'l still very early in my understanding of VBasic, and I'm at a loss as to how to do this. Thanks in advance.

 

[TheAceMan1]

How are ya osp001 . . . .

You need to consider what happens when you have to enter more than one of any item! [blue]You'll be a typing so & so![/blue] ;-) Two forms . . . No! . . . Only need one! ... maybe . . .

Here's what I propose:

Make a [blue]Continuous Form[/blue]. Then all you have to do is navigate to the approiate record and put the cursor in the [blue]item_qty[/blue] field. While there, with code in the [blue]Key Down[/blue] event of [blue]item_qty[/blue], [purple]you can increment & decrement at your leisure![/purple] And yes, you can hold the keys down for continuous action. By chance if your item count is abundant, you make a combobox or listbox that will on selection, take you to the desired record with focus already set on [blue]item_qty[/blue]. If by chance you already have a form, then you don't need further construction. just add the code.

The keys are [purple]Shift + UpArrow[/purple] and [purple]Shift + DownArrow[/purple]. The code that goes in the [blue]Key Down[/blue] event of [blue]item_qty[/blue] follows. Copy/paste it in the event:

[blue]   Dim KeyShift As Integer
   
   KeyShift = (Shift And acShiftMask) > 0

   If KeyShift And KeyCode = 38 Then [green]'Shift + UpArrow[/green]
      Me!qNum = Nz(Me!qNum, 0) + 1 [green]'Increment[/green]
   ElseIf KeyShift And KeyCode = 40 Then [green]'Shift + DownArrow[/green]
      If IsNull(Me!qNum) Then
         Me!qNum = 0 [green]'If Null set zero[/green]
      ElseIf Me!qNum > 0 Then
         Me!qNum = Me!qNum - 1 [green]'Decrement[/green]
      End If
   End If[/blue]

See Ya! . . . . . .

 

[osp001]

That's great advice, and would be a very slick way to do it. What I left out, unfortunately, was that I'd like to do the data entry with my trusty barcode scanner. So- when I wanted to log an item in or out, I'd just open the appropriate form, and beep away until I was done with the task.

Admittedly, I don't have that many items to work with just right now, but I anticipate my workload to increase as time goes on.

I've thought that there are two ways to do this- the first would have a very long list of ALL items I've ever added to the system, and then a shorter list of ALL items that have ever been removed. Given the number I work with (about 100 per month right now), that wouldn't bog down the system for a long time. But it would probably be easier for me (given my skill level) to increment or decrement at the time of use, giving me a running inventory.

Any suggestions how I might do that?

 

[TheAceMan1]

osp001 . . . . .

Thats a horse of an unknown color. I've never done any work with BarCode Scanners . . . . Sorry!

You need to post this in the [blue]Bar Coding Forum[/blue] . . . . the guru's for this are there . . . .

See Ya! . . . . . .

 

[TheAceMan1]

osp001 . . . .

Sorry again! A link to the [blue]Bar Code Forum[/blue] is below:

http://www.tek-tips.com/threadminder.cfm?pid=694

See Ya! . . . . . .

 

[osp001]

For my purposes, the barcoding part is very easy: the barcode scanner reads the number just as if it were typed in. So, if I scan a product with a part number of "1234," it enters "1234" and a carriage return. It's the same as if I were to type it in, and hit return.

 

[TheAceMan1]

OK osp001 . . . .

Can do! . . . .

Again proposing a Continuous Form (so you can see your items). Only this time, you have an unbond textbox in the footer to receive each input from the Scanner. Included will be the option to increment/decrement.

When the carriage return occurs code will do one of two things:

If code finds that [blue]item already listed in the table[/blue], it will increment/decrement the item by one (according to increment/decrement selected option).

If the [blue]item is not found in the table[/blue], a new record will be inserted with a count of 1.

For both conditions above, [blue]the record will be brought into view in the form![/blue] [purple]No matter howlong the list![/purple]

Through this thread, I've shown you can pretty much do what you want, so you can trim or change the logic of what you want done at your discretion.

Its a little late and I can't get to the code until mourning.

Let me know what ya think . . . .

See Ya! . . . . . .

 

[osp001]

A-yup. That would certainly do it.
The toggle that would have two "modes," one for "increment" and another for "decrement" would be ideal. The alternative (a "line-item" question box) would be dreadful- every entry, another "Increment/Decrement?" question!

 

[RoyVidar]

May I also suggest a read at Inventory Control: Quantity on Hand.

Incrementing/decrementing (i e calculting and storing) items while entering data poses some dangers, it is often better to add a record per each increment/decrement and calculate the item on hand when needed (in queries, form controls...).

The most appropriate solution for you, is of course up to you, but my thought is, you should also be aware of this way. (At the bottom of the page, is also some "food for thoughts" regarding the method presented by TheAceMan1).

Good Luck!

Roy-Vidar

 

[TheAceMan1]

RoyVidar . . . . .

Ya know . . . . its funny . . . . the [blue]Inventory Control Schema[/blue] you present is one I've always tried to impress on the companies & individuals I've designed this type of database for. From the meetings to personal discussions, [blue]they all voted[/blue] [purple]No[/purple], and [blue]perferred updating at the time of data entry.[/blue] This is even after being presented with simulations!

Its one schema I never got to install and watch work. As such, its one of those things that will remain a mystery to me as to why it was [purple]never logically accepted . . . .[/purple] espcially sinceI know I gave an excellent presentation.

See Ya! . . . . . .

 

[TheAceMan1]

osp001 . . . .

Before I finalize the code.

What is the [blue]PrimaryKey Name[/blue] & [blue]Data Type[/blue] of the table [purple]tbl_products[/purple]?

For clarity, what are the [blue]Data Types[/blue] of [purple]item_number[/purple] & i[purple]tem_qty[/purple]?

See Ya! . . . . . .

 

[osp001]

AceMan:
The primary key is set as AccNum_ID, and it is an autonumber field.
The item_number and item_qty are both number fields- long integer, which is overkill, of course.

 

[TheAceMan1]

osp001 . . . . .

Great . . . . I'm right at the end. But won't be able to post till later. Its 1:04am EST and I gotta get to bed. See ya later on today!

See Ya! . . . . . .

 

[TheAceMan1]

OK osp001 . . . . .

Lets do this!

The main routine requires the [purple]Microsoft DAO 3.6 Object Library[/purple]. So, in any code window, click [blue]Tools[/blue] - [blue]References...[/blue] Find the library and [blue]make sure its checked.[/blue] Then select the library and with the Priority UpArrow, [blue]push it up as high in priority as it will go.[/blue] Close Out.

Here we go . . . . .

1) Make a [blue]continuous form[/blue] that includes the Form [blue]Header/Footer[/blue].

2) Now add two [blue]checkboxes[/blue] to either header or footer (your choice). Set the following properties for the checkboxes:

CheckBox1 [blue]Name[/blue] as [purple]chkInc[/purple], [blue]Default Value[/blue] as [purple]True[/purple]
CheckBox2 [blue]Name[/blue] as [purple]chkDec[/purple], [blue]Default Value[/blue] as [purple]False[/purple]

The code for the checkboxes always leaves one of them checked.

3) Add an [blue]Unbound TextBox[/blue] to the same header/footer. Set the following property for the textbox:

TextBox [blue]Name[/blue] as [purple]BarEntry[/purple]

4) Now, in the forms [blue]code module[/blue], copy/paste the following code:

[blue]Private Sub BarEntry_AfterUpdate()
   If Len(Me!BarEntry & "") > 0 Then Call BarCodeRecordHandler
'End Sub

Private Sub chkDec_Click()
   
   If Me!chkDec Then
      Me!chkInc = False
   Else
      Me!chkInc = True
   End If

End Sub

Private Sub chkInc_Click()
   
   If Me!chkInc Then
      Me!chkDec = False
   Else
      Me!chkDec = True
   End If

End Sub

Public Sub BarCodeRecordHandler()
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim hldID As Long, pkName As String, Inc As Integer
   Dim tblName As String, Criteria As String
   
   Set db = CurrentDb()
   tblName = "tbl_products"
   pkName = "AccNum_ID"
   Criteria = "[item_number] = " & Me!BarEntry
   
   [green]'Check if BarEntry exist in table[/green]
   hldID = Nz(DLookup("[" & pkName & "]", tblName, Criteria), False)
   
   If hldID Then [green]'BarEntry exist[/green]
      If Me!chkInc Then [green]'Set Inc via checkboxes for BarEntry exist[/green]
         Inc = 1
      Else
         Inc = -1
      End If
   Else [green]'BarEntry does not exist. Add new record[/green]
      Inc = 1 [green]'Set Inc for new item[/green]
      
      [green]'Recordset method is used in order to hold newly assigned
      'Primary Key[/green]
      Set rst = db.OpenRecordset(tblName, dbOpenDynaset)
         rst.AddNew
         rst!item_Number = Me!BarEntry
         hldID = rst(pkName) [green]'hold PrimaryKey for lookup later on[/green]
         rst.Update
      Set rst = Nothing
      
      Me.Requery [green]'To include new record in form recordsource[/green]
   End If
   
   Set rst = Me.RecordsetClone
      rst.FindFirst pkName & " = " & hldID
      Me.Bookmark = rst.Bookmark [green]'goto record with PrimaryKey = hldID[/green]
   Set rst = Nothing
   
   Me!item_qty = Me!item_qty + Inc [green]'Increment +/- occurs here[/green]
   DoCmd.RunCommand acCmdSaveRecord
   If Me!item_qty < 0 Then Beep [green]'Audible warning for less than zero[/green]
   Me!BarEntry = "" [green]'Clear the textbox[/green]
   Me!chkInc.SetFocus [green]'Necessary in handling focus![/green]
   Me!BarEntry.SetFocus [green]'Set focus back for next entry[/green]
   
   Set db = Nothing
      
End Sub[/blue]

Thats it . . . . give it a whirl and let me know . . . .

See Ya! . . . . . .

 

[osp001]

Wow! Thanks for the detailed instructions; they're easy enough for me to follow. Sure do appreciate it.

I put in the code, and had two problems:

I took out the comment part of the third line so that instead of

'End Sub

it reads

End Sub

The second problem is where the program stalls on the Me.RecordsetClone line:

Set rst = Me.RecordsetClone

I get a runtime error 7951, "You entered an expression that has an invalid reference to the Recordset Clone property."

I've dinked around with it for an hour, and gotten nowhere. Suggestions?

 

[TheAceMan1]

[blue]I get a runtime [purple]error 7951[/purple], "You entered an expression that has an invalid reference to the Recordset Clone property."[/blue]

Thats because there's no [blue]RecordSource[/blue] for the form!

Open the form in design view. Call up the properties for the form, and in the dropdown list for the [blue]RecordSource[/blue] property, select the table. From the field list, drag/drop the fields you desire to the [blue]Details Section.[/blue]

Save, close, and try again . . . . .

See Ya! . . . . . .