using a field from a querry in some VBA code on a form

[FatMan]

Hi all:
Can anyone please help me with this.

I need to reefer to a field from a MS Access query (the query has been
created using the query by example grid and not SQL) on a form. The field
name is "BinQty" and shows up as "SumOfBinQty" as the Groupby and sum options
are bing used.

When I reefer to the field in VBA I get the error message "Object Required".
I reefer to the field as:
Queries![qryPackoutPreviousPackoutsSum].[SumOfBinQty]

What is wrong?

The background to this is that I am trying to check to see if the total of
BinQty exceeds the value of BinsIntake. The querry is calculating the
SumOfBinQty correctly but I get the darn error message when I try to
reference it in my code.

Any help anyone can offer would me greatly appreciated.

Thanks,
FatMan

 

[Allen Browne]

How about:
=DLookup("SumOfBinQty", "qryPackoutPreviousPackoutsSum")

 

[FatMan]

Allen:
Thanks for your help....the DLookup work great. I have used this before and
am not sure why I didn't think about it. Have to admitt I have never used it
or seen it with only the two arguments...only seen/used it with the three
arguments.

As a follow up question....
As I mentioned before I am using this to confirm that the total of BinQty
does not exceed the value of BinsIntake. What I am currently doing is when
the user updates a field on the form I run/open the querry and store the
"SumOfBinQty" in a variable with the DLookup (thanks to you) and then
Minimize the querry and run my Error message if needed.

What I would like to know is there a better way to do the above so the user
will not see the querry opening and then minimizing on the screen?

Any help would be greatly appreciated.

Thanks,
FatMan

How about:
=DLookup("SumOfBinQty", "qryPackoutPreviousPackoutsSum")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all:
Can anyone please help me with this.

I need to reefer to a field from a MS Access query (the query has been
created using the query by example grid and not SQL) on a form. The field
name is "BinQty" and shows up as "SumOfBinQty" as the Groupby and sum
options
are bing used.

When I reefer to the field in VBA I get the error message "Object
Required".
I reefer to the field as:
Queries![qryPackoutPreviousPackoutsSum].[SumOfBinQty]

What is wrong?

The background to this is that I am trying to check to see if the total of
BinQty exceeds the value of BinsIntake. The querry is calculating the
SumOfBinQty correctly but I get the darn error message when I try to
reference it in my code.

Any help anyone can offer would me greatly appreciated.

Thanks,
FatMan

 

[Allen Browne]

DLookup() should return an answer without opening the query for the user to
see.

It is also possible to open a recordset in code instead of opening a query
on screen. If you are interested in learning that technique, here is a
function that does what DLookup() does, by opening a recordset
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:
Thanks for your help....the DLookup work great. I have used this before
and
am not sure why I didn't think about it. Have to admitt I have never used
it
or seen it with only the two arguments...only seen/used it with the three
arguments.

As a follow up question....
As I mentioned before I am using this to confirm that the total of BinQty
does not exceed the value of BinsIntake. What I am currently doing is
when
the user updates a field on the form I run/open the querry and store the
"SumOfBinQty" in a variable with the DLookup (thanks to you) and then
Minimize the querry and run my Error message if needed.

What I would like to know is there a better way to do the above so the
user
will not see the querry opening and then minimizing on the screen?

Any help would be greatly appreciated.

Thanks,
FatMan

How about:
=DLookup("SumOfBinQty", "qryPackoutPreviousPackoutsSum")

Hi all:
Can anyone please help me with this.

I need to reefer to a field from a MS Access query (the query has been
created using the query by example grid and not SQL) on a form. The
field
name is "BinQty" and shows up as "SumOfBinQty" as the Groupby and sum
options
are bing used.

When I reefer to the field in VBA I get the error message "Object
Required".
I reefer to the field as:
Queries![qryPackoutPreviousPackoutsSum].[SumOfBinQty]

What is wrong?

The background to this is that I am trying to check to see if the total
of
BinQty exceeds the value of BinsIntake. The querry is calculating the
SumOfBinQty correctly but I get the darn error message when I try to
reference it in my code.

 

[FatMan]

Allen:
Thanks!!! The DLookup() work great.....perfect in fact.

Thanks for your advice it is greatly appreciated.

Regards,
FatMan

DLookup() should return an answer without opening the query for the user to
see.

It is also possible to open a recordset in code instead of opening a query
on screen. If you are interested in learning that technique, here is a
function that does what DLookup() does, by opening a recordset
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:
Thanks for your help....the DLookup work great. I have used this before
and
am not sure why I didn't think about it. Have to admitt I have never used
it
or seen it with only the two arguments...only seen/used it with the three
arguments.

As a follow up question....
As I mentioned before I am using this to confirm that the total of BinQty
does not exceed the value of BinsIntake. What I am currently doing is
when
the user updates a field on the form I run/open the querry and store the
"SumOfBinQty" in a variable with the DLookup (thanks to you) and then
Minimize the querry and run my Error message if needed.

What I would like to know is there a better way to do the above so the
user
will not see the querry opening and then minimizing on the screen?

Any help would be greatly appreciated.

Thanks,
FatMan

How about:
=DLookup("SumOfBinQty", "qryPackoutPreviousPackoutsSum")

Hi all:
Can anyone please help me with this.

I need to reefer to a field from a MS Access query (the query has been
created using the query by example grid and not SQL) on a form. The
field
name is "BinQty" and shows up as "SumOfBinQty" as the Groupby and sum
options
are bing used.

When I reefer to the field in VBA I get the error message "Object
Required".
I reefer to the field as:
Queries![qryPackoutPreviousPackoutsSum].[SumOfBinQty]

What is wrong?

The background to this is that I am trying to check to see if the total
of
BinQty exceeds the value of BinsIntake. The querry is calculating the
SumOfBinQty correctly but I get the darn error message when I try to
reference it in my code.