SQL prefixes

  • Thread starter Thread starter Chris Motherwell via AccessMonster.com
  • Start date Start date
C

Chris Motherwell via AccessMonster.com

I've created a form that contains 4 subforms. I am referencing a control on
the third subform in a query. The following is the message I receive:

Server: Msg 117, Level 15, State 2, Procedure qryInvoice, Line 14
The number name 'Forms.frmCustomersMain.subfProjects.Form.Tasks Subform.Form'
contains more than the maximum number of prefixes. The maximum is 3.

Is there a way to give .frmCustomersMain.subfProjects.Form.Tasks Subform.Form
another name? Does anyone have any other sggestions?


The Query is:
CREATE VIEW qryInvoice
AS

SELECT qryPreInvoice.Project_ID, qryPreInvoice.InvoiceID, qryPreInvoice.
Project_Name, qryPreInvoice.Company, qryPreInvoice.First_Name, qryPreInvoice.
Last_Name, qryPreInvoice.Address, qryPreInvoice.City, qryPreInvoice.Prov,
qryPreInvoice.PCode, qryPreInvoice.Task, qryPreInvoice.Associate,
qryPreInvoice.Date, qryPreInvoice.Details, qryPreInvoice.Comments,
qryPreInvoice.Legal_Description, qryPreInvoice.Scope, qryInvSum.
SumOfMiles_Amt, qryInvSum.HoursDM, qryInvSum.HoursOth, qryInvSum.
SumOfPhoto_Amt, qryInvSum.SumOfPostage, qryInvSum.SumOfFerry_Fare, qryInvSum.
SumOfTaxi_Fare, qryInvSum.SumOfPrints, qryInvSum.SumOfCourier, qryInvSum.
SumOfAir_Fare, qryInvSum.SumOfPublications, qryInvSum.SumOfVehicle_Rental,
qryInvSum.SumOfParking, qryInvSum.SumOfLong_Distance_Phone, [SumOfMiles_Amt]+
[SumOfPhoto_Amt]+[SumOfPostage]+[SumOfFerry_Fare]+[SumOfTaxi_Fare]+
[SumOfPrints]+[SumOfCourier]+[SumOfAir_Fare]+[SumOfPublications]+
[SumOfVehicle_Rental]+[SumOfParking]+[SumOfLong_Distance_Phone] AS [Sum],
qryInvSum.SumOfPhotocopies, qryInvSum.SumOfMileage, qryPreInvoice.
ProjectAddress, qryPreInvoice.SE, qryPreInvoice.ST, qryPreInvoice.
Invoice_Paid, [HoursDM]*[SECharge_out] AS SETot, [HoursOth]*[STCharge_Out] AS
STTot, qryPreInvoice.SECharge_Out, qryPreInvoice.STCharge_Out
FROM qryPreInvoice INNER JOIN qryInvSum ON qryPreInvoice.InvoiceID =
qryInvSum.InvoiceID
WHERE (((qryPreInvoice.InvoiceID)=[Forms].[frmCustomersMain].[subfProjects].
[Form].[Tasks Subform].[Form].[InvoiceID]))
ORDER BY qryPreInvoice.InvoiceID
 
Your problem comes from a misunderstanding of SQL-Server: you cannot use a
reference to any of your Access form controls in SQL-Server because it
doesn't know anything about them; so things like
[Forms].[frmCustomersMain].[subfProjects]... are invalid; whathever the
number of prefixes.

You must remove them from your view and use a client or a server filter or
create a stored procedure with a parameter @InvoiceID and use the property
InputParameters to send it:

@InvoiceID int = [Forms]![frmCustomersMain]![subfProjects].[Form]![Tasks
Subform].[Form]![InvoiceID]

Don't forget to set the Record Source Qualifier to dbo if the owner of the
stored procedure is dbo and the UniqueTable property to qryPreInvoice if you
want to make sure that your query will be updatable.

See other posts in this newsgroup (Google) for more information.

Finally, it won't be a bad idea to use an alias for the names of your tables
and using [Sum] as the name of one of your columns is probably calling for
trouble and if this is for a report then the Order By is superfleous because
Access will make its own ordering.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Chris Motherwell via AccessMonster.com said:
I've created a form that contains 4 subforms. I am referencing a control
on
the third subform in a query. The following is the message I receive:

Server: Msg 117, Level 15, State 2, Procedure qryInvoice, Line 14
The number name 'Forms.frmCustomersMain.subfProjects.Form.Tasks
Subform.Form'
contains more than the maximum number of prefixes. The maximum is 3.

Is there a way to give .frmCustomersMain.subfProjects.Form.Tasks
Subform.Form
another name? Does anyone have any other sggestions?


The Query is:
CREATE VIEW qryInvoice
AS

SELECT qryPreInvoice.Project_ID, qryPreInvoice.InvoiceID, qryPreInvoice.
Project_Name, qryPreInvoice.Company, qryPreInvoice.First_Name,
qryPreInvoice.
Last_Name, qryPreInvoice.Address, qryPreInvoice.City, qryPreInvoice.Prov,
qryPreInvoice.PCode, qryPreInvoice.Task, qryPreInvoice.Associate,
qryPreInvoice.Date, qryPreInvoice.Details, qryPreInvoice.Comments,
qryPreInvoice.Legal_Description, qryPreInvoice.Scope, qryInvSum.
SumOfMiles_Amt, qryInvSum.HoursDM, qryInvSum.HoursOth, qryInvSum.
SumOfPhoto_Amt, qryInvSum.SumOfPostage, qryInvSum.SumOfFerry_Fare,
qryInvSum.
SumOfTaxi_Fare, qryInvSum.SumOfPrints, qryInvSum.SumOfCourier, qryInvSum.
SumOfAir_Fare, qryInvSum.SumOfPublications, qryInvSum.SumOfVehicle_Rental,
qryInvSum.SumOfParking, qryInvSum.SumOfLong_Distance_Phone,
[SumOfMiles_Amt]+
[SumOfPhoto_Amt]+[SumOfPostage]+[SumOfFerry_Fare]+[SumOfTaxi_Fare]+
[SumOfPrints]+[SumOfCourier]+[SumOfAir_Fare]+[SumOfPublications]+
[SumOfVehicle_Rental]+[SumOfParking]+[SumOfLong_Distance_Phone] AS [Sum],
qryInvSum.SumOfPhotocopies, qryInvSum.SumOfMileage, qryPreInvoice.
ProjectAddress, qryPreInvoice.SE, qryPreInvoice.ST, qryPreInvoice.
Invoice_Paid, [HoursDM]*[SECharge_out] AS SETot, [HoursOth]*[STCharge_Out]
AS
STTot, qryPreInvoice.SECharge_Out, qryPreInvoice.STCharge_Out
FROM qryPreInvoice INNER JOIN qryInvSum ON qryPreInvoice.InvoiceID =
qryInvSum.InvoiceID
WHERE
(((qryPreInvoice.InvoiceID)=[Forms].[frmCustomersMain].[subfProjects].
[Form].[Tasks Subform].[Form].[InvoiceID]))
ORDER BY qryPreInvoice.InvoiceID
Click to expand...
 
Sylvain:
I don't know how to use the filter you mentioned or if I use stored
procedure, how do I use it to filter the query? Thanks
 
Back
Top