G'day all,
I have a form consisting of 3 tab forms (frmCompany which is the main form, frmContact, and frmSubmissions) based on tblCompany, tblContact and tblSubmissions.
Major fields are indexed as follows:
tblCompany tblContacts
tblSubmissions
CompanyID (no dupes) CompanyID (dupes ok) CompanyID (dupes ok)
ContactID (no dupes) ContactID (dupes ok)
SubID (no dupes)
cmdNew in frmSubmissions creates a new record for submissions. The following is the code that performs this operation:
Private Sub cmdNew_Click()
'If there are no subs in tblSubmission the first submission number will be given the value of 1. CompanyID is copied from frmCompany and ContactID is copied from frmContacts.
If IsNull(DLookup("[SubID]", "tblSubmissions")) Then 'I have also
tried If DCount("[SubID]", "tblSubmissions")=0 Then with the same results
Forms!frmCompany!frmSubmissions!CompanyID = Forms!frmCompany!CompanyID Forms!frmCompany!frmSubmissions!ContactID = Forms!frmCompany!frmContacts!ContactID
Me!SubID = 1
Me!SubDate.SetFocus
Else
'If there are already submissions in tblSubmissions the next available number will be allocated.
DoCmd.GoToRecord , , acNewRec
Forms!frmCompany!frmSubmissions!CompanyID = Forms!frmCompany!CompanyID Forms!frmCompany!frmSubmissions!ContactID = Forms!frmCompany!frmContacts!ContactID
Me!SubID = DMax("SubID", "tblSubmissions") + 1 Me!SubDate.SetFocus
End If
End Sub
When this code runs it allocates all the values as required but, when I try to exit the form I get an error that states I am creating duplications and the data will not be saved.
I can't see what I am doing wrong. Any ideas?
thanks
Dave
I have a form consisting of 3 tab forms (frmCompany which is the main form, frmContact, and frmSubmissions) based on tblCompany, tblContact and tblSubmissions.
Major fields are indexed as follows:
tblCompany tblContacts
tblSubmissions
CompanyID (no dupes) CompanyID (dupes ok) CompanyID (dupes ok)
ContactID (no dupes) ContactID (dupes ok)
SubID (no dupes)
cmdNew in frmSubmissions creates a new record for submissions. The following is the code that performs this operation:
Private Sub cmdNew_Click()
'If there are no subs in tblSubmission the first submission number will be given the value of 1. CompanyID is copied from frmCompany and ContactID is copied from frmContacts.
If IsNull(DLookup("[SubID]", "tblSubmissions")) Then 'I have also
tried If DCount("[SubID]", "tblSubmissions")=0 Then with the same results
Forms!frmCompany!frmSubmissions!CompanyID = Forms!frmCompany!CompanyID Forms!frmCompany!frmSubmissions!ContactID = Forms!frmCompany!frmContacts!ContactID
Me!SubID = 1
Me!SubDate.SetFocus
Else
'If there are already submissions in tblSubmissions the next available number will be allocated.
DoCmd.GoToRecord , , acNewRec
Forms!frmCompany!frmSubmissions!CompanyID = Forms!frmCompany!CompanyID Forms!frmCompany!frmSubmissions!ContactID = Forms!frmCompany!frmContacts!ContactID
Me!SubID = DMax("SubID", "tblSubmissions") + 1 Me!SubDate.SetFocus
End If
End Sub
When this code runs it allocates all the values as required but, when I try to exit the form I get an error that states I am creating duplications and the data will not be saved.
I can't see what I am doing wrong. Any ideas?
thanks
Dave