User to be warned of the current stock

[arnelgp]

i noticed on the form that you posted that there is a combobox for size?
which you don't have in your item table.
if you want to incorporate the "size" just add it to Item field, example:

1 record for: Elastic Ankle Support, small
1 record for Elastic Ankle Support, medium
etc.

your Combobox for "Item" should also include the "ItemCurrentStock" field,
so the Rowsource of your combo should be:

select Item, ItemCurrentStock from yourTable

then you add code to this combo's (Quantity combo) BeforeUpdate event to show the CurrentStock:

private sub Quantity_beforeUpdate(cancel as integer)
cancel = me.Item.column(1) < Me.Quantity
if cancel then
msgbox "Not enough stock to accommodate this request"
end if
end sub

 

[falcondeer]

i noticed on the form that you posted that there is a combobox for size?
which you don't have in your item table.
if you want to incorporate the "size" just add it to Item field, example:

1 record for: Elastic Ankle Support, small
1 record for Elastic Ankle Support, medium
etc.

your Combobox for "Item" should also include the "ItemCurrentStock" field,
so the Rowsource of your combo should be:

select Item, ItemCurrentStock from yourTable

then you add code to this combo's (Quantity combo) BeforeUpdate event to show the CurrentStock:

private sub Quantity_beforeUpdate(cancel as integer)
cancel = me.Item.column(1) < Me.Quantity
if cancel then
msgbox "Not enough stock to accommodate this request"
end if
end sub

Thanks for your suggestion, however

Because I have the two combos (item, size) cascaded, I moved the code in PRSB_Size not in PRSB_item and when selecting any quantity in the combo44 it is calculating but it exceeds 0 so I keep having negative numbers.

This is what I put in the before update event:
Cancel = Me.PRSB_Size.Column(1) < Me.Combo44
If Cancel Then
MsgBox "Not enough stock to accommodate this request"
End If

and this is the select statement in row source of size:
SELECT Size, ItemQuantity FROM tblSize WHERE ItemName = PRSB_Items;

Attached is tblsize (pic-4).

I appreciate your help

 

[falcondeer]

i noticed on the form that you posted that there is a combobox for size?
which you don't have in your item table.
if you want to incorporate the "size" just add it to Item field, example:

1 record for: Elastic Ankle Support, small
1 record for Elastic Ankle Support, medium
etc.

your Combobox for "Item" should also include the "ItemCurrentStock" field,
so the Rowsource of your combo should be:

select Item, ItemCurrentStock from yourTable

then you add code to this combo's (Quantity combo) BeforeUpdate event to show the CurrentStock:

private sub Quantity_beforeUpdate(cancel as integer)
cancel = me.Item.column(1) < Me.Quantity
if cancel then
msgbox "Not enough stock to accommodate this request"
end if
end sub

Dear arnelgp, I will appreciate it very much if you help me in finalizing this issue.

 

[falcondeer]

i noticed on the form that you posted that there is a combobox for size?
which you don't have in your item table.
if you want to incorporate the "size" just add it to Item field, example:

1 record for: Elastic Ankle Support, small
1 record for Elastic Ankle Support, medium
etc.

your Combobox for "Item" should also include the "ItemCurrentStock" field,
so the Rowsource of your combo should be:

select Item, ItemCurrentStock from yourTable

then you add code to this combo's (Quantity combo) BeforeUpdate event to show the CurrentStock:

private sub Quantity_beforeUpdate(cancel as integer)
cancel = me.Item.column(1) < Me.Quantity
if cancel then
msgbox "Not enough stock to accommodate this request"
end if
end sub

I also tried the following in the before update of my combobox but not working, I don't know what is wrong in my tables ?

Dim MyQuantity As Integer
MyQuantity = DLookup("[ItemQuantity]", "tblSize", _
"[SizeID] = " & Forms!tblPrescribedItemsSB_Subform3!SizeID)
If MyQuantity < Me.Combo44 Then
MsgBox "No Stock Available"
End If

 

[June7]

What does 'not working' mean - error message, wrong result, nothing happens?

 

[falcondeer]

What does 'not working' mean - error message, wrong result, nothing happens?

I don't know it just does not allow you to move any where, means if you click somewhere else like click on save or go to another tab it does not go. When you remove the code then everything is back to normal.

 

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

 

[falcondeer]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

should I send only the front end !

 

[June7]

If the backend is Access, include it.

If not Access then convert linked tables to local tables.

Of course, makes copies for this.

Zip the files with Windows Compression. If still too large, remove most data.

 

[falcondeer]

If the backend is Access, include it.

If not Access then convert linked tables to local tables.

Of course, makes copies for this.

Zip the files with Windows Compression. If still too large, remove most data.

Thanks, I got busy last few days, I will try to send it soon.

 

[June7]

Okay, what form? What steps do I take to replicate issue?

 

[falcondeer]

I want to deduct the prescribed item (e.g. what user choose in combo62 under tab Walking Aids say) from the ItemQuantity in table tblSize until we reach the ItemMinimum in tblSize then stop the user from completing the field and give him/her a msgbox saying "no more items available"

This is in main form called tblPatient (sorry it should be written as frmPatient) which has many tabs like (walking aids, supports , woman's health...etc)

 

[June7]

What steps do I need to do? I opened form from switchboard and there is no data.

Ideally, would not save balance values in table. Calculate inventory balance when needed. Review http://allenbrowne.com/AppInventory.html

Which is combo62? I had to hunt it down in design view, apparently it is for PR_Quantity. Should give it a meaningful name.

Does everything really have to be so big? Hard to view on my laptop and I have a 17-inch screen.

Don't need record selector on switchboard.

 

[falcondeer]

What steps do I need to do? I opened form from switchboard and there is no data.

Ideally, would not save balance values in table. Calculate inventory balance when needed. Review http://allenbrowne.com/AppInventory.html

Which is combo62? I had to hunt it down in design view, apparently it is for PR_Quantity. Should give it a meaningful name.

Does everything really have to be so big? Hard to view on my laptop and I have a 17-inch screen.

Don't need record selector on switchboard.

Hi

When the form loads it goes to a new record, You can use the navigation button to go back to see data. Open table tblPatient to see MRN to search for.

I agree that what I am trying to do is not a good solution but I am doing this personally for my department and it is just enough to stop user from proceeding if we reach the minimum quantity in table tblSize. I will try to read this http://allenbrowne.com/AppInventory.html

Yes combo62 is for PR_Quantity and I agree with you that I have a problem with objects naming.

I will try to make forms smaller

I set record selector to No.

Thanks bro

 

[June7]

You are saving PR_Items name into tblPrescribedItems instead of SizeID primary key autonumber. Should save designated primary key value, not the descriptive text. If you want to save the text then designate ItemName as primary key. As is, the autonumber field serves no purpose. Probably same case with other relationships. However, can't designate ItemName as primary key because it is not unique. As example, there are two Pediatric Standard Walker records in tblSize. Pediatric Standard Walker is saved in tblPrescribedItems - which one is intended? Need to fix this before continuing.

 

[falcondeer]

You are saving PR_Items name into tblPrescribedItems instead of SizeID primary key autonumber. Should save designated primary key value, not the descriptive text. If you want to save the text then designate ItemName as primary key. As is, the autonumber field serves no purpose. Probably same case with other relationships. However, can't designate ItemName as primary key because it is not unique. As example, there are two Pediatric Standard Walker records in tblSize. Pediatric Standard Walker is saved in tblPrescribedItems - which one is intended? Need to fix this before continuing.

Dear June7

I appreciate your input very much, however, I just figured out a simple solution for my issue.
Thanks again.

 

[jdraw]

I just figured out a simple solution for my issue.

How about telling us your solution. It may help someone else.

 

[falcondeer]

How about telling us your solution. It may help someone else.

Hi

Sorry, I don't mean a simple new solution rather it is still what arnelgp suggested, however I was unable to combine two conditions in the update statement then I made it work.
I also created a new field in table size called ItemMinimum to compare with when the user reaches the minimum quantity I pre determined that for each specific item, and this is much better than comparing with number 1 or 2 in the combobox because each item should have different minimum quantity and I to compare with around 93 items and sizes. I also put Column Count to 3 so the if statement was able to capture the quantity and the minimum quantity and compare them.

This is the update statement I used with two conditions:
private sub form_afterUpdate()
dim sql As String
sql = _
"Update tblItemsSB Set ItemCurrentStock = Nz(ItemCurrentStock, 0) + " & _
Nz([Combo44].OldValue, 0) & " - " & Val([Combo44] & "") & " " & _
"Where ItemName = '" & [PRSB_Items] & "' AND Size= '" & [PRSB_Size] & "' ;"
currentdb.Execute sql
end sub

and this is the if statement:
Cancel = Me.PRSB_Size.Column(1) < Me.PRSB_Size.Column(2)
If Cancel Then
MsgBox "Not enough stock to accommodate this request"
End If

I know this is not a smart and strong way but this is enough for me.

Thanks everybody especially arnelgp and June7.

 

[jdraw]

Thanks for posting back. Glad you have a solution.

You may want to research ReOrder Level or ReOrder Point as well as ReOrder Quantity.
From wikipedia:
The reorder point is the level of inventory which triggers an action to replenish that particular inventory stock. It is a minimum amount of an item which a firm holds in stock, such that, when stock falls to this amount, the item must be reordered.
Reorder level is the stock level of a particular item of inventory, at which a firm needs to place an order for the fresh supply or replenishment of the item; whereas reorder quantity is the magnitude or the number of units to be ordered in a new purchase order for the fresh supply of a particular inventory item

 

[falcondeer]

Thanks for posting back. Glad you have a solution.

You may want to research ReOrder Level or ReOrder Point as well as ReOrder Quantity.
From wikipedia:
The reorder point is the level of inventory which triggers an action to replenish that particular inventory stock. It is a minimum amount of an item which a firm holds in stock, such that, when stock falls to this amount, the item must be reordered.
Reorder level is the stock level of a particular item of inventory, at which a firm needs to place an order for the fresh supply or replenishment of the item; whereas reorder quantity is the magnitude or the number of units to be ordered in a new purchase order for the fresh supply of a particular inventory item

I will, thanks a lot.