Calendar Function

[ChrisPanet]

Hi all

I am currently writing a "Scheduling" database for all of our engineers to book them in on jobs etc. I was just wondering if anybody knew a way of doing a calendar form or function in Access which will display all jobs booked, completed, cancelled etc.

I know you can export to the Outlook calendar through VB but i wanted to try and keep everything within Access rather than having to reply upon 2 programs.

Any help would be much appreciated,

Thanks
Chris

 

[itmasterw]

If your info is in a table in a database, then you can query the table for the info you want.
To get the to select this through a calendar you can do the following.

First you will want to create a form with two ocx calendars, this in your componet tool list.

The code to get the dates the user selects is as follows:

Private Sub ocxCalendar1_AfterUpdate()
txtFirstDate.SetFocus
txtFirstDate.Text = Format(ocxCalendar1.Object.Value, "mm/dd/yy")

End Sub

Private Sub ocxCalendar2_AfterUpdate()
txtSecondDate.SetFocus
txtSecondDate.Text = Format(ocxCalendar2.Object.Value, "mm/dd/yy")

End Sub


Then you will create a parameter query like the one below :

SELECT tblFinal.Date1 AS [Date]
FROM tblFinal
WHERE (((tblFinal.Date1) Between [Forms]![Form1]![txtFirstDate] And [Forms]![Form1]![txtSecondDate]))
ORDER BY tblFinal.Date1;

unfortunantly I could not get screen captures in here for you
Hope this helps

 

[ChrisPanet]

Hi thanks for replying, im not too up on the calendar controls in Access so im strugling a bit with your answer

Would it be possible for you to email me the screen dumps?

It would be much appreciated!

Thanks
Chris

 

[itmasterw]

Sure Chris If you give me your email address I can do that. However, But all you do for the calendar is get it out of the tool controll item and it is in the list as Calendar control 8.0. Once you have it you just draw it on your form. You will also have to go to tools to reference the Calendar 8.0 reference or this will not work. You can look this up in the file ---> Add an ActiveX control (OLE custom control) to a form or report. from there yuo use the code that I have above for the calendar code.

 

[ChrisPanet]

Hi there, yeah i got as far as putting the calendars on the forms but im not sure where i should put the codes that you put and what fields are required on the form with the calendars on.

Chris

 

[itmasterw]

In deign mode If you right click on the calendar and go up to Build event and selct code; you will get to the code section. Then go to the area that has the ocxCalendar1_AfterUpdate() section and put in the following.

Private Sub ocxCalendar1_AfterUpdate()
txtFirstDate.SetFocus
txtFirstDate.Text = Format(ocxCalendar1.Object.Value, "mm/dd/yy")
End Sub

You might have named your calendar something different, but this is the basic idea.

The txtFirstDate.Text is a text box that I had put on my form so that my query can access the date that user selects.

also If you want the square on the calendar to always be on the current date whae the form is openeds add the following in the form load section as follows:

Private Sub Form_Load()
ocxCalendar1.Object.Value = Date
End Sub

And again in thec query you put this under the date field, and in the section criteria section:
Between [Forms]![Form1]![txtFirstDate] And [Forms]![Form1]![txtSecondDate]

 

[briareus]

itmasterw:

I have an Access form where I use an active X Calendar Control to get a start date and and end date from a user. The form works great, until I add the query criteria:

Between [Forms]![FormSearch]![StartDate] And [Forms]![FormSearch]![EndDate]

Then the entire form comes up blank! The query works great on its own. Why does the form go blank? Is it because it needs to be converted to a String before going to a query? I have done dates before and I have done calendars from existing dates before, but never unbound calendars where I want to pass the date to a master query. There is something about the query criteria that is not working. Any help? Here is my VBA:

Option Compare Database
Option Explicit
Dim Originator As ComboBox

Private Sub Calendar_Updated(Code As Integer)

End Sub

Private Sub EndDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = EndDate
Calendar.Visible = True
Calendar.SetFocus
If Not IsNull(Originator) Then
Calendar.Value = Originator.Value
Else
Calendar.Value = Date
End If
End Sub

Private Sub RunQuery_Click()
On Error GoTo Err_RunQuery_Click

Dim stDocName As String

stDocName = "QryAsOfCrewDescr"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_RunQuery_Click:
Exit Sub

Err_RunQuery_Click:
MsgBox Err.Description
Resume Exit_RunQuery_Click

End Sub
Private Sub Calendar_Click()
Originator.Value = Calendar.Value
Originator.SetFocus
Calendar.Visible = False
Set Originator = Nothing
End Sub

Private Sub StartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = StartDate
Calendar.Visible = True
Calendar.SetFocus
If Not IsNull(Originator) Then
Calendar.Value = Originator.Value
Else
Calendar.Value = Date
End If
End Sub

 

[sgfromny]

Be sure to put "#" on either side of your dates.

See below:

Between "#" & [Forms]![FormSearch]![StartDate] & "#" And "#" & [Forms]![FormSearch]![EndDate] & "#"

 

[briareus]

Thanks. I actually found out why. When creating the form originally with the wizard, it set the form record source to the underlying query (originally I wanted to make use of most of the existing fields). This wont work with an unbound control (the Active X calendar). Once I removed the query link and used all unbound controls, I was good to go.