Okay, the combo contains 2 columns.
It stores the SNO_ID value, but displays the SNO value.
Your query is applying it against the SNO_ID field, so that looks right.
Let's try declaring the parameter so Access knows the data type:
1. Open table 1008SNO in design view.
What data type is the SNO_ID field?
If Number, also take note of the Field Size (first property in the lower
pane of property design.) Close the table.
2. Open the query in design view.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row such as:
[Forms]![0019_WorkPlanConsult]![Combo6] Long Integer
Use the correct type in the 2nd column.
Save, and then test the query.
If the form is open, and the combo has a value, it should now recognise it.
If it does not recognise it, it will ask you for a value for the parameter.
Let us know how you go.
(I am assuming that this is a normal Access query, not a pass-through
query.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Meitatsu said:
Hi! Allen,
Thank you very much for your answer.
I am using MS Access 2003.
Following your instruction, however it still not works....
Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact/Repair Also tried....
? [Forms]![0019_WorkPlanConsult]![Combo6] Got the right value.
It is still fails, switch the query to SQL View (View menu in query
design.)
Copy the whole SQL statement out to clipboard.
Close, and delete the query.
Compact again.
Now create a new query, switch to SQL View, paste the statement in, and
save
it.
Done, but still failed to resolve the problem.
I NOTICED THAT AFTER ALL THE ABOVE ACTIVITIES, ACCESS STILL ADD A BRACKET
AUTOMATICALLY ON "Form!" after I edit the criterion
Forms![0019_WorkPlanConsult]![Combo6] by wizard.
Last, the SQL statement looks like this:
Query Name: 1001PartsTablePCS
SELECT [1004Block].BLK,
[1003Sub_Asmbly].Sub_Asmbly,
[1002Pre_Asmbly].Pre_Asmbly,
[1001PartsList].Part,
[1001PartsList].Part_Side,
[1001PartsList].Aux,
[1002PartsTablePCS].P,
[1002PartsTablePCS].C,
[1002PartsTablePCS].S,
[1001PartsList].Material,
[1001PartsList].Thick,
[1001PartsList].Weight,
[1008SNO].SNO_ID
FROM (1008SNO
INNER JOIN (1007Mega_Section
INNER JOIN 1006PES
ON [1007Mega_Section].MS_ID = [1006PES].[1007Mega_Section_ID])
ON [1008SNO].SNO_ID = [1007Mega_Section].[1008SNO_ID])
INNER JOIN (1005Pre_Erection
INNER JOIN (1004Block
INNER JOIN (1003Sub_Asmbly
INNER JOIN (1002Pre_Asmbly
INNER JOIN (1002PartsTablePCS
INNER JOIN 1001PartsList
ON [1002PartsTablePCS].PartIDFirst = [1001PartsList].PartID)
ON [1002Pre_Asmbly].PA_ID = [1001PartsList].[1002Pre_Asmbly_ID])
ON [1003Sub_Asmbly].SA_ID = [1002Pre_Asmbly].[1003Sub_Asmbly_ID])
ON [1004Block].BLK_ID = [1003Sub_Asmbly].[1004Block_ID])
ON [1005Pre_Erection].PE_ID = [1004Block].[1005Pre_Erection_ID])
ON [1006PES].PES_ID = [1005Pre_Erection].[1006PES_ID]
WHERE ((([1008SNO].SNO_ID)=[Forms]![0019_WorkPlanConsult]![Combo6]))
ORDER BY [1004Block].BLK,
[1003Sub_Asmbly].Sub_Asmbly,
[1002Pre_Asmbly].Pre_Asmbly,
[1001PartsList].Part;
these properties of the combo:
Control Source: NIL
Row Source:
SELECT [1008SNO].SNO_ID, [1008SNO].SNO
FROM 1008SNO
ORDER BY [1008SNO].SNO;
Bound Column:1
Column Count:2
Column Widths:0;2
Format: Not assigned
I will keep doing trouble shooting.
Thank you very much!
