HELP!

[Lori]

Okay, I've finally gotten my subform to work the way I want, but I have one
problem that is more annoying that anything else. Both the main form and the
subform are based on a query which asks the user "Which Project #?", now here
is the annoying part, every time I change records it asks "Which Project" and
if I'm updating it asks for EVERY SINGLE FIELD! HELP

 

[John Vinson]

Okay, I've finally gotten my subform to work the way I want, but I have one
problem that is more annoying that anything else. Both the main form and the
subform are based on a query which asks the user "Which Project #?", now here
is the annoying part, every time I change records it asks "Which Project" and
if I'm updating it asks for EVERY SINGLE FIELD! HELP

Lori, if you're assuming that all the volunteers here are intimately
knowledgable about your code and your form, and that none of us are
distracted by other questions or by our own work, you're mistaken.

Please post the SQL of your query, and your code.

John W. Vinson[MVP]

 

[Lori]

Okay the sql is below. I have the query separating by project # to naturally
filter the data for ease of use. I believe you helped me with one of these
formulae.

SELECT [Scope Changes].[Project#], [Scope Changes].SCID, [Scope
Changes].[SC#], [Scope Changes].[Date Submitted], [Scope Changes].[#], [Scope
Changes].[CO#], [Scope Changes].LineItemValue, [Scope Changes].Subcontractor,
[Scope Changes].Description, [Scope Changes].[Cost Code], [Scope
Changes].[Exception?], [Scope Changes].GenCon, [Scope Changes].Insurance,
[Scope Changes].BondCont, [Scope Changes].JSCGFee, ([Scope
Changes]!LineItemValue*Jobsites!GC) AS [General], IIf([Scope
Changes]![Exception?]=0,([General]),([Scope Changes]!GenCon)) AS GEN,
(([Scope Changes]!LineItemValue+[General])*Jobsites!CGL) AS INS, IIf([Scope
Changes]![Exception?]=0,([INS]),([Scope Changes]!Insurance)) AS GenIns,
IIf([Cont-Bond]=-1,([LineItemValue]*[Bond-Cont]),(([LineItemValue]+[General]+[INS])*[Bond-Cont]))
AS BOND, IIf([Scope Changes]![Exception?]=0,([BOND]),([Scope
Changes]!BondCont)) AS BOCO,
IIf([Cont-Bond]=-1,(([LineItemValue]+[General]+[INS])*[Fee]),(([LineItemValue]+[General]+[INS]+[BOND])*[Fee]))
AS JSCG, IIf([Scope Changes]![Exception?]=0,([JSCG]),([Scope
Changes]!JSCGFee)) AS JFEE, ([LineItemValue]+[GEN]+[GenIns]+[BOCO]+[JFEE]) AS
TOTAL, [Scope Changes].Status, [Scope Changes].[Date Submitted], Jobsites.GC,
Jobsites.CGL, Jobsites.[Bond-Cont], Jobsites.Fee, Jobsites.[Cont-Bond],
Jobsites.ProjectName, Jobsites.[Phone#], Jobsites.[Fax#], Jobsites.[EFax#],
Jobsites.[Jobsite Address], Jobsites.JobsiteCity, Jobsites.JobsiteState,
Jobsites.Zip, Jobsites.Company, Jobsites.OwnerAddress1,
Jobsites.OwnerAddress2, Jobsites.Salute, Jobsites.[Owner's Rep], [Scope
Changes].[Signed by Owner], [Scope Changes].[Signed by Rep], [Scope
Changes].Signature, [Scope Changes].ExecReview
FROM Jobsites INNER JOIN [Scope Changes] ON Jobsites.[Project#] = [Scope
Changes].[Project#]
WHERE ((([Scope Changes].[Project#])=["Which Project?"]))
ORDER BY [Scope Changes].[SC#];

 

[Scuda]

Lori, I had this same problem not long ago. Make sure your query (or report)
does not have fields in it that no longer exist or have changed names.

Okay the sql is below. I have the query separating by project # to naturally
filter the data for ease of use. I believe you helped me with one of these
formulae.

SELECT [Scope Changes].[Project#], [Scope Changes].SCID, [Scope
Changes].[SC#], [Scope Changes].[Date Submitted], [Scope Changes].[#], [Scope
Changes].[CO#], [Scope Changes].LineItemValue, [Scope Changes].Subcontractor,
[Scope Changes].Description, [Scope Changes].[Cost Code], [Scope
Changes].[Exception?], [Scope Changes].GenCon, [Scope Changes].Insurance,
[Scope Changes].BondCont, [Scope Changes].JSCGFee, ([Scope
Changes]!LineItemValue*Jobsites!GC) AS [General], IIf([Scope
Changes]![Exception?]=0,([General]),([Scope Changes]!GenCon)) AS GEN,
(([Scope Changes]!LineItemValue+[General])*Jobsites!CGL) AS INS, IIf([Scope
Changes]![Exception?]=0,([INS]),([Scope Changes]!Insurance)) AS GenIns,
IIf([Cont-Bond]=-1,([LineItemValue]*[Bond-Cont]),(([LineItemValue]+[General]+[INS])*[Bond-Cont]))
AS BOND, IIf([Scope Changes]![Exception?]=0,([BOND]),([Scope
Changes]!BondCont)) AS BOCO,
IIf([Cont-Bond]=-1,(([LineItemValue]+[General]+[INS])*[Fee]),(([LineItemValue]+[General]+[INS]+[BOND])*[Fee]))
AS JSCG, IIf([Scope Changes]![Exception?]=0,([JSCG]),([Scope
Changes]!JSCGFee)) AS JFEE, ([LineItemValue]+[GEN]+[GenIns]+[BOCO]+[JFEE]) AS
TOTAL, [Scope Changes].Status, [Scope Changes].[Date Submitted], Jobsites.GC,
Jobsites.CGL, Jobsites.[Bond-Cont], Jobsites.Fee, Jobsites.[Cont-Bond],
Jobsites.ProjectName, Jobsites.[Phone#], Jobsites.[Fax#], Jobsites.[EFax#],
Jobsites.[Jobsite Address], Jobsites.JobsiteCity, Jobsites.JobsiteState,
Jobsites.Zip, Jobsites.Company, Jobsites.OwnerAddress1,
Jobsites.OwnerAddress2, Jobsites.Salute, Jobsites.[Owner's Rep], [Scope
Changes].[Signed by Owner], [Scope Changes].[Signed by Rep], [Scope
Changes].Signature, [Scope Changes].ExecReview
FROM Jobsites INNER JOIN [Scope Changes] ON Jobsites.[Project#] = [Scope
Changes].[Project#]
WHERE ((([Scope Changes].[Project#])=["Which Project?"]))
ORDER BY [Scope Changes].[SC#];

--
Lori A. Pong

Lori, if you're assuming that all the volunteers here are intimately
knowledgable about your code and your form, and that none of us are
distracted by other questions or by our own work, you're mistaken.

Please post the SQL of your query, and your code.

John W. Vinson[MVP]

 

[Lori]

All of the fields are being used with the same names in multiple locations.
Since my report is also based on the same query I'm running into the same
problem where it's asking me for the project # as I view each page of the
report and since each record is a separate page... you can understand the
frustration..
--
Lori A. Pong

Lori, I had this same problem not long ago. Make sure your query (or report)
does not have fields in it that no longer exist or have changed names.

Okay the sql is below. I have the query separating by project # to naturally
filter the data for ease of use. I believe you helped me with one of these
formulae.

SELECT [Scope Changes].[Project#], [Scope Changes].SCID, [Scope
Changes].[SC#], [Scope Changes].[Date Submitted], [Scope Changes].[#], [Scope
Changes].[CO#], [Scope Changes].LineItemValue, [Scope Changes].Subcontractor,
[Scope Changes].Description, [Scope Changes].[Cost Code], [Scope
Changes].[Exception?], [Scope Changes].GenCon, [Scope Changes].Insurance,
[Scope Changes].BondCont, [Scope Changes].JSCGFee, ([Scope
Changes]!LineItemValue*Jobsites!GC) AS [General], IIf([Scope
Changes]![Exception?]=0,([General]),([Scope Changes]!GenCon)) AS GEN,
(([Scope Changes]!LineItemValue+[General])*Jobsites!CGL) AS INS, IIf([Scope
Changes]![Exception?]=0,([INS]),([Scope Changes]!Insurance)) AS GenIns,
IIf([Cont-Bond]=-1,([LineItemValue]*[Bond-Cont]),(([LineItemValue]+[General]+[INS])*[Bond-Cont]))
AS BOND, IIf([Scope Changes]![Exception?]=0,([BOND]),([Scope
Changes]!BondCont)) AS BOCO,
IIf([Cont-Bond]=-1,(([LineItemValue]+[General]+[INS])*[Fee]),(([LineItemValue]+[General]+[INS]+[BOND])*[Fee]))
AS JSCG, IIf([Scope Changes]![Exception?]=0,([JSCG]),([Scope
Changes]!JSCGFee)) AS JFEE, ([LineItemValue]+[GEN]+[GenIns]+[BOCO]+[JFEE]) AS
TOTAL, [Scope Changes].Status, [Scope Changes].[Date Submitted], Jobsites.GC,
Jobsites.CGL, Jobsites.[Bond-Cont], Jobsites.Fee, Jobsites.[Cont-Bond],
Jobsites.ProjectName, Jobsites.[Phone#], Jobsites.[Fax#], Jobsites.[EFax#],
Jobsites.[Jobsite Address], Jobsites.JobsiteCity, Jobsites.JobsiteState,
Jobsites.Zip, Jobsites.Company, Jobsites.OwnerAddress1,
Jobsites.OwnerAddress2, Jobsites.Salute, Jobsites.[Owner's Rep], [Scope
Changes].[Signed by Owner], [Scope Changes].[Signed by Rep], [Scope
Changes].Signature, [Scope Changes].ExecReview
FROM Jobsites INNER JOIN [Scope Changes] ON Jobsites.[Project#] = [Scope
Changes].[Project#]
WHERE ((([Scope Changes].[Project#])=["Which Project?"]))
ORDER BY [Scope Changes].[SC#];

--
Lori A. Pong

On Thu, 12 Oct 2006 12:06:02 -0700, Lori

Okay, I've finally gotten my subform to work the way I want, but I have one
problem that is more annoying that anything else. Both the main form and the
subform are based on a query which asks the user "Which Project #?", now here
is the annoying part, every time I change records it asks "Which Project" and
if I'm updating it asks for EVERY SINGLE FIELD! HELP

Lori, if you're assuming that all the volunteers here are intimately
knowledgable about your code and your form, and that none of us are
distracted by other questions or by our own work, you're mistaken.

Please post the SQL of your query, and your code.

John W. Vinson[MVP]

 

[John Vinson]

Okay the sql is below. I have the query separating by project # to naturally
filter the data for ease of use. I believe you helped me with one of these
formulae.

First off, change all the ! to . in table/field references. ! is for
Object references, such as on a Form.

Second - if you don't want it prompting you for the project #, remove
the criterion; or change it to

=[Forms]![YourFormName]![SomeControlName]

Here you DO need the ! because you're referring to an object (a form);
use your own form name and the name of a control on the form
containing the Project # field.

John W. Vinson[MVP]

 

[Lori]

The problem is I need the prompting, ONCE! but I'm getting it no less than
twice everytime I open the form and everytime I move to the next entry. Is
there a way to only get the prompting once? The subform is using parts of the
same query so it prompts for the project number when the form is opened and
then when it accesses the subform. It prompts for the project number at
least a dozen times when I run the report based on this data. There has to
be a way to stop this.
--
Lori A. Pong

Okay the sql is below. I have the query separating by project # to naturally
filter the data for ease of use. I believe you helped me with one of these
formulae.

First off, change all the ! to . in table/field references. ! is for
Object references, such as on a Form.

Second - if you don't want it prompting you for the project #, remove
the criterion; or change it to

=[Forms]![YourFormName]![SomeControlName]

Here you DO need the ! because you're referring to an object (a form);
use your own form name and the name of a control on the form
containing the Project # field.

John W. Vinson[MVP]

 

[John Vinson]

The problem is I need the prompting, ONCE! but I'm getting it no less than
twice everytime I open the form and everytime I move to the next entry. Is
there a way to only get the prompting once? The subform is using parts of the
same query so it prompts for the project number when the form is opened and
then when it accesses the subform. It prompts for the project number at
least a dozen times when I run the report based on this data. There has to
be a way to stop this.

There is; a very straightforward way. And I posted it.

Did you choose not to try it? Or did you try it and get an error? If
so what error?

Second - if you don't want it prompting you for the project #, remove
the criterion; or change it to

=[Forms]![YourFormName]![SomeControlName]

and use the Form to enter the criteria rather than a prompt.

John W. Vinson[MVP]