Trying to make a calendar to control the date..

[snicker]

I am using this code to make the calendar go to a specific date:

Private Sub Calendar1_Click()

Set rs = Me.Recordset.Clone
rs.FindFirst "[Date] = '" & Me![Calendar1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

I get an error every time. Any sugestions?

 

[ChrisO]

Try this and see how you go, but please get rid of a Field Name of Date it will cause you grief some day.

rs.FindFirst "[MyDate] = #" & Format(Me.Calendar1, "mmm d yyyy") & "#"
    
If Not rs.NoMatch Then
    Me.Bookmark = rs.Bookmark
End If

Hope that helps.

Regards
Chris

 

[snicker]

Chris,
This is what I have now (changed the [Date] to [MyDate]).

Private Sub Calendar1_Click()

Set rs = Me.Recordset.Clone
rs.FindFirst "[MyDate] = #" & Format(Me.Calendar1, "mmm d yyyy") & "#"

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub

Whenever I click on a date on the calander It says
"Run-time error '3022':
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

The date is the primary key on the field. I only want one record per date. I think the code is creating a new date (hence the duplicate value). Any suggestions from here?

 

[ChrisO]

I can see nothing in that code that would be trying to create a new record.

Looks like you are using A2K or A2K2 so I doubt if you will be able to convert your Calendar to A97.

When you get the error what line of code is highlighted?

 

[snicker]

Your right. Its not creating a new record. This is what is happening.

When the form opens it automatically opens to todays date.
When you click on a different date, the [MyDate] field goes to the selected date, but the data on the appointment page stays the same as todays appointments.

Its almost like the code is placing todays date into [MyDate] field without changing the record, (the db is set up for only one date to exist, and every time the db is opened it checks and creates dates for up to 2 months in advance) hence the duplicate record warning.

As for the code itself, I'm not getting any VB errors. its just a dup error. To stop the VB error I was getting earyler I had to change the format type (I live in canada). This is the code I have now:

Private Sub Calendar1_Click()

Set rs = Me.Recordset.Clone
rs.FindFirst "[MyDate] = #" & Format(Me.Calendar1, "dd mm yy") & "#"

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub

It is almost identical to the code you gave me just "mmm d yyyy" was changed to "dd mm yy".

I am using Access 2002.
PS. If it wasnt for guys like you and places like this forum, I would burn this project to cd... drink acid... and pee on it.

 

[SforSoftware]

Hi snicker,

This can't have to do with the code on Calendar1_Click, but must have to do with some other code in your forms class.

Is it possible to post the full code here (or attach a db with only the required form and tables)

 

[snicker]

would it be ok If I email the db to you?

 

[SforSoftware]

OK. see PM for the address

 

[SforSoftware]

I got the db and have send it back to you.

The problem was that you assigned a Control Source to your Calendar, so clicking your calendar sets the value of “MyDate”. I deleted the control source and it seems to work properly.

Greetz

 

[snicker]

ARRGGG!!!
Access does not apply some changes without a restarting the DB? Do you know why this is?


I tried this before and again when you told me the problem. I had to make the change, then close the database, then reopen the database to get the change to work.

thank you for the help

 

[snicker]

ok here is the final code I used.

Private Sub Calendar1_Click()

Set rs = Me.Recordset.Clone
rs.FindFirst "[MyDate] = #" & Me!Calendar1.Value & "#"

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub

And I made sure the Calendar was unbound

Thx to all that helped

 

[snicker]

The code I listed above worked great except if you clicked on a date the had a month as a day (day 1 - 12), I would confuse the search and return MM- DD- YYYY. Example if you clicked on 12-8-2003 it would return 8-12-2003. Here is the updated code that works perfectly. Thanks to all that helped me work this out.

Private Sub Calendar1_Click()
Set rs = Me.Recordset.Clone
rs.FindFirst "[MyDate] = #" & fCanDatum(Me!Calendar1.Value) & "#"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
End Sub
Private Function fCanDatum(d As Date) As Variant
If Not IsDate(d) Then Exit Function
fCanDatum = Month(d) & "/" & Day(d) & "/" & Year(d)
End Function