VBA code to check whether the amount in which wants to retrieve from the store is larger than the amount of stock

[Joanna_N]

Hi,
I have a problem!

I am trying to create a simple database that coworkes can take products from stock.
I need to check at x value is not bigger than stock value and if is I would like to show a comments : value is to bigg.
I have code like this :

Private Sub cmdlagre_Click()

If stock_level < x.value Then
MsgBox "x is to big", vbInformation, "stock"

end if
en sub

sometimes it works, but if we have a value in stock of 22 and I want to subtract 20, access says that the value of 20 is too big ...

What am I doing wrong?
Help - pleas

 

[The_Doc_Man]

Rather difficult to answer since we can't see everything involved in this activity. Since stock_level is not apparently defined inside your subroutine, it must be externally defined. So... how is stock_level determined? For that matter, what is x (as in, x.value)? Is that a form control of some sort? In which case, .VALUE is the default property of a control that has a value, so you hardly ever need to refer to .VALUE explicitly.

 

[arnelgp]

if stock_level is a textbox on your form:

If val("0" & [stock_level]) < [x].value Then
MsgBox "x is to big", vbInformation, "stock"

end if

 

[theDBguy]

Hi. Welcome to AWF!

Posting a description of your table structure might help clarify some of the questions we have about your current setup.

 

[jdraw]

Further to comments provided so far, it might be helpful to readers and to you if you could describe -in simple terms- the process you intend to support with this proposed database. It might be easier if you described a day at the office
where people were taking various products from Stock. It would also be helpful if we knew how stock is acquired.
You may get some insight from this article AppInventory by Allen Browne

 

[Joanna_N]

wow! this is the first forum where I got an answer so quickly - thank you very much.

So I have a table that contains values like this:
Warehouse_ID / series_product / stock_level

The employee records withdrawals from stock
specifies the product using the form - the form searches for the warehouse_ID and performs operations on the warehouse subtracting the x value (which is entered by the employee) from the current state (stock_level - x)
X is a textbox on my form.

I want to secure the base so that the employee cannot pick up more than is on the warehouse.

it is help?

 

[The_Doc_Man]

That helps. If X is a textbox on the form and this code is in the form's class module, you can drop .VALUE because it is a default.

The question, though, is whether you are helping yourself with this model. As a separate issue not related to your immediate problem, most of the inventory systems I've tripped over have used a transactional model where you describe each product but have a long table of short transaction records where you show that you add or subtract stock and determine stock level via a summation query.

OK, having given the obligatory note about a more rigorous inventory model, your question is resolved based on the details of this operation. The BIG question is exactly HOW does the form interact with this table? If you do this kind of thing with your current structure, you have three possible ways to adjust the table value.

1. Using a form bound to the warehouse table with the ID, series, and level info in textboxes, you adjust the level in the textbox and immediately save the record. You should be able to compute levels as long as the draw/add amount and the stock_level amount are of the same data type.

2. Using a form not bound to the warehouse table, you would need to us DLookup to pull the current inventory level. After that,

2.a You could use a recordset operation to find the correct warehouse_ID and series_product record, then update the slock_level field in the recordset and immediately update through the recordset.

2.b You could synthesize an SQL UPDATE clause to adjust the record using some variant of CurrentDB.Execute or DoCmd.RunSQL to execute the SQL to update the record.

Which of the above most closely resembles what you are doing? IF your answer is "none of the above" then what ARE you doing?

 

[Joanna_N]

I am using the DoCmd.RunSQL clause
DoCmd.RunSQL " Update tbl_stock Set stock_level = stock_level - x.Value WHERE Warehouse_ID = series_product.Value "

I refer to series_product because the series allows the worker to define the product.

 

[Joanna_N]

One more issue the form allows me to subtract the value 1, 2 and 3 but any other is thrown out as too large.
Even if the warehouse level is 1, the form allows you to subtract the value 3 ...

 

[jdraw]

Can you post a copy of your database in zip format?I'm interested in your tables and relationships.
But others may have more focused comments/suggestions if they can see and "test" your database.

 

[Joanna_N]

Yes I can, there is only one small problem - my database is in Norwegian. So if you can look at my datebase without language, it would be great

 

[The_Doc_Man]

DoCmd.RunSQL " Update tbl_stock Set stock_level = stock_level - x.Value WHERE Warehouse_ID = series_product.Value "

That syntax doesn't work because you need special syntax to cross over from the SQL environment to the Graphic User Interface (GUI). X is not known to the SQL environment AND you are not qualifying it correctly so that SQL can find it. In the first code segment, substitute the correct name of your form in the place as indicated.

Update tbl_stock Set stock_level = stock_level - [Forms]![your-form-name]!X WHERE Warehouse_ID = series_product

You need to understand that SQL executes in the context of the database engine ACE, where as forms are in the context of MSACCESS.EXE, which is different from ACE. I.e. ACE is a child process of Access with its own SEPARATE memory.

Your "DoCme.RunSQL" transfers the SQL string to the ACE engine at which point everything you needed to be in that string has to already be there already. BUT your way treats X.VALUE as just part of a quoted string that will not be interpreted while still in the context of the form. Which is why your code probably isn't doing what you wanted.

Also, in your post #6 of this thread, you identified two fields in your table called Warehouse_ID and series_product. This doesn't "look" right because ONE of those two should be something from the form but again, based on what happens when the SQL gets copied to ACE, those two references in the WHERE clause will look like references to the table, not the form.

There is also THIS approach:

strSQL = "UPDATE tbl_stock SET stock_level = stock_level - " & CStr( Me.X ) & " WHERE WarehouseID = .... (something from the form) ;"
DoCmd.RunSQL strSQL

 

[Gasman]

@The_Doc_Man

Might be wise to be extra explicit here for the o/p?, else I can see something like

strSQL = "UPDATE tbl_stock SET stock_level = stock_level - " & CStr( Me.X ) & " WHERE WarehouseID = Me.WarehouseID"
DoCmd.RunSQL strSQL

being written, instead of more like

strSQL = "UPDATE tbl_stock SET stock_level = stock_level - " & CStr( Me.X ) & " WHERE WarehouseID = " & Me.WarehouseID 
DoCmd.RunSQL strSQL

HTH

 

[Joanna_N]

@The_Doc_Man

I made a change based on your suggestion. I tested and I see that it works correctly.

I would now like to secure my form against the possibility of deducting too much value - what an employee may do by mistake.

I have used IF functions to force employees to enter data or select them, can I, using the same function, enter a condition checking whether the value that the employees wants to take from the warehouse is smaller than the actual stock level?

And if it is larger, the form returns him information that the value is too large.

 

[Joanna_N]

Hello again

I found such a solution and it works:

If Me.x > DLookup("[stock_level]", "tbl_stock", "[Warehouse_ID] = " & Forms!frm_stock!series_product ) Then
MsgBox "x is to big", vbInformation, "Stock"
Me.x.SetFocus

I think they referred to the wrong cell in the table.
now looks up the specific value and does the correct check.

Thanks to everyone for your help - this forum is great!

 

[The_Doc_Man]

Sounds like you are making headway again. Great! And glad to offer what help I could.