Private Sub Command42_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
If Len(Me.PIP_Account) <> 8 Then
MsgBox "Please enter an account number to update", vbInformation, "No Account"
Else
If Me.Starts_On_PIP < Date Then
MsgBox "The start date you entered is prior to todays date. Please enter a date that is after todays date.", vbInformation, "End Date"
Me.Starts_On_PIP = ""
[Forms]![PIP_INFO_FORM]![Starts_On_PIP].SetFocus
Else
If me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),7) Or Me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),21) Then
MsgBox "Please make sure the date you PIP you enter starts on either the 7th or 21st of the month"
If Me.Amount_PIP = "" Or Me.PPM_Code_PIP = "" Or Me.Pay_Date_PIP = "" Or Me.PPM_Code_PIP = "" Or Me.Pay_Month_PIP = "" Or Me.Starts_On_PIP = "" Then
MsgBox "Please enter all information relating to this PIP Set-up"
Else
If Me.Ends_On_PIP < Date Then
MsgBox "The end date you entered is prior to todays date. Please enter a date that is after todays date.", vbInformation, "End Date"
Me.Ends_On_PIP = ""
[Forms]![PIP_INFO_FORM]![Ends_On_PIP].SetFocus
Else
If Me.Ends_On_PIP < Me.Starts_On_PIP Then
MsgBox "You end date is less than your start date", vbInformation, "Dates"
Me.Ends_On_PIP = ""
[Forms]![PIP_INFO_FORM]![Ends_On_PIP].SetFocus
Else
Me.User_Name_PIP = fOSUserName
Me.Date_Add_PIP = Now()
sqlinsert = "insert into Main_PIP_TBL (Account, Amount, PPM_Code, Pay_Date, Pay_Month, Starts_on, Ends_on, Funding, Add_Date, Associate_Name) values('" & Me.PIP_Account & "', '" & Me.Amount_PIP & "','" & Me.PPM_Code_PIP & "','" & Me.Pay_Date_PIP & "','" & Me.Pay_Month_PIP & "','" & Me.Starts_On_PIP & "','" & Me.Ends_On_PIP & "','" & Me.Funding_PIP & "','" & Me.Date_Add_PIP & "','" & Me.User_Name_PIP & "');"
db.Execute (sqlinsert)
MsgBox "updated", vbInformation, "Update"
Refresh
Me.Amount_PIP = ""
Me.PPM_Code_PIP = ""
Me.Pay_Date_PIP = ""
Me.Pay_Month_PIP = ""
Me.Starts_On_PIP = ""
Me.Ends_On_PIP = ""
Me.Funding_PIP = ""
Me.Date_Add_PIP = ""
Me.User_Name_PIP = ""
End If
End If
End If
End If
End If
End If
SQL = " select * from Main_PIP_TBL where account = '" & Me.PIP_Account & "';"
Me.Main_PIP_TBL_subform.Form.RecordSource = SQL
End Sub