I
Ian Burton
Hi All,
I have a really urgent problem that i have spent two days staring at, with
no success.
I get an error that i can reproduce that i simply cannot understand.
I have a form: frmTickets, with about 8 subforms, and 6 of them point to
the same table, albeit with slightly different queries.
When I change the data in one of the subforms via delete and append queries
from several differnet places, I get the error message: "Record is
deleted", and then some of the subforms, but not the one i have been
changing display #Name?
The forms are unbound initially, and only get their recordsource and child
fields etc on form_load. (this speeds up the form opening by 50%)
Some of the code is below:
called from a button which reads the contact ref from another text box, and
then adds the data.
Private Sub cmdClientInsuranceGo_Click()
'0. if no change, then open up contacts to view the source details
'1. delete existing client insurance
'4. check whether the ref exists.
'5. if not ask whether user wants to search for it
'6. if user wants to search, then open the all contacts ready to add
'7. otherwise if ref does exist, then add it
On Error GoTo Err_handler
Me.Refresh
'1. check whether there is an existing source
Dim tempCc As Long
Dim response As Integer
tempCc = Nz(DLookup("[Cc]", "qryfrmTicketsSub
Tickets_ClientInsurance1"), 0)
If tempCc <> 0 Then
'2. if so, ask if they want to delete it
response = MsgBox("Click Yes if you want to replace the existing
Client Insurance Company", vbYesNoCancel)
If response = vbCancel Then Exit Sub
If response = vbNo Then
'open contacts, and put the ref search in.
DoCmd.OpenForm "frmContacts"
If Forms![frmContacts].RecordSource <> "qryfrmContacts" Then
On Error Resume Next
Forms![frmContacts].RecordSource = "qryfrmContacts"
On Error GoTo Err_handler
End If
Forms![frmContacts]![txtrefsearch] = Me![txtClientInsuranceRef]
Forms![frmContacts].Requery
Exit Sub
End If
End If
'1. delete existing source
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryfrmTicketsSub Tickets_ClientInsurance2"
DoCmd.SetWarnings True
Me.[txtSource] = ""
Forms![frmTickets]![frmTicketsSubContacts].Form.Requery
Forms![frmTickets]![frmTicketsSubClientInsurance].Form.Requery
'4. check whether the ref exists.
tempCc = Nz(DLookup("[Cc]", "qryfrmTicketsSub
Tickets_ClientInsurance3"), 0)
If tempCc = 0 Then
'5. if not ask whether user wants to search for it
response = MsgBox("There is no Insurance Company with this Ref. Do
you want to search?", vbYesNoCancel)
If response = vbCancel Then Exit Sub
If response = vbYes Then
'6. if user wants to search, then open the all contacts ready to add
DoCmd.OpenForm "frmContactsAll"
Forms![frmContactsAll]![cmdAdd].Enabled = True
Forms![frmContactsAll]![txtDestination] = "Client Insurance"
End If
Else
'7. otherwise if ref does exist, then add it
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryfrmTicketsSub Tickets_ClientInsurance4"
DoCmd.SetWarnings True
Forms![frmTickets]![frmTicketsSubContacts].Form.Requery
Forms![frmTickets]![frmTicketsSubClientInsurance].Form.Requery
End If
Me![txtClientInsuranceRef] = ""
Err_handler:
Select Case Err
Case 0
Exit Sub
Case Else
MsgBox " An Error has occurred. Please try later. The Error
Information is:" & Chr(13) & Chr(10) & "Error No: " & Err, , AppTitle
Exit Sub
End Select
End Sub
combo box with a list of insurance companies to select and add.
Private Sub cboClientInsurance_AfterUpdate()
On Error GoTo txtRelSearch_AfterUpdate_Error
'Me.Refresh
Me![frmTicketsSubClientInsurance].Form.Refresh
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryfrmTicketsClientInsuranceDelete"
DoEvents
DoEvents
DoCmd.OpenQuery "qryfrmTicketsClientInsuranceAddcombo"
DoCmd.SetWarnings False
Me![frmTicketsSubContacts].Form.Requery
Me![frmTicketsSubClientInsurance].Form.Requery
Me.Refresh
Exit Sub
txtRelSearch_AfterUpdate_Error:
Select Case Err
Case 0
Exit Sub
Case Else
MsgBox " An Error has occurred. Please try later. The Error
Information is:" & Chr(13) & Chr(10) & "Search on Relationship ; Error No: "
& Err
Exit Sub
End Select
End Sub
The subform SQL is below:
SELECT Tickets_Contacts.Tc, Contacts.Cc, Contacts.Cc AS Cc1,
Tickets_Contacts.Cc, Tickets_Contacts.Cr, Contacts.Ccomp, Contacts.Cref,
Contacts.Ctype, Contacts.Csecurity, Contacts.Cacctmngr, Contacts.Cnt,
Contacts.Cnf, Contacts.Cnc, Contacts.Cns, Tickets_Contacts.TCx, Contacts.Cn,
Contacts.Cjob, Contacts.Ctel1, Contacts.Ctelother, Contacts.Cmobile,
Contacts.Ca, Contacts.Cpc, Contacts.Cmail, Contacts.Clicenceno,
Contacts.Cdob, Contacts.Clicenceissue, Contacts.Tdrivinglic_expirtdt
FROM Contacts INNER JOIN Tickets_Contacts ON Contacts.Cc =
Tickets_Contacts.Cc
WHERE (((Tickets_Contacts.Cr)="Client Insurance"));
relationship is:
Tickets table PK is Tc
Contacts Table PK is Cc
Tickets_Contacts PK is TCc
Foreign Keys, Tc and Cc
cascade update on relationships 1 to many.
The main Form load calls the following sub, which loads the data, some code
removed for the sake of brevity.
Private Sub Form_Query_Refresh()
On Error Resume Next
Me.RecordSource = "qryfrmTickets"
Me![cboClientInsurance].RowSource = "qryfrmTicketsClientInsurance"
Me![cboClientInsuranceBroker].RowSource = "qryfrmTicketsClientInsurance"
Me.frmTicketsSubClientInsurance.LinkChildFields = "Tc"
Me.frmTicketsSubClientInsurance.LinkMasterFields = "Tc"
Me.frmTicketsSubClientInsuranceBroker.LinkChildFields = "Tc"
Me.frmTicketsSubClientInsuranceBroker.LinkMasterFields = "Tc"
End Sub
Anyway, if you run the first two subs in any order, there is an error
message as described.
I have tried importing all the tables into a new table as definition only,
and starting again, to see if it is the data, but this made no difference.
Any thoughts are much appreciated.
I have a really urgent problem that i have spent two days staring at, with
no success.
I get an error that i can reproduce that i simply cannot understand.
I have a form: frmTickets, with about 8 subforms, and 6 of them point to
the same table, albeit with slightly different queries.
When I change the data in one of the subforms via delete and append queries
from several differnet places, I get the error message: "Record is
deleted", and then some of the subforms, but not the one i have been
changing display #Name?
The forms are unbound initially, and only get their recordsource and child
fields etc on form_load. (this speeds up the form opening by 50%)
Some of the code is below:
called from a button which reads the contact ref from another text box, and
then adds the data.
Private Sub cmdClientInsuranceGo_Click()
'0. if no change, then open up contacts to view the source details
'1. delete existing client insurance
'4. check whether the ref exists.
'5. if not ask whether user wants to search for it
'6. if user wants to search, then open the all contacts ready to add
'7. otherwise if ref does exist, then add it
On Error GoTo Err_handler
Me.Refresh
'1. check whether there is an existing source
Dim tempCc As Long
Dim response As Integer
tempCc = Nz(DLookup("[Cc]", "qryfrmTicketsSub
Tickets_ClientInsurance1"), 0)
If tempCc <> 0 Then
'2. if so, ask if they want to delete it
response = MsgBox("Click Yes if you want to replace the existing
Client Insurance Company", vbYesNoCancel)
If response = vbCancel Then Exit Sub
If response = vbNo Then
'open contacts, and put the ref search in.
DoCmd.OpenForm "frmContacts"
If Forms![frmContacts].RecordSource <> "qryfrmContacts" Then
On Error Resume Next
Forms![frmContacts].RecordSource = "qryfrmContacts"
On Error GoTo Err_handler
End If
Forms![frmContacts]![txtrefsearch] = Me![txtClientInsuranceRef]
Forms![frmContacts].Requery
Exit Sub
End If
End If
'1. delete existing source
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryfrmTicketsSub Tickets_ClientInsurance2"
DoCmd.SetWarnings True
Me.[txtSource] = ""
Forms![frmTickets]![frmTicketsSubContacts].Form.Requery
Forms![frmTickets]![frmTicketsSubClientInsurance].Form.Requery
'4. check whether the ref exists.
tempCc = Nz(DLookup("[Cc]", "qryfrmTicketsSub
Tickets_ClientInsurance3"), 0)
If tempCc = 0 Then
'5. if not ask whether user wants to search for it
response = MsgBox("There is no Insurance Company with this Ref. Do
you want to search?", vbYesNoCancel)
If response = vbCancel Then Exit Sub
If response = vbYes Then
'6. if user wants to search, then open the all contacts ready to add
DoCmd.OpenForm "frmContactsAll"
Forms![frmContactsAll]![cmdAdd].Enabled = True
Forms![frmContactsAll]![txtDestination] = "Client Insurance"
End If
Else
'7. otherwise if ref does exist, then add it
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryfrmTicketsSub Tickets_ClientInsurance4"
DoCmd.SetWarnings True
Forms![frmTickets]![frmTicketsSubContacts].Form.Requery
Forms![frmTickets]![frmTicketsSubClientInsurance].Form.Requery
End If
Me![txtClientInsuranceRef] = ""
Err_handler:
Select Case Err
Case 0
Exit Sub
Case Else
MsgBox " An Error has occurred. Please try later. The Error
Information is:" & Chr(13) & Chr(10) & "Error No: " & Err, , AppTitle
Exit Sub
End Select
End Sub
combo box with a list of insurance companies to select and add.
Private Sub cboClientInsurance_AfterUpdate()
On Error GoTo txtRelSearch_AfterUpdate_Error
'Me.Refresh
Me![frmTicketsSubClientInsurance].Form.Refresh
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryfrmTicketsClientInsuranceDelete"
DoEvents
DoEvents
DoCmd.OpenQuery "qryfrmTicketsClientInsuranceAddcombo"
DoCmd.SetWarnings False
Me![frmTicketsSubContacts].Form.Requery
Me![frmTicketsSubClientInsurance].Form.Requery
Me.Refresh
Exit Sub
txtRelSearch_AfterUpdate_Error:
Select Case Err
Case 0
Exit Sub
Case Else
MsgBox " An Error has occurred. Please try later. The Error
Information is:" & Chr(13) & Chr(10) & "Search on Relationship ; Error No: "
& Err
Exit Sub
End Select
End Sub
The subform SQL is below:
SELECT Tickets_Contacts.Tc, Contacts.Cc, Contacts.Cc AS Cc1,
Tickets_Contacts.Cc, Tickets_Contacts.Cr, Contacts.Ccomp, Contacts.Cref,
Contacts.Ctype, Contacts.Csecurity, Contacts.Cacctmngr, Contacts.Cnt,
Contacts.Cnf, Contacts.Cnc, Contacts.Cns, Tickets_Contacts.TCx, Contacts.Cn,
Contacts.Cjob, Contacts.Ctel1, Contacts.Ctelother, Contacts.Cmobile,
Contacts.Ca, Contacts.Cpc, Contacts.Cmail, Contacts.Clicenceno,
Contacts.Cdob, Contacts.Clicenceissue, Contacts.Tdrivinglic_expirtdt
FROM Contacts INNER JOIN Tickets_Contacts ON Contacts.Cc =
Tickets_Contacts.Cc
WHERE (((Tickets_Contacts.Cr)="Client Insurance"));
relationship is:
Tickets table PK is Tc
Contacts Table PK is Cc
Tickets_Contacts PK is TCc
Foreign Keys, Tc and Cc
cascade update on relationships 1 to many.
The main Form load calls the following sub, which loads the data, some code
removed for the sake of brevity.
Private Sub Form_Query_Refresh()
On Error Resume Next
Me.RecordSource = "qryfrmTickets"
Me![cboClientInsurance].RowSource = "qryfrmTicketsClientInsurance"
Me![cboClientInsuranceBroker].RowSource = "qryfrmTicketsClientInsurance"
Me.frmTicketsSubClientInsurance.LinkChildFields = "Tc"
Me.frmTicketsSubClientInsurance.LinkMasterFields = "Tc"
Me.frmTicketsSubClientInsuranceBroker.LinkChildFields = "Tc"
Me.frmTicketsSubClientInsuranceBroker.LinkMasterFields = "Tc"
End Sub
Anyway, if you run the first two subs in any order, there is an error
message as described.
I have tried importing all the tables into a new table as definition only,
and starting again, to see if it is the data, but this made no difference.
Any thoughts are much appreciated.