Form filter on two tables

[Paul B.]

I have a form that I need to filter based on two fields on different tables.

I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.

I currently have the following code:

DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"


When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.

The form is bound to the tblVehicleInfo

Thanks in advance

 

[Nikos Yannacopoulos]

Paul,

To begin with, your concatenation and the quotes around it are not correct.
Also, I think you are not referencing the recordsource fields properly. Try
this:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = " & "'T'
AND" & _
" tblVehicles.[CurrentDisposition] = " & "'Available'"

Also, why break down the string just to assign hard-coded values? Why not
just:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = 'T' AND"
& _
" tblVehicles.[CurrentDisposition] = 'Available'"

HTH,
Nikos

 

[Paul B.]

Sorry, tried both, doesn't work. I still get a pop up asking for one or the
other value and it does not filter.

Any other ideas?

Cheers

Paul,

To begin with, your concatenation and the quotes around it are not correct.
Also, I think you are not referencing the recordsource fields properly. Try
this:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = " & "'T'
AND" & _
" tblVehicles.[CurrentDisposition] = " & "'Available'"

Also, why break down the string just to assign hard-coded values? Why not
just:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = 'T' AND"
& _
" tblVehicles.[CurrentDisposition] = 'Available'"

HTH,
Nikos

I have a form that I need to filter based on two fields on different tables.

I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.

I currently have the following code:

DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"


When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.

The form is bound to the tblVehicleInfo

Thanks in advance

 

[Nikos Yannacopoulos]

Paul,

Check the query being the form's recordsource. Any chance you have criteria
asking for parameters in there, or just parameters declared under Query >
Parameters? If yes you should remove them.

HTH,
Nikos

Sorry, tried both, doesn't work. I still get a pop up asking for one or the
other value and it does not filter.

Any other ideas?

Cheers

Paul,

To begin with, your concatenation and the quotes around it are not correct.
Also, I think you are not referencing the recordsource fields properly. Try
this:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = " & "'T'
AND" & _
" tblVehicles.[CurrentDisposition] = " & "'Available'"

Also, why break down the string just to assign hard-coded values? Why not
just:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = 'T' AND"
& _
" tblVehicles.[CurrentDisposition] = 'Available'"

HTH,
Nikos

I have a form that I need to filter based on two fields on different tables.

I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.

I currently have the following code:

DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"


When I click the button, a popup window ask for the

CurrentDisposition,
even

if I type one in, it does not filter.

The form is bound to the tblVehicleInfo

Thanks in advance

 

[John Smith]

You can only filter a form on columns that are in it's recordsource otherwise
Access does not know what you are referring to and asks for the value, so to
make this work you would need to add tblVehicles to the recordsource.

Having said that there is a filter that you could use without changing the
recordsource but you might find the performance unacceptable :-

DoCmd.OpenForm stDocName, , , "VehicleAssignment = 'T' AND _
EXISTS (SELECT 1 FROM tblVehicles _
WHERE tblVehicles.Key = tblVehicleInfo.Key _
AND CurrentDisposition = 'Available'"

Substitute your key column names where I have put 'Key'.

 

[Paul B.]

John,

Thank you. your suggestion works great. A little slow as you said, but that
is okay. Just missed the closing ) at the end of the statement.

Cheers

You can only filter a form on columns that are in it's recordsource otherwise
Access does not know what you are referring to and asks for the value, so to
make this work you would need to add tblVehicles to the recordsource.

Having said that there is a filter that you could use without changing the
recordsource but you might find the performance unacceptable :-

DoCmd.OpenForm stDocName, , , "VehicleAssignment = 'T' AND _
EXISTS (SELECT 1 FROM tblVehicles _
WHERE tblVehicles.Key = tblVehicleInfo.Key _
AND CurrentDisposition = 'Available'"

Substitute your key column names where I have put 'Key'.

--
HTH
John

I have a form that I need to filter based on two fields on different tables.
I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.
I currently have the following code:
DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"
When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.
The form is bound to the tblVehicleInfo

 

[John Smith]

Sorry about the bracket, that's the trouble with air-code; it does not spot the
typos!

Glad it helped anyway
John

Thank you. your suggestion works great. A little slow as you said, but that
is okay. Just missed the closing ) at the end of the statement.

You can only filter a form on columns that are in it's recordsource otherwise
Access does not know what you are referring to and asks for the value, so to
make this work you would need to add tblVehicles to the recordsource.
Having said that there is a filter that you could use without changing the
recordsource but you might find the performance unacceptable :-
DoCmd.OpenForm stDocName, , , "VehicleAssignment = 'T' AND _
EXISTS (SELECT 1 FROM tblVehicles _
WHERE tblVehicles.Key = tblVehicleInfo.Key _
AND CurrentDisposition = 'Available'"
Substitute your key column names where I have put 'Key'.

I have a form that I need to filter based on two fields on different tables.
I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.
I currently have the following code:
DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"
When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.
The form is bound to the tblVehicleInfo