Tick box based on value of field - VBA question

[SprayCan]

Morning all,

My scenario: I'm building a form that has 3 fields, and 2 tick boxes. The tick boxes will change depending on the value of the fields. Brief description:

Field A is a current stock # of printer cartridges.
Field B is the number I wish to book in or out of the system.
Field C is the minimum # of cartridges we need to have in stock.

Tick Box 1 is a notification that the minimum stock level has been breached, but has not yet reached zero.
Tick Box 2 is a notification that the stock level has reached zero.

Here is my code for the command button used to book cartridges INTO the the database.

-----------------------------------------------------------

Private Sub Command2_Click()

Dim instock, bookedin As Single
Dim instock_field As String
Dim bookedin_field As String

instock = Me!instock_field
bookedin = Me!bookedin_field

If Not IsNull(bookedin_field) Then
instock = instock + bookedin
End If

If (instock) < (minnumber) Then
MsgBox "Please be aware there is still less than the minimum # of cartridges in stock. Consider purchasing more.", _
vbOKOnly + vbExclamation, _
"Cartridge Stock Alert - Warning"

Me!chkReorder_box = True

Else

Me!chkReorder_box = False

End If

If (instock) = 0 Then

Me!chkOutOfStock = True

Else

Me!chkOutOfStock = False

End If

Me!instock_field = instock

End Sub

-----------------------------------------------------------

This code is identical for booking cartridges OUT of the system.

The problem: The error messages and the tick boxes respond perfectly for the BOOK OUT command button, but they refuse to for the BOOK IN command button. Instead, both tick boxes simply return to False, even if the # in stock is less than the minimum required.

Now while this isn't a problem if I'm booking in enough cartridges so that instock is higher than the min # needed, it's bugging me and I want to fix it.


If any of you can help, it'd be great

 

[kjv1611]

Me!chkOutOfStock = False

End If

Me!instock_field = instock

End Sub

Maybe I'm missing something, butwhy do you have Me!instock_field being set to instock after all the conditional statements? I think that may be your problem. If you are setting it to Instock, regardless of whatever else happens in your sub-procedure, then you are "unchecking" or "unticking" the box.

 

[Eupher]

SprayCan,

If Not IsNull([red]bookedin_field[/red]) Then

I see where you have declared this variable, but where have you initialized it?

Ken S.

 

[Craig0201]

Why is there two sets of code to start with?

Could be achieved in one set and a couple of call statements.


Sub AlterStock(InStock As Integer, Alteration As Integer, MinNumber As Integer)

InStock = InStock + Alteration

Select Case InStock
Case 0
Me!chkOutOfStock = True
Me!chkReorder_box = True
Case < MinNumber
Me!chkOutOfStock = False
Me!chkReorder_box = True
Case Else
Me!chkOutOfStock = False
Me!chkReorder_box = False
End Select

End Sub


Then call from each of your click button.



If (instock) < (minnumber) Then
MsgBox "Please be aware there is still less than the minimum # of cartridges in stock. Consider purchasing more.", _
vbOKOnly + vbExclamation, _
"Cartridge Stock Alert - Warning"

Me!chkReorder_box = True

Else

Me!chkReorder_box = False

End If

If (instock) = 0 Then

Me!chkOutOfStock = True

Else

Me!chkOutOfStock = False

End If

Me!instock_field = instock

 

[SprayCan]

I forgot to mention, I'm completely new to VBA coding which is why it's probably the worst piece of programming you've ever seen hehe. Trying to teach myself.

Thanks for the responces, I'll put them to use and see if I can get it to work.

 

[Eupher]

SprayCan,

Nothing to be ashamed of. And not the worst code ever seen - not by far! You should see some of *my* early (and later!) efforts... ;-)

Ken S.

 

[SprayCan]

I'm going to keep tapping away with VBA, havn't done coding before but it's something I've been waiting to do for ages.

The code suggested by Craig2001 works perfectly, thanks a bundle

 

[Craig0201]

Good luck with coding......

If you're just learning, i'd try to learn either C# or Java. You can get a free C# IDE called SharpDevelop.

 

[SprayCan]

I've wondered whether I should learn another language or not, but it's really a case of learning what I need to use at work.

I'll stick with VBA for now see what I come up with, but thanks anyway