Sub Sub Form OnCurrent looping twice (1 Viewer)

[Peter Bellamy]

I have added some new code to some I wrote a long time ago and during debugging it I found that the On Current code was being actioned twice.
Is that normal?
I used a breakpoint and stepped through the code and there does not appear to be any code run elsewhere between the 1st and 2nd pass?

 

[vbaInet]

I can't see why it would loop twice. Can we see a stripped down version of your db and the form in concern?

 

[Peter Bellamy]

I will see if I can 'reduce' it !

Meanwhile, is there any other way of tracing the way the code is being actioned?

 

[NigelShaw]

Hi,

it would be easier if the code was posted but in the meantime? did the OnCurrent run a separate procedure that maybe your new code calls too thus potentially calling the routine twice?


Nidge

 

[Peter Bellamy]

Meanwhile this is the code of the OnCurrent event

Private Sub Form_Current()
On Error GoTo Callform_current_error

Dim tim As Variant
Dim lab As Currency
Dim serial As Variant
Dim instdate As Date
Dim expiredate As Variant
Dim TestDate As Date
Dim equip As String
Dim cust As String
Dim callno As Long
Dim FullPath As String
Dim strDocName As String
Dim StrRst As Recordset
Dim StrDb As Database
Dim StrSql As String


StrSql = "SELECT * FROM Equipment WHERE equip_index = " & Me.call_equip_sno
Set StrDb = CurrentDb()
Set StrRst = StrDb.OpenRecordset(StrSql)

expiredate = StrRst("[equip_expire_date]")
serial = StrRst("[equip_serialno]")
equip = StrRst("[equip_name1]")

callno = Me.call_no
cust = DLookup("[product_customer]", "Product", "[product_name] = '" & equip & "'")
'Debug.Print equip, callno, cust

If cust = "Andrews" Then cust = "BAXI GROUP COMPANIES"
strDocName = "*" & callno & ".doc"
FullPath = "Z:\Data\CUSTOMERS\" & cust & "\Site Visits\" & strDocName
'Debug.Print FullPath

'Check folder contains a doc, open info window if not, doc if it does
    If Dir([FullPath]) <> Empty Then
        Me.btndoc.Caption = "Report"
    Else
        Me.btndoc.Caption = "No Report"
    End If

If IsNull(expiredate) Then
    If IsNull(serial) Then
        expiredate = #1/1/1992# ' One year after Company started.
    Else
        serial = Left(serial, 2) + 1
        expiredate = CDate("01/01/" & serial)
    End If
End If
    
TestDate = Format(Now(), "Short Date")

    If (TestDate - expiredate) > 0 Then
        Me![call_cost].Visible = True ' outof warranty
        Me![call_price].Visible = True
        Me![labour].Visible = True
        Me![warranty].Visible = False
    Else
        Me![warranty].Visible = True ' in warranty
      
    End If

If IsNull(call_finishtime) Or expiredate - TestDate > 0 Then 'in warranty no labour
    ' no labour calc needed.
    lab = 0
Else
    tim = DateDiff("n", [call_startime], [call_finishtime])
        If tim < 60 Then
            lab = firsthour    ' Callout and first hour £40.00 set on form open
        Else
            lab = firsthour + (secondhour * (tim - 60) / 60) ' Hourly rate after first hour, £30.00
        End If
End If
Me.labour = lab

Exit_Form_Current:
Exit Sub

Callform_current_error:
    MsgBox Err.Description
    Resume Exit_Form_Current
End Sub

pnb

 

[vbaInet]

On quick glance I can't spot the culprit. If there was a function making it run twice it would be something like an AfterUpdate function calling OnCurrent again or the function is calling itself. Let us know when you've managed to reduce the db.

 

[Peter Bellamy]

After much fiddling around I have managed to cut the db down to just the required parts to post.
Autoexec will open the main form and the code window for the sub-subform will open with the Oncurrent code to be stepped through.
For the first 'run' it only goes through once. Move to the next reecord and it goes through twice ??

Any suggestions welcome !!!

pnb

 

[gemma-the-husky]

its funny

i have sometimes put a msgbox in a function called by a query (say) to check stuff, and it often seems to be called twice, where you expect it to be called once

maybe this current event "firing" twice is a similar thing.

 

[vbaInet]

the joys of "accessing" Access.

Peter, I've just had a look and the problem is pointing to your second subform (called Call Record), i.e. the one embedded the subform that is looping twice. When the main form loads, you can see that the 2nd subform's record has become dirty (i.e. it has been editted). You need to revise the 2nd subform's (i.e. Call Record's) On Current event to ensure that you're not editting the record and if you are, save it. Or look for an alternative way.

 

[Peter Bellamy]

Note to users. Don't compose a reply, then try to add to someones reputation before posting.
You loose your post !!!!????

Thanks vbaInet, one or two questions.
How can you 'see' the Call Record form is Dirty?
If I i=understand you correctly the editing in OnCurrent is making some fields visible, or not and changing a button caption, or not. I tried a form save after them but it still looped twice?

The form previously used Domain Lookups and was visibly slow. Having changed it to SQL based Recordsets it is not noticable. Perhaps looping twice is the price you have to pay to tailor a form to the data is is about to display!

pnb

 

[vbaInet]

When you edit a record, you will notice the pencil symbol at the top left corner of the form, where the record selector is. You will notice that appears when the form loads. Also, temporarily removing the OnCurrent event of that subform (Call Record) caused its parent form to loop once.

If I've got a minute I will have another look and tell you where it causes it to be dirty.

 

[vbaInet]

And here's the culprit:

Me.labour = lab

Edit: That got rid of the dirty problem but there's still the double call to Current. I just had a look at your relationships and I noticed none of the tables are linked. Is this not your main database?

 

[Peter Bellamy]

Did you remove the Me.labour = lab line to 'clean' it, or something else?

There are links in the original db, of which this is just a part, but I didn't set them up for the part I posted.
I have attached it with them completed.

 

[ChrisO]

Add Option Explicit to Form Call Record.
Doesn’t compile.

Use Option Explicit and compile your code.
Fix the errors raised by the compile.

Comment out the error handling call.

call_equip_sno is Null in two records in table Calls.

StrSql = "SELECT * FROM Equipment WHERE equip_index = " & Me.call_equip_sno

raises an error.

StrSql = "SELECT * FROM Equipment WHERE equip_index = " & Nz(Me.call_equip_sno, 0)

prevents that error but returns no records.

If StrRst.RecordCount > 0 Then
Blah
Blah
Blah
End If

call_startime, call_finishtime, Me.call_no and tim can be Null.

"Certikin Oil 175" is being assigned to an integer.
With: -
equip = StrRst("[equip_name1]")

Too many errors to fix.

Start by using Option Explicit and turn off error handling.

Chris.

 

[vbaInet]

Just answering this question

Did you remove the Me.labour = lab line to 'clean' it, or something else?

Yes that line was causing the subform to be dirty on load. There's a similar one (I think) on the Equipment subform. I haven't traced what that line does but nonetheless, Chris has pointed you in the right direction.

 

[Peter Bellamy]

Aggh, 3 out 10 for me!
Thanks for the time, effort and help from both of you.

Chris I forgot to include a Module when I shrunk the db and some of the variables are dimensioned there as Global. However not all of them so I have now cleaned that up.

There were 4 equipment records assigned as integers, this was because I had just re-assigned the relationship between Equipment and Product and they had not been defined in the Product table and Equipment still had the old info it. Now sorted

The 2 Null call_equip_sno records I can't explain except that the db goes back to 1998 and at some time they must have lossed their reference to their equipment. Now fixed.

It was the first db I ever wrote and is full of mistakes and poor code which, if they haven't stopped it being used, I have never gone back to.
Like a lot of Access users I do this as a sideline to provide data tools for my Company, I still have loads to learn. That is why this Forum is so good, it is great help in learning.

I will tidy it up and see if it still loops twice.

pnb
Thanks again

 

[vbaInet]

It brings back memories seeing your first db at work

I for one would be wary using my first db without carrying out a major overhaul of it.

Let us know how things progress.

 

[ChrisO]

Code is not the problem but I don’t know what is.

I have reduced it to the point of anorexia, decompiled, imported what’s left into another blank database and it still does it twice.

Just to check, I created two new tables with different names and different field names.
Created two new Forms, Main/Sub, and it only called the Current event once.
Using the two new Forms, changed the Record Source to the old tables and it calls it twice.

That would imply that the problem is in one or both of the tables but I can’t find it.

Anorexic demo with fault is attached if someone wants to try.
To simulate the problem, move to a new record on the Main Form.

Chris.

 

[Peter Bellamy]

That makes me feel a little better that it is baffling you, thanks again!
I am going to set about it and go through all the code to try and sort it out.

It is nearly done but have hit a snag in opening a form from the subform 'Equipment Record'. (post: Error 2465.....) I wonder if it relates to this problem?

Peter

 

[vbaInet]

I think your forms have experienced some form of corruption which is what is making it loop twice. I initially thought that it had something to do with the the subform being in the Detail event of its parent form but it turned out not to be the case. This would explain why you would find that your original forms (in the attachment) have been moved about

I used the wizard to create all three forms and placed the subforms in the same section as you have it on your db and it works as it should, loop once. See the attached and you will notice the same.

My suggestion would be to rebuild the forms using the wizard, change the layout then add the code and reattach the Event Procedures.