Multiple Query ?????? 2

[Animaniac]

Hi there,
Could anyone out there help me with a problem that I've just inherited . At present we have a humungous white elephant of a database that only contains one HUGE.. Table,
which my boss wants referenced via query , sql or code .
The way i can currently access the records he wants is via SQL as detailed below .
SELECT FAR2.ID, FAR2.[Opened Date], FAR2.[Unit Under Test], FAR2.[Drawing No], FAR2.[Unit Serial No], FAR2.QTY, FAR2.REFERENCE, FAR2.[FAILED PART ASSY], FAR2.[FAILED ASSY DRG], FAR2.[FAILED ASSY SERIAL NO], FAR2.[SUB ASSEMBLY], FAR2.[SUB ASSY DRG NO], FAR2.[SUB SERIAL NO], FAR2.[FAIL STAGE], FAR2.ENVIRONMENT, FAR2.[RUNNING HOURS], FAR2.[PROJECT/PRODUCT], FAR2.[BATCH NO], FAR2.[SUB-ASSY TEST], FAR2.[UNIT TEST], FAR2.RSP, FAR2.[POST RSP], FAR2.[SYSTEM TEST], FAR2.[DETAILS OF FAILURE], FAR2.[CAUSE OF FAILURE], FAR2.[ACTION PROPOSED], FAR2.[REPLACEMENT PART], FAR2.[REPLACEMENT DRAWING], FAR2.[REPLACEMENT SERIAL NO], FAR2.ID1, FAR2.FAULT, FAR2.[DQN/ECR REQUIRED], FAR2.[DCR/ECR REQUIRED], FAR2.[CONCESSION REQUIRED], FAR2.[RAISED BY], FAR2.[RAISED DATE], FAR2.[REWORKED BY], FAR2.[REWORKED DATE], FAR2.[INSPECTED BY], FAR2.[INSPECTED DATE], FAR2.[RETESTED BY], FAR2.[RETEST DATE], FAR2.[ANALYSIS/FURTHER ACTION], FAR2.[SOLDERING FAULT], FAR2.[WIRING FAULT], FAR2.DOCUMENTATION, FAR2.DAMAGE, FAR2.CLEANLINESS, FAR2.IDENTIFICATION, FAR2.[MISSING ITEMS], FAR2.[SHORTAGE FAULTS], FAR2.[INCORRECTLY ASSEMBLED], FAR2.[DESIGN FAULT], FAR2.[INCORRECT ITEM], FAR2.[FINISHING FAULT], FAR2.[OPERATIONS MISSED], FAR2.[COMPONENT FAILURE], FAR2.[BONDING OR WELDING FAULTS], FAR2.[PROCESS FAILURE], FAR2.[SEALING:LEAK/PRESSURE], FAR2.[NO FAULT FOUND], FAR2.[UNDER INVESTIGATION], FAR2.DQN, FAR2.DCR, FAR2.CONCESSION, FAR2.[FAULT CLEARED], FAR2.[Prod/Eng], FAR2.[Incident Code], FAR2.[Failure Code], FAR2.[Defect Class Code], FAR2.Issue, FAR2.[Works Order No], FAR2.[Sample Size], FAR2.[Number of Incidents], FAR2.[Component Ident], FAR2.[Sub Issue], FAR2.[Circuit Reference], FAR2.[Ecp/dci number], FAR2.[R & M Log No], FAR2.[Log Date], FAR2.Env2, FAR2.[Parent FAR], FAR2.[Child FAR], FAR2.[Corrective Date], FAR2.Status1, FAR2.[Signed Off], FAR2.[Signed off date]
FROM FAR2
WHERE (((FAR2.[FAILED ASSY DRG])=[Please type in failed Assy No]) AND ((FAR2.[FAILED ASSY SERIAL NO])=[Please type in Unit Serial No])) OR (((FAR2.[Drawing No])=[Please type in UUT No]) AND ((FAR2.[Unit Serial No])=[Please type in UUT SER No])) OR (((FAR2.[SUB ASSY DRG NO])=[Please type in Sub Assy No]) AND ((FAR2.[SUB SERIAL NO])=[Please type in Sub Assy Serial No]));

The problem is i can do this the SQL way and he has to type the part No and the serial No 3 times ( and it works fine), and he just wants to type in the PART No Once and the Serial No Once ( as in the query he wants to run the other two instances are duplicates of the first two entries.) , is there any way that i could do this in code , as i have been trying but i seem to go round in circles .

Thanx for any comments or suggestions.

 

[JerryDennison]

Use an unbound form to capture the parameters you want in unbound controls. Then substitute the address to the controls in your SQL.

...((FAR2.[SUB SERIAL NO])=Forms![FormName]![SerialNumberControlName]));

 

[turtlemaster]

Before you try this make sure that all of your parameters are spelled exactly the same to rule out that as your problem.

You can create a function in a module that prompts the user for a value and returns it to your query.

in module:

function PartNumber()
PartNumber = inputbox("Enter Part Number}
end function

in query:

... SQL Statement ... WHERE FAR2.[FAILED ASSY DRG]=PartNumber()

Nick