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

Joanna_N

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
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

Immoderate Moderator
Staff member
Local time
Today, 08:45
Joined
Feb 28, 2001
Messages
20,625
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:45
Joined
May 7, 2009
Messages
13,691
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

I’m here to help
Staff member
Local time
Today, 06:45
Joined
Oct 29, 2018
Messages
16,078
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

Super Moderator
Staff member
Local time
Today, 09:45
Joined
Jan 23, 2006
Messages
13,832
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

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
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

Immoderate Moderator
Staff member
Local time
Today, 08:45
Joined
Feb 28, 2001
Messages
20,625
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

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
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

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
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

Super Moderator
Staff member
Local time
Today, 09:45
Joined
Jan 23, 2006
Messages
13,832
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

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
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

Immoderate Moderator
Staff member
Local time
Today, 08:45
Joined
Feb 28, 2001
Messages
20,625
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.

Code:
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:

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

Gasman

Enthusiastic Amateur
Local time
Today, 14:45
Joined
Sep 21, 2011
Messages
8,752
@The_Doc_Man

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

Code:
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
Code:
strSQL = "UPDATE tbl_stock SET stock_level = stock_level - " & CStr( Me.X ) & " WHERE WarehouseID = " & Me.WarehouseID 
DoCmd.RunSQL strSQL

HTH
 

Joanna_N

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
@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

New member
Local time
Today, 15:45
Joined
Aug 3, 2021
Messages
7
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

Immoderate Moderator
Staff member
Local time
Today, 08:45
Joined
Feb 28, 2001
Messages
20,625
Sounds like you are making headway again. Great! And glad to offer what help I could.
 

Users who are viewing this thread

Top Bottom