Hi All

Asking help again. I am a casual user of Access. I have a simple
application which works correctly under Access 2003. I tried it under
2007. One of the module refuses to work. I have :

A table : PersonId, Name, Birthday
----
A form (frmBirthDay) which includes ActiveX MonthView (ctlCalendar)
and a subform (frmBirthDaySub) which displays names and year of birth
of people born on same day and month selected on the calendar. A
module:

Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub CtlCalendar_DateClick(ByVal DateClicked As Date)
frmBirthDaySub.Requery
End Sub

Private Sub CtlCalendar_SelChange(ByVal StartDate As Date, ByVal
EndDate As Date, Cancel As Boolean)
frmBirthDaySub.Requery
End Sub

Private Sub Form_Load()
CtlCalendar.Value = Date
frmBirthDaySub.Requery
End Sub
----
A select query (qryBirthDay) which has the following field :Name,
A:Year(birthday), Month(birthday), Day(BirthDay)
[Month(Birthday) criteria = Forms]![FrmBirthDay]![ctlCalendar].[Month]
[Day(Birthday) criteria = Forms]![FrmBirthDay]![ctlCalendar].[Day]

Under Access 2007, when I run the form (frmBirthDay), I get :

Enter value of parameter
Forms]![FrmBirthDay]![ctlCalendar].[Month, the same question for
month....

I spent 3 days fighting with this. I read the forum but did not find
similar posts. I cannot figure out where is the problem.

TKS AGN for helping.

Vaudousi wrote:
What happens if you change
Forms]
to
[Forms]
?


Feuer Frei!
http://www.youtube.com/watch?v=nHWicNDkbpw

On 6 mar, 00:18, Salad <o... (AT) vinegar (DOT) com> wrote:
Hi Salad

Sorry, typo in my message, not in the real thing. I confirm that the
whole procedure works on 2003. I copied it to another computer running
2007 (with 2003 database format).

It just does not work under 2007. I am very confused.

Vaudousi wrote:

Can't you pause, or step thru the code, and attempt to run the query in
the debug window or perhaps in the query builder?

You might have to rewrite the query.

Can you create a cheap form that opens the calendar control and then
attempting to run the query. Or attempt to see what
[Forms]![FrmBirthDay]![ctlCalendar].[Day] or .[Month] is in the debug
window?

You might get some better responses if you posted the SQL statement. I
had a hard time deciphering it...seemed like psuedo code.

On 6 mar, 07:37, Salad <o... (AT) vinegar (DOT) com> wrote:
TKS for your patience. This is the exact code. A table, a form and a
qurey. That cannot be simpler. (That's all I can do).

SELECT [LastName] & " " & [FirstName] AS FullName, Year([BirthDay]) AS
An
FROM tPerson
WHERE (((Month([BirthDay]))=[Forms]![frmBirthDay]![ctlCalendar].
[Month]) AND ((Day([BirthDay]))=[Forms]![FrmBirthDay]![ctlCalendar].
[Day]))
ORDER BY Year([BirthDay]), tPerson.LastName;

Its OK with 2003. When I run the .mdb (example today), the subform
displays names of persons born March 6, any year. If I change date,
different names are displayed (or nothing).

Now, I copy the .mdb and run it under 2007. It keeps asking ; Enter
value of parameter.

Salad, I think that something has changed in 2007.

Vaudousi wrote:

Most likely. What happens if you were to enter
msgbox "Month " & [Forms]![frmBirthDay]![ctlCalendar].[Month] &
vbNewLine & "Day " & [Forms]![FrmBirthDay]![ctlCalendar].[Day]

just prior to creating your SQL statement.

I'd probably start chopping off the SQL statements and see what it
objects to.

strSQL = SELECT [LastName] & " " & [FirstName] AS FullName, _
Year([BirthDay]) AS An
FROM tPerson
WHERE Month([BirthDay])=[Forms]![frmBirthDay]![ctlCalendar].[Month] _
AND Day([BirthDay])=[Forms]![FrmBirthDay]![ctlCalendar].[Day] _
ORDER BY Year([BirthDay]), tPerson.LastName;
Debug.print "Full"
Debug.print strSQL

strSQL = SELECT [LastName] & " " & [FirstName] AS FullName, _
Year([BirthDay]) AS An
FROM tPerson
WHERE Month([BirthDay])=[Forms]![frmBirthDay]![ctlCalendar].[Month] _
AND Day([BirthDay])=[Forms]![FrmBirthDay]![ctlCalendar].[Day]
Debug.print "No order"
Debug.print strSQL

strSQL = SELECT [LastName] & " " & [FirstName] AS FullName, _
Year([BirthDay]) AS An
FROM tPerson
WHERE Month([BirthDay])=[Forms]![frmBirthDay]![ctlCalendar].[Month]
Debug.print "No Day"
Debug.print strSQL


strSQL = SELECT [LastName] & " " & [FirstName] AS FullName, _
Year([BirthDay]) AS An
FROM tPerson
Debug.print "No Month"
Debug.print strSQL

Now you can copy/paste these 4 SQL statements into the query
builder...open up a new query in design and select View/SQL and paste
the SQL statement in there and run.

or make 3 copies of the QUerydef (you have 4 types like above)
Set qdf = dbs.QueryDefs("Query1")
Debug.print qdf.SQL
...
Set qdf = dbs.QueryDefs("Query4")
Debug.print qdf.SQL
and run the results

Bell
http://www.youtube.com/watch?v=Z5iQb9ydhR4

On 6 mar, 18:54, Salad <o... (AT) vinegar (DOT) com> wrote:
OK...Let me study your suggestions. I'll be back later.

TKS

On 6 mar, 19:27, Vaudousi <f... (AT) tele2 (DOT) fr> wrote:
Hi Salad

I am not a professional, so I say that with prudence. I think that
something is broken somewhere : in Access 2007 or more probably, in my
version.

Apparently, the subform (frmBirthDaySub is activated before the date
is set. I modified the Form_Load following your suggestion (adding a
line with MsgBox) :

Private Sub Form_Load()
CtlCalendar.Value = Date
'added for test
MsgBox "Month " & [Forms]![FrmBirthDay]![CtlCalendar].[Month] & _
vbNewLine & "Day " & [Forms]![FrmBirthDay]![CtlCalendar].[Day]
frmBirthDaySub.Requery
End Sub.

Running frmBirthDay : on 2003, the program stops, displays the MsgBox
and continues normally after clicking OK.
On 2007, it goes directly to "Enter a value of parameter"

Thanks for your help. I surrender.

Vaudousi wrote:
Shame on you! Programmers don't surrender, they adjust!

I wish I could come up with the "solution" for you. I think at this
time I'll pop you over to
http://allenbrowne.com/ser-51.html

Most, or should I say many, of us don't use the ActiveX calendar. As
Allen noted "There are plenty of ActiveX control calendars, but they
have issues with versioning, broken references, and handling Nulls."

I suggest you check out the built in calendar Allen mentions that is
built in to A2007. You could check out the version:
syscmd(acSysCmdAccessVer)
and if it returns 11 or less, use your current control otherwise use the
new control in A2007 (I have NO expericient with it. Or check out the
code for getting the version at Allen's page
http://allenbrowne.com/ser-53code.html. Perhaps the built in calendar
will be sufficient.

If you want to look at other calendars I suggest you look in
GoogleGroups in the group comp.databases.ms-access. What I would do is
find one that you can modify to suit your needs and bypass the ActiveX
one. I have a calendar form I modified presents a From and a To
calendar for those times a person wants to select dates in a range.

Turn It Around
http://www.youtube.com/watch?v=JYB_hvVBFYc

On 8 mar, 20:42, Salad <o... (AT) vinegar (DOT) com> wrote:
The problem is not with the ActiveX itself. I have it working on
another form in the same program on 2007. I suspect the combination
SubForm/Query.

Switching from this calendar to another one is also a capitulation.
Hi...Hi.