Select which set of data a form uses

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP PRO.

Here's what I am trying to do. I have a Fuel Card inventory. It has a
History table that knows when a fuel card was assigned and when it was
unassigned.

FCNo, Assign, Unassign.

I want to fill a form (columnar, one record shown at a time.) with the
results of one of two possibilities, with a preference.

If Assigned <> NULL and UNASSIGNED is Null Then
Display Record where this is true for that FCNo
Else if Assigned <> NULL and UNASSIGNED is <> Null then
Display Record where this is true for that FCNo
Else
Fuel Card has never been used, perhaps display a message box with that
piece of info.

endif.

IS THERE ANY GOOD WAY TO DO THIS? What I had been doing is setting up
separate queries to pull this info separately, unioning them, and then
sorting so that the form displays the right one. The problem with this is
that it becomes a read only form because we think you can't update through a
UNION query.

IDEAS?
Thanks

 

[Jeanette Cunningham]

Hi Blue,
I haven't tried this - here is how I would try to do it.
Put the results of the union query in a separate form that the user can open
and choose which record to use to put into the form where you are adding a
new record.
Once the user makes a selection from the second form, grab the primary key
from the selected record and save it in an unbound textbox on the main form.
Close the second form ( the one with the selections ).
Now use that saved primary key to grab that record from the history table
and use it for your main form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[KARL DEWEY]

Maybe I mis-read your IF statement but it seems that in all cases you want to
display the record.
Or could a single FCNo have multiple records and you want to view the
cleanest one?
FCNo Assign Unassign
123 1/1/08 2/1/08
123 3/2/08 - display this one
124 - display this one
125 2/2/08 - display this one

 

[KARL DEWEY]

Try this --
SELECT BlueWolverine.FCNo, Format(Sum([Assign]+0),"Short Date") AS Assigned,
Format(Sum([Unassign]+0),"Short Date") AS Unassigned
FROM BlueWolverine
GROUP BY BlueWolverine.FCNo;