Form 'OnCurrent' Event not firing properly, but does when stepping through it?? (1 Viewer)

[JeffBarker]

Hi guys, this is a bit of a weird one, and probably more to do with my rubbish VBA skills than anything else, but I'm hoping someone is able to clarify either way!!

We use our CRM for entering client orders, which is done through our 'OrderEntry' form. On that form we have a subform that we use to enter order lines - as we have a range of products on offer, an order can be made up of one product or anything up to 100.

We have an 'amount' textbox on the subform, that the users enter the cost of each line, and as they add more lines the total cost of the order is calculated using a textbox in the subform Footer, with the ControlSource set to =Sum([amount]).

On the main form, we have a Net textbox, that the user will add in the total cost of the order once they've added all the order lines, and providing the =Sum([amount]) textbox on the subform matches the figure in the Net textbox, a button to Proceed the order and go on to the next step appears.

The intermittent problem I have, is that the OnCurrent event I use to show the Proceed button does not seem to fire on its own - but when I stop the code and step through it, it will show the Proceed button without any problems.

Here's the code:

(the Proceed button is referred to a 'Command80')

    Dim OS As String
    Dim UT As String
    
    OS = OrderStatus
    UT = fGetUserType
    
    If Me.AccountsStatus = "account on stop" Then
        Me.Command80.Visible = False
        MsgBox "Account is on stop" & Chr(13) & "You can view orders but can not place orders for this customer.", vbInformation
    End If

    If [Main Function] = "Agency" Then
        Client.Visible = True
        Label73.Visible = True
    Else
        Client.Visible = False
        Label73.Visible = False
    End If

    If Me.CanxReason > "" Then
        Me.Text108.Visible = True
        Command110.Visible = True
        Command104.Visible = False
        Me.OrderType.Locked = True
        Me.InvoiceContact.Locked = True
        Me.ProductionContact.Locked = True
        Me.Client.Locked = True
        Me.PO_Number.Locked = True
        Me.Gross.Locked = True
        Me.Discount.Locked = True
        Me.Net.Locked = True
        Me.Command52.Enabled = False
        Me.Command53.Enabled = False
        Me.Order_Detail.Enabled = False
        Me.Production_Notes.Enabled = False
        Me.Finance_Notes.Enabled = False
        Me.tblAllocation_subform.Locked = True
        Me.Command80.Enabled = False
    Else
        Me.Text108.Visible = False
        Me.Command110.Visible = False
        Me.Command104.Visible = True
        Me.Label105.Visible = False
        Me.CanxReason.Visible = False
        Me.OrderType.Locked = False
        Me.InvoiceContact.Locked = False
        Me.ProductionContact.Locked = False
        Me.Client.Locked = False
        Me.PO_Number.Locked = False
        Me.Gross.Locked = False
        Me.Discount.Locked = False
        Me.Net.Locked = False
        Me.Command52.Enabled = True
        Me.Command53.Enabled = True
        Me.Order_Detail.Enabled = True
        Me.Production_Notes.Enabled = True
        Me.Finance_Notes.Enabled = True
        Me.tblAllocation_subform.Locked = False
        Me.Command80.Enabled = True
    End If
    
    Select Case OS
        Case "Confirmed"
            Me.cmdPDF_NoCosts.Visible = True
            Me.cmdPDF_Costs.Visible = True
        
        Case "New"
            Me.cmdPDF_NoCosts.Visible = False
            Me.cmdPDF_Costs.Visible = False
    End Select
    
    Select Case UT
        Case "Admin"
            If OrderStatus = "Confirmed" Then
                Me.cmdFinancePDF.Visible = True
            Else
                Me.cmdFinancePDF.Visible = False
            End If
            
        Case "Director"
            If OrderStatus = "Confirmed" Then
                Me.cmdFinancePDF.Visible = True
            Else
                Me.cmdFinancePDF.Visible = False
            End If
            
            If Me.txtLogDate <> Date Then
                Me.AllowEdits = False
                Me.[tblAllocation subform].Locked = True
                Me.FinanceNotes.Enabled = False
                Me.ProductionNotes.Enabled = False
                Me.OrderDetail.Enabled = False
                Me.Command52.Enabled = False
                Me.Command53.Enabled = False
                Me.Command104.Enabled = False
                Me.Command110.Enabled = False
                Me.cmdPDF_Costs.Enabled = False
                Me.cmdFinancePDF.Enabled = False
                Me.cmdPDF_NoCosts.Enabled = False
            Else
                Me.AllowEdits = True
                Me.[tblAllocation subform].Locked = False
                Me.FinanceNotes.Enabled = True
                Me.ProductionNotes.Enabled = True
                Me.OrderDetail.Enabled = True
                Me.Command52.Enabled = True
                Me.Command53.Enabled = True
                Me.Command104.Enabled = True
                Me.Command110.Enabled = True
                Me.cmdPDF_Costs.Enabled = True
                Me.cmdFinancePDF.Enabled = True
                Me.cmdPDF_NoCosts.Enabled = True
            End If

        Case "User"
            If Me.txtLogDate <> Date Then
                Me.AllowEdits = False
                Me.[tblAllocation subform].Locked = True
                Me.FinanceNotes.Enabled = False
                Me.ProductionNotes.Enabled = False
                Me.OrderDetail.Enabled = False
                Me.Command52.Enabled = False
                Me.Command53.Enabled = False
                Me.Command104.Enabled = False
                Me.Command110.Enabled = False
                Me.cmdPDF_Costs.Enabled = False
                Me.cmdFinancePDF.Enabled = False
                Me.cmdPDF_NoCosts.Enabled = False
            Else
                Me.AllowEdits = True
                Me.[tblAllocation subform].Locked = False
                Me.FinanceNotes.Enabled = True
                Me.ProductionNotes.Enabled = True
                Me.OrderDetail.Enabled = True
                Me.Command52.Enabled = True
                Me.Command53.Enabled = True
                Me.Command104.Enabled = True
                Me.Command110.Enabled = True
                Me.cmdPDF_Costs.Enabled = True
                Me.cmdFinancePDF.Enabled = True
                Me.cmdPDF_NoCosts.Enabled = True
            End If

        Case "Manager+"
            If Me.txtLogDate <> Date Then
                Me.AllowEdits = False
                Me.[tblAllocation subform].Locked = True
                Me.FinanceNotes.Enabled = False
                Me.ProductionNotes.Enabled = False
                Me.OrderDetail.Enabled = False
                Me.Command52.Enabled = False
                Me.Command53.Enabled = False
                Me.Command104.Enabled = False
                Me.Command110.Enabled = False
                Me.cmdPDF_Costs.Enabled = False
                Me.cmdFinancePDF.Enabled = False
                Me.cmdPDF_NoCosts.Enabled = False
            Else
                Me.AllowEdits = True
                Me.[tblAllocation subform].Locked = False
                Me.FinanceNotes.Enabled = True
                Me.ProductionNotes.Enabled = True
                Me.OrderDetail.Enabled = True
                Me.Command52.Enabled = True
                Me.Command53.Enabled = True
                Me.Command104.Enabled = True
                Me.Command110.Enabled = True
                Me.cmdPDF_Costs.Enabled = True
                Me.cmdFinancePDF.Enabled = True
                Me.cmdPDF_NoCosts.Enabled = True
            End If
        
        Case "Finance"
            If OrderStatus = "Confirmed" Then
                Me.cmdFinancePDF.Visible = True
            Else
                Me.cmdFinancePDF.Visible = False
            End If
        
    End Select
    
    If Me.Text123 = 0 Then
        Me.Text125.Visible = False
    Else
        Me.Text125.Visible = True
    End If
 
[B]    If Me.Net = 0 Then
        Me.Command80.Visible = False
    Else
        If Me.Net = Forms!orderentry![tblAllocation subform].Form.Text9 Then
            Me.Command80.Visible = True
        Else
            Me.Command80.Visible = False
        End If
    End If[/B]
    
    MsgBox "Main Form/Form Current has Fired!"

I've made the part that refers to the Proceed button bold, but thought I'd add in the whole OnCurrent event in case there was anything in there that was blocking it.

I added in the MsgBox code at the bottom to make sure the OnCurrent was firing, and that works fine.

I've searched through the rest of the code, and there's nothing else in there that references the Visible property of the button.

I've been through the decompile process detailed here, and also been through this similar thread with a fine toothcomb and this still won't work.

I've saved the form out as a textfile and then imported it back in, both through the immediate window, and short of importing everything in to a new DB and starting again, I'm actually pretty stumped!!

So any help will be most appreciated, thanks!

 

[Uncle Gizmo]

By way of a "Bump" and to start an elimination process:-

Please add MsgBox " >>> Current Event Fired"

in your current event.

Comment out all the other code in the "Current Event" and report back what happened ....

 

[JeffBarker]

By way of a "Bump" and to start an elimination process:-

Please add MsgBox " >>> Current Event Fired"

in your current event.

Comment out all the other code in the "Current Event" and report back what happened ....

Hey, Uncle Gizmo - thanks for the reply and the bump!

I've done the above, and this fires okay...

*EDIT: also worth mentioning, the Proceed button is visible at this point!

 

[vbaInet]

Hi guys, this is a bit of a weird one, and probably more to do with my rubbish VBA skills than anything else, but I'm hoping someone is able to clarify either way!!

Your "rubbish" VBA skills isn't that bad but the names you give to your controls are not great, i.e. Command80, Label173, Text108, Command104... to name but a few.

Also you always need to use Me. or Me! to refer to a control or field respectively. You're not always doing that.
And there's a lot of repetition of code which you can put in one function and call when needed.

We need to understand the net textbox and the amount field in relation to which one users enter values into.

 

[JeffBarker]

Your "rubbish" VBA skills isn't that bad but the names you give to your controls are not great, i.e. Command80, Label173, Text108, Command104... to name but a few.

Also you always need to use Me. or Me! to refer to a control or field respectively. You're not always doing that.
And there's a lot of repetition of code which you can put in one function and call when needed..

Hey vbaInet - thanks for the reply!

Well, in my defence here, I didn't actually write this code - it was already in place when I took over the role! I know a few of the naming conventions for prefixing control/field names (cmd, txt, cbo, chk etc), and I always use them when initiating new code.

I'd love to be able to tidy up all the old code, but I'm not 100% sure on the most time effective way to do that...there are a HELL of a lot of unused controls and fields on this one form in particular and, where it's been copied from other databases instead of starting afresh, all the old fields have been made invisible and moved out of sight from the user, and the new stuff put over the top.

A lot of other forms reference back to each other too, so I have no idea what's safe to keep and what's not!

It's the same in ALL of our major databases, has been for years I guess - one main database was created in the beginning, and then that's been copied and repurposed for something else, but not cleaned out properly, so you get the same forms and queries etc showing up in practically all our major databases...it's a nightmare!

But that's another thread in itself, I feel!

Although, if you're able to provide some tips on how to truncate the repeated code in to a function or two, that would be amazing!

We need to understand the net textbox and the amount field in relation to which one users enter values into.

Okay, so as the users enter order lines into the subform the (unbound) amount field in the (subform) footer automatically tots up the total order cost as they go.

Once they've added in all their lines, they enter the total from the amount field into the Net textbox on the main form, so the two tally up - which is when the Proceed button should appear.

 

[vbaInet]

Give controls meaningful names, do the same to the code behind the form and upload the db so that we can take a look. We can't tell by just looking at your code because or starters the control names don't mean anything to us.

 

[JeffBarker]

Give controls meaningful names, do the same to the code behind the form and upload the db so that we can take a look. We can't tell by just looking at your code because or starters the control names don't mean anything to us.

Hello again, vbaInet!

Is it okay to upload a sample form again this time?

The form we're talking about here is actually the one that opens the form we were just dealing with in the other thread!

 

[vbaInet]

Sample form is fine as long as it demonstrates the problem. If it doesn't then there's really no point

 

[JeffBarker]

Sample form is fine as long as it demonstrates the problem. If it doesn't then there's really no point

Of course!

I'll give it a bash!

Thanks again!

 

[JeffBarker]

Sample form is fine as long as it demonstrates the problem. If it doesn't then there's really no point

Hi guys, sorry it's taken a couple of days to get this done - really quite busy at the moment.

So I've rebuilt the form, dropped out a few unnecessary controls, tidied up the code and tried to give everything a meaningful name! All in all, a rather painstaking process, especially when getting this form to work within the rest of the db...

But I'm still having the same problem!

:banghead:

So I'm actually well and truly stumped now!!

The offending bit of code is now here (as we realised there was a bit of conflicting code between these two if statements, so I merged the two), but it still only works if you stop the code and step through it, not when you just run the form normally (and this is the first if statement in the OnCurrent event):

*EDIT* To clarify, cmdProceed can't make it's mind up whether it wants to be visible or not!

    If Me.txtAccountsStatus = "Account on Stop" Then
        Me.cmdProceed.Visible = False
        MsgBox "Account is on stop" & Chr(13) & "You can view orders but can not place orders for this customer.", vbInformation
    Else
        If Me.txtNet <> 0 Then
            If Me.txtNet <> Forms!frmorderentry![tblAllocation_subform].Form.txtSumAmount Then
                Me.cmdProceed.Visible = False
            Else
                Me.cmdProceed.Visible = True
            End If
        Else
            Me.cmdProceed.Visible = True
        End If
    End If

Sample form attached!

Look forward to hearing what you guys are able to come up with!!

 

[JeffBarker]

Hey guys, in addition to the above, we've found a temporary fix for the problem!

The Proceed button is visible at all times, but now we've got an If statement in the OnClick event of that button that flashes up a messagebox warning to the user if txtNet on the Main Form does not match txtSumAmount on the sub.

Quite a simple alternative really, when you think about it, but ideally we'd like the database to work as intended - so if you guys are able to come up with a fix in the meantime, that would be ace!

Cheers,

Jeff.

 

[DavidAtWork]

have you tried stepping through the code and inspecting the values in the controls:
Me.txtAccountsStatus
Me.txtNet
Forms!frmorderentry![tblAllocation_subform].Form.txtSumAmount

to see if the values are being evaluated correctly by your If statements

David

 

[JeffBarker]

have you tried stepping through the code and inspecting the values in the controls:
Me.txtAccountsStatus
Me.txtNet
Forms!frmorderentry![tblAllocation_subform].Form.txtSumAmount

to see if the values are being evaluated correctly by your If statements

David

Hi David,

Thanks for the reply!

Excuse my ignorance here, but would I be right in assuming that as I step through the code I should hover the cursor over each control to see what value appears, or is there an alternative way of inspecting them?

 

[Cotty42]

Either that or add watch points for all relevant data (Debug menu)

 

[JeffBarker]

Either that or add watch points for all relevant data (Debug menu)

Hi Cotty42,

Thanks for the reply - how would I add a watch point?

And also, how can I be sure that the values are being evaluated properly?

Cheers,

Jeff.

 

[DavidAtWork]

A simple way is to put a breakpoint in the code after all the controls have been referenced, then highlight the 3 controls, one at a time, hovering the mouse over will usually display the value but obviuosly you can only view one at a time. A better way is to highlight each in turn and right-click selecting the 'Add watch' option and you should see a 'Watches' window appear at the bottom, add a 'Watch' for each control and here you can see the values of all the controls as you navigate records

David

 

[DavidAtWork]

"And also, how can I be sure that the values are being evaluated properly"
Once you know the values in the controls, you'll know if they are being evaluated correctly by the route the code takes in your If statements
Drag the yellow highlighted code line back to the beginning of the If statement and using the F8 key to step through, see if it's making the right decisions

David

 

[Cotty42]

Highlight the variable and Pres Shift F9.

Presumably you know what the variables should be so you can see if they are being evaluated correctly in the Watch window (usually at the bottom of the VBA window).

I guess the key values to watch are:
Me.Net
Forms!orderentry![tblAllocation subform].Form.Text9
Me.Command80.Visible

As those are the ones that you are trying to evaluate.

Dave

 

[DavidAtWork]

easiest way to add a watch is to highlight the control name and right-click/Add Watch, this will open the Watch window, add one for each control and then put a breakpoint in the code and step through the If statements taking note which route the code takes, look at the values of the controls in the Watch window and common sense will tell you if the values are being evaluated correctly

David

 

[JeffBarker]

easiest way to add a watch is to highlight the control name and right-click/Add Watch, this will open the Watch window, add one for each control and then put a breakpoint in the code and step through the If statements taking note which route the code takes, look at the values of the controls in the Watch window and common sense will tell you if the values are being evaluated correctly

David

Thanks David and Cotty42, I'm going to try this now!

I have to say this Watch tool is really useful, and a bit cheeky!