jackson_jl
03-14-2005, 09:40 AM
What I have is a form that has five combo boxes, Customer, Resource Type, Patch, StartDate, and EndDate. On the form I select a Customer, a Resource Type, a Patch, a StartDate and an EndDate. I then have a SaveRecord Command Button. The On Click function is as follows:
Private Sub cmdSaveRecord_Click()
Dim strWhere As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim cmdSave As String
Dim Patch As String
Dim ResourceType As String
stDocName = "frmScheduleResource"
stLinkCriteria = "[cmdSaveRecord]=" & "" & cmdSaveRecord & ""
Resourcetype = "qryScheduledResourceType.Resource Type"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If IsNull(Me.Patch) Then
MsgBox "You must specify a Patch."
Exit Sub
Else
strWhere = "qryScheduledResourceType.Patch = " & Me.Patch
End If
If IsNull(Me.ResourceType) Then
MsgBox "You must specify a Resource Type."
Exit Sub
Else
strWhere = "qryScheduledResourceType.ResourceType = " & Me.ResourceType
End If
If IsNull(Me.StartDate) _
Or (Not IsDate(Me.StartDate)) Then
MsgBox "You must enter a start date."
Exit Sub
Else
strWhere = strWhere & _
" AND EndDate >= #" & Me.StartDate & "#"
End If
If IsNull(Me.EndDate) _
Or (Not IsDate(Me.EndDate)) Then
MsgBox "You must enter an end date."
Exit Sub
Else
If Me.StartDate > Me.EndDate Then
MsgBox "Start date cannot be greater than end date."
Exit Sub
End If
strWhere = strWhere & _
" AND EndDate <= #" & Me.EndDate & "#"
End If
If the new Patch and Resource Type have a entry that matches dates between an old StartDate and EndDate, Then
MsgBox ?Resources Already scheduled. Pick another resource or dates?
Exit Sub
DoCmd.OpenForm "frmScheduledResource", WhereCondition:=strWhere
End Sub
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdSaveRecord_Click:
Exit Sub
Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
Text in Red = Code that I don't know how to express.
What I want the function to do is On Click:
Open a query that has the five fields listed above.
Compare the Patch, Resource Type and StartDate, EndDate fields to see if:
a: if Patch = Null, Then an error message box appears.
b: if Resource Type = Null, Then an error message box appears.
c: if StartDate = Null, Then an error message box appears.
d: if EndDate = Null, Then an error message box appears.
e: if StartDate > EndDate, Then an error message box appears.
f: if Patch and Resource Type > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:
CustomerID Patch ResourceTypeID StartDate EndDate
Customer A (1) NO TEST TYPE 1 01-Mar-05 03-Mar-05
Customer B (2) NO TEST TYPE 2 02-Mar-05 04-Mar-05
Customer C (3) NO TEST TYPE 3 03-Mar-05 05-Mar-05
Customer D (4) NO TEST TYPE 4 04-Mar-05 06-Mar-05
In the table above, if I input a new record as follows:
Customer E (1) NO TEST TYPE 1 2-Mar-05 3-Mar-05
I should get an error message, because Patch: (1) NO TEST and ResourceTypeID, TYPE 1 is already being used by Customer A from
1 to 3 March 2005. Customer E should not be able to use this Patch and Resource Type until 4 March, 2005. So I need code that searches dates between the start and end dates)
f: if Patch and Resource Type = 0, Then perform save record function.
Close the query
When I run the code above, I am getting a Compile Error: method or data member not found.
If you or someone knows what I should do, Please help.
Private Sub cmdSaveRecord_Click()
Dim strWhere As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim cmdSave As String
Dim Patch As String
Dim ResourceType As String
stDocName = "frmScheduleResource"
stLinkCriteria = "[cmdSaveRecord]=" & "" & cmdSaveRecord & ""
Resourcetype = "qryScheduledResourceType.Resource Type"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If IsNull(Me.Patch) Then
MsgBox "You must specify a Patch."
Exit Sub
Else
strWhere = "qryScheduledResourceType.Patch = " & Me.Patch
End If
If IsNull(Me.ResourceType) Then
MsgBox "You must specify a Resource Type."
Exit Sub
Else
strWhere = "qryScheduledResourceType.ResourceType = " & Me.ResourceType
End If
If IsNull(Me.StartDate) _
Or (Not IsDate(Me.StartDate)) Then
MsgBox "You must enter a start date."
Exit Sub
Else
strWhere = strWhere & _
" AND EndDate >= #" & Me.StartDate & "#"
End If
If IsNull(Me.EndDate) _
Or (Not IsDate(Me.EndDate)) Then
MsgBox "You must enter an end date."
Exit Sub
Else
If Me.StartDate > Me.EndDate Then
MsgBox "Start date cannot be greater than end date."
Exit Sub
End If
strWhere = strWhere & _
" AND EndDate <= #" & Me.EndDate & "#"
End If
If the new Patch and Resource Type have a entry that matches dates between an old StartDate and EndDate, Then
MsgBox ?Resources Already scheduled. Pick another resource or dates?
Exit Sub
DoCmd.OpenForm "frmScheduledResource", WhereCondition:=strWhere
End Sub
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdSaveRecord_Click:
Exit Sub
Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
Text in Red = Code that I don't know how to express.
What I want the function to do is On Click:
Open a query that has the five fields listed above.
Compare the Patch, Resource Type and StartDate, EndDate fields to see if:
a: if Patch = Null, Then an error message box appears.
b: if Resource Type = Null, Then an error message box appears.
c: if StartDate = Null, Then an error message box appears.
d: if EndDate = Null, Then an error message box appears.
e: if StartDate > EndDate, Then an error message box appears.
f: if Patch and Resource Type > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:
CustomerID Patch ResourceTypeID StartDate EndDate
Customer A (1) NO TEST TYPE 1 01-Mar-05 03-Mar-05
Customer B (2) NO TEST TYPE 2 02-Mar-05 04-Mar-05
Customer C (3) NO TEST TYPE 3 03-Mar-05 05-Mar-05
Customer D (4) NO TEST TYPE 4 04-Mar-05 06-Mar-05
In the table above, if I input a new record as follows:
Customer E (1) NO TEST TYPE 1 2-Mar-05 3-Mar-05
I should get an error message, because Patch: (1) NO TEST and ResourceTypeID, TYPE 1 is already being used by Customer A from
1 to 3 March 2005. Customer E should not be able to use this Patch and Resource Type until 4 March, 2005. So I need code that searches dates between the start and end dates)
f: if Patch and Resource Type = 0, Then perform save record function.
Close the query
When I run the code above, I am getting a Compile Error: method or data member not found.
If you or someone knows what I should do, Please help.