ACCESS 2003 -- form and subform HELP........

[Hung.cao]

I've a form in Datasheet view which have 5 fields ... Last name,
firstname, city, zipcode, phone. When the user double click on the
phone number field it will call another form which run from the query
(The query is contrain by the phone value from the main form --
[forms]![mainform]![phone]).

It's working great in Access 2000 and Access 2003. When i try to run
it on Access 2003 in XP at work. Everytime, i double click the phone
from the main form, a window popup and ask me to enter the phone
number.


Can someone help me how to correct the problem in ACCESS 2003. Thank
you in advance and hope to hear from you soon.

 

[Hung.cao]

sorry i means it's working fine on ACCESS 97, 2000, 2002 but the same
code not work in ACCESS 2003

 

[Guest]

1- I wouldn't suggest posting the same question 5 times as you have done.
This is very impolite and will turn people off from answering your questions
at all.

2- please post the code that you are using on the doubleclick event so we
can troubleshoot it.

Daniel

 

[Guest]

Actually, you might as well post the SQL statement for the query as well to
give us the full picture of what is going on.

 

[Hung.cao]

1- I wouldn't suggest posting the same question 5 times as you have done.
This is very impolite and will turn people off from answering your questions
at all.

2- please post the code that you are using on the doubleclick event so we
can troubleshoot it.

Daniel

Didn't mean to post it 5 times. The code in the doubleclick event is
only do the open the second form which run from the query using the
value of the the main form.

DoCmd.OpenForm "form_detail"

 

[Guest]

Could you post the SQL for the query. Is there any code being run as the
form is opened (Load or Open Events or something else we should be aware of)?

Daniel

 

[Hung.cao]

Here is the query to run the sub form

SELECT Student_financial_year.last, Student_financial_year.first,
Student_financial_year.address, Student_financial_year.state,
Student_financial_year.phone
FROM Student_financial_year
WHERE (Student_financial_year.phone)=[forms]![mainform]![phone]);

It's working great in the ACCESS 2000 and ACCESS 2002. But when i'm
trying to run the same code in ACCESS 2003 in XP op. It's popup a
winder to ask the value of [forms]![mainform]![phone everytime i double
click on one of the student on the list from the main form. that's
when the subform get call to give me more detail and run the query
above.

Thanks for your help.

 

[Guest]

I'm not an SQL expert, so perhaps someone else might have a better idea of
what is wrong but I did a fast mockup on my PC and I have a slightly modify
synthax. Give it a try, hopefully it will fix your problem.

SELECT Student_financial_year.last, Student_financial_year.first,
Student_financial_year.address, Student_financial_year.state,
Student_financial_year.phone
FROM Student_financial_year
WHERE (((Student_financial_year.phone)=[forms]![mainform]![phone]));

Daniel

 

[Hung.cao]

I'm not an SQL expert, so perhaps someone else might have a better idea of
what is wrong but I did a fast mockup on my PC and I have a slightly modify
synthax. Give it a try, hopefully it will fix your problem.

SELECT Student_financial_year.last, Student_financial_year.first,
Student_financial_year.address, Student_financial_year.state,
Student_financial_year.phone
FROM Student_financial_year
WHERE (((Student_financial_year.phone)=[forms]![mainform]![phone]));

Daniel

Appreciate for your helps but i don't think it was an syntax error. As
i mention the same code is working fine when i run the application in
ACCESS 2000 or ACCESS 2002. It's only popup and asking me to input
value when i run it in ACCESS 2003. It's the same application. Thanks
for trying.

 

[John Nurick]

Here is the query to run the sub form

SELECT Student_financial_year.last, Student_financial_year.first,
Student_financial_year.address, Student_financial_year.state,
Student_financial_year.phone
FROM Student_financial_year
WHERE (Student_financial_year.phone)=[forms]![mainform]![phone]);

That SQL statement has a superfluous ")" which could be a source of
trouble. Different versions of Access may respond differently to errors
such as this.

It's working great in the ACCESS 2000 and ACCESS 2002. But when i'm
trying to run the same code in ACCESS 2003 in XP op. It's popup a
winder to ask the value of [forms]![mainform]![phone everytime i double
click on one of the student on the list from the main form. that's
when the subform get call to give me more detail and run the query
above.

Thanks for your help.

 

[Hung.cao]

Here is the actual SQL statement. I just want to make it simple so i
make a mistake on type it.

SELECT Student_financial_year.StudentID, Student_financial_year.year,
Student_financial_year.Applydate, Student_financial_year.PSAScomplete,
Student_financial_year.Totalincome, Student_financial_year.amountpay,
Student_financial_year.amountneed,
Student_financial_year.Totalmoneyaward,
Student_financial_year.Totalchildrens,
Student_financial_year.Totalchildrensstvm, Student_financial_year.GPA,
Student_financial_year.replacementtestresults,
Student_financial_year.Ranking, Student_financial_year.leadership,
Student_financial_year.religion, Student_financial_year.awardaccepted,
Student_financial_year.awardaccepteddate,
Student_financial_year.comment
FROM Student_financial_year
WHERE
(((Student_financial_year.StudentID)=[forms]![student]![studentid]) AND
((Student_financial_year.year)=[Forms]![student]![Student_Sub]![Student_financial_year
subform]![year]));

As i mention on the above, I don't think it've any errors on the
application. It's running fine when i run the application in ACCESS
2000 and ACCESS 2002 but it doesn't work when i run the same
application under ACCESS 2003 in XP.
It's giving me a popup and ask me to enter
[forms]![student]![studentid] and
[Forms]![student]![Student_Sub]![Student_financial_year subform]![year]
which it should get the value from the mainform calling this form. And
this form is the one run the SQL statenent above.
I just wonder is there any config that i need to know in ACCESS 2003 so
i can make it work like ACCESS 2000 or ACCESS 2002. Thank you for all
the advice but i doubt that there is anything wrong with the code
itself. The machine i run this application in ACCESS 2003, it've only
512 MB memory, is that the problem?

 

[Hung.cao]

Here is the actual SQL statement. I just want to make it simple so i
make a mistake on type it.

SELECT Student_financial_year.StudentID, Student_financial_year.year,
Student_financial_year.Applydate, Student_financial_year.PSAScomplete,
Student_financial_year.Totalincome, Student_financial_year.amountpay,
Student_financial_year.amountneed,
Student_financial_year.Totalmoneyaward,
Student_financial_year.Totalchildrens,
Student_financial_year.Totalchildrensstvm, Student_financial_year.GPA,
Student_financial_year.replacementtestresults,
Student_financial_year.Ranking, Student_financial_year.leadership,
Student_financial_year.religion, Student_financial_year.awardaccepted,
Student_financial_year.awardaccepteddate,
Student_financial_year.comment
FROM Student_financial_year
WHERE
(((Student_financial_year.StudentID)=[forms]![student]![studentid]) AND
((Student_financial_year.year)=[Forms]![student]![Student_Sub]![Student_financial_year
subform]![year]));

As i mention on the above, I don't think it've any errors on the
application. It's running fine when i run the application in ACCESS
2000 and ACCESS 2002 but it doesn't work when i run the same
application under ACCESS 2003 in XP.
It's giving me a popup and ask me to enter
[forms]![student]![studentid] and
[Forms]![student]![Student_Sub]![Student_financial_year subform]![year]
which it should get the value from the mainform calling this form. And
this form is the one run the SQL statenent above.
I just wonder is there any config that i need to know in ACCESS 2003 so
i can make it work like ACCESS 2000 or ACCESS 2002. Thank you for all
the advice but i doubt that there is anything wrong with the code
itself. The machine i run this application in ACCESS 2003, it've only
512 MB memory, is that the problem?

 

[John Nurick]

The two parts of the WHERE clause:

((Student_financial_year.StudentID)=[forms]![student]![studentid])

I can't think why this is causing problems, unless your main form is not
called "student" and/or the control is not called "studentid".

((Student_financial_year.year)=[Forms]![student]![Student_Sub]![Student_financial_year subform]![year])

This one I don't understand, and I wouldn't have expected it to work in
any version of Access. The usual syntax for referring to a control on a
subform would be

Forms![Student]![StudentSub].Form![year]

Here is the actual SQL statement. I just want to make it simple so i
make a mistake on type it.

SELECT Student_financial_year.StudentID, Student_financial_year.year,
Student_financial_year.Applydate, Student_financial_year.PSAScomplete,
Student_financial_year.Totalincome, Student_financial_year.amountpay,
Student_financial_year.amountneed,
Student_financial_year.Totalmoneyaward,
Student_financial_year.Totalchildrens,
Student_financial_year.Totalchildrensstvm, Student_financial_year.GPA,
Student_financial_year.replacementtestresults,
Student_financial_year.Ranking, Student_financial_year.leadership,
Student_financial_year.religion, Student_financial_year.awardaccepted,
Student_financial_year.awardaccepteddate,
Student_financial_year.comment
FROM Student_financial_year
WHERE
(((Student_financial_year.StudentID)=[forms]![student]![studentid]) AND
((Student_financial_year.year)=[Forms]![student]![Student_Sub]![Student_financial_year
subform]![year]));

As i mention on the above, I don't think it've any errors on the
application. It's running fine when i run the application in ACCESS
2000 and ACCESS 2002 but it doesn't work when i run the same
application under ACCESS 2003 in XP.
It's giving me a popup and ask me to enter
[forms]![student]![studentid] and
[Forms]![student]![Student_Sub]![Student_financial_year subform]![year]
which it should get the value from the mainform calling this form. And
this form is the one run the SQL statenent above.
I just wonder is there any config that i need to know in ACCESS 2003 so
i can make it work like ACCESS 2000 or ACCESS 2002. Thank you for all
the advice but i doubt that there is anything wrong with the code
itself. The machine i run this application in ACCESS 2003, it've only
512 MB memory, is that the problem?

 

[Hung.cao]

Thank John for the reply......

I have a student form which contain the control name studentid. Inside
that form i have an subform which give me the Year. When the user
double click on the year it's calling another single form name
"Student_detail" which run from the Query as a control sourse.

It's working fine in any ACCESS version expet ACCESS 2003. When the
user double click on the year it's asking them to enter the StudendID
and the Year again before open the single form "Student_detail". I'm
not sure what is going on but I found this artical over the net. Is
that something i should do when run the application in ACCESS 2003 on
XP machice.

http://allenbrowne.com/bug-03.html

Thanks again. I hope someone have the same problems and they can show
me what i should do to make the application to run on ACCESS 2003.

 

[John Nurick]

Thank John for the reply......

I have a student form which contain the control name studentid. Inside
that form i have an subform which give me the Year. When the user
double click on the year it's calling another single form name
"Student_detail" which run from the Query as a control sourse.

That is what I assumed you were doing.

It's working fine in any ACCESS version expet ACCESS 2003.

Different versions of Access sometimes respond differently to
non-standard syntax or inputs. Have you actually tried any of the things
Daniel and I have suggested?

When the
user double click on the year it's asking them to enter the StudendID
and the Year again before open the single form "Student_detail". I'm
not sure what is going on but I found this artical over the net. Is
that something i should do when run the application in ACCESS 2003 on
XP machice.

http://allenbrowne.com/bug-03.html

It's certainly worth turning Name Autocorrect off. Having done so, make
certain that all the controls and fields still have the names you expect
them to have.

Having done that, I would delete the original query and create a new one
to return the data for the Student_detail form.