Accessing form data from a query in 2003

[Douglas Hitchcock]

I'm trying to upgrade an Access 97 application to 2003. This query:

SELECT qry_Performance.Batch, qry_Performance.Value, qry_Excluded.Excluded,
[Forms]![frm_Main]![sfrm_Right]![LCLss] AS LCL,
[Forms]![frm_Main]![sfrm_Right]![UCLss] AS UCL,
[Forms]![frm_Main]![sfrm_Right]![Avgss] AS Avg,
[Forms]![frm_Main]![sfrm_Right]![LSL] AS LSL,
[Forms]![frm_Main]![sfrm_Right]![USL] AS USL FROM qry_Performance LEFT
JOIN qry_Excluded ON qry_Performance.Batch = qry_Excluded.Batch ORDER BY
qry_Performance.Date, qry_Performance.Batch;

works fine in 97, but in 2003 the fields coming from the form references
are blank. what's changed between 97 and 2003 that's causing the
behaviour, any ideas for a quick fix?

thanks

 

[Marshall Barton]

I'm trying to upgrade an Access 97 application to 2003. This query:

SELECT qry_Performance.Batch, qry_Performance.Value, qry_Excluded.Excluded,
[Forms]![frm_Main]![sfrm_Right]![LCLss] AS LCL,
[Forms]![frm_Main]![sfrm_Right]![UCLss] AS UCL,
[Forms]![frm_Main]![sfrm_Right]![Avgss] AS Avg,
[Forms]![frm_Main]![sfrm_Right]![LSL] AS LSL,
[Forms]![frm_Main]![sfrm_Right]![USL] AS USL FROM qry_Performance LEFT
JOIN qry_Excluded ON qry_Performance.Batch = qry_Excluded.Batch ORDER BY
qry_Performance.Date, qry_Performance.Batch;

works fine in 97, but in 2003 the fields coming from the form references
are blank. what's changed between 97 and 2003 that's causing the
behaviour, any ideas for a quick fix?

They've tightend up some of the lax syntax that slipped by
in previous versions. You now need to go through the Form
property of a subform control:

Forms!frm_Main!sfrm_Right.FORM!LCLss AS LC

 

[Douglas Hitchcock]

They've tightend up some of the lax syntax that slipped by

in previous versions. You now need to go through the Form
property of a subform control:

Forms!frm_Main!sfrm_Right.FORM!LCLss AS LC

hey marshall, thanks for the tip i didn't know.

however, my problem still remains. i removed the extra bits from the
sql to make it less confusing, so i have just this:

SELECT Forms!frm_Main!sfrm_Right.FORM!LCLss AS LCL

and the result i get is two weird little boxes:

http://purplerhino.mailcan.com/boxes.png

a little more info, frm_Main is a big main window, it has a sub_form
area named sfrm_Right but different forms get plugged into it as you
press different buttons. this query on one the charts sub form needs to
get a value from a text box in the same main window but in a different
sub form that holds some stats. that text box is just bound to a field
in a table.

thanks again

 

[Marshall Barton]

hey marshall, thanks for the tip i didn't know.

however, my problem still remains. i removed the extra bits from the
sql to make it less confusing, so i have just this:

SELECT Forms!frm_Main!sfrm_Right.FORM!LCLss AS LCL

and the result i get is two weird little boxes:

http://purplerhino.mailcan.com/boxes.png

a little more info, frm_Main is a big main window, it has a sub_form
area named sfrm_Right but different forms get plugged into it as you
press different buttons. this query on one the charts sub form needs to
get a value from a text box in the same main window but in a different
sub form that holds some stats. that text box is just bound to a field
in a table.

I don't know if a chart has anything to do with it.

I also don't get the funny characters when I try the same
kind of query. Little boxes typically indicates a character
(e.g. tab) that the font does not contain. Try the same
kind of query with a simple text box value.

 

[Douglas Hitchcock]

I don't know if a chart has anything to do with it.

I also don't get the funny characters when I try the same
kind of query. Little boxes typically indicates a character
(e.g. tab) that the font does not contain. Try the same
kind of query with a simple text box value.

hey marshall, thanks for your help

ok i've definetly found a problem, in access 97 if you try and get a
value from a text box with the Forms!form!Textbox way and that text box
is bound to a number field, it comes back as a string of that number.

in 2003, getting the value from a text box bound to a number field
returns strange unknown characters.

seems broken? why would that be the desired behaviour, i'm hoping i can
fix this with some use of cstr() and val()

 

[Marshall Barton]

hey marshall, thanks for your help

ok i've definetly found a problem, in access 97 if you try and get a
value from a text box with the Forms!form!Textbox way and that text box
is bound to a number field, it comes back as a string of that number.

in 2003, getting the value from a text box bound to a number field
returns strange unknown characters.

seems broken? why would that be the desired behaviour, i'm hoping i can
fix this with some use of cstr() and val()

It's not broken, but it is confused. When a query parameter
has no context, you need to tell the query what to do. This
is easy to do by right clicking anywhere in in the query
design grid and select Parameters from the pop up menu.
Enter the parameter name (e.g.
Forms!frm_Main!sfrm_Right.FORM!LCLss) and select the
datatype of the value (e.g. Long Integer)

 

[Douglas Hitchcock]

It's not broken, but it is confused. When a query parameter
has no context, you need to tell the query what to do. This
is easy to do by right clicking anywhere in in the query
design grid and select Parameters from the pop up menu.
Enter the parameter name (e.g.
Forms!frm_Main!sfrm_Right.FORM!LCLss) and select the
datatype of the value (e.g. Long Integer)

Marshall,

Thank you so much, that was a huge help and fixed the problems I was having.

take care

 

[Marshall Barton]

Marshall,

Thank you so much, that was a huge help and fixed the problems I was having.

Glad to help. I sympathize with the frustration of tracking
down these subtle settings. Sometimes it can make you feel
like you're lost in a twisty maze and a simple nudge in the
right direction can make all the difference. Live, learn
and pass it on ;-)