Thanks for the reply. I finally have that part working but now I have another problem. The employee enters their employee ID in the form EmployeeIDInput. I need to take this ID and pull thje record from the Employee Table, which I have done with no problem. this table is has a one-to-many relationship with the schedule table. So one employee may have several days scheduled off. The form where I want to view this information is called PTO Request Date (which has fields from the employee table and the subform from the schedule table).
It tells me that the form PRO Request Date is not found. I suspect there is a step I have forgotten.
Here is what I have so far.
Dim strSQL As String
Dim strSQL_insert As String
Dim strRequest As String
Dim stLinkCriteria As String
Dim testString As String
Dim EMPID As String
EMPID = Me.EMP_ID1
strSQL = "SELECT * " _
& "FROM [EMPLOYEE TABLE] " _
& "WHERE (([EMPLOYEE TABLE].EMP_ID) = '" & EMPID & "') "
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rsData = dbCurrent.OpenRecordset(strSQL)
testString = rsData.Fields(1)
stLinkCriteria = "Forms![PTORequestForm]!EMP_ID = Forms![Employee ID Input]![EMP_ID1]"
DoCmd.OpenForm "PTO Request Form", , , stLinkCriteria, , ,acWindowNormal
Me.Refresh
rsData.Close
dbCurrent.Close
Set rsData = Nothing
Set dbCurrent = Nothing
Exit_SubmitButton_Click:
Exit Sub
Err_SubmitButton_Click:
MsgBox Err.Description
Resume Exit_SubmitButton_Click
End Sub
Hi where the user tries to introduce this date in a form? ;)
Or elsewhere?
If in a form this calculation need a VB to do the check and show the message...
In your field in Before Update property choose event procedure and type:
If dcount("[Mydate]", "Mytable","[Mydate]=Cvdate('"+Me![YourNewDate]+"')")>4 then
Msgbox "PLS STOP"
Cancel=true
End if
So you need to change some things:
Mydate is the field in the table where you do a search
Mytable is the place where you store your data
YourNewDate is the name of the control where you introduce your new date
:)
4
