My Access 2007 database is used for creating a 5-year financial plan each year for planned projects. An administrator runs a routine each year to carry forward any projects from the previous 5-year plan that had dollars planned for the upcoming 5-year period.
It is possible that in last year's plan, there were no dollars projected beyond the first planning year for a project and therefore, that project does not carry over. However, in some cases, the project was not completed as planned during the prior year and so it also needs to be carried forward.
I created a button to allow the user to copy the basic project info from the previous year to the current year. If it was already carried over, a message asks if they want to overwrite the data.
In any case, it is possible that with the new planning year, values in the various drop-down lists have changed. (Please note that since historical data cannot be changed, it's okay that values in those fields do not match a value in the drop-down list.) However, if the user copies that record over, I want to check that the values in the combo box fields are valid values. If not, I want to display a message and force the user to update the field to a valid value from the drop-down list and save the record.
How can this be done? Below is the relevent code I have after copying the record into the table and displaying the new record as the current record. I plan to have similar code for each combo box control, but I don't know what to put in the commented line.
It is possible that in last year's plan, there were no dollars projected beyond the first planning year for a project and therefore, that project does not carry over. However, in some cases, the project was not completed as planned during the prior year and so it also needs to be carried forward.
I created a button to allow the user to copy the basic project info from the previous year to the current year. If it was already carried over, a message asks if they want to overwrite the data.
In any case, it is possible that with the new planning year, values in the various drop-down lists have changed. (Please note that since historical data cannot be changed, it's okay that values in those fields do not match a value in the drop-down list.) However, if the user copies that record over, I want to check that the values in the combo box fields are valid values. If not, I want to display a message and force the user to update the field to a valid value from the drop-down list and save the record.
How can this be done? Below is the relevent code I have after copying the record into the table and displaying the new record as the current record. I plan to have similar code for each combo box control, but I don't know what to put in the commented line.
Code:
Me.Base_Year = TempVars!CurrentBaseYear
Me.RecordSource = "SELECT tbl_Project.* FROM tbl_Project " & _
"WHERE tbl_Project.[Base Year]=" & TempVars!CurrentBaseYear & " ORDER BY tbl_Project.[Project Name];"
Me.Base_Year.ForeColor = RGB(0, 0, 0) ' Black
Me.btn_CopyProject.Visible = False
Call SetProjectFormPermissions(TempVars!CurrentBaseYear)
Set rs = Me.RecordsetClone
With rs
.FindFirst "ProjectID=" & Me.ProjectID
If .NoMatch Then
MsgBox "The new record could not be found. Please contact the database administrator.", vbCritical, "Critical Error"
GoTo Exit_CopyProject_Click
Else
Me.Bookmark = .Bookmark
If DCount("*", "tbl_BusinessGroup", "[Business Group]='" & Me.Business_Group & "'") = 0 Then
''''''''''''''''' What do I put here???
End If
End If
.Close
End With
