If value greater then on form controls does not work (1 Viewer)

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Hi everyone,

Ive never had this problem before and im completely at a loss of trying everything. Really need some support because its getting shameful :p


I have a subform with a few fields. I want to compare 2 of them and get a result.

My subform has a query as a control source.
My subform is a continuous form
My subform has 2 field (names) that i am comparing for value and if one is greater then the other, it should 'do something...

But i get NO errors, nor does my code get executed.

Code:
Private Sub scanquantity_AfterUpdate()
    If Me.scanquantity.value > Me.whsquantity.value Then
    MsgBox "Limit exceeded"
Else
    MsgBox "test"
End If
End Sub

The fields have numbers in it, but are stored as short text.
I tried making them number fields, but still it didnt work.
I tried TEXT instead of VALUE. Still it dont work.

Why the **** does it get ignored?
Please, some help :confused:
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:36
Joined
Sep 21, 2011
Messages
14,048
AfterUpdate event only runs after a change has been made (updated?)

Have you tried amending scanquantity ? and tab out

I would have them as number fields not text as well?
 

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Hi Gasman! Always to the rescue?

Well, i am scanning parts, so the number does change on my screen.
But it doesnt pick up the else statement either.

It seems it doesnt know the value has been updated. I also tried on change.
But no luck.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:36
Joined
Sep 21, 2011
Messages
14,048
I've not played with scanners, so I do not know what events might fire with those, but I would certainly try amending manually with a breakpoint on your If statement.

When silly errors like this happen to me, i always go to the debugger and F8 :D

HTH
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:36
Joined
May 21, 2018
Messages
8,463
The after update event is not raised if the value is changed by code. You can test this easily

Code:
Private Sub budget_AfterUpdate()
  'Nothing happens here
  MsgBox "Update"
 End Sub

Private Sub Command14_Click()
  Me.budget = 7
  Me.Dirty = False
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:36
Joined
May 21, 2018
Messages
8,463
This may be a good place for a custom event. You can define an event and raise it when your scanner code executes.
 

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Hey guys,

still no luck :(

So my subform has a query as a control source.
When i scan on my main form the data into a field, it will requery the subform and show me the results.

This might be why it doesn't see the "after update" event because, its not really updated, its requeried.

SO i thought i use the on load event on my subform...but that means it will repeat the statement every time i scan something.

What i want is that my "stock" says i need 1 part, and i scan 2, it will tell me that i scanned too many items.

How can i throw my statement on a subform with a query as a record source that gets requeried?
 

Minty

AWF VIP
Local time
Today, 04:36
Joined
Jul 26, 2013
Messages
10,355
Put the calculation in the underlying query and make that result available in a text box in the sub form. It will update with your data changes.

You can use conditional formatting to Hide/display/highlight the value.
 

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Put the calculation in the underlying query and make that result available in a text box in the sub form. It will update with your data changes.

Thanks minty. I use conditional formatting to show there is a difference :)
That works, but i need some IF in there to execute a bunch of actions.

So, i go to the "source" query lets say

- Make a new field in the query
- With my calculation as an expression underneath it
- Have a field on my form that shows the result
- Lets say if bigger "yes or no", i execute whatever i want...

correct? :) at least its a plan. ill have to wait till tomorrow :banghead:
Thanks!!!!!
 

Minty

AWF VIP
Local time
Today, 04:36
Joined
Jul 26, 2013
Messages
10,355
You can use the OnCurrent event in the subform to check the result and execute changes ?
 

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Thanks Minty :)

Damn, i was sure this would work. But it doesnt.

So i put my calculation in my query as an expression.
Which works when i run the query.

Then i put that field in my subform. Which gives the correct result.
But again, when i put the if statement in ANY event possible...it seems to be ignored. :confused::confused::confused:
Code:
Private Sub difference_AfterUpdate()
    If Me.difference.Value > 0 Then
        MsgBox "You scanned too many items"
End If

If Me.difference.Value < 0 Then
        MsgBox "You scanned too less items"
End If
End Sub

(yes, my field NAME is difference)

On current, on change, before update....dont matter.
I get no errors and no popups...
 

Minty

AWF VIP
Local time
Today, 04:36
Joined
Jul 26, 2013
Messages
10,355
You are checking the wrong value.

Although the Difference value is changed by the calculation, as stated earlier the form won't "see" that change as an event to trigger from.

You need to check on the After update of the fields that drive that result. They are being changed on the form.
 

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Right, its exactly the same situation indeed as before.
So i need to do a Dlookup on the query field (difference) ?

Im lost...


Code:
    Dim varX As Variant
        varX = DLookup("[difference]", "compare_all_q")
            If varX > 0 Then
                MsgBox "too many"
            End If

            If varX < 0 Then
                MsgBox "too less"
            End If

Hey that works...kind of :eek:
But it only does work for my first record in the continuous form.
 

Minty

AWF VIP
Local time
Today, 04:36
Joined
Jul 26, 2013
Messages
10,355
No I don't think you need a Dlookup.

I assume that after your scanner has picked up the qty, it sends a <Enter> key to effectively tab out of the field, if not then your change isn't made to the field, and your after update event won't fire.
 

bruceblack

Registered User.
Local time
Today, 04:36
Joined
Jun 30, 2017
Messages
119
Cool thanks!

Indeed, my scanner itself sends an enter after scanning.
So thats covered.

But you have an idea on how i should do this without the dlookup?
 

Minty

AWF VIP
Local time
Today, 04:36
Joined
Jul 26, 2013
Messages
10,355
I'm not sure why it wouldn't work.
Any chance of posting up a stripped down DB with enough stuff left in it to replicate the issue ?

I use a barcode scanner to check invoices against job numbers and box numbers and it all works seamlessly, except it's not a continuous form?
 

Users who are viewing this thread

Top Bottom