trouble with saving changes

[Peter2222]

Hi,

I've put a button on a form (lets say form 1), that when clicked opens up form 2.

Both forms are connected to ONE table each.

There is a sub table on form 2 that displays records with the same Id as on form 2.

The Problem is when I open The table that feeds the subtable. If I change the A to Z ordering on this table, whenever I close form 2 (not from design view, from user view), it asks me whether I want to save the changes to the design of the table feeding the sub table (the subtable that is on form 2, that is)?

When form 2 is closed there are calculations that are made and data is updated on form 1. I don't know if this is part of the problem? or perhaps I have set up the subtable incorrectly?

Please help!

Thanks

Peter

 

[RuralGuy]

How do you close Form2? If it is a command button then please post the code behind the button.

 

[Peter2222]

Here's the code.

Hi there,

thanks for the reply, I close the form by clicking the close button. I added an event to the form that calls this sub when closing:

Private Sub MoveDataToWorkLog()

Dim curTotal As Currency
Dim strTotal As String
Dim ThisJobNumber As Long
Dim frm1 As Form
Dim ctl1 As Control
Dim TotalN As Integer
Dim PresentN As Integer

On Error GoTo errorhandler

Set frm1 = Forms("frmWorkItemsAndDetails")
Set ctl1 = frm1.Form.subfrmAllWorkItems

TotalN = ctl1.Form.Recordset.RecordCount
If TotalN = 0 Then
curTotal = 0
strTotal = " "
GoTo NoWorkEtcError
End If

ctl1.Form.Recordset.MoveFirst

PresentN = 1
curTotal = 0
strTotal = ""
Do Until PresentN = TotalN + 1

If Not IsNull(ctl1.Form.Recordset![ItemPrice]) And Not IsNull(ctl1.Form.Recordset![ItemQuantity]) And IsNumeric(ctl1.Form.Recordset![ItemPrice]) And IsNumeric(ctl1.Form.Recordset![ItemQuantity]) Then
curTotal = curTotal + (Round(ctl1.Form.Recordset![ItemPrice], 2) * Round(ctl1.Form.Recordset![ItemQuantity], 2))
End If

If Not IsNull(ctl1.Form.Recordset![ItemDescription]) And Not IsNull(ctl1.Form.Recordset![ItemQuantity]) And IsNumeric(ctl1.Form.Recordset![ItemQuantity]) Then
strTotal = strTotal & ctl1.Form.Recordset![ItemDescription] & " (" & ctl1.Form.Recordset![ItemQuantity] & "). "
End If

PresentN = PresentN + 1
ctl1.Form.Recordset.MoveNext
Loop

NoWorkEtcError:

Forms![frmWorkLog].Controls![PresentValueOfJob] = curTotal
Forms![frmWorkLog].Controls![Work Planned] = strTotal

Exit Sub

errorhandler:
MsgBox Err.Description

End Sub


Could the problem be with the form properties? any help much appreciated!

Thanks

Peter

 

[RuralGuy]

I assume by "clicking on the close button" you mean the red button in the top right of the form. You might eliminate the error (though I'm not sure) if you used the RecordSetClone of the SubForm rather than the RecordSet. It is a little confusing because it looks like you might have 3 or 4 forms open at the same time and working with all of them. I suspect there is a far less complicated way to accomplish your goals.

 

[Peter2222]

A bit of progress

Hey there,

I've been having a wrestle over that form, I went to the properties of the form, and have set the 'Order by' criteria to the Id of the form, this has got rid of the problem.

Is this sensible practice though?

Hopefully hear back from anyone

Thanks

Peter

 

[RuralGuy]

I'm glad you seemed to have resolved your issue but there is not enough information to determine if this is a sensible practice or not. Hopefully someone else will comment.