afterupdate refresh sends cursor to first control and stops mouse scrolling from work

[cowboy]

I have a form lets say its called formA which has a subform formB on it. When I am entering data into the subform I have an afterupdate code that calls a Forms("formA").Refresh on formA because it has a text boxes which values are calculated from the info that went into FormB.

Problem 1: When I do a refresh in the afterupdate command I have to click on the next box twice, the first time because it does the refresh and sends the cursor back to the first control on the subform and then the second time to get it to go to the desired textbox.

Attempts: I tried to put it into the onchange event, but whenever I would go to put a decimal like .5 after I hit the decimal key it tries to refresh and gives me an error saying invalid type, because the type of the field is Number -> Long Integer



Problem 2: When I do the refresh it stops my mouse scrolling button from working.

Any help on either of these problems would be appreciated.

[June7]

1. Try DoCmd.RepaintObject instead of Refresh or Requery. I've never used it but seems appropriate for your situation.

2. Maybe fix No.1 will fix this.

[cowboy]

I like the idea, but it didnt work. I tried the docmd.RepaintObject and then after looking up some stuff microsoft recommends you use just repaint, but I couldnt get that to work either.

[June7]

Repaint alone probably not going to work. Form B record/edits probably need to be committed to table first. This happens when move to another record, close form, or run code DoCmd.RunCommand acCmdSave. Then the new data will be available and Repaint should reflect that in calcs.

[cowboy]

I thought of that too, I had tried putting a save then a repaint call of the main form and it didnt update the box I wanted. I was disappointed. I will see if I can get a dumbdown version of my database and post it.

[cowboy]

Ok here is a version of my program, I just took customer info out and stuff for safety.

From the main page click "View Bids" button
then click on the actual number on the left which is the bid number which in this case is "1206" and that will open the bid
On this form if you change any of the quantities or products or other things you can change you will see the refresh kicks it back to the first control in that subform.

Anyone who looks at this feel free to leave feedback, I am always looking to improve.

Thanks

[June7]

The only way I could get rid of the behavior was to eliminate the DLookup calcs and Requery/Refresh/Recalc. Instead I put a textbox in each subform footer with =Sum(LaborHours) and then I commit the record with DoCmd.RunCommand acCmdSave in the AfterUpdate event. Then a calc in TotalLaborHours textbox adds the 3 subform sums.

Here is the revised project for your review. I will let you clean up the other 3 calcs on the main form.

How do you want to handle if user starts a record in subform and changes mind or if they forget a field. You have record selectors turned off so can't select record and delete. Need code to check that fields are left empty and ask user if they want to abort the record.

EDIT: Purpose service, file removed.

[cowboy]

All they have to do is leave the items blank and I call this function when they print or close the form.

Sub clearBlanks()    Dim rst As DAO.Recordset


    Set rst = CurrentDb.OpenRecordset("Services")
    If rst.RecordCount <> 0 Then
        rst.MoveFirst
        Do While Not rst.EOF
                If IsNull(rst![LaborHours]) And IsNull(rst![ServiceName]) Then
                        rst.Delete
                End If
            rst.MoveNext
        Loop
    End If
    
    Set rst = CurrentDb.OpenRecordset("Materials")
    If rst.RecordCount <> 0 Then
        rst.MoveFirst
        Do While Not rst.EOF
                If IsNull(rst![LaborHours]) And IsNull(rst![Quantity]) And IsNull(rst![ProductName]) Then
                        rst.Delete
                End If
            rst.MoveNext
        Loop
    End If
    
    Set rst = CurrentDb.OpenRecordset("Equipment")
    If rst.RecordCount <> 0 Then
        rst.MoveFirst
        Do While Not rst.EOF
                If IsNull(rst![LaborHours]) And IsNull(rst![Quantity]) And IsNull(rst![EquipmentName]) Then
                        rst.Delete
                End If
            rst.MoveNext
        Loop
    End If
End Sub

[cowboy]

Do you think I should turn on record selectors and allow deletions?

[June7]

Depends on your users. Some are comfortable with the intrinsic Access features some need a button that says 'Delete this record'. Sounds like you have the issue dealt with.

[cowboy]

Finally got it to work

    If fIsLoaded("f_Estimate") = True Then        RunCommand acCmdSaveRecord
        Forms("f_Estimate").Controls("txtLabor").Requery
        Forms("f_Estimate").Controls("txtLaborCost").Requery

I had to save the change made and only requery the text boxes needed and not the whole main form which also requeried the subform.