Sum query part2

[Rene]

I have made a 2nd query in design view and have the result I wanted but...
I would like to use this 2nd query as recourdsource for a form in vba.

1st qryTotal: SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS
SumOfQuantity...
2nd qryShortage: SELECT qryTotal.SumOfQuantity, tblStandard.Required,
[Required]-[SumOfQuantity] AS [Shortage]...

Is it possible not to use a saved query and use the SQL(=qryShortage) as
recourdsource in vba?
When I have only one SQL it is no problem but I do not know how to to it
with the second SQL based on the first.
How do I refer my 2nd SQL to my 1st SQL?

Now I want something like:
me.recourdsource=qryShortage

Rene

 

[George Nicholson]

Open your form in design view. Look at the Recordsource property. If you
activate the drop-down for the property, you should see stored queries as
legitimate choices, along with tables. Simply select "qryShortage" from the
list (only Select queries will be listed. "Action" queries (Append, Delete,
Insert) will not be listed since they don't return records.)

If you are trying to set the Recordsource property via code, it expects a
string so don't forget the quotes:
Me.Recordsource="qryShortage"

HTH,

 

[Rene]

My problem is that I want in vba set the recordsource for my form.
But I do not know how to combine the 2 sql's into one string so I can set it
as recordsource for my form.

Normally I do something like
strSQL="SELECT * FROM tblTest WHERE ..."
I build up the SQL and set the recordsource.
me.recourdsource=strSQL

But up to now I always used only one query.
My form should be based on 2 queries because I need a SumOfQuantity field as
said earlier.

I do not know how to combine this into one SQL?
If not possible then I must use the saved queries I think.

Rene

Open your form in design view. Look at the Recordsource property. If you
activate the drop-down for the property, you should see stored queries as
legitimate choices, along with tables. Simply select "qryShortage" from
the list (only Select queries will be listed. "Action" queries (Append,
Delete, Insert) will not be listed since they don't return records.)

If you are trying to set the Recordsource property via code, it expects a
string so don't forget the quotes:
Me.Recordsource="qryShortage"

HTH,

I have made a 2nd query in design view and have the result I wanted but...
I would like to use this 2nd query as recourdsource for a form in vba.

1st qryTotal: SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS
SumOfQuantity...
2nd qryShortage: SELECT qryTotal.SumOfQuantity, tblStandard.Required,
[Required]-[SumOfQuantity] AS [Shortage]...

Is it possible not to use a saved query and use the SQL(=qryShortage) as
recourdsource in vba?
When I have only one SQL it is no problem but I do not know how to to it
with the second SQL based on the first.
How do I refer my 2nd SQL to my 1st SQL?

Now I want something like:
me.recourdsource=qryShortage

Rene

 

[George Nicholson]

My form should be based on 2 queries because I need a SumOfQuantity field

as said earlier.

Well, you can't base it on 2 queries. But from the information you have
provided, I think you can create one query that has all the information you
require.

The only thing I can see in qryTotal that isn't in qryShortage is ID. Can't
you just add ID to qryShortage and use that for your recordsource? I'm
guessing you're already using ID to join qryTotal and tblStandard (you have
to be using something for a join...), so it should be simple to add it to
the result set.

If there are additional complexities that you haven't shared here, you might
be able to incorporate DSum() into a solution independent of your
Recordsource.

HTH,

My problem is that I want in vba set the recordsource for my form.
But I do not know how to combine the 2 sql's into one string so I can set
it as recordsource for my form.

Normally I do something like
strSQL="SELECT * FROM tblTest WHERE ..."
I build up the SQL and set the recordsource.
me.recourdsource=strSQL

But up to now I always used only one query.
My form should be based on 2 queries because I need a SumOfQuantity field
as said earlier.

I do not know how to combine this into one SQL?
If not possible then I must use the saved queries I think.

Rene

Open your form in design view. Look at the Recordsource property. If you
activate the drop-down for the property, you should see stored queries as
legitimate choices, along with tables. Simply select "qryShortage" from
the list (only Select queries will be listed. "Action" queries (Append,
Delete, Insert) will not be listed since they don't return records.)

If you are trying to set the Recordsource property via code, it expects a
string so don't forget the quotes:
Me.Recordsource="qryShortage"

HTH,

I have made a 2nd query in design view and have the result I wanted
but...
I would like to use this 2nd query as recourdsource for a form in vba.

1st qryTotal: SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS
SumOfQuantity...
2nd qryShortage: SELECT qryTotal.SumOfQuantity, tblStandard.Required,
[Required]-[SumOfQuantity] AS [Shortage]...

Is it possible not to use a saved query and use the SQL(=qryShortage) as
recourdsource in vba?
When I have only one SQL it is no problem but I do not know how to to it
with the second SQL based on the first.
How do I refer my 2nd SQL to my 1st SQL?

Now I want something like:
me.recourdsource=qryShortage

Rene

 

[Rene]

I can not add my field from tblStandard to the qryTotal because this is a
group query thus resulting in a SumOfQuantity field.
I can not refer to this field direct and need a 2nd query.
1st querie sums a total and with the 2nd query I check if enough supply in
store.

I think I just try to have the qryTotal saved and setting the recourdsource
with SQL in VBA based on the qryTotal.
I was hoping I did not need a saved query but in this case seems
unavoidable.

Thanks

Rene

My form should be based on 2 queries because I need a SumOfQuantity field
as said earlier.

Well, you can't base it on 2 queries. But from the information you have
provided, I think you can create one query that has all the information
you require.

The only thing I can see in qryTotal that isn't in qryShortage is ID.
Can't you just add ID to qryShortage and use that for your recordsource?
I'm guessing you're already using ID to join qryTotal and tblStandard (you
have to be using something for a join...), so it should be simple to add
it to the result set.

If there are additional complexities that you haven't shared here, you
might be able to incorporate DSum() into a solution independent of your
Recordsource.

HTH,

My problem is that I want in vba set the recordsource for my form.
But I do not know how to combine the 2 sql's into one string so I can set
it as recordsource for my form.

Normally I do something like
strSQL="SELECT * FROM tblTest WHERE ..."
I build up the SQL and set the recordsource.
me.recourdsource=strSQL

But up to now I always used only one query.
My form should be based on 2 queries because I need a SumOfQuantity field
as said earlier.

I do not know how to combine this into one SQL?
If not possible then I must use the saved queries I think.

Rene

Open your form in design view. Look at the Recordsource property. If you
activate the drop-down for the property, you should see stored queries
as legitimate choices, along with tables. Simply select "qryShortage"
from the list (only Select queries will be listed. "Action" queries
(Append, Delete, Insert) will not be listed since they don't return
records.)

If you are trying to set the Recordsource property via code, it expects
a string so don't forget the quotes:
Me.Recordsource="qryShortage"

HTH,

I have made a 2nd query in design view and have the result I wanted
but...
I would like to use this 2nd query as recourdsource for a form in vba.

1st qryTotal: SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS
SumOfQuantity...
2nd qryShortage: SELECT qryTotal.SumOfQuantity, tblStandard.Required,
[Required]-[SumOfQuantity] AS [Shortage]...

Is it possible not to use a saved query and use the SQL(=qryShortage)
as
recourdsource in vba?
When I have only one SQL it is no problem but I do not know how to to
it
with the second SQL based on the first.
How do I refer my 2nd SQL to my 1st SQL?

Now I want something like:
me.recourdsource=qryShortage

Rene

 

[George Nicholson]

From the information you provided:
1) qryTotal returns a SumOfQuantity *for each InventoryID*
2) SumOfQuantity is used in qryShortage to calculate [Shortage].

If InventoryID can't be added to qryShortage, I have no idea what you are
doing. Without InventoryID tieing things together I don't see how the
calculation of [Shortage] can be 1) accurate and/or b) of any possible value
(apples, meet oranges...).

At this point I have no choice but to take your word that it can't be done
and simply assume that there is more to your queries that you haven't posted
and that I've been working on false assumptions or incomplete information.

Sorry I couldn't be of more help.

I can not add my field from tblStandard to the qryTotal because this is a
group query thus resulting in a SumOfQuantity field.
I can not refer to this field direct and need a 2nd query.
1st querie sums a total and with the 2nd query I check if enough supply in
store.

I think I just try to have the qryTotal saved and setting the
recourdsource with SQL in VBA based on the qryTotal.
I was hoping I did not need a saved query but in this case seems
unavoidable.

Thanks

Rene

My form should be based on 2 queries because I need a SumOfQuantity
field as said earlier.

Well, you can't base it on 2 queries. But from the information you have
provided, I think you can create one query that has all the information
you require.

The only thing I can see in qryTotal that isn't in qryShortage is ID.
Can't you just add ID to qryShortage and use that for your recordsource?
I'm guessing you're already using ID to join qryTotal and tblStandard
(you have to be using something for a join...), so it should be simple to
add it to the result set.

If there are additional complexities that you haven't shared here, you
might be able to incorporate DSum() into a solution independent of your
Recordsource.

HTH,

My problem is that I want in vba set the recordsource for my form.
But I do not know how to combine the 2 sql's into one string so I can
set it as recordsource for my form.

Normally I do something like
strSQL="SELECT * FROM tblTest WHERE ..."
I build up the SQL and set the recordsource.
me.recourdsource=strSQL

But up to now I always used only one query.
My form should be based on 2 queries because I need a SumOfQuantity
field as said earlier.

I do not know how to combine this into one SQL?
If not possible then I must use the saved queries I think.

Rene

Open your form in design view. Look at the Recordsource property. If
you activate the drop-down for the property, you should see stored
queries as legitimate choices, along with tables. Simply select
"qryShortage" from the list (only Select queries will be listed.
"Action" queries (Append, Delete, Insert) will not be listed since they
don't return records.)

If you are trying to set the Recordsource property via code, it expects
a string so don't forget the quotes:
Me.Recordsource="qryShortage"

HTH,

I have made a 2nd query in design view and have the result I wanted
but...
I would like to use this 2nd query as recourdsource for a form in vba.

1st qryTotal: SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS
SumOfQuantity...
2nd qryShortage: SELECT qryTotal.SumOfQuantity, tblStandard.Required,
[Required]-[SumOfQuantity] AS [Shortage]...

Is it possible not to use a saved query and use the SQL(=qryShortage)
as
recourdsource in vba?
When I have only one SQL it is no problem but I do not know how to to
it
with the second SQL based on the first.
How do I refer my 2nd SQL to my 1st SQL?

Now I want something like:
me.recourdsource=qryShortage

Rene