combining results from two seperate forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to combine results of two seperate forms into one result set.
I know in a query you can do this with a UNION but can you also do the same
with form datasheet results?

Thanks
 
Dave

I don't understand "results of two separate forms". If you have two
separate queries (for two separate forms), why can't you use a UNION on the
queries?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
dave said:
Is there a way to combine results of two seperate forms into one result set.
I know in a query you can do this with a UNION but can you also do the same
with form datasheet results?

Thanks
Click to expand...
 
Jeff,
To try to make a long story short I have two list boxes that return results
to a Union query. In one of the list boxes I was trying to create an IN
statement with a loop code below. I could not read the IN statement to be
read corretly into the query. It is my understanding that I could not do this
because in a SQL statement, it creates an implicit parameter for that query,
Thus not giving me the correct results. It was recommended to try bringing
the results into a form because of the flexibilty of the 4rth parameter.
Well this all works great now I have 2 forms seperate forms with proper data
but I still need somehow to bring the results together.

Code to get values for the list box.

Dim strIN As String, vitem As Variant

For Each vitem In List0.ItemsSelected
strIN = strIN & "'" & List0.Column(0, vitem) & "',"
VTEST = vitem
Next vitem
'remove the last comma

If VTEST <> 0 Then
strIN = Left(strIN, Len(strIN) - 1)
End If

'Where clause for form
strWhere = "nha in (" & strIN & ")"

End If

Union Query -
SELECT [Bradley BOM's].CONTRACT, [Bradley BOM's].END_ITEM, [Bradley
BOM's].NHA, [Bradley BOM's].NHA_PCC, [Bradley BOM's].NHA_SRCPLT, [Bradley
BOM's].ITEM_NO, [Bradley BOM's].PART, [Bradley BOM's].DESCR, [Bradley
BOM's].PCC, [Bradley BOM's].SOURCEPLT, [Bradley BOM's].QTY_PER, [Bradley
BOM's].QPV, [Bradley BOM's].ISSUE_UOM, [Bradley BOM's].PUR_UOM, [Bradley
BOM's].ECN_FROM, [Bradley BOM's].EFF_FROM, [Bradley BOM's].EFF_THRU, [Bradley
BOM's].ECN_THRU, [Bradley BOM's].WORKCENTER, [Bradley BOM's].OPER, [Bradley
BOM's].LTOFFSET, [Bradley BOM's].POSTDEDUCT, [Bradley BOM's].RTG_PRIOR,
[Bradley BOM's].PNCATEGORY, [Bradley BOM's].FROM_ASSY, [Bradley
BOM's].EFFFROM_OV, [Bradley BOM's].EFFTHRU_OV, [Bradley BOM's].THRU_ASSY,
[Bradley BOM's].PLANNER
FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].WORKCENTER) Like forms!WorkCenterandNHAKit!List2)
And (([Bradley BOM's].EFFFROM_OV)<=Forms!Form1!Text77) And (([Bradley
BOM's].EFFTHRU_OV)>=Forms!Form1!Text79) And (([Bradley BOM's].TOP_BGEXPN) Is
Null) And ((IIf(forms!form1!list72 Is Null Or
forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is Null
Or forms!form1!list74=[Contract],1,0))=1)) Or ((([Bradley BOM's].WORKCENTER)
Like forms!WorkCenterandNHAKit!List2) And (([Bradley
BOM's].EFFFROM_OV)<=Forms!Form1!Text77) And (([Bradley BOM's].EFFTHRU_OV)
Between Forms!Form1!Text77 And Forms!Form1!Text79) And (([Bradley
BOM's].TOP_BGEXPN) Is Null) And ((IIf(forms!form1!list72 Is Null Or
forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is Null
Or forms!form1!list74=[Contract],1,0))=1)) Or ((([Bradley BOM's].WORKCENTER)
Like forms!WorkCenterandNHAKit!List2) And (([Bradley BOM's].EFFFROM_OV)
Between Forms!Form1!Text77 And Forms!Form1!Text79) And (([Bradley
BOM's].EFFTHRU_OV)>=Forms!Form1!Text79) And (([Bradley BOM's].TOP_BGEXPN) Is
Null) And ((IIf(forms!form1!list72 Is Null Or
forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is Null
Or forms!form1!list74=[Contract],1,0))=1)) Or ((([Bradley BOM's].WORKCENTER)
Like forms!WorkCenterandNHAKit!List2) And (([Bradley BOM's].EFFFROM_OV)
Between Forms!Form1!Text77 And Forms!Form1!Text79) And (([Bradley
BOM's].EFFTHRU_OV) Between Forms!Form1!Text77 And Forms!Form1!Text79) And
(([Bradley BOM's].TOP_BGEXPN) Is Null) And ((IIf(forms!form1!list72 Is Null
Or forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is
Null Or forms!form1!list74=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley BOM's].END_ITEM

UNION SELECT [Bradley BOM's].CONTRACT,
[Bradley BOM's].END_ITEM,
[Bradley BOM's].NHA,
[Bradley BOM's].NHA_PCC,
[Bradley BOM's].NHA_SRCPLT,
[Bradley BOM's].ITEM_NO,
[Bradley BOM's].PART,
[Bradley BOM's].DESCR,
[Bradley BOM's].PCC,
[Bradley BOM's].SOURCEPLT,
[Bradley BOM's].QTY_PER,
[Bradley BOM's].QPV,
[Bradley BOM's].ISSUE_UOM,
[Bradley BOM's].PUR_UOM,
[Bradley BOM's].ECN_FROM,
[Bradley BOM's].EFF_FROM,
[Bradley BOM's].EFF_THRU,
[Bradley BOM's].ECN_THRU,
[Bradley BOM's].WORKCENTER,
[Bradley BOM's].OPER,
[Bradley BOM's].LTOFFSET,
[Bradley BOM's].POSTDEDUCT,
[Bradley BOM's].RTG_PRIOR,
[Bradley BOM's].PNCATEGORY,
[Bradley BOM's].FROM_ASSY,
[Bradley BOM's].EFFFROM_OV,
[Bradley BOM's].EFFTHRU_OV,
[Bradley BOM's].THRU_ASSY,
[Bradley BOM's].PLANNER

FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].NHA) Like forms!WorkCenterandNHAKit!List0) AND
(([Bradley BOM's].EFFFROM_OV)<=[Forms]![Form1]![Text77]) AND (([Bradley
BOM's].EFFTHRU_OV)>=[Forms]![Form1]![Text79]) AND (([Bradley
BOM's].TOP_BGEXPN) Is Null) AND ((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1)) OR ((([Bradley BOM's].NHA) Like
forms!WorkCenterandNHAKit!List0) AND (([Bradley
BOM's].EFFFROM_OV)<=[Forms]![Form1]![Text77]) AND (([Bradley
BOM's].EFFTHRU_OV) Between [Forms]![Form1]![Text77] And
[Forms]![Form1]![Text79]) AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1)) OR ((([Bradley BOM's].NHA) Like
forms!WorkCenterandNHAKit!List0) AND (([Bradley BOM's].EFFFROM_OV) Between
[Forms]![Form1]![Text77] And [Forms]![Form1]![Text79]) AND (([Bradley
BOM's].EFFTHRU_OV)>=[Forms]![Form1]![Text79]) AND (([Bradley
BOM's].TOP_BGEXPN) Is Null) AND ((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1)) OR ((([Bradley BOM's].NHA) Like
forms!WorkCenterandNHAKit!List0) AND (([Bradley BOM's].EFFFROM_OV) Between
[Forms]![Form1]![Text77] And [Forms]![Form1]![Text79]) AND (([Bradley
BOM's].EFFTHRU_OV) Between [Forms]![Form1]![Text77] And
[Forms]![Form1]![Text79]) AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley BOM's].END_ITEM;
 
Back
Top