Tab controls - Access 2002 (1 Viewer)

[dcobau]

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