Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 8th, 2005, 01:12 PM
Registered User
Join Date: Mar 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default date comparison function for a form

Please Help,

What I have is a form that has four combo boxes, Customer, Resourcetype, StartDate, and EndDate. On the form I select a Customer, a Resource Type, a StartDate and an EndDate. I then have a SaveRecord Command Button. The On Click function is as follows:

Private Sub cmdSave_Click()
  Dim strWhere As String
  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim cmdSave As String
  Dim Resourcetype As String
  stDocName = "frmScheduleReosurce"
  stLinkCriteria = "[cmdSave]=" & "" & cmdSave & ""
  Resourcetype = "qryScheduledResourcetype.Resourcetype"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  If IsNull(Me.Resourcetype) Then
    MsgBox "You must specify a Resource Type."
    Exit Sub
    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
    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
    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
  DoCmd.OpenForm "frmScheduledReosurce", WhereCondition:=strWhere

End Sub

What I want the function to do is On Click:
1. Open a query that has the four fields listed above.
2. Compare the Resourcetype and StartDate, EndDate fields to see if:
a: if Resourcetype = Null, Then an error message box appears.
b: if StartDate = Null, Then an error message box appears.
c: if EndDate = Null, Then an error message box appears.
d: if StartDate > EndDate, Then an error message box appears.
e: if Resourcetype > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:

Customer Resourcetype StartDate EndDate
Jim Item 1 1-Mar-05 4-Mar-05

In the table above, if I input a new record as follows:

Mike Item 1 2-Mar-05 3-Mar-05

I should get an error message, because Item 1 is already being used by Jim from
1 to 4 March 2005. Mike should not be able to use this Resourcetype until 5 March, 2005. So I need code that searches dates between the start and end dates)

f: if Resourcetype = 0, Then perform save record function.

3. 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.

James Jackson

Old March 8th, 2005, 04:32 PM
Friend of Wrox
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post

Where is the data for each combo box on your form coming from?

Old March 9th, 2005, 01:28 PM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

That is a lot of issues:

I think you might mean you have four text boxes, or two combo boxes and two text boxes, or two combo boxes and two date pickers. In any event, you shouldn't have combo boxes for dates, unless they are specific dates that are predetermined and must be used.

The problem with passing combo box values is that you are passing the PK, and not the meaningful data. If you have a Customer whose name is Smith, John, and they have a PK of 1, then when you select Smith, John, you are passing 1. So you have to account for that in your SQL statement.

Also, why are you checking for Null values on your form AFTER you open the frmScheduleResource? You should check for Null values before you open that form, it seems.

If this is working otherwise and you are getting this error message, perhaps you need to pass your PKs like this:

    stLinkCriteria = "[cmdSave]=" & cmdSave

This allows you to pass integers, which don't need the single quotes.

I also noted in pasting your code that you write it like this:

    stLinkCriteria = "[cmdSave]=" & "" & cmdSave & ""

It should be written like this:

    stLinkCriteria = "[cmdSave]= " & "'" & cmdSave & "'"

Note the single quotes withtin the double double quotes.

If you get past your errors, we can discuss fees for further consultation :D

(Okay, that was a joke, but there are a lot of issues here that will require many more posts to resolve.)

Hope this gets you started.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Comparison jroxit Classic ASP Databases 5 October 5th, 2007 05:39 PM
Access Form problem, date comparison jackson_jl VB Databases Basics 0 March 9th, 2005 02:13 PM
Date comparison rajuru Beginning PHP 2 February 19th, 2005 10:33 AM
Date comparison lily611 General .NET 5 January 4th, 2005 07:08 AM
Date Comparison cmiller PHP How-To 3 June 19th, 2003 02:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.