Query By Form, Master, SubForm, SubSubForm

[Red Rider]

I have the following forms: frmContracts, inside that is frmInvoice
and inside that is frmInvoiceDetail_1. I’m trying to display the
results of the active invoice. Since there are only two records in
the frmInvoiceDetail_2 form that’s all that I should see but no matter
what I enter as a parameter I can’t get this to work correctly. The
query button is being executed from the frmInvoice form. The
following setup popups a parameter box asking me to enter a value,
which I don’t want to happen.

SELECT tblContracts.ContractID, tblInvoiceDetail_2.InvoiceDetailID,
tblInvoiceDetail_2.TransServiceName,
tblInvoiceDetail_2.ContractNumber, tblInvoiceDetail_2.ChargeType,
tblInvoiceDetail_2.Capacity, tblInvoiceDetail_2.Rate,
tblInvoice.InvoiceID
FROM (tblContracts INNER JOIN tblInvoice ON tblContracts.ContractID =
tblInvoice.ContractID) INNER JOIN tblInvoiceDetail_2 ON
tblInvoice.InvoiceID = tblInvoiceDetail_2.InvoiceID_2
WHERE (((tblContracts.ContractID) Like IIf(IsNull([Forms]!
[frmContracts]![ContractID]),"*",[Forms]![frmContracts]![ContractID] &
"*")) AND ((tblInvoice.InvoiceID) Like IIf(IsNull([Forms]!
[frmContracts]![frmInvoice].[Form]![InvoiceID]),"*",[Forms]!
[frmContracts]![frmInvoice].[Form]![InvoiceID] & "*")));

This version has no popup value box and has no errors but doesn’t read
the [InvoiceID] textbox so I get unrelated invoice records in my
result.

SELECT tblContracts.ContractID, tblInvoiceDetail_2.InvoiceDetailID,
tblInvoiceDetail_2.TransServiceName,
tblInvoiceDetail_2.ContractNumber, tblInvoiceDetail_2.ChargeType,
tblInvoiceDetail_2.Capacity, tblInvoiceDetail_2.Rate,
tblInvoice.InvoiceID
FROM (tblContracts INNER JOIN tblInvoice ON tblContracts.ContractID =
tblInvoice.ContractID) INNER JOIN tblInvoiceDetail_2 ON
tblInvoice.InvoiceID = tblInvoiceDetail_2.InvoiceID_2
WHERE (((tblContracts.ContractID) Like IIf(IsNull([Forms]!
[frmContracts]![ContractID]),"*",[Forms]![frmContracts]![ContractID] &
"*")) AND ((tblInvoice.InvoiceID)=[InvoiceID]));

I have tried many different entries but can’t get any to work. Any
ideas?

 

[MGFoster]

I have the following forms: frmContracts, inside that is frmInvoice
and inside that is frmInvoiceDetail_1. I’m trying to display the
results of the active invoice. Since there are only two records in
the frmInvoiceDetail_2 form that’s all that I should see but no matter
what I enter as a parameter I can’t get this to work correctly. The
query button is being executed from the frmInvoice form. The
following setup popups a parameter box asking me to enter a value,
which I don’t want to happen.

SELECT tblContracts.ContractID, tblInvoiceDetail_2.InvoiceDetailID,
tblInvoiceDetail_2.TransServiceName,
tblInvoiceDetail_2.ContractNumber, tblInvoiceDetail_2.ChargeType,
tblInvoiceDetail_2.Capacity, tblInvoiceDetail_2.Rate,
tblInvoice.InvoiceID
FROM (tblContracts INNER JOIN tblInvoice ON tblContracts.ContractID =
tblInvoice.ContractID) INNER JOIN tblInvoiceDetail_2 ON
tblInvoice.InvoiceID = tblInvoiceDetail_2.InvoiceID_2
WHERE (((tblContracts.ContractID) Like IIf(IsNull([Forms]!
[frmContracts]![ContractID]),"*",[Forms]![frmContracts]![ContractID] &
"*")) AND ((tblInvoice.InvoiceID) Like IIf(IsNull([Forms]!
[frmContracts]![frmInvoice].[Form]![InvoiceID]),"*",[Forms]!
[frmContracts]![frmInvoice].[Form]![InvoiceID] & "*")));

This version has no popup value box and has no errors but doesn’t read
the [InvoiceID] textbox so I get unrelated invoice records in my
result.

SELECT tblContracts.ContractID, tblInvoiceDetail_2.InvoiceDetailID,
tblInvoiceDetail_2.TransServiceName,
tblInvoiceDetail_2.ContractNumber, tblInvoiceDetail_2.ChargeType,
tblInvoiceDetail_2.Capacity, tblInvoiceDetail_2.Rate,
tblInvoice.InvoiceID
FROM (tblContracts INNER JOIN tblInvoice ON tblContracts.ContractID =
tblInvoice.ContractID) INNER JOIN tblInvoiceDetail_2 ON
tblInvoice.InvoiceID = tblInvoiceDetail_2.InvoiceID_2
WHERE (((tblContracts.ContractID) Like IIf(IsNull([Forms]!
[frmContracts]![ContractID]),"*",[Forms]![frmContracts]![ContractID] &
"*")) AND ((tblInvoice.InvoiceID)=[InvoiceID]));

I have tried many different entries but can’t get any to work. Any
ideas?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sometimes the queries don't "understand" the parameters so we declare
the parameters like this:

PARAMETERS Forms!frmContracts!ContractID Text,
Forms!frmContracts!frmInvoice!InvoiceID Text;
SELECT ... etc. ...

If these values are some other data type, substitute the correct data
type.

Make the WHERE clause like this:

WHERE tblContracts.ContractID Like Nz(Forms!frmContracts!ContractID +
"*", "*")
AND tblInvoice.InvoiceID Like Nz(Forms!frmContracts!frmInvoice!InvoiceID
+ "*", "*")

The "+" sign with the form reference will return NULL if the form
reference value is NULL. It will return the string plus the "*" if the
reference value is not NULL. The Nz() function will return the form
reference value plus the "*" if the form reference value is not NULL, or
just the "*" if the reference value is NULL.

To test the form reference - make sure the form is open, and there is
data in the InvoiceID TextBox; then open the Debug window (Ctrl-G) and
type in this:

? Forms!frmContracts!frmInvoice!InvoiceID <hit the Enter key>

You should get the current value of the InvoiceID, which means the query
should be getting the same thing.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBShT9boechKqOuFEgEQKUBgCeJ6zpaUs7npSPitptfYWqhHgi4+sAoIQz
IMdp7NDnyYpi3GwzyP34O446
=L/Q9
-----END PGP SIGNATURE-----