T
Tony Williams
I have two tables 1.tblmonth which holds two fields txtmonth and txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform based
on tblmain, the two forms are also linked on the same fields.
The data entered into txtmonth could be "30/09/04" and the txtqtrlabel data
would be "September 2004" Both the controls are comboboxes with NotInList
events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new date?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
The code for txtqtrlabel is the same apart from the field references.
PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains
the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the
date to appear once. So there should be no duplicates in tblmonth but the
field txtqtrlabel can appear more than once in the tblmain.
Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list, hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform based
on tblmain, the two forms are also linked on the same fields.
The data entered into txtmonth could be "30/09/04" and the txtqtrlabel data
would be "September 2004" Both the controls are comboboxes with NotInList
events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new date?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
The code for txtqtrlabel is the same apart from the field references.
PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains
the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the
date to appear once. So there should be no duplicates in tblmonth but the
field txtqtrlabel can appear more than once in the tblmain.
Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list, hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams