Subform filter

[corbinap]

I have a subform that I am trying to filter one column based on another as in the Select statement below. I am using a continuous form on the subform and I am trying not to show records in one column that are showing in cmbVeh. I just can't get it to work. It may only filter out one of the Vehicle numbers but not all. Can someone give me ideas why this is not working and maybe a better solution. Thanks in advance.

SELECT Vehicle.VehicleID
FROM Vehicles
WHERE (((Vehicle.VehicleID)<>[Forms]![Vehicles]![Vehiclesubform].[Form]![cmbVeh]))

 

[MajP]

the value of this
[Forms]![Vehicles]![Vehiclesubform].[Form]![cmbVeh]
is the value of the current record.

You will probably need to use a form of "not in" query. {untested}

SELECT VehicleID FROM Vehicles
WHERE VehcileID
NOT IN (SELECT VehicleID From Vehicles Where TheKeyToMainForm = forms![MainForm]![txtBxPrimaryKey] )

The second query returns the same records as the subform. If the subform is linked to the Master form the where condition should be basically what links the main to sub.

 

[MajP]

http://office.microsoft.com/en-us/access/HP010322711033.aspx

Also as you add records to the subform, ensure that you requery the list.

 

[TheAceMan1]

How are ya corbinap . . .

It would help if you state how you want the filter to work! . . . via the field of the current record, specific values or some other method?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[corbinap]

Let me see if I can.

They are two numeric fields. Not a combo box (although it is named a cmb* now).

VehId Assigned (Combo)
101
102
103

I have a continous subform where I need the Vehicles numbers in the Assigned column not to show the vehid's that are in the VehID column. It is not logically difficult - I just can't figue the magic combination.

 

[MajP]

The term "columns" has different meanings in Access and this is causing confusion. I assume you have two "fields": "Assigned" and "Vehicles". "Vehicles" input is a combobox, and you want the rowsource of the Vehicles combo not to include records that are already assigned.

 

[corbinap]

Yes - thank you. That is correct..