Query problem

[Shell]

In Access 2000, I have a query which references a field on a form for a
criteria. The reference is written as [Forms]![frmReportTool].txtRegion]
where Region is an integer.

When the form is open and a region is selected, the query runs properly. It
recognizes the region. However, when in code I transfer to another module
and try to run the query, the reference is not converted to the value on the
form. It defaults to 0 and I get the incorrect output from the query.

When the code executes in the other module I run a docmd.openquery statement
referencing the queryname.

Does any one know why and how I can correct it?

Thanks

 

[Shell]

Yes, the form is always open. I don't know if it has the focus or if it even
needs the focus when the called module is running.
--
Shell

Is the frmReportTool form always open during your test?

In Access 2000, I have a query which references a field on a form for a
criteria. The reference is written as [Forms]![frmReportTool].txtRegion]
where Region is an integer.

When the form is open and a region is selected, the query runs properly. It
recognizes the region. However, when in code I transfer to another module
and try to run the query, the reference is not converted to the value on the
form. It defaults to 0 and I get the incorrect output from the query.

When the code executes in the other module I run a docmd.openquery statement
referencing the queryname.

Does any one know why and how I can correct it?

Thanks

 

[Shell]

I did what you suggested and got the proper value.
--
Shell

Put in a diagnostic MsgBox in your module code that displays the value of the
control when referenced through the Forms collection. Maybe you can then
determine what is happening.
The other form does *not* need the focus, only being open.

Yes, the form is always open. I don't know if it has the focus or if it even
needs the focus when the called module is running.

Is the frmReportTool form always open during your test?

[quoted text clipped - 13 lines]

Thanks

 

[John W. Vinson]

In Access 2000, I have a query which references a field on a form for a
criteria. The reference is written as [Forms]![frmReportTool].txtRegion]
where Region is an integer.

If it really *is* written exactly as posted, you're missing a left square
bracket before txtRegion.

 

[Shell]

Sorry. The query does have the left square bracket.
--
Shell

In Access 2000, I have a query which references a field on a form for a
criteria. The reference is written as [Forms]![frmReportTool].txtRegion]
where Region is an integer.

If it really *is* written exactly as posted, you're missing a left square
bracket before txtRegion.

 

[John W. Vinson]

Sorry. The query does have the left square bracket.

As my young friend Sherlock used to say "one should never overlook the obvious
solution" - too bad that wasn't it this time!

 

[Shell]

This is the table:
Region DR Criteria
242 NonPay NonPay
242 Voluntary Tfr
242 Voluntary Moved
242 Voluntary Vol
242 Voluntary
127 NonPay NP
127 Voluntary

This is the query:
SELECT tbl_DR.DR, tbl_DR.Criteria, tbl_DR.Region
FROM tbl_DR
WHERE (((tbl_DR.Criteria) Is Null) AND
((tbl_DR.Region)=[Forms]![frmReportingTool]![txtRegion]));


When no code is executing the query works correctly (here I had selected
Region 127 on the frmReportingTool)
DR Criteria Region
Voluntary 127

When the code is executing or in a pause condition the results of the query
are:
DR Criteria Region
0
--
Shell

Hmm...how about posting the SQL for the query that is not working properly?
Maybe we'll see something.

I did what you suggested and got the proper value.

Put in a diagnostic MsgBox in your module code that displays the value of the
control when referenced through the Forms collection. Maybe you can then

[quoted text clipped - 8 lines]

Thanks

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Shell]

Kinda hard to have the latest updates when you are running Access 2000 on
WIndows XP Pro.

I'll look into providing the code

Thanks
--
Shell

The darn thing looks like it should work all of the time. How about posting
the code you have to run this query? From Public Sub to End Sub if possible.
Are you sure you have all of the latest updates to Jet and Access?

This is the table:
Region DR Criteria
242 NonPay NonPay
242 Voluntary Tfr
242 Voluntary Moved
242 Voluntary Vol
242 Voluntary
127 NonPay NP
127 Voluntary

This is the query:
SELECT tbl_DR.DR, tbl_DR.Criteria, tbl_DR.Region
FROM tbl_DR
WHERE (((tbl_DR.Criteria) Is Null) AND
((tbl_DR.Region)=[Forms]![frmReportingTool]![txtRegion]));

When no code is executing the query works correctly (here I had selected
Region 127 on the frmReportingTool)
DR Criteria Region
Voluntary 127

When the code is executing or in a pause condition the results of the query
are:
DR Criteria Region
0

Hmm...how about posting the SQL for the query that is not working properly?
Maybe we'll see something.

[quoted text clipped - 5 lines]

Thanks

 

[Rob G]

When I need a value to persist in a query, I use code from a module and a
couple get and set functions or sub procedures. Can you do that in Access
2000?

So create a module or use a module that already is in your application:

Public strColumnCriteria as String

Public sub SetColumnCriteria( strValue as String )
strColumnCriteria = Nz(strValue,"")
End Sub

Public Function GetColumnCriteria() as String
GetColumnCriteria = Nz(strColumnCriteria,"")
End Function

On your form open event set a default value if you want to:
....
Call SetColumnCriteria("some default value")
....

The when your txtRegion text box get updated or evaluate the txtRegion text
box when they select the open report button, set the global column criteria
to the txtRegion value:

....
Call SetColumnCriteria(Nz(<whatever>.txtRegion,""))
....

Finally in your query just use the get function.

SELECT *
FROM <some table name>
WHERE <some column> = GetColumnCriteria();

depending how you start using this new module, you can add additional error
checking etc, but this can be pretty useful over the whole application
especially if the query's often contain date parameters.

Yes, the form is always open. I don't know if it has the focus or if it even
needs the focus when the called module is running.
--
Shell

Is the frmReportTool form always open during your test?

In Access 2000, I have a query which references a field on a form for a
criteria. The reference is written as [Forms]![frmReportTool].txtRegion]
where Region is an integer.

When the form is open and a region is selected, the query runs properly. It
recognizes the region. However, when in code I transfer to another module
and try to run the query, the reference is not converted to the value on the
form. It defaults to 0 and I get the incorrect output from the query.

When the code executes in the other module I run a docmd.openquery statement
referencing the queryname.

Does any one know why and how I can correct it?

Thanks