I have an Unbound Menu Form that brings up all the records in the db by name after the user types into a Parameter window. The record Form ("Special Acceptance Form Letter") is launched when the User Double Clicks their selection. I've used this form before on TONS of db's, however, on this particular db I'm getting the Update or CancelUpdate without AddNew or Edit error after the record Form is opened and I try to move from one record to the next or make an Edit.
Additionally, the Main Switchboard opens ON TOP of my form. Grrrrr.
Funny thing is, it doesn't happen when I go to the Database Window and just open it from there. I can move from record to record, opening the form that way, no problem, so I don't know what to think. The code running my Unbound Box is:
Private Sub Like_Search_DblClick(Cancel As Integer)
'Dimension variables.
Dim formname As String, SyncCriteria As String
Dim frm As Form, rs As DAO.Recordset
'Set the formname to "IRF Input form," the form that will be
'synchronized (opened).
formname = "Special Acceptance Form Letter"
'Check to see if the Main form is open. If it
'is not open, open it.
If Not SysCmd(acSysCmdGetObjectState, acForm, formname) Then
DoCmd.OpenForm formname
End If
'Define the form object and Recordset object for
'the Main form.
Set frm = Forms(formname)
Set rs = frm.RecordsetClone
'Define the criteria used for the synchronization.
SyncCriteria = BuildCriteria("ID", dbLong, Me.Like_Search.Column(5))
'Synchronize the corresponding record in the form to
'the current record in the subform.
rs.FindFirst SyncCriteria
'If a record exists in the form, find the
'matching record.
If rs.NoMatch Then
MsgBox "No match exists!", 64, formname
Else
frm.Bookmark = rs.Bookmark
End If
DoCmd.Close acForm, "Customer Search Dialog"
Exit_ReportList_Click:
Exit Sub
Set rs = Nothing
End Sub
However, when I get the error and click DEBUG, it shows breaking on this line (Bolded) below:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'*******************Prem Ops Mod**********************************
If Me.Prem_Ops_U_L_Mod_1 = 0 Then
[Prem/Ops U/L Manual 1] = [Prem/Ops U/L Prem 1]
Else
[Prem/Ops U/L Manual 1] = ([Prem/Ops U/L Prem 1] / [Prem/Ops U/L Mod 1])
End If
If Me.Prem_Ops_U_L_Mod_2 = 0 Then
[Prem/Ops U/L Manual 2] = [Prem/Ops U/L Prem 2]
Else
[Prem/Ops U/L Manual 2] = ([Prem/Ops U/L Prem 2] / [Prem/Ops U/L Mod 2])
End If
If Me.Prem_Ops_U_L_Mod_3 = 0 Then
[Prem/Ops U/L Manual 3] = [Prem/Ops U/L Prem 3]
Else
[Prem/Ops U/L Manual 3] = ([Prem/Ops U/L Prem 3] / [Prem/Ops U/L Mod 3])
End If
'*******************Prem Ops Mod 1 **********************************
Dim Effective_Date As Date
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
[Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
[Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
[Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
[Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
[Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
End If
'*******************Prem Ops Mod 2**********************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.13)
[Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]
ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
[Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.13)
[Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]
ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
[Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.13)
[Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]
End If
'*******************Prem Ops Mod 3 **********************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.2)
[Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]
ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
[Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.2)
[Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]
ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
[Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.2)
[Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]
End If
'*************************************Prods******* ******************
If [Prods U/L Mod A] = 0 Then
[Prods U/L Manual A] = [Prods U/L Prem A]
Else
[Prods U/L Manual A] = ([Prods U/L Prem A] / [Prods U/L Mod A])
End If
If [Prods U/L Mod B] = 0 Then
[Prods U/L Manual B] = [Prods U/L Prem B]
Else
[Prods U/L Manual B] = ([Prods U/L Prem B] / [Prods U/L Mod B])
End If
If [Prods U/L Mod C] = 0 Then
[Prods U/L Manual C] = [Prods U/L Prem C]
Else
[Prods U/L Manual C] = ([Prods U/L Prem C] / [Prods U/L Mod C])
End If
'*******************Prods A*****************************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prods Manual XS A] = ([Prods U/L Manual A] * 0.1)
[Prods Prem A] = [Prods Manual XS A] * [Prods Mod A]
End If
'*******************Prods B*****************************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prods Manual XS B] = ([Prods U/L Manual B] * 0.15)
[Prods Prem B] = [Prods Manual XS B] * [Prods Mod B]
End If
'*******************Prods C*****************************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prods Manual XS C] = ([Prods U/L Manual C] * 0.22)
[Prods Prem C] = [Prods Manual XS C] * [Prods Mod C]
End If
'*************************************Auto******** *****************
If [AL U/L Mod] = 0 Then
[AL Manual] = [AL U/L Prem]
Else
[AL U/L Man] = ([AL U/L Prem] / [AL U/L Mod])
End If
'*******************Prods A*****************************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[AL Man XS] = ([AL U/L Man] * 0.1)
[AL Prem] = [AL Man XS] * [AL Mod]
End If
End Sub
I'm sure it's something simply but I can't work myself around it cuz I don't know much VBA....can someone please take a look?
big THANKS,
LB in GA