Lebans Calendar

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I've been using the Lebans calendar on a few forms and it works great.

I have a new form where I am using the calendar to get starting dates
and ending dates for a query by form. After the query is run I'd like
the user to respond that the data is what they intended and then it
will be emailed.

I find that if the dates are the desired ones it works fine, however,
if after running the query and responding (either to a yes/no msgbox or
a checkbox) the calendar gives an error saying some code has to be on
the onload event of the form.

The code is in the on load event but I think the form has to be
re-intitialized in this event somehow.

Any Ideas?

Thanks in advance
 
I have to get in this from Google Groups right now so I can't attach a
file. I'll try again later tonight through a newsgroup program.
 
I'm not asking you to attached a file. In fact, I would caution you against
that.

Simply copy-and-paste the code into your post.
 
Here's the code:

The code is in several places. It also needs some cleaning up.

First the month calendar is set up as per instructions. Then the on
click code the query command button is:

Private Sub cmdSearch_Click()


'Define the variables used to build the SQL

Dim stroutfallnumber As String 'to hold the value for the outfall
number
Dim strcompliancesample As String 'to hold the value for compliance
sample
Dim dcollectiondate As Date 'to hold the value for collection date
Dim denddate As Date




Dim strSQLSelectStatement As String
Dim strSQLClause1 As String 'for combo box 1 outfall number
Dim strSQLClause5 As String 'for combo box 5 compliance sample
Dim strSQLClause8 As String 'for text box EndDate

Dim strSQLClause6 As String 'for the text box collection date


Dim strOrderByClause As String 'the sort order for the query
Dim strFinalSQLStatement As String 'the final SQL string to return
results with

fFirstSQLClause = True 'tells the VB function to use WHERE or AND
before each SQL search criteria

'Combo box 1 criteria for outfall number
stroutfallnumber = Nz(Me.cmbOutfallNameNumber.Column(1,
Me.cmbOutfallNameNumber.ListIndex), "")
If stroutfallnumber <> "" Then 'then we have a valid customer
criteria
'create the search criteria for the customer id entry
strSQLClause1 = "[qry Analyzed Results].[Outfall Number] = """
+ CStr(stroutfallnumber) + """"
'append WHERE or AND as necessary to the SQL clause
strSQLClause1 = strFixedSQLClause(strSQLClause1)
Else
'no restriction here-just make it equal to ""
strSQLClause1 = ""
End If



'strSQLClause5 = "[qry Analyzed Results].[Compliance Sample])=
Yes"
strSQLClause5 = " WHERE [qry Analyzed Results].[compliance
sample] = yes"



'Text box 6 criteria for collection date

' test sql Results.[Collection Date]) Between dcollectiondate And
[forms]![Export Form]![end]

dcollectiondate = Nz(Me.CollectionDate, #1/1/1901#)
Rem If dcollectiondate <> #1/1/1901# Then 'then we have a valid
product criteria
'create the search criteria for the product id entry
strSQLClause6 = " AND [qry Analyzed Results].[Collection Date]
=" + "#" + CStr(dcollectiondate) + "#"
Click to expand...
'strSQLClause6 = "test"
'append WHERE or AND as necessary to the SQL clause
'strSQLClause6 = strFixedSQLClause(strSQLClause6)
Rem Else
'no restriction here-just make it equal to ""
Rem strSQLClause6 = ""
Rem End If



'Text box 8 criteria for collection date

' test sql Results.[Collection Date]) Between dcollectiondate And
[forms]![Export Form]![end]

denddate = Nz(Me.txtEndDate, #1/1/1901#)
Rem If dcollectiondate <> #1/1/1901# Then 'then we have a valid
product criteria
'create the search criteria for the product id entry
strSQLClause8 = " AND [qry Analyzed Results].[Collection Date]
<=" + "#" + CStr(denddate) + "#"
'strSQLClause6 = "test"
'append WHERE or AND as necessary to the SQL clause
'strSQLClause6 = strFixedSQLClause(strSQLClause6)
Rem Else
'no restriction here-just make it equal to ""
Rem strSQLClause6 = ""
Rem End If







'set up the sort order clause
strOrderByClause = "ORDER BY [qry Analyzed Results].[Collection
Date]"
'strOrderByClause = ""
If fFirstSQLClause Then
strOrderByClause = ""
End If


'add it all together
strFinalSQLStatement = strDefaultSQLString + " " + strSQLClause1 +
" " + strSQLClause5 + " " + strSQLClause6 + " " + strSQLClause8 + " " +
strOrderByClause
'strFinalSQLStatement = strDefaultSQLString + " " + strSQLClause5 +
" " + strSQLClause6

strqrySQL = strFinalSQLStatement


'set the new recordsource
Rem old Me.Child0.Form.RecordSource = strFinalSQLStatement

'new test

Me.Child0.Form.RecordSource = strFinalSQLStatement



End Sub
Private Function strFixedSQLClause(strSQLClause As String) As String

If fFirstSQLClause = True Then
fFirstSQLClause = False
strFixedSQLClause = "WHERE " + strSQLClause
Else
strFixedSQLClause = "AND " + strSQLClause
End If

End Function



Then there is the checkbox to indicate that the dataset is the dataset
that you desire to send. If checked it makes another command button to
send email visible. The after update code for this is:

Private Sub CheckBox_AfterUpdate()
If CheckBox.Value = -1 Then
Command40.Visible = True
Label44.Visible = True
Else
Command40.Visible = False
Label44.Visible = False
End
End If

End Sub

Finally, the command button for the send email button is (note, right
now it opens the dataset as an Excel file, this will be changed -
recycling some code):

Private Sub Command40_Click()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "By Selecting yes I certify that..." ' Define message.
Style = vbYesNo ' Define buttons.
Title = "Certification" ' Define title.

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then ' User chose No.
End ' Perform some action.
Else ' User chose Yes.
'MyString = "Yes" ' Perform some action.

End If

Call cmdSearch_Click

If strqrySQL = "" Then

strqrySQL = "SELECT [qry Analyzed Results].[Outfall Number], [qry
Analyzed Results].[Outfall Name], [qry Analyzed Results].[Collection
Date], [qry Analyzed Results].Analyte, [qry Analyzed Results].Result,
[qry Analyzed Results].[Result Number], [qry analyzed results].[units],
[qry Analyzed Results].[Compliance Sample], [qry Analyzed
Results].[Sample Type], [qry Analyzed Results].Sampler FROM [qry
Analyzed Results]"

ExportToExcel (strqrySQL)

Else

DoCmd.Requery "Child0"

ExportToExcel (strqrySQL)

End If

End Sub

If the query is run, and the checkbox is checked, you can use the
calendar control again to reset the dates. On the other hand if you
uncheck the checkbox and then try to use the calendar you get an error.

Also, the is a yes/no msgbox in the "send email" command button.
Yes means the dataset is correct, while No indicates, that no this is
not the dataset that I want to send. It stops the process of send the
mail and takes you back to the form. If no is selected you can't use
the calendar control to set other dates.

Thanks
 
I don't see any calls to ShowMonthCalendar in what you've posted below.
Since presumably it's the code that shows the calendar that's complaining
when the checkbox is checked, that's what I need to see.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Tom said:
Here's the code:

The code is in several places. It also needs some cleaning up.

First the month calendar is set up as per instructions. Then the on
click code the query command button is:

Private Sub cmdSearch_Click()


'Define the variables used to build the SQL

Dim stroutfallnumber As String 'to hold the value for the outfall
number
Dim strcompliancesample As String 'to hold the value for compliance
sample
Dim dcollectiondate As Date 'to hold the value for collection date
Dim denddate As Date




Dim strSQLSelectStatement As String
Dim strSQLClause1 As String 'for combo box 1 outfall number
Dim strSQLClause5 As String 'for combo box 5 compliance sample
Dim strSQLClause8 As String 'for text box EndDate

Dim strSQLClause6 As String 'for the text box collection date


Dim strOrderByClause As String 'the sort order for the query
Dim strFinalSQLStatement As String 'the final SQL string to return
results with

fFirstSQLClause = True 'tells the VB function to use WHERE or AND
before each SQL search criteria

'Combo box 1 criteria for outfall number
stroutfallnumber = Nz(Me.cmbOutfallNameNumber.Column(1,
Me.cmbOutfallNameNumber.ListIndex), "")
If stroutfallnumber <> "" Then 'then we have a valid customer
criteria
'create the search criteria for the customer id entry
strSQLClause1 = "[qry Analyzed Results].[Outfall Number] = """
+ CStr(stroutfallnumber) + """"
'append WHERE or AND as necessary to the SQL clause
strSQLClause1 = strFixedSQLClause(strSQLClause1)
Else
'no restriction here-just make it equal to ""
strSQLClause1 = ""
End If



'strSQLClause5 = "[qry Analyzed Results].[Compliance Sample])=
Yes"
strSQLClause5 = " WHERE [qry Analyzed Results].[compliance
sample] = yes"



'Text box 6 criteria for collection date

' test sql Results.[Collection Date]) Between dcollectiondate And
[forms]![Export Form]![end]

dcollectiondate = Nz(Me.CollectionDate, #1/1/1901#)
Rem If dcollectiondate <> #1/1/1901# Then 'then we have a valid
product criteria
'create the search criteria for the product id entry
strSQLClause6 = " AND [qry Analyzed Results].[Collection Date]
=" + "#" + CStr(dcollectiondate) + "#"
Click to expand...
'strSQLClause6 = "test"
'append WHERE or AND as necessary to the SQL clause
'strSQLClause6 = strFixedSQLClause(strSQLClause6)
Rem Else
'no restriction here-just make it equal to ""
Rem strSQLClause6 = ""
Rem End If



'Text box 8 criteria for collection date

' test sql Results.[Collection Date]) Between dcollectiondate And
[forms]![Export Form]![end]

denddate = Nz(Me.txtEndDate, #1/1/1901#)
Rem If dcollectiondate <> #1/1/1901# Then 'then we have a valid
product criteria
'create the search criteria for the product id entry
strSQLClause8 = " AND [qry Analyzed Results].[Collection Date]
<=" + "#" + CStr(denddate) + "#"
'strSQLClause6 = "test"
'append WHERE or AND as necessary to the SQL clause
'strSQLClause6 = strFixedSQLClause(strSQLClause6)
Rem Else
'no restriction here-just make it equal to ""
Rem strSQLClause6 = ""
Rem End If







'set up the sort order clause
strOrderByClause = "ORDER BY [qry Analyzed Results].[Collection
Date]"
'strOrderByClause = ""
If fFirstSQLClause Then
strOrderByClause = ""
End If


'add it all together
strFinalSQLStatement = strDefaultSQLString + " " + strSQLClause1 +
" " + strSQLClause5 + " " + strSQLClause6 + " " + strSQLClause8 + " " +
strOrderByClause
'strFinalSQLStatement = strDefaultSQLString + " " + strSQLClause5 +
" " + strSQLClause6

strqrySQL = strFinalSQLStatement


'set the new recordsource
Rem old Me.Child0.Form.RecordSource = strFinalSQLStatement

'new test

Me.Child0.Form.RecordSource = strFinalSQLStatement



End Sub
Private Function strFixedSQLClause(strSQLClause As String) As String

If fFirstSQLClause = True Then
fFirstSQLClause = False
strFixedSQLClause = "WHERE " + strSQLClause
Else
strFixedSQLClause = "AND " + strSQLClause
End If

End Function



Then there is the checkbox to indicate that the dataset is the dataset
that you desire to send. If checked it makes another command button to
send email visible. The after update code for this is:

Private Sub CheckBox_AfterUpdate()
If CheckBox.Value = -1 Then
Command40.Visible = True
Label44.Visible = True
Else
Command40.Visible = False
Label44.Visible = False
End
End If

End Sub

Finally, the command button for the send email button is (note, right
now it opens the dataset as an Excel file, this will be changed -
recycling some code):

Private Sub Command40_Click()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "By Selecting yes I certify that..." ' Define message.
Style = vbYesNo ' Define buttons.
Title = "Certification" ' Define title.

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then ' User chose No.
End ' Perform some action.
Else ' User chose Yes.
'MyString = "Yes" ' Perform some action.

End If

Call cmdSearch_Click

If strqrySQL = "" Then

strqrySQL = "SELECT [qry Analyzed Results].[Outfall Number], [qry
Analyzed Results].[Outfall Name], [qry Analyzed Results].[Collection
Date], [qry Analyzed Results].Analyte, [qry Analyzed Results].Result,
[qry Analyzed Results].[Result Number], [qry analyzed results].[units],
[qry Analyzed Results].[Compliance Sample], [qry Analyzed
Results].[Sample Type], [qry Analyzed Results].Sampler FROM [qry
Analyzed Results]"

ExportToExcel (strqrySQL)

Else

DoCmd.Requery "Child0"

ExportToExcel (strqrySQL)

End If

End Sub

If the query is run, and the checkbox is checked, you can use the
calendar control again to reset the dates. On the other hand if you
uncheck the checkbox and then try to use the calendar you get an error.

Also, the is a yes/no msgbox in the "send email" command button.
Yes means the dataset is correct, while No indicates, that no this is
not the dataset that I want to send. It stops the process of send the
mail and takes you back to the form. If no is selected you can't use
the calendar control to set other dates.

Thanks
Click to expand...
 
Oops,

Here is the code from the command button to call this code, I'm also
calling daterange.

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.CollectionDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.CollectionDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.CollectionDate.Value = Null
Me.txtEndDate.Value = Null
End If


End Sub
 
Let's try to recap.

You've got a form with text boxes CollectionDate and txtEndDate on it. You
click on cmdDateRange, and you invoke the calendar to let you change what's
in those two text boxes.

You've also got a button Command40 that when the user clicks on it makes
them answer Yes or No to a message. If they select Yes, you invoke routine
cmdSearch_Click that generates a SQL statement that uses (among other
things) the dates contained in CollectionDate and txtEndDate and stores that
SQL in variable strqrySQL. Command40_Click then uses that query to export
data to Excel. strqrySQL must be a global variable, since I don't see it
declared anywhere. (BTW, I think it would make more sense to have a function
that returns the SQL, rather than calling cmdSearch_Click and having it set
the global variable. Global variables aren't really appropriate in this
case. See below for one reason why not.)

There's also a check box on the form that toggles whether Command40 (and
Label44) is visible or not, hence (obviously) determining whether or not the
user can click on button Command40.

You're saying that if the query is run and the check box is unchecked, and
then you try and use the calendar again (presumably by clicking on
cmdDateRange), you get an error. What's the exact error message you're
getting? What triggers the error message (clicking on cmdDateRange, clicking
on Command40, or something else)? (actually, I guess it can't be clicking
on Command40, since having the check box unchecked means that Command40
isn't visible)

I don't see any connection between the check box and the calendar.
cmdDateRange_Click doesn't reference the check box, and CheckBox_AfterUpdate
doesn't reference any of CollectionDate, txtEndDate or cmdDateRange.

You're also talking about a problem if the user selected No to the message
generated when clicking on Command40. That may well be caused by your use of
End in that part of the If statement. End is a fairly drastic command. When
executed, the End statement resets all module-level variables and all static
local variables in all modules. This implies that strqrySQL gets reset to
nothing. You probably only want Exit Sub, rather than End.

BTW, you're trimming too much from your posts. It's a nuisance for me to
have to go searching for your previous posts in an attempt to keep the
entire issue in focus.
 
Thanks

Let's try to recap.

You've got a form with text boxes CollectionDate and txtEndDate on it. You
click on cmdDateRange, and you invoke the calendar to let you change what's
in those two text boxes.

You've also got a button Command40 that when the user clicks on it makes
them answer Yes or No to a message. If they select Yes, you invoke routine
cmdSearch_Click that generates a SQL statement that uses (among other
things) the dates contained in CollectionDate and txtEndDate and stores that
SQL in variable strqrySQL. Command40_Click then uses that query to export
data to Excel. strqrySQL must be a global variable, since I don't see it
declared anywhere. (BTW, I think it would make more sense to have a function
that returns the SQL, rather than calling cmdSearch_Click and having it set
the global variable. Global variables aren't really appropriate in this
case. See below for one reason why not.)

There's also a check box on the form that toggles whether Command40 (and
Label44) is visible or not, hence (obviously) determining whether or not the
user can click on button Command40.

You're saying that if the query is run and the check box is unchecked, and
then you try and use the calendar again (presumably by clicking on
cmdDateRange), you get an error. What's the exact error message you're
getting? What triggers the error message (clicking on cmdDateRange, clicking
on Command40, or something else)? (actually, I guess it can't be clicking
on Command40, since having the check box unchecked means that Command40
isn't visible)
Click to expand...

This error is triggered by the following sequence. You select some
dates, run the query, check the checkbox, then uncheck the checkbox and
then trying to use the calendar again.

The error is:

The month calendar class instance you passed tp this function is
INVALID! You must instantiate the Month Calendar class object before
you call this function. The code behind the sample Form shows you how
to do it in the Forms load event.

'This must appear here!
'Create an instance of our class
Private Sub Form_Load()
Set mc = NewclsMonthCal
'You must set the cass hWndFrm prop!!
mc.hWndForm = mc.hWnd

I don't see any connection between the check box and the calendar.
cmdDateRange_Click doesn't reference the check box, and CheckBox_AfterUpdate
doesn't reference any of CollectionDate, txtEndDate or cmdDateRange.

You're also talking about a problem if the user selected No to the message
generated when clicking on Command40. That may well be caused by your use of
End in that part of the If statement. End is a fairly drastic command. When
executed, the End statement resets all module-level variables and all static
local variables in all modules. This implies that strqrySQL gets reset to
nothing. You probably only want Exit Sub, rather than End.
Click to expand...

Exit sub solves the problem
 
Thanks Doug for going the extra mile. I was away for a few days and missed
the start of this thread.
:-)

--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
THen your best bet is to repost with a proper Subject heading and a
clear/detailed explanation of your issue.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lebans Calendar - Must Instantiate Error 1
Lebans Calendar - Select a Date Range 1
Calendar 2 part question. 1
Calendar help with dtCriteria1 = StartDateCalendar.Value calendar 0
Validation and Popup Calendar 5
How do you escape from Access Calendar. 2
Escape from Access Calendar. 3
Help implementing Steven Lebans' Get/Set Scrollbars with GetCurren 1

Back
Top